PostgreSQL 19 new features that matter most for API developers: INSERT ... ON CONFLICT DO SELECT for one-statement get-or-create endpoints, WAIT FOR LSN for read-your-writes on replicas, REPACK (CONCURRENTLY) for online bloat cleanup, JSON output from COPY, new jsonpath string methods, and faster foreign-key checks on bulk inserts.
This guide covers what changes in your handlers, replicas and upgrade plan. Every syntax example comes from the PostgreSQL 19 release notes and reference pages.
What’s new in PostgreSQL 19 at a glance
PostgreSQL 19 is the yearly major release of the open source database, announced by the PostgreSQL Global Development Group on November 19, 2026. It adds get-or-create inserts, replica waits for read-your-writes, online table repacking, plan advice and logical replication of sequences, and it changes several defaults you should check before upgrading.
| Feature | What it replaces | Where you’ll feel it |
|---|---|---|
ON CONFLICT DO SELECT |
SELECT-then-INSERT retry loops | Idempotent POST and signup endpoints |
WAIT FOR LSN |
Sticky routing of reads to the primary | Read replicas behind an API |
REPACK (CONCURRENTLY) |
VACUUM FULL, CLUSTER, pg_repack |
Bloated hot tables, no downtime |
COPY ... (FORMAT json) |
Row-to-JSON conversion in app code | Export and bulk download endpoints |
jsonpath string methods |
Post-processing JSONB in app code | Filtering and normalizing JSON columns |
pg_plan_advice + pg_stash_advice |
Rewriting queries to steer the planner | Plan regressions in production |
FOR ALL SEQUENCES publications |
Manual sequence resets after cutover | Blue-green upgrades via logical replication |
The official release announcement also lists parallel index vacuuming and auto-scaling async I/O workers. Those don’t change the SQL your API sends, so this post skips them.
ON CONFLICT DO SELECT: get-or-create in one statement
Every API has an endpoint that must return a row whether it exists or not: register a device, create a tag, start an idempotent checkout. Before version 19 you ran a SELECT then an INSERT (and lost races under load), or wrote a dummy ON CONFLICT DO UPDATE just to make RETURNING fire, which writes a new row version for nothing.
PostgreSQL 19 adds a fourth conflict action. The INSERT reference calls it an “idempotent insert or get or create”:
INSERT INTO distributors (did, dname) VALUES (11, 'Global Electronics')
ON CONFLICT (did) DO SELECT
RETURNING *;
If did = 11 is free, the row is inserted and returned. If it’s taken, the existing row comes back and nothing is modified. Three rules from the docs:
- A
RETURNINGclause is required withDO SELECT. - A
conflict_targetis required, the same asDO UPDATE. - Exclusion constraints can’t act as arbiters; use a unique index or a
NOT DEFERRABLEunique constraint.

Your handler usually needs to know which branch ran, so it can answer 201 Created or 200 OK. The docs note that RETURNING old values are non-NULL when the row conflicted, so one column tells you:
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT (email) DO SELECT
RETURNING *, (old.id IS NULL) AS created;
Need to lock the existing row before you touch related tables? Add a locking clause: ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING *. You can also filter with WHERE, for example DO SELECT WHERE d.dname != EXCLUDED.dname, and only rows that pass come back.
Privileges: DO SELECT needs SELECT on the table, and the FOR UPDATE/FOR SHARE forms also need UPDATE on at least one column.
WAIT FOR LSN: read-your-writes on replicas
The classic replica bug: a user saves their profile, the next GET hits an async replica that hasn’t replayed the change, and old data comes back. Most teams pin reads to the primary after a write, which wastes the replica.
PostgreSQL 19 adds the WAIT FOR command. After a write, grab the primary’s WAL position:
UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
SELECT pg_current_wal_insert_lsn(); -- returns 0/0306EE20
Store that LSN in the user’s session (Simon Willison suggests a signed cookie in his write-up). On the next request, run this on the replica before the read:
WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '100ms', NO_THROW);
SELECT * FROM movie WHERE genre = 'Drama';

The details that matter in an API:
- Modes. The default
MODE 'standby_replay'waits until the change is applied, which is what read-your-writes needs.standby_write,standby_flushandprimary_flushexist for durability checks. - Timeouts. With no
TIMEOUT, orTIMEOUT '0', it waits forever. Always set one in request paths. NO_THROW. Without it, a timeout raises an error and aborts your transaction. With it, you get a status ofsuccess,timeoutornot in recovery, and you can fall back to the primary.- Placement.
WAITmust be a top-level command, not inside a function orDOblock. Run it outside a transaction or as its first statement, before anything that takes a lock, or it’s rejected.
REPACK (CONCURRENTLY) replaces VACUUM FULL for live tables
Tables behind busy endpoints, like sessions or webhook_deliveries, bloat fast. VACUUM FULL and CLUSTER fix that but lock the table. PostgreSQL 19 adds an online option:
REPACK (CONCURRENTLY) webhook_deliveries;
REPACK (CONCURRENTLY, ANALYZE) orders USING INDEX orders_created_at_idx;
The release notes say REPACK (CONCURRENTLY) reclaims space and can reorder a table by an index without blocking reads or writes. VACUUM FULL and CLUSTER stay for compatibility. Watch progress in the new pg_stat_progress_repack view, and look for the new max_repack_replication_slots server setting when you tune the cluster.
JSON output from COPY and new jsonpath string methods
Export endpoints often stream rows through app code just to turn them into JSON. COPY TO can now do it in the database, per the COPY reference:
COPY (SELECT * FROM (VALUES(1),(2)) val(id)) TO STDOUT (FORMAT JSON, FORCE_ARRAY);
That prints [, {"id":1}, ,{"id":2} and ] on separate lines, so the output is one valid JSON array. Without FORCE_ARRAY you get one JSON object per line, which is NDJSON-friendly. SQL NULL becomes JSON null, and the json format works only with COPY TO.
For JSONB columns, jsonpath gains string methods: lower(), upper(), initcap(), ltrim(), rtrim(), btrim(), replace() and split_part(). The docs show:
jsonb_path_query('"TOM"', '$.lower()') -- "tom"
jsonb_path_query('" hello "', '$.btrim()') -- "hello"
jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", 3)') -- "ghi"
The release notes mark them immutable, and they let you normalize a JSONB field (trim it, lowercase it, split it) inside the query instead of in your handler.
Also, json_array() over zero rows now returns [] instead of NULL, which can break a client that checks for null.
Other PostgreSQL 19 changes API teams should know
- Faster foreign-key checks. The announcement says they made bulk inserts nearly 2x faster in testing. Useful for import endpoints that write child rows.
- Plan advice. The
pg_plan_adviceextension pins planner choices, andpg_stash_adviceapplies that advice by query ID, so you can fix a regressed query without redeploying. - UUID and encoding. You can cast between
byteaanduuid, andencode()/decode()supportbase64url, the URL-safe format for IDs in paths and tokens. - Logical replication for upgrades.
CREATE PUBLICATION all_sequences FOR ALL SEQUENCES;syncs sequences, andFOR ALL TABLES EXCEPT (TABLE users, departments)excludes tables. That closes the “IDs collide after cutover” gap in blue-green migrations.
Breaking changes to check before you upgrade
The release notes’ migration section lists changes that can hit an API without a single query failing in staging:
| Change in PostgreSQL 19 | What to check |
|---|---|
max_locks_per_transaction default 64 to 128, and existing settings must be doubled |
Custom values in your config or managed-service parameter group |
| JIT disabled by default | Analytics endpoints that relied on JIT for long queries |
Default TOAST compression pglz to lz4 |
Nothing breaks, but storage and CPU profiles shift |
standard_conforming_strings forced on |
Old dumps taken with it off won’t restore |
| MD5 password auth logs a warning; RADIUS removed | Move app roles to SCRAM-SHA-256 |
json_array() on zero rows returns [] |
Clients that test for null |
READ ONLY passed through to postgres_fdw |
Read-only transactions that wrote through a foreign table |
log_lock_waits on by default |
Log volume and alert noise |
Upgrading from any earlier major version still needs pg_upgrade, a pg_dumpall dump and restore, or logical replication. Plan the move now if you’re on PostgreSQL 14: per the versioning policy, its final release was November 12, 2026.
Let your coding agent regression-test your API after the upgrade with Apidog CLI
A major Postgres upgrade rarely breaks SQL syntax. It breaks behavior: a json_array() that returns [], a lock limit, a slower plan. Catch it by running your API’s test scenarios against a PostgreSQL 19 staging database, asserting on the HTTP response and the rows behind it. Let your coding agent do it with Apidog CLI.
npm install -g apidog-cli@latest
apidog login --with-token <YOUR_TOKEN>
npx -y skills add https://apidog.com # pick your agent when prompted
Then give your agent one prompt:
In Apidog project <projectId>, add a test case for POST /users that sends the same
email twice and asserts the first call returns 201 and the second returns 200 with the
same id. Then run test scenario <scenarioId> against the pg19-staging environment with
database assertions, and summarize every failure next to the PostgreSQL 19 change
that could explain it.
The agent reads the payload schema, validates it, creates the case, then runs the scenario with your database connection file so database steps hit the new cluster:
apidog cli-schema get test-case-create
apidog cli-schema validate test-case-create --file case.json
apidog test-case create --project <projectId> --file case.json
apidog run -t <scenarioId> --project <projectId> -e <envId> \
--database-connection ./database-connections.json -r cli,junit
Apidog’s database operations support PostgreSQL on the Free plan, so you can query the table after each request and assert that the get-or-create endpoint wrote exactly one row. The docs walk through database queries in API tests and running scenarios with database connections from the CLI. Put the same apidog run ... -r junit command into your CI pipeline and every later minor release gets the same check. If you’d rather let your agent query Postgres directly, see the PostgreSQL MCP server guide.
FAQ
When was PostgreSQL 19 released? November 19, 2026. The PostgreSQL project supports each major version for 5 years, so version 19 gets fixes until November 19, 2031.
Is ON CONFLICT DO SELECT the same as an upsert? No. DO UPDATE changes the existing row; DO SELECT returns it untouched. Use it for get-or-create and idempotent POSTs. It requires a RETURNING clause and a conflict target.
Does WAIT FOR LSN work on the primary? The default standby_replay mode only works on a standby during recovery. On a primary you get an error, or not in recovery with NO_THROW. Use MODE 'primary_flush' if you need to wait on the primary itself.
Can I upgrade from PostgreSQL 18 to 19 in place? Yes, with pg_upgrade. You can also dump and restore or use logical replication, which now replicates sequences with FOR ALL SEQUENCES. Review the breaking changes table above first.
Does PostgreSQL 19 replace pg_repack? For most cases, yes. REPACK (CONCURRENTLY) reclaims bloat and reorders tables by index without blocking reads or writes, and it ships in core.



