PostgreSQL 19 ships on November 19 and PostgreSQL 14 loses support on November 12. What's new for application code (ON CONFLICT DO SELECT, NOT IN as an anti join, REPACK), which defaults changed, a preflight script for pg_upgrade and a rollout plan.
- Published
- Reading
- 16 min
- Author
- Yaroslav Tsarenko

Two PostgreSQL dates land in the same week this November. On November 12, 2026, PostgreSQL 14 gets its final update and goes out of support. On November 19, PostgreSQL 19 ships, and unlike many major releases, several of its changes matter to application developers, not just to whoever runs the database.
This guide is for teams running a product on PostgreSQL and Node.js. It covers what's new and worth using right away, which defaults changed quietly, what will make pg_upgrade refuse to run, and the order to do things in. I ran every example on PostgreSQL 19 RC 1 and 18.6 in Docker with Node.js 25 and pg (node-postgres); the output below is real.
The dates that matter
PostgreSQL 19 RC 1 shipped on October 15. According to the release announcement, RC 2 is scheduled for November 12 (the same day as the regular minor release for every branch), and PostgreSQL 19 itself for November 19, 2026, unless an issue turns up that needs a delay or another release candidate.
Each PostgreSQL major version is supported for five years. Here is the support table as it stands today:
| Version | First release | End of support | What to do |
|---|---|---|---|
| 14 | Sep 30, 2021 | Nov 12, 2026 | upgrade now |
| 15 | Oct 13, 2022 | Nov 11, 2027 | plan for 2027 |
| 16 | Sep 14, 2023 | Nov 9, 2028 | no rush |
| 17 | Sep 26, 2024 | Nov 8, 2029 | no rush |
| 18 | Sep 25, 2025 | Nov 14, 2030 | wait for 19.1 |
| 19 | Nov 19, 2026 (planned) | five years after release | new projects |
Nothing stops working on November 13. The difference is that the next vulnerability found in 14.x won't be fixed by anyone. If you're on a managed host, check its policy for unsupported versions too; providers handle them differently.
Who should upgrade, and when
A major database upgrade is the riskiest routine change in most stacks: one operation rewrites the format of the data your whole product depends on. So the right timing depends on where you are.
- PostgreSQL 14 or older. Upgrade this quarter. If 19 is the target, start testing on the RC now. If your host doesn't offer 19 yet, go to 17 or 18: they're supported until 2029–2030.
- PostgreSQL 15. Supported until November 2027. Plan the move to 19 for the first half of 2027, after 19.1 or 19.2.
- PostgreSQL 16–18. No rush. Upgrade when you need one of the features below, or together with your next planned infrastructure upgrade.
- New project. Start on 19 as soon as your host supports it. A new major version costs nothing on day one; moving a year later costs a week.
The most common mistake is upgrading production in the first week after release. The first minor release, 19.1, is scheduled for February 11, 2027 on the project's roadmap. Test now; move the production database after that.
ON CONFLICT DO SELECT: get-or-create without the race
The most useful change for backend code is a new INSERT action. The classic case: a payment provider sends a webhook, and you need to store the event exactly once and know whether it's new or a retry. Providers deliver the same event more than once by design, and without that check an order can be processed twice. I write handlers like this regularly for e-commerce provider integrations, and this is exactly where PostgreSQL before 19 had an awkward gap.
On conflict, ON CONFLICT DO NOTHING RETURNING id returns zero rows. So the usual pattern is a CTE: try the insert, and if nothing was inserted, read the existing row. It looks safe, but it has a race. Here's a test: transaction A has inserted the event but not committed yet, and transaction B runs the same get-or-create at that moment.
import { Client } from "pg";
const url = process.env.DATABASE_URL;
const a = new Client({ connectionString: url });
const b = new Client({ connectionString: url });
await a.connect();
await b.connect();
await a.query("truncate webhook_events restart identity");
const getOrCreate = `
with ins as (
insert into webhook_events (event_id, payload) values ($1, '{}')
on conflict (event_id) do nothing
returning id
)
select id, true as created from ins
union all
select id, false from webhook_events where event_id = $1 and not exists (select 1 from ins)`;
await a.query("begin");
await a.query("insert into webhook_events (event_id, payload) values ('evt_race', '{}')");
const pending = b.query(getOrCreate, ["evt_race"]);
await new Promise((resolve) => setTimeout(resolve, 200));
await a.query("commit");
const { rows } = await pending;
console.log("rows for B:", rows);
await a.end();
await b.end();Output on PostgreSQL 18.6:
rows for B: []B waits for A to commit, sees the conflict and inserts nothing. But the second SELECT in the same statement uses the snapshot taken when the statement started, before A committed, so it can't see the row either. The handler gets "nothing" even though the event is in the table. In production that's a rare and nasty bug: a Cannot read properties of undefined once every few thousand webhooks, impossible to reproduce on demand.
PostgreSQL 19 adds ON CONFLICT DO SELECT: on conflict, the statement returns the existing row, and with FOR UPDATE it also locks it. The docs call it an "idempotent insert" or "get or create". Same test, only B's query changes:
const pending = b.query(
`insert into webhook_events (event_id, payload) values ($1, '{}')
on conflict (event_id) do select
returning id, old.id is null as created`,
["evt_race"],
);rows for B: [ { id: '1', created: false } ]old in RETURNING arrived in PostgreSQL 18: if the row was inserted, old.id is NULL; if the existing row was returned, it isn't. One statement gives you both the row and a "new or retry" flag. A complete webhook recorder:
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });
type Recorded = { id: string; created: boolean };
async function recordEvent(eventId: string, payload: unknown): Promise<Recorded> {
const { rows } = await pool.query<Recorded>(
`insert into webhook_events (event_id, payload) values ($1, $2)
on conflict (event_id) do select
returning id, old.id is null as created`,
[eventId, payload],
);
return rows[0];
}
const attempts = await Promise.all(
Array.from({ length: 20 }, (_, i) => recordEvent("evt_3Qx", { attempt: i })),
);
console.log("rows returned:", attempts.length);
console.log("created:", attempts.filter((a) => a.created).length);
console.log("same id:", new Set(attempts.map((a) => a.id)).size === 1);rows returned: 20
created: 1
same id: trueTwenty concurrent attempts, exactly one "new", all with the same id. Before you adopt it:
- A conflict target is required, e.g.
on conflict (event_id), and so isRETURNING. DO SELECT FOR UPDATElocks the existing row until the transaction ends, which is handy when you're about to change the related order.- A
WHEREafterDO SELECTreturns the existing row only if the condition holds; otherwise the row is simply not in the result. - ORMs. If Drizzle or Prisma can't build this yet, use raw SQL (
sqlor$queryRaw). It's one query in a repository, not a reason to wait.
If you hand webhook jobs to a queue, the same idempotency key protects the worker side as well; I covered that in Node.js job queues with retries and a DLQ.
NOT IN is no longer a trap
Old PostgreSQL advice: "never write NOT IN (select ...), write NOT EXISTS." The reason is NULL. If the subquery returns a single NULL, NOT IN returns nothing by the SQL standard, so the planner couldn't turn it into an anti join. As long as the subquery fit in work_mem, a hash table saved you. Once it didn't, you got a row-by-row comparison.
PostgreSQL 19 turns NOT IN into an anti join when it can prove there are no NULLs on either side. A test: 20,000 customers, 40,000 orders, both columns NOT NULL, and a deliberately tiny work_mem to reproduce the "subquery no longer fits" situation.
create table customers (id bigint primary key);
create table orders (id bigint primary key, customer_id bigint not null);
insert into customers select g from generate_series(1, 20000) g;
insert into orders select g, (g % 10000) + 1 from generate_series(1, 40000) g;
analyze;
set work_mem = '64kB';
explain (costs off)
select count(*) from customers where id not in (select customer_id from orders);PostgreSQL 18.6:
Aggregate
-> Seq Scan on customers
Filter: (NOT (ANY (id = (SubPlan 1).col1)))
SubPlan 1
-> Materialize
-> Seq Scan on orders
Time: 14207.453 ms (00:14.207)PostgreSQL 19 RC 1:
Aggregate
-> Hash Right Anti Join
Hash Cond: (orders.customer_id = customers.id)
-> Seq Scan on orders
-> Hash
-> Seq Scan on customers
Time: 8.924 ms14 seconds versus 9 milliseconds, same data, same laptop. To be clear, this is a deliberately bad scenario, not a typical benchmark: with a normal work_mem the gap on this data set is small. But this is exactly the kind of query that suddenly falls over in production once a table grows. The practical takeaway: the optimization only kicks in when the columns are declared NOT NULL. If a column is nullable, nothing changes, so either add the constraint or keep writing NOT EXISTS. Reading plans like these and finding slow queries in the first place is covered in my PostgreSQL query optimization guide for Node.js.
REPACK replaces VACUUM FULL
A table with frequent updates and deletes bloats over time: VACUUM marks dead rows' space as reusable, but the file doesn't shrink. Until now, shrinking it meant VACUUM FULL or CLUSTER, and both hold an ACCESS EXCLUSIVE lock for the whole run, so the table can't even be read. That's why almost everyone installed the pg_repack extension.
PostgreSQL 19 adds a built-in REPACK command. With CONCURRENTLY, it does most of the work under a weaker lock and takes the exclusive lock only briefly at the end to swap the files.
repack (concurrently) webhook_events;
repack (concurrently, analyze) orders using index orders_created_at_idx;Limits to know up front:
- It needs a primary key or an index-based replica identity. It doesn't work on partitioned or
UNLOGGEDtables, materialized views, or inside a transaction block. - Disk. You need free space of at least the table plus all its indexes.
- Not MVCC-safe. A transaction whose snapshot predates the repack will see the table as empty after it commits. Avoid long analytical transactions while it runs.
- Concurrent changes are captured through logical decoding. On my test container with the default
wal_level = replicait ran with no extra configuration;max_repack_replication_slotslimits how many can run at once.
VACUUM FULL and CLUSTER are kept for compatibility, but new runbooks should say REPACK.
Small things that shorten application SQL
A few changes that don't need their own section but remove code. All output is from PostgreSQL 19 RC 1.
IGNORE NULLS in window functions. The last known price or status, no hand-rolled subqueries:
select day, price,
last_value(price) ignore nulls over (order by day) as last_known
from prices order by day; day | price | last_known
------------+-------+------------
2026-11-01 | 10 | 10
2026-11-02 | | 10
2026-11-03 | | 10
2026-11-04 | 12 | 12COPY TO as JSON. Exports without glue code in Node.js; force_array wraps everything in a single array:
copy (select event_id, payload from webhook_events) to stdout (format json, force_array);[
{"event_id":"evt_1","payload":{"a": 1}}
]EXPLAIN (ANALYZE, IO) puts I/O statistics right into the plan, and log_lock_waits is now on by default, so queries waiting on a lock longer than deadlock_timeout show up in your logs on their own.
Heads-up, changed behavior: json_array(select ...) with no rows now returns [] instead of NULL. I checked both: 18.6 returns NULL, 19 RC 1 returns []. If your code has if (row.items === null), that branch will stop firing.
Defaults that changed quietly
The nastiest upgrade surprises aren't removed functions; they're new defaults. Everything still works, just differently. I compared SHOW on both containers:
| Setting | PostgreSQL 18.6 | PostgreSQL 19 RC 1 | What to check |
|---|---|---|---|
jit |
on | off | heavy analytical queries: if JIT helped, enable it explicitly |
max_locks_per_transaction |
64 | 128 | if you set a custom value, roughly double it |
default_toast_compression |
pglz | lz4 | nothing; new large values compress faster |
log_lock_waits |
off | on | log volume if lock waits are common |
New for app code
- ON CONFLICT DO SELECTget-or-create in one statement
- NOT IN as anti joinwhen columns are NOT NULL
- REPACK (CONCURRENTLY)shrink a table without downtime
Changed defaults
- JIT offenable it for analytics yourself
- max_locks_per_transaction64 → 128, double custom values
- lz4 and log_lock_waitsTOAST compression and lock logs
Upgrade blockers
- btree_gist on inet/cidrpg_upgrade refuses
- RADIUS and MULE_INTERNALremoved entirely
Three more changes from the release notes that affect almost everyone:
- MD5 passwords. Every successful MD5 login now logs a warning (controlled by
md5_password_warnings); MD5 was already deprecated in 18. Time to move roles to SCRAM, which node-postgres has supported for years. standard_conforming_stringsis alwayson. Dumps made by olderpg_dumpwith itoffwon't load; recreate them with version 19 tools.- Wraparound warnings now start 100 million transactions before the limit instead of 40 million. Expect earlier warnings in the logs: not a new problem, an earlier notice of an old one.
What will block pg_upgrade: a preflight script
Some changes make the upgrade impossible until you deal with them. pg_upgrade will refuse a cluster that has btree_gist indexes on inet/cidr, or database, role or tablespace names containing line breaks. The MULE_INTERNAL encoding and RADIUS authentication are gone entirely.
The official check is pg_upgrade --check, but it runs on a server with both versions installed. To size the work before that, I wrote a short TypeScript check you can run from a laptop against any database:
import { Client } from "pg";
type Check = { name: string; sql: string; hint: string };
const checks: Check[] = [
{
name: "MD5 passwords",
sql: `select rolname as item from pg_authid where rolpassword like 'md5%'`,
hint: "PostgreSQL 19 warns on every MD5 login. Re-set these passwords with password_encryption = 'scram-sha-256'.",
},
{
name: "standard_conforming_strings = off",
sql: `select name as item from pg_settings where name = 'standard_conforming_strings' and setting <> 'on'`,
hint: "The setting is always on in 19. Find code that relies on backslash escapes in '...' literals.",
},
{
name: "Names with CR or LF",
sql: `select datname as item from pg_database where datname ~ '[\\r\\n]'
union all select rolname from pg_roles where rolname ~ '[\\r\\n]'
union all select spcname from pg_tablespace where spcname ~ '[\\r\\n]'`,
hint: "pg_upgrade rejects such clusters. Rename them first.",
},
{
name: "btree_gist inet/cidr indexes",
sql: `select i.indexrelid::regclass::text as item
from pg_index i join pg_opclass oc on oc.oid = any(i.indclass::oid[])
where oc.opcname in ('gist_inet_ops', 'gist_cidr_ops')`,
hint: "pg_upgrade rejects them. Recreate with the core inet_ops GiST opclass.",
},
{
name: "MULE_INTERNAL databases",
sql: `select datname as item from pg_database where pg_encoding_to_char(encoding) = 'MULE_INTERNAL'`,
hint: "The encoding is gone. Dump and restore with UTF8.",
},
{
name: "Custom max_locks_per_transaction",
sql: `select setting as item from pg_settings where name = 'max_locks_per_transaction' and source <> 'default'`,
hint: "The default moves 64 -> 128 and allocation changed: a custom value needs roughly doubling.",
},
{
name: "JIT in use",
sql: `select setting as item from pg_settings where name = 'jit' and setting = 'on'`,
hint: "JIT is off by default in 19. If heavy analytical queries got faster with it, turn it on explicitly.",
},
];
const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
const { rows: [{ server_version }] } = await client.query("show server_version");
console.log(`Server ${server_version}\n`);
let found = 0;
for (const check of checks) {
try {
const { rows } = await client.query<{ item: string }>(check.sql);
if (rows.length === 0) {
console.log(`ok ${check.name}`);
continue;
}
found++;
console.log(`FOUND ${check.name}: ${rows.map((r) => r.item).join(", ")}\n ${check.hint}`);
} catch (error) {
console.log(`skip ${check.name}: ${(error as Error).message}`);
}
}
console.log(`\n${found} item(s) to fix before pg_upgrade`);
await client.end();Run it with DATABASE_URL=postgres://... npx tsx preflight.ts. On a test 18.6 database where I deliberately created a role with an MD5 password and a btree_gist index on inet:
Server 18.6 (Debian 18.6-1.pgdg13+2)
FOUND MD5 passwords: legacy_app
PostgreSQL 19 warns on every MD5 login. Re-set these passwords with password_encryption = 'scram-sha-256'.
ok standard_conforming_strings = off
ok Names with CR or LF
FOUND btree_gist inet/cidr indexes: sessions_ip_gist
pg_upgrade rejects them. Recreate with the core inet_ops GiST opclass.
ok MULE_INTERNAL databases
ok Custom max_locks_per_transaction
FOUND JIT in use: on
JIT is off by default in 19. If heavy analytical queries got faster with it, turn it on explicitly.
3 item(s) to fix before pg_upgradepg_authid is readable only by a superuser, so on a managed host that check prints skip; look for MD5 roles in your provider's console instead. The script doesn't replace pg_upgrade --check or the release notes, but in a second it tells you whether there's work to do before the upgrade.
How to upgrade: pg_upgrade, dump or replication
There are three ways, and you pick by database size and how much downtime you can afford.
| Method | Downtime | When to use it |
|---|---|---|
pg_dump / pg_restore |
minutes to hours, grows with size | databases up to a few tens of GB, changing encoding, moving hosts |
pg_upgrade with --link or --swap |
minutes, mostly independent of size | large databases on your own servers |
| logical replication | seconds for the switchover | when downtime isn't acceptable and you have time to set it up |
Useful points from the pg_upgrade docs:
--checkinspects both clusters without changing data and can run while the old server is up;- since version 18,
pg_upgradecarries over most planner statistics, so the database doesn't crawl for an hour on cold statistics after the upgrade; - afterwards, the docs recommend
vacuumdb --all --analyze-in-stages --missing-stats-only, thenvacuumdb --all --analyze-only; --linkand--swapare fast, but the old cluster can't be used afterwards. Take the backup before, not after.
On managed hosts (RDS, Supabase, Neon and others), the major upgrade runs through their tooling, and a new version doesn't appear there on release day. Before you plan dates, check when your provider adds 19 and whether it offers a replica-based upgrade with minimal downtime.
A special case is when you want to clean the data while you migrate. When I rebuilt the AllShip AI marketplace, I moved only real accounts into the new PostgreSQL database and left the bot accounts behind in the old one. pg_upgrade can't do that: it moves the cluster as is. That calls for a selective, scripted migration and a check that no real record was lost.
Step-by-step rollout
- Oct 15RC 1test on a copy of your data
- Nov 12RC 2 and 14 EOLfinal 14.x minor release
- Nov 19GAPostgreSQL 19.0, new projects
- Dec–JanStagingpg_upgrade --check, tests, load
- Feb 11, 202719.1 and productionfirst minor release
- This week. Run the preflight script against production (or a replica) and write down what it finds. If you're on 14, this is your priority for the quarter.
- After November 12. Install the new minor release on your current branch; updates for every supported version ship that day.
- A copy on 19. Bring up 19 from a copy of your data (
postgres:19rc1is already on Docker Hub), run your tests and heaviest queries withEXPLAIN (ANALYZE, IO), and compare with your current version. - CI on two versions. Add 19 to your test matrix next to your current version. That's how you catch behavior changes like
json_array()before production does. - Rehearse the upgrade.
pg_upgrade --check, then a full upgrade of the copy, timed. That number defines your maintenance window. - Production after 19.1 (planned for February 11, 2027): backup, upgrade,
vacuumdb, and a day of watching errors and slow queries. - Then the new features. Replace CTEs with
DO SELECT, putREPACKin your runbooks, addNOT NULLwhere it's missing for anti joins.
It's the same rule as for runtime upgrades: security patches on the current branch first, then a calm major upgrade on a schedule. I laid out the same order for Node.js in the Node.js 26 LTS upgrade guide.
Pre-upgrade checklist
| Check | How | Red flag |
|---|---|---|
| Version and end of support | show server_version |
14 or older |
pg_upgrade blockers |
the script above + pg_upgrade --check |
any FOUND |
| MD5 passwords | pg_authid or the provider console |
application roles on MD5 |
| Custom settings | select name, setting from pg_settings where source <> 'default' |
a custom max_locks_per_transaction |
| Extensions | select * from pg_extension |
no build for 19 yet |
| Analytical queries | EXPLAIN ANALYZE before and after |
slower without JIT |
Code expecting NULL |
search SQL for json_array |
null checks on the result |
| Backups | restore one on a test server | nobody has ever restored one |
| Upgrade duration | rehearse on a copy | unknown |
What to do this week
PostgreSQL 19 is a good release for application developers: ON CONFLICT DO SELECT closes a long-standing race in get-or-create, NOT IN stops being a landmine, and REPACK removes the need for an external extension. But the value of any upgrade depends on how uneventful it is.
Run the preflight script, check the version in production, and put three dates in the calendar: November 12 for the last 14.x patch, November 19 for the release, February 11 for the first minor. If you'd like someone to walk that path with you, from a database audit to an upgrade without downtime, get in touch.
Was this useful?
– likes · – views
Questions people ask
When is PostgreSQL 19 released?
PostgreSQL 19 RC 1 shipped on October 15, 2026, RC 2 is scheduled for November 12, and general availability is planned for November 19, 2026, unless an issue requires a delay or another release candidate.
What happens to PostgreSQL 14 after November 12, 2026?
Your database keeps running, but the 14.x line gets no more fixes, including security fixes. The final 14.x minor release ships on November 12. Move to at least 17 or 18, or plan straight for 19.
Can I upgrade from PostgreSQL 14 directly to 19?
Yes. pg_upgrade, pg_dump/pg_restore and logical replication all support jumping several major versions. Read the migration notes of every version in between, though: 15, 16, 17, 18 and 19.
Should I run PostgreSQL 19 in production right after release?
For a new project, yes, once your host supports it. For an existing production database, test on a copy of the data now and upgrade after the first minor release, scheduled for February 11, 2027.
What does ON CONFLICT DO SELECT do?
It's a new INSERT action in PostgreSQL 19: if a row with the same key already exists, RETURNING gives you that existing row instead of nothing, as DO NOTHING does. It is an atomic get-or-create with no extra SELECT and no race between transactions.