r/programming • • 3d ago

PostgreSQL 19 Released!

https://www.postgresql.org/about/news/postgresql-19-released-3449/
756 Upvotes

32 comments sorted by

439

u/senatorium 3d ago

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.

Neat.

139

u/Solonotix 3d ago

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.

22

u/Perfekt_Nerd 2d ago

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.

7

u/davvblack 2d ago

do clustered indexes even matter on nvme storage?

9

u/TheBanger 2d ago

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

9

u/Perfekt_Nerd 2d ago

Absolutely. There is a massive difference between a seek and a scan on any moderately complex query

5

u/Solonotix 2d ago

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.

83

u/jmickeyd 3d ago

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.

45

u/IamPic 2d ago

Does it even make sense to use it then? It's hard to imagine what sort of weird bugs you can run into with such behavior.

14

u/Mognakor 2d ago

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.

4

u/Freeky 2d ago

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.

-6

u/iamapizza 2d ago

REPACK

Fitgirl: 😻

28

u/X-lem 2d ago

Can we add a new column after another column yet?

10

u/leros 2d ago

I just want to reorder columns.

0

u/baseballlover723 2d ago

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.

11

u/halcyon_aporia 2d ago edited 2d ago

Why do people want this?

Edit: Huh, interesting. I literally never use a GUI with postgresql, so it never even occurred to me. Fair enough.

16

u/baseballlover723 2d ago

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.

6

u/badmonkey0001 2d ago

makes it easier to read when related columns are next to each other. Or are in some kind of grouping.

It also makes CREATE statements cleaner to read if anyone needs a non-GUI reason.

5

u/halcyon_aporia 2d ago

You could always just run a select with the columns you want in the order you want, can't you? The result set will be in the order you select them.

9

u/baseballlover723 2d ago

I mean you could. But doing something every single time you want to use a * query (which is quite common for me) is a huge annoyance.

Just like how I could just type out my underlying view query every single time instead of just querying a view I wrote once.

Convenience is worth something imo. And needing to type out all of my columns for most of my queries is very inconvenient.

3

u/josh_ml_1127 2d ago

Convenience wins; listing all columns for most queries is a pain.

1

u/SenoraRaton 2d ago

I mean you could. But doing something every single time you want to use a * query (which is quite common for me) is a huge annoyance.

Store it as a view and access the view directly?

4

u/baseballlover723 2d ago

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.

7

u/baseballlover723 2d ago

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.

4

u/leros 2d ago

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.

2

u/davvblack 2d ago

guis use column order, and the dump

2

u/X-lem 2d ago

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.

Also column grouping makes things easier to read

1

u/leros 1d ago

GUI tools are pretty common when you're doing ad-hoc data analysis

34

u/AnderssonPeter 2d ago

Great i just upgraded from 17 to 18..

1

u/kre8tv 1d ago

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!

2

u/valarauca14 1d ago

lz4 default compression lets goo