PostgreSQL 19 New Features Every API Developer Should Know

PostgreSQL 19 adds ON CONFLICT DO SELECT for get-or-create endpoints, WAIT FOR LSN for read-your-writes on replicas, REPACK (CONCURRENTLY), JSON output from COPY and a list of default changes to check before you upgrade.

INEZA Felin-Michel

INEZA Felin-Michel

20 November 2026

PostgreSQL 19 New Features Every API Developer Should Know

Apidog for Enterprise

On-Premises Deploy

SSO & RBAC

SOC 2 Compliant

Explore Apidog Enterprise

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:

Flowchart of a POST /users request using INSERT ON CONFLICT (email) DO SELECT RETURNING: no conflict inserts and returns the row (201), a conflict returns the existing row unmodified (200)

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';
Sequence diagram: API server commits an UPDATE on the primary, reads pg_current_wal_insert_lsn 0/0306EE20, stores it in the session, then on the next request sends WAIT FOR LSN to the read replica, gets success, and SELECTs the fresh row

The details that matter in an API:

  1. Modes. The default MODE 'standby_replay' waits until the change is applied, which is what read-your-writes needs. standby_write, standby_flush and primary_flush exist for durability checks.
  2. Timeouts. With no TIMEOUT, or TIMEOUT '0', it waits forever. Always set one in request paths.
  3. NO_THROW. Without it, a timeout raises an error and aborts your transaction. With it, you get a status of success, timeout or not in recovery, and you can fall back to the primary.
  4. Placement. WAIT must be a top-level command, not inside a function or DO block. 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

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.

Explore more

GPT-Live-1.1 Pricing: Active vs Standby Minutes Explained

GPT-Live-1.1 Pricing: Active vs Standby Minutes Explained

GPT-Live-1.1 pricing explained: $0.05 per active minute, $0.02 per standby minute, billed per second, with worked cost examples for a support line, a tutor and an always-on assistant.

20 November 2026

How to Use GPT-Live-1.1 API ?

How to Use GPT-Live-1.1 API ?

GPT-Live-1.1 is now in the OpenAI API on v1/live/sessions. Open a WebSocket or WebRTC session, configure Responses delegation to gpt-6-luna, add a function tool, and test the backend it calls with Apidog CLI.

20 November 2026

Rails RCE Vulnerability (Ruby2Shell): How to Check If Your API Is Affected

Rails RCE Vulnerability (Ruby2Shell): How to Check If Your API Is Affected

Ruby2Shell (CVE-2026-132825) is a pre-auth heap overflow in Ruby that reaches stock Rails. Check the Ruby your API really runs, find every pinned version, upgrade to 3.3.14, 3.4.13 or 4.0.10, and verify the patched build.

20 November 2026

Practice API Design-first in Apidog

Discover an easier way to build and use APIs

PostgreSQL 19 New Features Every API Developer Should Know