Tables that receive many updates and deletes can build up unused space, known as "bloat," which can also significantly impact query performance. The VACUUM FULL and CLUSTER commands were previously used to handle this, but had tradeoffs, including preventing access to the table until the cleanup finished. PostgreSQL 19 introduces the REPACK command, which reclaims this space and can reorder a table's data to match an index, and with REPACK (CONCURRENTLY), can do so without blocking reads or writes. REPACK replaces the VACUUM FULL and CLUSTER commands, both of which remain available for compatibility.
Probably the best thing about using SQL these days is that you can just kind of expect that the only things added version-over-version are quality of life things.
There are some exceptions, of course, like yesterday I had to contrive a solution in SQL Server 2019 that didn't depend on regex when it would have been immensely helpful. Had to re-educate my brain on using CHARINDEX and SUBSTRING to get the same result. But for the most part, I just know that the syntax will work, and I can do what I want with little concern.
Yeah, regex was just introduced in SQL Server 2025. Still vastly prefer it over Postgres because of clustered indexes, it's just a shame that running it on Linux has so many limitations.
It's not just a matter of randomly placed seeks, for small tuples a clustered index also means you're more likely to have multiple matching tuples per page
Being able to design a clustered index is a means of ordering the data as it is stored within a logical volume. An index seek is akin to accessing an array by its index. An index scan is akin to iterating over the entire array until you find the relevant items. The clustered index is what determines the order. Additionally, SQL Server will take sargable predicates and attempt a binary search to get in the rough area of the table where the relevant rows are.
So, yes, the clustered index is very beneficial. Most other SQL implementations get by using nonclustered indexes for this purpose, and it works well enough.
Unfortunately the new concurrent repack comes with a heck of a caveat:
Warning REPACK with the CONCURRENTLY option is not MVCC-safe. After the repacking transaction commits, the table will appear empty to concurrent transactions, if they are using a snapshot taken before repacking started. See Section 13.6 for more details.
If i understand correctly the sequencing needs to be: TransactionStart -> RepackStart -> RepackEnd (-> TransactionRead) -> TransactionEnd
Sounds like it only becomes an issue when the transaction runs longer than the repack in which case either the transaction takes too long or your REPACK is very fast (which i assume implies small tables or little need for work to be done).
From the top of my head the only scenario where this becomes a concern is if you have something long running like a nightly report and someone decides to schedule a REPACK during the night.
Otherwise i'd say optimize your transactions and make sure you only run REPACK when necessary.
That's my reading too - it's up to you to make sure no long-running transactions might use a stale snapshot. Most transactions are probably both short, and use the default read committed isolation level which takes a fresh snapshot for each statement, so the set of scenarios where it's likely to be a problem seems fairly limited.
If you want to be completely certain, enforce a bit of downtime prior to running the REPACK - once the server is quiesced, you can kick it off, and then allow traffic again. Still a win over needing downtime for the entire duration of the operation.
If you could reorder columns, then you could do this (and more), cause you could just add a column to the end and then immediately reorder it to where you want it.
makes it easier to read when related columns are next to each other. Or are in some kind of grouping.
An easy scenario to image is you start with 1 metric column, say column 2 of 10. And then some years later you come up with another metric, and you want to see how it compares to your original metric. If it's in column 3, you can shift your eyes back and forth between them quite easily. If you have to cross 8 unrelated columns, then it's much less efficient for you to process the data.
And for the sake of argument, lets say that those 8 columns contain secondary information and the query takes some time to run, so simply omitting the other information also creates a big hassle.
So then I have a table I never use, and a view that is virtually identical to the table I never use. And they both have to have names, which ideally describe them, but can't be the same.
I mean it works, but it doesn't feel like a good solution to me cause 2 things that apparently do the same thing is sure to cause confusion.
Edit: Huh, interesting. I literally never use a GUI with postgresql, so it never even occurred to me. Fair enough.
It's even more relevant in the CLI, where you don't get things like expandable cells or could potentially have a post query feature to rearrange things (though idk if any sql gui actually lets you do that).
Wrapping is a huge issue too. Imagine you have a table that stores reddit comments. There's a bunch of relevant fields, the id36, the username of who posted it, the body, when it was made, etc. If one of your middle fields has a very long width or has new lines in it, then everything after it can't be aligned and when one column is taking up 2-20 rows, it's really hard to read, because it's not very clear where one row starts and the next ends, or if it's in the middle of another column.
At least with a gui, you don't need to have all of the information being shown on the screen all the time, and you can simply show an excerpt of a really long cell, and have it expand on click etc.
When you query tables in a GUI tool the columns are ordered in the natural order of the table. Any new columns are at the end with Postgres. It's really annoying. Other databases let you modify the order.
created/updated/deleted at should always (imo) be at the end. If I see those columns I assume there's no more. I've found I miss columns when they're "hidden" after that.
Same ha from 16 to 18. I knew 19 was coming and was going to wait, but then it got delayed and then when launch was supposed to happen they said they found a bunch of bugs after an AI-review and had to drop features and that was going to take them time to fix. I thought maybe the delay would be months not weeks!
439
u/senatorium 3d ago
Neat.
28
u/X-lem 2d ago
Can we add a new column after another column yet?
34
u/AnderssonPeter 2d ago
Great i just upgraded from 17 to 18..
2
u/valarauca14 1d ago
lz4 default compression lets goo