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. Vacuum uses a ring buffer so it does not evict the rest of shared_buffers
(Postgres's shared page cache), but on a table larger than RAM it still competes with user queries for the same disks.
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.
| Shape | Rows the formula sees | Default trigger (50 + 0.2 × rows) |
|---|---|---|
| One heap, 500 million rows | 500,000,000 | ~100,000,050 dead tuples |
| Same data as 12 monthly partitions | ~41.7 million each | ~8.3 million dead tuples per partition |
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. Vacuum freezes old tuples so XIDs can be reused; wraparound is the age of the oldest unfrozen XID (relfrozenxid
in pg_class
), not how many XIDs the database has ever issued. If that age reaches around 2.1 billion, 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.
Be aware that pg_squeeze
has no throttle. Squeezing a large table can saturate I/O and CPU and cause an outage. Only run it when the cluster has spare capacity.
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.
Note
If you're interested, we also have an article covering big tables with MySQL and Vitess.
Facts Only
* Large tables can be wide (many columns), have many rows, or contain oversized values ("fat").
* Cascade deletes on large, unpartitioned tables can cause timeouts and WAL saturation between primary and replicas, leading to replica lag.
* Vacuuming a single large table can tie up workers and compete with user queries for disk space when the table exceeds RAM.
* Postgres modifies how vacuum triggers based on table size (e.g., 20% of the table) and thresholds (e.g., autovacuum\vacuum\max\threshold).
* Vacuum runs per table, which can be slow to start and finish on very large tables.
* Dead tuples remain until vacuuming occurs, leading to data bloat unless explicitly removed.
* Slow operations against large tables cause connections to be held longer during sequential scans or heavy joins.
* Logical dumps include live rows, and file system backups copy all data, including bloat.
* Partitioning splits a table into smaller heaps, potentially reducing dead tuple counts for vacuuming per partition.
* Sharding distributes the large table load across separate database clusters, isolating I/O and transaction IDs.
* Wide tables with oversized values may use TOAST, which consumes OID space, and file walks can pin cores via functions like pg\database\size().
Executive Summary
Large tables in databases can cause performance issues stemming from data management and operational procedures, particularly when operations like cascade deletes occur on unpartitioned, large tables. A large table can be wide (many columns) or fat (oversized values), which exacerbates problems related to vacuuming, index management, and I/O contention. Deletions on such tables can generate significant Write-Ahead Log activity that impacts replicas, leading to synchronization lag and potential outages if read operations are directed to lagging replicas.
Solutions involve several strategies: partitioning splits the large table into smaller, more manageable chunks, which improves vacuuming efficiency and reduces autovacuum overhead. Vertical scaling increases resources but does not inherently solve architectural inefficiencies. Sharding horizontally scales the data across multiple database clusters, isolating workloads, distributing I/O contention, and mitigating cluster-wide limits related to transaction IDs and vacuum processes.
The complexity of large tables involves trade-offs across these solutions. Partitioning addresses structure; vertical scaling addresses resource capacity; and sharding addresses systemic workload distribution. The choice depends on the specific bottlenecks encountered, as each strategy manages different facets of the performance problem.
Full Take
The narrative pivots on the systemic failure introduced when a single monolithic data structure overwhelms the operational boundaries of a relational database. The underlying pattern is that physical scale (size/width) interacts negatively with logical operations (deletes, vacuuming, indexing). When this interaction triggers cascading failures—like replication lag during high-volume writes or extended query hold times—the architectural limits of the single-cluster model are exposed.
The proposed solutions demonstrate a move toward spatial and structural decomposition as the necessary remedy for scale-related problems: partitioning addresses internal organizational structure; vertical scaling addresses raw throughput limitations; and sharding addresses systemic isolation. The mechanism of sharding proves most potent because it resolves constraints imposed by Postgres' internal design, such as the 32-bit XID limits and cluster-wide vacuum behavior, by establishing independent boundaries where these limitations become shard-local concerns.
The implication is that performance bottlenecks are often not merely resource deficits but structural incompatibilities between data volume and transactional coherence. The tension lies in the trade-off: partitioning offers local optimization at the expense of global consistency during operations like backups, while sharding achieves maximum isolation but introduces complexity in managing cross-shard data topologies. Cognitive sovereignty requires recognizing that physical scale demands architectural separation to manage dynamic operational realities, rather than simply adding more computational horsepower or optimizing internal maintenance routines for a single structure.
Bridge Questions: If sharding resolves transaction ID constraints per shard, what are the inherent complexities introduced when queries must span multiple shards? How does the overhead of managing independent vacuum processes across many smaller heaps compare to the centralized management in a single system when data locality is not guaranteed? What metrics should be prioritized to determine whether partitioning or sharding is the appropriate initial move for any given large-table scenario?
Sentinel — Human
This text presents a detailed, expert-level argument about managing large tables in Postgres by systematically evaluating technical solutions like partitioning and sharding against performance bottlenecks.