Postgres at 10 TB: What Breaks First, and in What Order

What actually breaks first as Postgres grows?

Connections, before anything else. Then autovacuum falls behind on your highest-churn table. Then index bloat quietly doubles your working set until it stops fitting in cache. Raw data volume is usually the last thing to hurt you, which is why teams that plan for "scale" by planning for size get surprised by an outage at 400 GB.

The sequence below is the one we have watched play out repeatedly on transactional Postgres systems.

Stage 1. Connection exhaustion

Every Postgres connection is a separate OS process holding its own work memory. A few hundred idle application connections will consume gigabytes and saturate the scheduler doing nothing at all. Serverless functions and container autoscaling make this dramatically worse, because each new instance opens its own pool.

The fix is a pooler, not a bigger max_connections.

; pgbouncer.ini, transaction pooling
[databases]
app = host=primary.internal port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 40
server_idle_timeout = 120

pool_mode = transaction is what buys the ratio, a connection returns to the pool at commit rather than at disconnect, so 5,000 clients share 40 real backends. The constraint you inherit is that session-scoped state stops working: SET outside a transaction, session advisory locks, and LISTEN/NOTIFY will not behave. Audit for those before switching, not after.

Stage 2. Autovacuum falls behind

Postgres marks deleted and updated rows dead rather than removing them, and autovacuum reclaims them later. On a table taking heavy updates, the default settings, vacuum at 20% dead tuples, mean a 500M-row table waits for 100M dead rows before anything happens. By then the vacuum is long, disruptive, and competing with your traffic.

Tune the hot tables individually. Global settings are the wrong granularity:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 1%, not 20%
  autovacuum_vacuum_cost_limit = 2000,     -- let it actually keep up
  autovacuum_analyze_scale_factor = 0.02
);

Watch n_dead_tup in pg_stat_user_tables and alert when it trends up between vacuums. A dead-tuple count that never comes back down is a vacuum that is losing, and that is the leading indicator of the outage six weeks out.

Stage 3. Partition the tables that only grow

Time-series tables, events, logs, ledger entries, audit trails, get partitioned by range. The wins compound:

• Index maintenance stays proportional to the active partition, not the table
• Dropping old data becomes DETACH PARTITION, which is instant, instead of a DELETE that generates hundreds of millions of dead tuples
• The planner prunes irrelevant partitions from the scan entirely

CREATE TABLE events (
  id          bigserial,
  tenant_id   bigint      NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload     jsonb       NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Two things to get right up front. The partition key must appear in your WHERE clauses or you gain nothing and lose the ability to have a simple global unique constraint. And partition creation must be automated, pg_partman or a scheduled job, because the failure mode of a missing future partition is writes erroring at midnight.

Stage 4. Bloat and the index you forgot

Indexes bloat as pages fragment under updates. A 40 GB index doing the work of a 12 GB one is not just disk: it is cache you no longer have. REINDEX CONCURRENTLY rebuilds without an exclusive lock and is safe to schedule.

While you are in there, drop what nobody uses. pg_stat_user_indexes will tell you which indexes have never been scanned; on most mature schemas that is a meaningful fraction of them, and every one is write amplification you are paying for on every insert.

What we would not do

Shard. Not until partitioning, pooling, and moving analytical reads to a replica have all been done and measured. Sharding is a permanent tax on every query, migration, and backup you will ever run, and most systems that reach for it still have an unpartitioned 2 TB table and no pooler.

References

• PostgreSQL. Table Partitioning.
• PostgreSQL. Routine Vacuuming.
• PgBouncer configuration reference.
• pg_partman, for automated partition management.