Maintaining Large Tables in PostgreSQL | POSETTE: An Event for Postgres 2026

Key Takeaways

Hello, my name is Balaji A Palli. My talk is about maintaining large tables in Postgres SQL. I'll be discussing about various performance challenges associated with maintaining large tables in Postgres database along with recommendations or solutions to have a consistent performance. The talk basically targets database developers and database administrators. Okay, let's get started and do a deep dive on maintaining large tables in Postgres SQL. So, why large tables matter? Scaling data to challenges. As applications grow, data accumulates and large tables become inevitable. So, what really happens? More and more users start using the application. The data growth happens. More More and more rights, more and more updates, more and more deletes happen on the on the database. As a result, table size grows. What started with thousands of records sometimes goes up to billions of records. So, internally on the Postgres side, more and more pages gets added. The index sizes get larger. So, this leads to some hidden performance risks. Large tables may might appear stable, but gradually if they are not maintained, we will see slowness in the queries, increased maintenance overhead, and unpredictable performance. So, the key insight here is performance doesn't break suddenly. It degrades silently over time. So, in this talk, in the next few slides, I'm going to talk about seven different aspects where things can go wrong and how what are the recommendations one can follow to make sure that the performance is maintained all the time as the table size grows. So, the first one that we are going to talk about is auto vacuum table and index bloat explosion. So, the table size grows, there are more frequent updates and deletes on the database. The auto vacuum cannot keep it up with the workload. The workload becomes more aggressive. More and more users start using the application. Then that tuples start accumulating once the auto vacuum doesn't keep up with the workload. The table size grows far beyond the active data. The indexes also degrade. They also get bloated with more and more data being added, more and more data being updated and deleted. So, the storage expands silently. We won't see any immediate failure, but the there is there would be bloat on the table. So, what is the impact of this? So, more pages, more disk reads, more inefficient lookups, more data uh more data doubles in index entries. This will lead to slowness in the queries, randomly randomly IO increases, read latency increases, the cache hit ratio drops. So, this is one of the things we get into once auto vacuum cannot keep keep up with the workload. The second thing that we could get into is caching inefficiency. So, buffer cache trashing will happen. So, working set basically exceeds the memory. And as in as in when new queries come and hit the database, hot pages gets continuously evicted. Queries repeatedly hit the disk instead of cache. So, the system behavior, the way it happens is cache reuse reduces, cache hit ratio drops, system becomes more IO bound. So, there again, the impact would be the IO latencies increase, even though there are shared buffers, since the uh pages are getting evicted continuously, shared buffer buffers become ineffective. The throughput plateaus. The server might have enough CPU resources, but given the workload becomes more I/O bound, the throughput plateaus to a point that whatever we do, it doesn't scale. The workload doesn't scale uh accordingly. The third thing that we will get into is vacuum freeze storms. Uh so, before explaining about vacuum freeze storms, I would like to explain about a post with parameter autovacuum_freeze_max_age. Basically, it's the max allowed age of transaction IDs uh before Postgres forces a vacuum to prevent wraparound. So, this this particular parameter is set to 200 million by default. So, whenever a table age is equal to 200 million or greater than 200 million, what uh Postgres does is it uh triggers anti-wraparound vacuums. So, when can this happen? There are two specific scenarios we can think of. One is in write-intensive workloads, table age grows or increases faster. Uh the vacuums are not able to keep up with the uh workload. That is one situation where we will see the age of the transaction hitting 100 million pretty often. The second scenario is a table is like very much insert-only workload. So, the vacuums never kick in because vacuums can only kick in when there is an num- there are dead tuples getting created on the server. So, when the value hits near the 200 million, again, there's a very high chance that anti-wraparound vacuums get kickstarted in the uh kickstarted by post-rest. So, why are anti-wraparound vacuums intensive? Because they use uh high heavy IO. Uh the CPU also spike. Basically, they are more uh they consume more server resources. So, the background maintenance dominates the workload rather than the actual workload. So, these anti-wraparound vacuums uh do the full table scans uh looking at each page. The freezes the table to tuples and also visibility map is rebuilt. So, the effect of this is we might see query latencies becoming unpredictable uh if it is not taken care of. The next one that we we could get into um when it comes to uh maintaining of large tables is that valve volume uh explosion. So, right heavy workloads we will see uh millions of inserts, updates, or bulk deletes happening on the server. So, valve generation rate uh increases. So, if it increases to a point where it it starts impacting disk throughput or replica apply capacity, basically replica is not able to sustain the workload. Then we start seeing various IO related or disk related uh constraints on the server. So, we could see checkpoints trigger too often. So, checkpoints move from being time to requested. And uh they will they would be considerable IO spikes whenever the checkpoint is trying to uh flush the dirty buffers. And after the checkpoints, we would see full page writes amplify. Uh so, that's another thing that we would see. Again, depending on the nature of the workload, uh we could see something like LW locks related to valve uh valve writes, which would uh show the contention around commit latencies. So, basically, the system shifts from random data IO to sequential WAL dominated IO pattern. Uh which could create a lot of performance issues. And the adding to that, if the replica is not able to sustain the workload, then replication lag the replica falls behind. Lag increases. And this lot starts retaining a lot of WAL in it. Which could lead to more disk usage and disk full issues also could come up, because it's not able to consume as fast as possible, um leading to storage related disk full issues also. So, what is the impact of this? So, the impact uh write throughput spikes, sustained high WAL generation rate, commit latency, as we discussed, if sync and WAL flush becomes a bottleneck, storage IO saturation, sequential WAL at checkpoints, write block collide, replication lag grows, standby cannot replay WAL fast enough, buffer cache basically becomes uh uh drops, more writes, less useful caching. Checkpoint pressure amplifies spikes, burst IO during uh flush cycle. So, basically, the checkpoints becomes from time to requested. Uh that becomes more bursty, and IO becomes a bottleneck. Next one uh that we could see on very last table says a lock contention amplification. So, this could happen in two different scenarios. One, long-running transactions, which are like batch updates, or ETL jobs, which could be having long-running transactions, which are holding some hot rows updates, indexes on on same tables for very long time. And the second scenario is high concurrency again on same rows, indexes, and tables. So, this leads to some poor access patterns. So, what could happen is we have a transaction or multiple uh sessions are trying to use the same row. So, the row gets locked. Then, there are more sessions that are blocked because they are they want to access the same row that has been blocked by another session. Leading to cascading blocking chain. So, this leads to system-wide latency spikes. So, end users start seeing end-to-end application latencies to increase. So, which is not only related to database, but even the application layer the uh sessions start piling up. So, even the fast queries get stuck behind the slow uh slower ones or the the sessions that got locked. So, this is another performance implication we have. The next one is statistics staleness. So, as the data volume increases, uh data distribution changes. Uh the analyzed samples becomes too unrepresentative. So, we could have histograms MCV drift. Uh cardinality estimates become inaccurate. So, the impact of this is planner uses incorrect row estimates. Misestimates prop uh mis- misestimates propagate across the joints. Optimizer basically picks an inefficient plan. So, ultimately, this leads to slower queries, spike in uh uh spike in uh performance uh performance-related issues can crop up because of uh uh statistics staleness. The next one we could get into is uh hot update failure. So, what is in a hot update failure? So, it is nothing but heap-only tuple. So, in Postgres, the MVCC architecture is such that every update uh Uh a the existing row is and a new row version is added. The new row version is added into the same page. The index uh entry that points to the page where the row is to the associated row, it doesn't have to change. But if the if the row That that that is known as hip leap uh to hip leap tuple or hip leap tuple update. But if there is no space for the new row version to be added in the page, then the new row version gets moved to a new page. Suppose this for is forced to allocate to a new page location. What this would lead is uh the index entry has to point to the new page where to the new row version. So we'll see cross page plus index churn. Uh so that would lead to more well page splits. So the impact of this is well generation increases. More index page splits can happen. Right amplification spikes we could see. Increased IO pressure. Again, cache churn. More pages are being touched per per update, so more and more uh more more and more pages are getting evicted or being used. Latency increases under right heavy workloads. So these are some of the impacts that one could see in uh hot update uh failure kind of a scenario. So what are the different performance optimizations we could do? So first thing and foremost we should try is that partitioning of table. So having a table with one single table with say 500 GB or 1 TB is generally not recommended because of all the maintenance issues and the various performance challenges we have talked about. So best thing we could do is if it is possible partition the table. So time based partition or key based partition we could do it. The advantage of that is uh the it keeps the working set smaller per query. Uh we can partition according to the needs of the application also. For example, uh we need only 1 month's worth of data and all the older data uh in uh say last 6 months data or 1 year data it the application doesn't need. So, we may have one active partition and all the other data in other partition. That keeps the active set uh uh into a smaller uh smaller size compared to the non-used set. So, and then we could think about having archival solutions. Basically, maintenance becomes very easy when we start partitioning the part depending on the application needs. So, it makes a vacuum effective uh maintenance easy. The index sizes don't grow to a point that it is bigger than the RAM size of the uh skew. The lock scope also is controlled. Um uh the queries become more effective because uh once we have a partition table, we could uh start seeing partition uh write queries in such a way that the partition pruning is happening. And only the data from that uh active partition from where uh it is needed is picked up. So, there are a few ways we could monitor it once we partition a table. Say, the table size growth of the partition can be looked into. We briefly talked about partition pruning. So, it is very important when we change from the main table to a partition table that the application changes the queries in such a way that all the queries use the part only the partitions uh that they had need. So, the partition pruning is happening. Otherwise, again, we will see the same issue that it the database the query starts looking into all the partitions to retrieve the data, which may not be ideal. We could also look into number of sequential scans, number of index scans that are happening for partition and then try to see what best and how to improve the performance by adding indexes. So, these are some of the tricks and what we could do with the partitioning tables. The second thing that we could and we should definitely look into is tuning of auto vacuum. So, there are four things here that we could do when it comes to auto vacuum. One is vacuum parameters. Second one is tuning at the table level. Third one is having the vacuuming done at uh manually. Fourth one is uh changing the auto vacuum max freeze age if that is that can be doable. So, let's talk about the parameters that we could change. So, in if the database schema is such that you have multiple uh sir uh dead sir tables with uh large tables with large sizes, say 100 GB, 200 GB, 400 GB. What we could do is uh depending on the workload, you could we could think of changing the parameters like auto vacuum scale factor, auto vacuum analyze factor from default 20% to 10% or 5% to make sure they with the end goal being that these tables are picked more often and the vacuums are happening more frequently on these tables. We could also consider increasing the cost limit, auto vacuum cost limit. Uh but it depends on the I/O that is being used uh on the server. If the server has enough bandwidth and uh I/O on the uh on it, then definitely we could consider increasing the cost limit. Uh make sure that uh the vacuums run on the tables faster. The second one that we talked about is uh tuning per table settings. So, in scenarios where you have a uh skewed uh database schema, say you have one table with 1 TB and 100 tables with 1 GB. So, in those kind of a scenarios, it might be a good option to tune only the table that is really big, that's 7 terabyte table. Uh the tuning again can be done at the table level. The same parameters can be tuned. Auto vacuum auto auto vacuum vacuum scale factor auto vacuum analyze scale factor auto vacuum cost limit and we could also consider something like using something like threshold parameter also. So, what you could do is like we could look into uh how many dead tuples are getting generated say every 2 days or 3 days. Understand the threshold, put the threshold as if in the table level setting. That would automatically trigger the auto vacuum on the table with that many after that many amount of dead tuples are created. So, that's another second thing that we could do. The third thing that we could do is like having schedule of a regular vacuum manual vacuum through a cron job. Uh that essentially cleans the database table large table periodically. Uh we could take a maintenance window say once in a week, once in 15 days or out of business hours and run these vacuums uh manual vacuums frequently. In scenarios where the vacuum table bloat is too high, as a one-off case we could consider something like PG re-pack to compact the table and then tune all the parameters that we have discussed and make sure that the table don't get bloat to a point that it starts impacting the queries. And the last one that we spoke of is the auto vacuum vacuum freeze age. So, in a very aggressive workload kind of a database what we could do is probably increase the freeze max age from 200 million default to 300 million or 400 million and tune these auto vacuum parameters to give ourselves arms so that the aggressive anti-wrapper on vacuums don't kick in and start consuming the uh server resources. So, how do we maintain look and monitor the vacuums? Again, ended to pull count. We should periodically keep looking into these uh table sizes, index sizes we could we should look. Uh and again, the most important thing is on all the last tables how often the vacuum is happening, how long the vacuum is running, when was the last vacuum that has that ran, and uh uh these kind of things where we could definitely look into and monitor it. So, PC stat user tables and PC stat all tables is one place we could definitely go and keep them keep keep monitoring on how the vacuums are not a vacuum is behaving on the server. The next two that we could do is preserving of the hot updates and controlling the wall pressure. So, preserving of the hot updates there is a a table level setting that we could do, the fill factor. So, in a very update heavy tables, we could make the fill factor to something like 80%. So, what essentially that would give is uh 80% of the rows are we will still have some space for the new rows to be inserted. Uh so, the idea here is with the fill factor at 80 the new versions of the rows when they get created, they don't get moved to the another page because there is no space left in the current page, but there would be enough there there would be space for the row to be inserted into the same page. So, that would preserve the heap only tuple update. And also avoid unnecessary index columns in the updates, so that also reduces the hot updates and creates this wall generation more wall generation. So, how could we monitor? There are a couple of couple of columns in PG stat user tables and our PG stat PG stat all tables. The first one is into hot update and the other one is into updated. So, we could take a ratio and keep monitoring it over a period of time to make sure that we have enough hot updates happening. So, controlling the valve pressure, I think this is something very important and given the nature of the workload and the size of the database. Uh sometimes where we have workloads where we have an insert update kind of a insert and then immediately an update. That means we are doing millions of inserts and then committing uh committing also very often and updates are also single single updates. We could think of doing something like uh uh batching those rights. So, that would reduce the commit latency on the database. The second thing is definitely tuning of the checkpoints. Uh we need to understand the wall generation rate. Tune the max wall size. It is always recommended to have checkpoints timed rather than rather than having requested. More checkpoints requested leads to I work a lot of I work pressure and other system complications like disk scratch edition, disk bandwidth consumption, disk apps consumption. So, we could tune the checkpoints. So, how do we monitor it? So, checkpoint frequency like what we have been talking about. Checkpoint duration also we could look into how often the checkpoint is happening. So, in the older versions we have PG stat BG writer. In the newer versions we have PG checkpointer table that can that we can look into. Again, for commit latencies we could look into some of the weight events like wall right, wall wall insert, wall sync. Uh how how many we are getting whether we could not tune something around batching up the large rights. The last two performance optimizations that we could then think of is reducing the lock condition and keeping the statistics fresh. So, briefly we touched upon the lock contention, how it can create the blocking chains locking chains. So, what we could do is like break these large transactions into smaller batches. Add indexes where they are missing so that it it gives a reduces the locking scope. Avoid idle transactions, have timeouts in space in place like idle in session transaction timeout. Also, it is advised that in a highly concurrent environment to see whether PG advisory locks can be used from the application level so that we don't get into a situation where we are seeing a lock chain being formed on the database and that is impacting the performance of the database. So, how do we monitor we could we could monitor PG stat activity, look for wait event type of lock and then see if there is any any a session that is that is taking that is locking other sessions and that is blocking the other sessions. And also, we could see the connection pool saturation. If we have a connection pool that is being used, so one of the things that could be there is that these locks are preventing the sessions to complete and there are sessions that are just waiting for one of these sessions and they're waiting on each other. So, that's another symptom basically that can cause an issue so that we could monitor. The last one is keep statistics fresh. So, we discussed about how as the data increases the analyze might lose relevance because Uh, have data with a large volume of data that it it's not able to basically it's not able to estimate correctly. So, what we could do is change the default statistics target. Increase it for this skewed columns are the are the columns where we feel optimizer is not estimated properly. That would give more rows that would give more rows for an optimizer to analyze and such give better estimates. And also we could run analyze more frequently on large tables. So, we talked about the maintenance maintaining the a manual vacuum periodically. Maybe we could also add manual vacuum analyze so that these tables are vacuumed and analyzed more frequently. And from these other parameter perspective, we could change the auto vacuum analyze scale factor so that for large tables these are picked up more often than 10% which is the post grace default. So, how do we monitor plan regressions? Same query different plan start we start seeing it. Row estimates goes off the estimated row and actual estimate there is a mismatch when we see the explain analyze. Then the workload doesn't start doesn't change basically, but we start seeing there is sudden latency spikes. So, all this can lead to the queries being slow and that can lead to different issues. So, keeping the statistics fresh with these two things might help the queries to remain performing on the database. That's pretty much from my side. Uh, thank you everyone.