Squeezing tables with Postgres 19
Working with the Postgres development community is such a rewarding experience.
In January 2024, Pavel Stehule asked in the hackers mailing list: why is there no VACUUM FULL CONCURRENTLY?
I happen to know that Pavel works as a consultant with some very large, very high velocity databases. He knows many interesting stories, and he doesn’t ask questions just because. If he asks why don’t we have VACUUM FULL CONCURRENTLY, you can bet that there is an interesting production-server story behind that.
Why VACUUM FULL wasn't enough
Now you, gentle reader, might be wondering what the deal is with this VACUUM FULL CONCURRENTLY thing in the first place. Let me explain very succinctly. Stock vacuum, as you probably know, is a tool that Postgres uses to remove dead tuples and unused space (a.k.a. bloat) from tables. Because it doesn’t want to lock out other processes that want to read or modify the data in the table, it clears that space one page at a time, and it is not able to compact empty space that's sitting in the middle of the table — only at the end.
VACUUM FULL, by contrast, is more thorough. Rather than going page by page, it processes the whole table in one go: it creates another, transient table, and moves every individual live tuple to that new table; then it swaps the storage files of these two tables, and with that, magically the new table is neat and compact and the bloat goes away.
This cannot be done if the table is being modified, because such modification would be lost with the swap. So for all intents and purposes, if you do this on a table that’s important to your application, your application is effectively down for the duration of the compaction. This is not a big deal... unless the table is large and the compaction takes a long time.
How pg_squeeze solved it outside of core
Back to Pavel: he wants to be able to recover the space like VACUUM FULL, but he doesn’t want the downtime.
Luckily for this story, we also have Antonin Houska, another very smart engineer at Cybertec. Years ago he developed a project called pg_squeeze, a tool that does exactly what Pavel wants.
pg_squeeze leverages the change data capture (CDC) technology in Postgres, called logical decoding of WAL: like VACUUM FULL, it first creates a second (transient) table, filling it with all the live tuples from the original table; when that copy completes, all the changes that occurred during the copy are obtained from WAL by logical decoding, and from there pg_squeeze applies them to that second copy of the table. This allows applications to continue to operate on the table during the copy, which is what Pavel wanted. Access is blocked only during the final phase of the processing, when we do one last apply cycle (in case more changes were done to the data during the first apply cycle) and finally swap the storage.
So this is what Pavel wanted, right? Well, yes, except that pg_squeeze is not part of Postgres proper. For some Postgres users, installing third-party software packages is a nuisance at best, or in some cases even impossible; or simply because of not being in core, the chances for bugs are higher. which is why having this functionality integrated in core is so valuable. And thus began the effort to get it integrated.
Naming is hard
Beginning in the Postgres 18 development cycle, Antonin undertook the task of reworking VACUUM FULL so that it could host (parts of) the pg_squeeze code, and proposed it just like Pavel had first asked about: VACUUM FULL CONCURRENTLY.
But one problem we soon faced was the enthusiasm for discussing the names of things in the Postgres community, and our dislike for presenting users with confusing interfaces. So early on we did away with the idea of calling this VACUUM FULL CONCURRENTLY, because, you see, VACUUM FULL has long been known as the non-concurrent version of VACUUM, so what sense would it make to have one thing called VACUUM and another thing called VACUUM FULL CONCURRENTLY, both of which are concurrent but each in its own way? So I steered this away to the name REPACK, and we agreed to mix both VACUUM FULL and CLUSTER as one thing with that name — a choice I had to defend against wind and tide.
Time was too tight and life is busy, so we couldn't get that in Postgres 18. But we did get it in Postgres 19! This didn't add any new functionality, but by grouping old things in new ways, it paved the road for the new option to be added without creating even more confusion.
But we were not there yet: we still needed CONCURRENTLY, the really novel part. Antonin worked hard to add that by extracting that functionality from pg_squeeze and integrating it in the Postgres code. He and I spent a lot of time preparing that code and making it match current Postgres standards, as well as dealing with bugs old and new, especially bugs in logical decoding itself; in part caused by the fact that REPACK (CONCURRENTLY) uses logical decoding in a way that’s so completely different from its use for logical replication. Eventually we got it to a place I felt was good enough, and in it went!
With this, we particularly wanted to avoid problems that users of tools like pg_repack and pg_squeeze had problems with. For example, letting WAL accumulate during the whole process is not acceptable, so Antonin added a background worker that decodes WAL and writes the relevant changes to a separate spill area on disk, so that the REPACK replay can read the changes from there, allowing WAL to be recycled normally regardless of how long REPACK runs.
Fighting to keep it in Postgres 19
At the same time, I didn't want to risk making it too complex, because that might have added difficult bugs. So we punted on some features, such as MVCC safety, snapshot refreshing, and more complicated handling of the lock upgrade in the final phase, in the name of simplicity. This makes it inconvenient for some use scenarios, and it means the user has to be vigilant while running it, but I think my bet that adding those things would have risked the whole feature was a good one. We decided to tackle these limitations in the next version of Postgres, and Antonin has already posted the code for that, so there's hope.
At one point, disaster was declared for Postgres 19 and it seemed like, as with many other Postgres 19 would-be features, this one would have to be reverted. I had to fight tooth and nail for that not to happen. A number of the fixes exist just because I'm just not smart enough to notice many obvious problems, but there were a few that were quite very subtle. So there's a lengthy trail of fixes in git log that's even tragicomical to see.
I feel I was able to keep things on track however, and today Postgres was released and I'm happy that REPACK is a headline feature:
A community effort
I cannot end this story without thanking all the people who tested and tortured this code. I want to thank especially Mihail Nikalayeu for spending so much time stress-testing it in early stages of development, which uncovered many bugs not only in the new code, but also ancient bugs in logical decoding.
And I bet some names are going to be missing from this list, but also deserve thanks Nathan Bossart, Amit Kapila, Fujii Masao, Masahiko Sawada, Matheus Alcantara, Thom Brown, Jian He, Sami Imseih, Nikita Kalinin, Chao Li, Radim Marek, Zsolt Parragi, Justin Pryzby, Srinath Reddy Sadipiralla, Bharath Rupireddy, Nikolay Samokhvalov, Baji Shaik, Robert Treat, Ewan Young, Junwang Zhao, Rui Zhao, Shihao Zhong.
A toast to you all — I'm rewarded with the feeling of achievement of a successful project, not in small part due to the time and energy you all spent in this project.
And now, on to the next one!