Scaling Postgres Beyond One Billion Rows

At LeverLoop, Postgres is our primary datastore. It holds contacts, deals, emails, activity logs, and search indexes for every workspace. By late 2025, our largest tables crossed one billion rows — and query performance was starting to degrade.

The symptoms were familiar: p99 latencies climbing from 8ms to 45ms, vacuum operations running for hours, and index bloat consuming 3x the space of the actual data. We needed a strategy that would buy us at least 10x headroom without a full re-architecture.

We evaluated three partitioning strategies: hash, list, and range. Range partitioning by workspace_id won because nearly every query in our application is scoped to a single workspace, which means Postgres can prune partitions at plan time and only touch the relevant subset of data.

• Hash partitioning distributes data evenly but doesn't help with workspace-scoped queries.
• List partitioning gives precise control but requires manual partition management as workspaces grow.
• Range partitioning balances even distribution with automatic pruning and simple operational management.

After migrating our three largest tables to 40 range partitions, p99 query times dropped from 45ms back to under 10ms — even as data continued to grow.

Partitioning solved the scan problem, but we still had index bloat and connection pressure. Two changes made the biggest impact:

Partial indexes on hot columns — like WHERE status = 'active' — reduced total index size by 60%. Since most queries filter on active records, the partial indexes serve the same queries with a fraction of the storage and maintenance cost.

PgBouncer in transaction mode became essential once we passed 500 concurrent connections. Without it, Postgres spent more time managing connections than executing queries. Transaction-mode pooling reduced active backend connections from 800+ to under 100, freeing resources for actual work.

Schema changes on large tables are where most teams hit operational pain. A naive ALTER TABLE can lock a billion-row table for hours. We use a combination of pg_repack for table rewrites and careful lock management for additive changes.

1. Additive changes (new columns, new indexes) use CREATE INDEX CONCURRENTLY and ALTER TABLE ... ADD COLUMN with defaults — both non-blocking in Postgres 11+.
2. Type changes and rewrites use pg_repack, which rebuilds the table in the background and swaps it atomically.
3. Destructive changes (dropping columns) are deferred to maintenance windows with explicit lock timeout guards.

We haven't had a migration-related outage in 14 months using this approach.

Staging environments lie. They have different data distributions, different table statistics, and different connection patterns. Three of our worst performance regressions were invisible in staging but immediately apparent in production query plans.

We now run pg_stat_statements with custom alerting on:

• Any query whose mean execution time increases by more than 2x week-over-week.
• Any query that triggers a sequential scan on a table with more than 1M rows.
• Any new query pattern (not seen in the previous 7 days) that exceeds 50ms.

This monitoring has caught regressions from ORM-generated queries, unexpected index drops, and autovacuum configuration drift — all before users noticed.