Skip to main content

PostgreSQL 19 is out: what changes for developers?


On Wednesday, November 19, PostgreSQL 19 was released, two months later than its usual September appointment. If you're not a developer, PostgreSQL is a database: the big cupboard where applications store everything they need to remember, customer accounts, orders, inventory. It's free, open source, and it runs behind a crazy number of websites and apps. As for me, it's the database I use every day in my projects, alongside SQL Server. So when there's a new version, I read it line by line. And this time, there are two new features that are really going to make my life easier.

In an immense old library, a gray elephant wearing small round glasses puts books away on the high shelves with its trunk, while readers continue to read peacefully at the tables around it

The elephant is PostgreSQL's mascot. And it puts its shelves in order without closing the library

The big cleanup without closing shop

To understand the first one, you need to know a little secret about PostgreSQL. When you modify or delete a row, it doesn't erase it right away. It keeps the old version, in case another query that's still running needs it. Automatic cleanup, the famous VACUUM, comes back afterward to mark those old rows as reusable, but it doesn't give the space back to the disk. On a table that's modified very often, the file ends up weighing much more than the data it actually contains. English speakers call this “bloat”, the swelling.

Until now, to get that space back, there was VACUUM FULL. It rewrites the table cleanly... but locks it for the entire operation. Nobody reads, nobody writes. On a big table, that means an app brought to a stop, or the famous “site under maintenance” on a Sunday at 3 a.m. A lot of people therefore went through pg_repack, an extension that has to be installed separately, which does the job without blocking everything.

PostgreSQL 19 finally includes it directly, with a new command: REPACK. It replaces VACUUM FULL and CLUSTER, which are still there so nothing breaks, and above all it knows how to work gently:

-- Rebuild the table and give the free space back to the disk
REPACK clients;

-- Same thing, without blocking reads and writes
REPACK (CONCURRENTLY) clients;

-- Rebuild and sort the rows along an index (what CLUSTER used to do)
REPACK clients USING INDEX clients_pkey;

With CONCURRENTLY, PostgreSQL copies the table while the app keeps running, notes the modifications that arrive in the meantime, and only takes its exclusive lock right at the end, for the time it takes to swap the files. A short moment, instead of the entire length of the operation. For you, if you're not a developer, that means less “scheduled maintenance” on the sites you use.

In an open supermarket, an employee stocks and reorganizes an aisle while a customer walks right past with a full shopping cart, without being inconvenienced, other customers and employees in the background

With REPACK (CONCURRENTLY), we tidy up the aisles while customers do their shopping

Still, read the conditions before rushing in. The table must have a primary key, it can't be partitioned, split into several pieces, and the command refuses to run inside a transaction. The documentation also warns about a subtler trap: a query that started before the REPACK and rereads the table right afterward can see it... empty. It's rare, but it's better to know before launching it in the middle of the day on the invoices table!

The “get it or create it”, finally without a hack

The second one is something every developer has hacked together at some point. You want to save a client if they don't exist yet, and if they already exist, retrieve their record. Since 2016, PostgreSQL has known how to do “insert, and in case of a duplicate, do nothing” (ON CONFLICT DO NOTHING). The problem is that in that case, it doesn't return anything at all. So we cheated with a fake update, just to get the row back:

-- The old trick: a fake update, only to get the row back
INSERT INTO clients (email, name) VALUES ('alice@example.com', 'Alice')
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
    RETURNING *;

It works, but it rewrites the row for no reason. And what does every rewrite leave behind? An old version of the row. Enough to make the table swell, and there you have it. PostgreSQL 19 therefore adds DO SELECT, which does exactly what we wanted from the start:

-- PostgreSQL 19: insert, or simply return the existing row
INSERT INTO clients (email, name) VALUES ('alice@example.com', 'Alice')
    ON CONFLICT (email) DO SELECT
    RETURNING *;

-- Same, and lock the existing row so nobody changes it meanwhile
INSERT INTO clients (email, name) VALUES ('alice@example.com', 'Alice')
    ON CONFLICT (email) DO SELECT FOR UPDATE
    RETURNING *;

At a hotel reception, a smiling receptionist holds out a room card to a traveler holding his suitcase, while a second unused card is left on the counter

DO SELECT: you already have a reservation, so we hand you your key instead of opening a second room for you

The little gifts you notice afterward

The COPY command, which is used to export data in bulk, can now output JSON, the text format that all web apps understand. No need to go through a script to turn an export into a JSON array:

-- Export a query straight to JSON, as one single array
COPY (SELECT id, email FROM clients) TO STDOUT (FORMAT JSON, FORCE_ARRAY);

The functions that fetch the previous or next row (lag, lead and their cousins) can finally skip gaps. Handy for a series of readings with missing values:

-- For each reading, the last temperature that is not missing
SELECT measured_at, temperature,
       lag(temperature) IGNORE NULLS OVER (ORDER BY measured_at) AS previous_known
FROM readings;

The random() function can also pick a date at random between two bounds, which will please those who make test data. And foreign key checks (which stop you from saving an order for a customer who doesn't exist) are faster: according to the PostgreSQL team, bulk inserts were almost twice as fast in their tests.

Read this before migrating

JIT, a just-in-time compilation that theoretically speeds up large queries, is now turned off by default. The team explains that PostgreSQL was too often wrong about when it was worth using. If you run a lot of large analytical queries, measure before and after, and turn it back on if needed with jit = on.

Passwords stored the old-fashioned way, in MD5, now trigger a warning after every successful login. MD5 is an old method, deprecated since version 18, and it will disappear in a future version. It's time to switch to SCRAM, the modern method. RADIUS, an old network authentication system, is disappearing entirely. If you had manually set the number of input-output processes (io_workers), the setting is replaced by two new ones, io_min_workers and io_max_workers. And if you use full-text search or the pg_trgm extension (approximate search, the one that finds “Brussels” when you type “Bruxel”), the team recommends rebuilding these indexes after the update.

Timeline of the patch periods for PostgreSQL versions, five years per version, based on the postgresql.org version support page read on November 21, 2026. PostgreSQL 14: no more patches since November 12, 2026. PostgreSQL 15: ends on November 11, 2027. PostgreSQL 16: ends on November 9, 2028. PostgreSQL 17: ends on November 8, 2029. PostgreSQL 18: ends on November 14, 2030. PostgreSQL 19: ends on November 19, 2031.

Five years of patches per version, and 14 has just dropped off the list

And the truly urgent point isn't even in version 19. Each version receives five years of patches, and PostgreSQL 14 hasn't received any since November 12. If one of your databases is still running on 14, that's the one you need to migrate first, long before dreaming of REPACK.

As for me, I'm testing 19 in development right now. In production, I'll wait for 19.1, the first patch version. A “point zero” version is like a restaurant on opening night: the menu is promising, but I let others deal with the teething problems. As for VACUUM FULL, it remains available “for compatibility”. A bit like the fax machine in some offices: nobody should be using it anymore, but nobody dares unplug it!

Sources

Article written with the help of Claude Code, proofread and corrected by me.

Join the conversation

You need an account to comment on this article. Creating one is free and takes under a minute.

  • The XMLTV file, free to download every day
  • Comment on articles and reply to other readers
  • Get an e-mail when an article you follow is updated

No comments yet.

Une erreur s'est produite. Cette application peut ne plus répondre jusqu'à ce qu'elle soit rechargée.Veuillez contacter l'auteur. Reload 🗙