PostgreSQL 19 was released on 19 November 2026. The headline for DBAs is REPACK (CONCURRENTLY), which removes table bloat without blocking reads and writes. Developers get plenty too: “read-your-writes” on standbys with WAIT FOR LSN, INSERT ... ON CONFLICT DO SELECT for get-or-create, JSON output from COPY, IGNORE NULLS for window functions, and faster foreign-key checks.

This guide focuses on the features you can use from application code and the upgrade points to check. Sources: the official PostgreSQL 19 release announcement, the release notes, and the PostgreSQL 19 docs for REPACK, WAIT FOR, INSERT and COPY.

1. Get-or-create in one round trip: ON CONFLICT DO SELECT

A classic pattern: insert a row if it doesn’t exist, otherwise return the existing one. Before PostgreSQL 19 you either did a SELECT then INSERT (two round trips and a race condition), or used a no-op DO UPDATE just to make RETURNING work, which writes a new row version every time.

PostgreSQL 19 adds DO SELECT. RETURNING then returns the existing conflicting row:

CREATE TABLE tags (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

INSERT INTO tags (name)
VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id, name;

Run it twice: the first call inserts, the second returns the same id without writing anything.

The full syntax is DO SELECT [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } ] [ WHERE condition ]. Adding a lock clause is useful when you’ll modify the row next in the same transaction:

INSERT INTO carts (user_id) VALUES (42)
ON CONFLICT (user_id) DO SELECT FOR UPDATE
RETURNING id;

Permissions: DO SELECT needs SELECT privilege on the table; with FOR UPDATE/SHARE, you also need UPDATE privilege on at least one column.

2. Read-your-writes on replicas: WAIT FOR LSN

If you send writes to the primary and reads to an async replica, a user who just saved something may not see it on the next page load. PostgreSQL 19’s WAIT FOR LSN lets a standby session wait until it has replayed WAL up to a given position.

On the primary, after your write commits, capture the LSN:

UPDATE profiles SET bio = 'Hello' WHERE user_id = 42;
SELECT pg_current_wal_insert_lsn();   -- e.g. 0/0306EE20

Store that value in the user’s session (or pass it to the next request). Then on the replica, before reading:

WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '200ms', NO_THROW);
-- returns: success | timeout | not in recovery
SELECT bio FROM profiles WHERE user_id = 42;

Options:

  • MODE – standby_replay (default, the one you want for read-your-writes), standby_write, standby_flush, or primary_flush.
  • TIMEOUT – milliseconds or a value with units such as '200ms'. Zero (the default) means wait indefinitely.
  • NO_THROW – return a status instead of raising an error on timeout, so your app can fall back to the primary.

An application-side helper in Python with psycopg 3 might look like this. WAIT is a utility command, so the LSN is composed as a safely quoted literal rather than a bind parameter:

from psycopg import sql

def read_profile(replica, primary, user_id, last_lsn):
    if last_lsn:
        with replica.cursor() as cur:
            cur.execute(
                sql.SQL("WAIT FOR LSN {} WITH (TIMEOUT '200ms', NO_THROW)")
                .format(sql.Literal(last_lsn))
            )
            status = cur.fetchone()[0]
        if status != "success":
            conn = primary   # replica too far behind: read from primary
        else:
            conn = replica
    else:
        conn = replica
    with conn.cursor() as cur:
        cur.execute("SELECT bio FROM profiles WHERE user_id = %s", (user_id,))
        return cur.fetchone()

Two rules from the docs: WAIT must be a top-level command (not inside a function, procedure or DO block), and it can’t run while the transaction holds a snapshot or locks. Run it outside a transaction block or as the first statement of one.

3. Remove bloat without downtime: REPACK

Tables with lots of updates and deletes accumulate dead space (“bloat”). VACUUM FULL and CLUSTER fixed that but locked the table. PostgreSQL 19 adds REPACK, which replaces both (they remain for compatibility):

-- Rewrite the table compactly (takes an ACCESS EXCLUSIVE lock)
REPACK orders;

-- Reorder by an index (clustering)
REPACK orders USING INDEX orders_created_at_idx;

-- Online: reads and writes continue
REPACK (CONCURRENTLY) orders;

-- Analyse afterwards and show progress messages
REPACK (ANALYZE, VERBOSE) orders;

With CONCURRENTLY, most of the work runs under a SHARE UPDATE EXCLUSIVE lock, changes made meanwhile are captured with logical decoding, and an ACCESS EXCLUSIVE lock is held only briefly to swap files. Important limitations from the docs:

  • It needs free disk space of at least the table plus its indexes.
  • CONCURRENTLY doesn’t work on partitioned or unlogged tables, materialised views, or tables without a primary key or index-based replica identity.
  • It’s not MVCC-safe: transactions using a snapshot taken before the repack started will see the table as empty after it commits.
  • It can fail if other sessions run DDL on the table meanwhile.

Monitor progress in pg_stat_progress_repack.

4. Other developer-facing additions

JSON from COPY. Export query results as JSON, optionally as a single array:

COPY (SELECT id, name FROM tags ORDER BY id)
TO STDOUT (FORMAT JSON, FORCE_ARRAY);

IGNORE NULLS in window functions. lead(), lag(), first_value(), last_value() and nth_value() can now skip NULLs. That’s the classic “carry the last known reading forward” query:

SELECT ts,
       reading,
       last_value(reading) IGNORE NULLS OVER (ORDER BY ts) AS filled
FROM sensor_data;

More additions listed in the announcement:

  • SQL/JSON path string methods such as lower(), upper(), replace(), split_part() and btrim().
  • random() can generate random dates and timestamps within a range.
  • Foreign-key checks are faster; the announcement says bulk inserts were nearly 2x faster in testing.
  • The planner can turn NOT IN into anti-joins when NULLs aren’t present and perform eager aggregation before joins.
  • Logical replication is easier to start: the effective WAL level rises to logical automatically when you create the first logical slot, and sequences can be replicated with FOR ALL SEQUENCES.
  • The new pg_plan_advice extension lets you stabilise query plans, and pg_stash_advice applies stored advice by query identifier, so you can fix a regressed plan without changing application code.
  • Server-side TLS SNI, configured in a new pg_hosts.conf file.

Upgrade notes

  • Upgrade with pg_upgrade, dump/restore, or logical replication, and test first. Defaults and some behaviour changed, so read the “Migration to Version 19” section of the release notes.
  • The io_workers setting is replaced by io_min_workers and io_max_workers (when io_method is worker, the default).
  • Check your driver, ORM and pooler versions. New SQL syntax such as DO SELECT and WAIT FOR LSN won’t appear in ORM query builders right away, so use raw SQL for now.
  • According to the PostgreSQL versioning policy, version 19 is supported until November 2031.

Key takeaways

  • Use INSERT ... ON CONFLICT DO SELECT ... RETURNING for race-free get-or-create in one statement.
  • Use WAIT FOR LSN with a timeout and NO_THROW for read-your-writes on replicas, with a fallback to the primary.
  • Use REPACK (CONCURRENTLY) instead of VACUUM FULL for online bloat removal, within its documented limits.
  • Test the upgrade on a copy of production data before you switch.