Problems with large tables in Postgres

Tell me to stop when I name the largest table in your database: logs, ledger, notifications, feed, events, chonk ... admit it, I got it, right?

Product success leads to lots of data, lots of data leads to big tables, but big tables lead to predictable problems.

A table can be large (many rows), wide (many columns), or "fat" (oversized values). Any of these can cause you problems.

How a large table can cause an outage

First, let me tell you a story.

A customer had two tables, A and B. Table B is large and unpartitioned. It has a foreign key to A with cascade delete turned on. A single delete on A has to delete every child row in B. That can be 100s or 1000s of rows in B for every one in A.

In this customer's case, deletions from A ran in a background job and attempted to remove around 100,000 rows from A. The deletes took so long that they timed out. The job retried. Each attempt generated a pile of WAL (the write-ahead log Postgres uses to record every change), which saturated the network and CPU between the primary and its replicas. This caused the replicas to lag.

The application checked replica LSN (a replica's position in that WAL stream) before sending a read to a secondary. The secondaries were not caught up, so every query went to the primary.

The primary was already busy writing WAL. It also had to respond to all of the reads. And so a simple cascade delete on a large table quickly became an outage.

Possible fixes for a large table

The problems covered in this post are common to all Postgres databases but are made worse by those containing large tables. You might reach for one of a few solutions, which we'll examine throughout.

Partitioning splits a large table based on a key value. For example, if your large table stores rows with a specific date, such as when an event occurred, cutting that table into partitions of months or years creates more, smaller, and more manageable tables.

This can be useful for some large-table problems.

Vertical scaling refers to adding more resources to make more space or brute-forcing your way out of performance bottlenecks such as increasing CPU, RAM, or buying larger and faster disks.

It's almost always a band-aid solution that only hides the real problem.

Sharding (horizontal scaling) splits the large table, or the entire database, into separate database clusters connected by a router to act as one. Just as Vitess is sharded MySQL, Neki is sharded Postgres.

With more, smaller, and isolated database clusters, almost all of your expected large table problems are resolved. More on that later in the post.

Slow, late vacuum

You may already know that deleted rows in Postgres aren't automatically removed; they are marked for deletion. These marked rows hold space until the vacuum process reclaims it. Read Every UPDATE leaves a ghost to learn more.

What you might not realize is that vacuum runs per table.

Vacuuming a single large table can tie up a worker for a long time. On a table larger than RAM, vacuum competes with user queries for the same disks and shared_buffers (Postgres's shared page cache).

Large tables take longer to vacuum, and they also wait longer to start.

On Postgres 17, the default trigger is 20% of the table. A table with ~500 million rows needs ~100 million dead tuples before autovacuum begins. You can modify this value cluster-wide or per table by changing autovacuum_vacuum_scale_factor.

Postgres 18 added autovacuum_vacuum_max_threshold with a default value of 100,000,000. So a billion-row table triggered autovacuum at 100 million (still a high number) instead of 200 million. Below 500 million rows, the old scale factor was still in play.

Postgres 19 adds parallel autovacuum, off by default. Extra workers can finish one large table faster, but that still doesn't change when vacuum starts.

Autovacuum also runs ANALYZE, which has the same default-doesn't-scale problem. It samples a fixed number of rows no matter how large the table is. So the planner's estimates potentially get worse as the table grows. Raise the target with ALTER TABLE ... SET STATISTICS on the columns the planner actually needs to get right.

Vacuum on large tables is late to start, slow to finish, with more dead rows while it runs. Each cycle is worse than the last, even when vacuum reports success.

Partitioning can be a good first move to split your large table into more reasonable-size chunks, if it has a suitable key.

Partitioning also helps avoid autovacuum's late start. Each partition is its own smaller heap, so the 20% trigger fires after far fewer dead tuples. Workers vacuum partitions in parallel and ANALYZE samples a smaller relation.

Disks and shared_buffers are still shared with the rest of the cluster, so it's not a perfect solution.

Vertical scaling can help more workers finish sooner with faster disks. That does not change the trigger on one huge heap, and the table may still not fit in RAM.

Sharding (horizontal scaling) gives you the best of both worlds. Smaller tables on smaller databases, each with their own isolated resources.

Sharding also solves foundational parts of Postgres that can't be resolved by more resources.

Every write transaction gets a 32-bit ID, and vacuum uses these IDs (xmin) to decide which row versions are still visible. These IDs are cluster-wide, not per-table, and if your database contains around 2.1 billion of them, Postgres goes into read-only mode. Postgres backups under the hood covers transaction wraparound in more detail.

Since each shard is its own cluster, a large table separated across shards is less likely to put a cluster into read-only mode.

Additionally with isolated I/O, any work vacuum does on one shard does not contend with queries on another. ANALYZE becomes a per-shard operation.

Wasted time on incomplete repacking

Vacuum can be prevented from reclaiming space even if it finishes successfully.

Vacuum can't remove a dead tuple while any transaction in the cluster still needs it.

But a slow vacuum on a large table may have wasted its time to completion. Many of the rows it found to remove may still be required by transactions that started before vacuum finished and haven't completed yet. Vacuum will need to walk the entire table again later.

This can be an issue on tables of any size, but is more annoying on large tables as they take longer to complete.

Free space that was successfully reclaimed can be reused for new rows, but the table file usually does not shrink. That leftover file size is bloat.

Should your table get bloated, PlanetScale Insights scans once a day and opens a recommendation when estimated bloat is over 25% and 100MB.

To compact a bloated table while reads and writes continue, enable pg_squeeze on the database's Clusters page (that requires a restart), then run a one-time squeeze or register the table for regular cleanup.

Partitioning creates more, smaller heaps. So a wasted pass is cheaper and can be retried sooner. Squeeze is per partition, so the spare disk you need to run squeeze is the size of a partition, not the whole table.

Splitting the table does not split the snapshot horizon. xmin is still cluster-wide, so a long query anywhere on the cluster still pins every partition.

You can drop old or bloated partitions instead of compacting them.

Vertical scaling adds more spare disk space, which may be the only reason squeeze can run at all and/or finish sooner. That lets you compact the same large file faster.

Sharding creates distinct Postgres database clusters, so a long query or dump on one shard doesn't pin vacuum on any other. Squeeze runs only on the copy in its shard, not the entire large table. Each cluster tracks its own xmin.

In a sharded database, wasted vacuum passes are cheaper and less likely to happen.

Slow queries hold connections longer

It is impossible to write a Postgres article about performance and not mention that Postgres has a connection-per-process architecture. Connection hygiene matters even more when your database has a large table.

Sequential scans (reading large segments of the table from start to finish), big sorts, and heavy joins against a large table take longer, so they hold connections longer.

Raising max_connections feels like a logical solution to avoid "too many clients already," but this only lets more concurrent connections perform these slow queries. At best, this starves other workloads of new connections. At worst, a traffic spike on your large table is more likely to trigger an out-of-memory (OOM) event.

A large table will not stay in shared_buffers. More processes scanning it means more cache eviction, more disk usage, slower queries, and longer-held connections.

Partitioning splits your large table, which can help queries finish faster (and release connections sooner) so long as they access only one or a few partitions. But cross-partition queries can get worse.

Buy all the RAM and CPU you like with vertical scaling, but you're still not putting a hundreds-of-GB table into cache. You cannot spend your way out of inefficient connection handling.

Sharding spreads your backends across multiple clusters, where each shard-distinct query is shorter and more likely cached.

A connection held by a large table is one the rest of the product cannot use. With workloads spread across shards there may be less overlap.

A scatter-gather query with no shard key can hold more connections, not fewer. Selecting the right key to shard on is critical to seeing performance benefits from sharding.

Slower backups and recovery

Postgres gives you three ways to take a backup. A large table complicates each one in a unique way.

A logical dump with pg_dump writes live rows, so dead tuples stay out of the output. The dump still walks the bloated heap, and it holds a transaction snapshot the whole time. On a small table, that pin is brief. On a large table, the backup is a long-running transaction against the cluster.

A file system backup copies the data directory, bloat and all. Faster than dumping rows. A consistent copy usually means shutting the database down, or relying on atomic snapshots from the file system. You might be okay with a maintenance window for a database of small tables, but a single large table could add hours just to copy one relation.

Continuous archiving copies the data files while Postgres is still running, then keeps the WAL so you can replay through the modified bits and land on a consistent copy. No dump pin, no shutdown. You still copy the large table, bloat, and its indexes. What grows with the table is restore time. How long an incident waits on this heap plus the WAL written while the copy ran.

That third option is how PlanetScale backs up Postgres. We copy the data files and continuously archive WAL on a throwaway node rather than on your primary, so the backup doesn't compete with production queries for disk and doesn't pin the primary.

We can't skip the size of those files. A large table that doesn't fit in memory, plus its indexes, plus its bloat, is what has to land in object storage.

Partitioning rearranges files but doesn't shrink the copy. A pin held during dump is still cluster-wide. A physical backup includes the same bloat even if it is split across smaller tables.

Vertical scaling means faster copies through more resources. But the files aren't any smaller, so restore is only quicker because the disks are faster.

But neither partitioning nor vertical scaling changes the size of the data.

Sharding breaks down backups and restores into smaller, distinct units of work, allowing them to complete much faster. Recovery time depends on the slowest shard instead of the largest table.

Too many indexes

A large table isn't viable to scan from start to finish and is likely queried in many different ways.

To keep queries fast, you keep adding indexes to the table. While indexes make queries fast, they aren't free. They take disk, get vacuumed, get backed up, and make every write touch more files.

There are no good options here. Prune indexes and your queries get slower. Accumulate indexes and pay for it with an enlarged heap, while also vacuuming, packing, and copying many access paths.

The indexes that make a large table usable are part of what makes it too large to keep indexing.

Indexes on a partitioned table are created on the logical table but stored and updated on each partition. Those smaller indexes are faster to build and cheaper to REINDEX.

Vertical scaling buys you more space to store more indexes ... but that's like fixing traffic congestion by adding another lane to the freeway.

Sharding creates more, smaller databases, so having more indexes is less of a problem. You can afford to create many more indexes than if your large table was on a single cluster since a write only updates indexes on the shard that owns that row. Sharding is the good option for balancing a lot of data and many indexes.

Wide tables split across pages and files

A table is not only large because it has many rows; it might be that the rows are wide with many columns, or each row contains a large amount of data.

If a row exceeds ~2KB, oversized values go to TOAST, a separate table and indexes next to the heap you are already struggling to vacuum.

Each toasted value gets a 32-bit OID, about 4 billion per table. Updates of toasted values take a new OID. High-churn wide tables fill that space, and inserts slow down as Postgres hunts for a free one before the hard stop.

Whatever is left still has to fit into a single 8KB page, as Postgres will not split a row across pages. If a tuple cannot fit in the free space on any existing page, it gets a new page.

For a read-heavy table, you want pages packed tight (a high fillfactor), so scans walk as few pages as possible. For an update-heavy table, you want the opposite. Lower the fillfactor so an update can often stay on the same page instead of allocating a new one.

On a small table, the wrong fillfactor is a few wasted pages. On a large, wide table it could be terabytes of sparse pages, or a constant stream of new ones that vacuum, WAL, and backups all have to follow.

A large table is a heap, plus indexes, plus TOAST, and some operations still walk those files one at a time. pg_database_size() is one example. It stats every file in the database serially. On a large, wide table, that can pin a core.

Partitioning is a genuinely good solution for this class of large-table problems. Fillfactor can differ per-partition. Each partition gets its own TOAST table, so OID space is per slice.

However, with more files in one cluster, pg_database_size() can get worse.

Vertical scaling can hide a bad fillfactor for a while with extra disk space. Faster disks make TOAST vacuum and file stat()s cheaper. But you cannot buy greater limits than 8KB pages, the 2KB TOAST threshold, or the 4 billion OIDs.

Sharding creates individual Postgres instances, so TOAST OID space resets per cluster. A bad fillfactor hurts less because the heap is smaller. File walks are per shard.

Sharding does not, by itself, let hot and cold rows use different fillfactors. Partitioning still wins there.

Sharding is the solution to large tables

Just as partitioning breaks up a single table into many small pieces, sharding distributes the workload of your reads and writes across many database clusters.

Let's revisit the cascade delete story.

When replicas lagged, every read went to the primary. On a sharded database, each shard is its own cluster, with its own WAL and replicas. If the affected rows of A and B lived on one shard, that shard could still lag, and its reads pile onto its primary, but that could not fail the LSN check for the rest of the product. If those 100,000 deletes were spread across shards, the WAL burst would be split. No replica stream would have to replay the whole thing.

Your single-cluster large table likely processes a high throughput of traffic. Vertical scaling can help, but sharding does it better. With more clusters receiving and processing writes, a single table doesn't strain the entire cluster.

Additionally, cluster limits that cannot be solved by partitioning or vertical scaling are resolved. Postgres' hard-coded ceilings of 32-bit XIDs, TOAST OID and more become per-shard limits instead.

Sharded database backups are individually smaller and can be massively parallelized, making database restoration faster as well. Operations finish at the time of the slowest shard, not all at once waiting on large, bloated tables.

Cache eviction is a problem with large tables and their working sets. While more resources won't fit a large table into RAM, you often can per shard. Shard-local queries become shorter and faster.

Neki, sharded Postgres, lets your application write queries as if it were a single database, while splitting a large table into smaller, automatically distributed parts.

It contains the logic for where to send reads and writes. You still have to determine how that data is spread. That fan-out is covered in our post on data topologies.

Sharding shares the large table load across multiple clusters. If that's a problem you need solved, request access to Neki.