PostgreSQL 19 New Features: A Practical Guide for Developers

Every autumn the PostgreSQL team ships a new major version, and this year’s release is a big one for everyday developers. In this guide we walk through the PostgreSQL 19 new features that actually change how you write SQL and run your database: a non-blocking way to fix table bloat, a one-line “get or create”, read-your-writes on replicas, cleaner grouping, JSON exports and more. Every feature comes with a small, runnable example, so you can try it yourself in a few minutes.

This post is written for beginner and intermediate developers — whether you build APIs in Node.js or Python, or you just look after a Postgres database at work. You do not need to be a database administrator to follow along.

What is new in PostgreSQL 19?

PostgreSQL 19 is still in the beta stage at the time of writing. The project released Beta 4 on 24 September and said the final release is expected around September/October, so the stable version is very close. Features rarely change this late, but small details can, so always confirm with the official PostgreSQL 19 release notes before you upgrade production.

Here are the headline changes, grouped by who benefits most:

Try PostgreSQL 19 locally with Docker

The quickest way to play with a new version is Docker. The official image publishes beta tags; once the final version ships you can simply use postgres:19.

# Start a throwaway PostgreSQL 19 container
docker run --name pg19 -e POSTGRES_PASSWORD=devdojo \
  -p 5433:5432 -d postgres:19beta4

# Open psql inside it
docker exec -it pg19 psql -U postgres

# Check the version
SELECT version();

Now create a tiny sample table that we will use in the next sections:

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text,
  country    text,
  plan       text DEFAULT 'free',
  created_at timestamptz DEFAULT now()
);

INSERT INTO users (email, name, country, plan) VALUES
  ('asha@example.com',  'Asha',  'IN', 'pro'),
  ('ravi@example.com',  'Ravi',  'IN', 'free'),
  ('emma@example.com',  'Emma',  'UK', 'pro'),
  ('lucas@example.com', 'Lucas', 'BR', 'free');

REPACK: shrink tables without downtime

When you update or delete lots of rows, PostgreSQL leaves “dead” space behind. Normal autovacuum marks that space as reusable, but it does not give it back to the operating system. Over time a busy table can become bloated — much larger on disk than the data it holds — which slows down scans and backups.

Until now the fix was VACUUM FULL or CLUSTER. Both rewrite the whole table but hold an exclusive lock the entire time, so your app cannot even read the table. Many teams used the external pg_repack extension to avoid that. PostgreSQL 19 brings this idea into the core with a new command called REPACK.

-- Same behaviour as VACUUM FULL (takes an exclusive lock)
REPACK users;

-- Rewrite in index order, like CLUSTER used to do
REPACK users USING INDEX users_pkey;

-- The big one: rebuild while reads and writes continue
REPACK (CONCURRENTLY) users;

-- Rebuild, then refresh planner statistics, with progress output
REPACK (CONCURRENTLY, ANALYZE, VERBOSE) users;

You can follow a running repack in the new pg_stat_progress_repack view. The old VACUUM FULL and CLUSTER commands still work for compatibility, so existing scripts will not break.

ON CONFLICT DO SELECT: get-or-create in one query

A very common task: “insert this tag (or user, or product) if it is new, otherwise give me the existing row”. Before PostgreSQL 19, ON CONFLICT DO NOTHING returned no row when the value already existed, so you had to run a second SELECT — or use a fake update like DO UPDATE SET email = EXCLUDED.email, which creates a dead row every time.

PostgreSQL 19 adds ON CONFLICT … DO SELECT. On a conflict, it simply returns the existing row through RETURNING. Click the tabs below to compare the old and new approaches:

-- Step 1: try to insert, ignore duplicates
INSERT INTO users (email, name)
VALUES ('asha@example.com', 'Asha')
ON CONFLICT (email) DO NOTHING
RETURNING id, email;   -- returns 0 rows if Asha exists!

-- Step 2: so we need another round trip
SELECT id, email FROM users WHERE email = 'asha@example.com';

-- One statement, always returns exactly one row
INSERT INTO users (email, name)
VALUES ('asha@example.com', 'Asha')
ON CONFLICT (email) DO SELECT
RETURNING id, email, plan;

-- Lock the existing row too, if you will update it next
INSERT INTO users (email, name)
VALUES ('asha@example.com', 'Asha')
ON CONFLICT (email) DO SELECT FOR UPDATE
RETURNING id, plan;

// npm install pg
import pg from "pg";

const pool = new pg.Pool({
  connectionString: "postgres://postgres:devdojo@localhost:5433/postgres",
});

export async function getOrCreateUser(email, name) {
  const { rows } = await pool.query(
    `INSERT INTO users (email, name)
     VALUES ($1, $2)
     ON CONFLICT (email) DO SELECT
     RETURNING id, email, plan`,
    [email, name]
  );
  return rows[0]; // always defined: new row or existing row
}

console.log(await getOrCreateUser("asha@example.com", "Asha"));
console.log(await getOrCreateUser("neha@example.com", "Neha"));
await pool.end();

WAIT FOR LSN: read your own writes on replicas

Many apps send writes to the primary server and reads to a read replica to spread the load. The catch is replication lag: a user saves their profile, the page reloads from the replica, and the old data shows up for a moment. It looks like a bug, and users lose trust.

PostgreSQL 19 adds the WAIT FOR LSN command. An LSN (Log Sequence Number) is simply a position in the database’s change log. The idea is simple:

1. After writing on the primary, ask for the current log position with pg_current_wal_lsn().
2. On the replica, run WAIT FOR LSN '…' — it pauses until the replica has replayed up to that point.
3. Now run your SELECT. The user is guaranteed to see their own change.

Here is a complete Python example using psycopg 3. We build the WAIT FOR LSN statement with sql.Literal, because utility commands like this one do not accept normal query parameters.

# pip install "psycopg[binary]"
import psycopg
from psycopg import sql

PRIMARY = "postgresql://postgres:devdojo@primary-host:5432/app"
REPLICA = "postgresql://postgres:devdojo@replica-host:5432/app"

def update_plan_and_read_back(email: str, plan: str):
    # 1. Write on the primary and capture the log position
    with psycopg.connect(PRIMARY, autocommit=True) as primary:
        primary.execute(
            "UPDATE users SET plan = %s WHERE email = %s", (plan, email)
        )
        lsn = primary.execute("SELECT pg_current_wal_lsn()").fetchone()[0]

    # 2. Make the replica wait (max 2 seconds), then 3. read
    with psycopg.connect(REPLICA, autocommit=True) as replica:
        wait = sql.SQL("WAIT FOR LSN {} WITH (TIMEOUT '2s', NO_THROW)").format(
            sql.Literal(str(lsn))
        )
        status = replica.execute(wait).fetchone()[0]
        if status != "success":
            # Replica is too far behind: fall back to the primary
            raise RuntimeError("replica lagging, read from primary instead")
        return replica.execute(
            "SELECT email, plan FROM users WHERE email = %s", (email,)
        ).fetchone()

print(update_plan_and_read_back("ravi@example.com", "pro"))

The command also supports modes: standby_replay (the default — data is visible to queries), standby_write, standby_flush and primary_flush. Without NO_THROW, a timeout raises an error instead of returning timeout.

Smaller SQL features you will use every day

GROUP BY ALL

Tired of copying every column from SELECT into GROUP BY? PostgreSQL 19 adds GROUP BY ALL, which groups by every column in the select list that is not an aggregate (or window function). Other databases like DuckDB and Snowflake already have this, and it is a real time-saver for reports.

-- Before
SELECT country, plan, count(*) AS users
FROM users
GROUP BY country, plan;

-- PostgreSQL 19
SELECT country, plan, count(*) AS users
FROM users
GROUP BY ALL;

COPY to JSON

COPY TO can now write JSON directly — one JSON object per line, or a single JSON array with the FORCE_ARRAY option. Great for quick exports and data hand-offs.

-- One JSON object per row (newline-delimited JSON)
COPY (SELECT id, email, plan FROM users) TO STDOUT (FORMAT json);

-- From psql, save a single JSON array to a file on your machine
\copy (SELECT id, email, plan FROM users) TO 'users.json' (FORMAT json, FORCE_ARRAY)

New jsonpath string methods

If you store JSON in jsonb columns, you can now clean strings right inside a jsonpath expression with lower(), upper(), initcap(), replace(), split_part(), ltrim(), rtrim() and btrim().

SELECT jsonb_path_query('{"city": "  new delhi "}', '$.city.btrim().initcap()');
-- Expected result: "New Delhi"

Handy little helpers

• random() for dates and timestamps — perfect for seed data: SELECT random('2026-01-01'::date, '2026-12-31'::date);
• base64url and base32hex encodings — URL-safe tokens without manual replace calls: SELECT encode(gen_random_bytes(16), 'base64url'); (gen_random_bytes comes from the pgcrypto extension).
• Casts between bytea and uuid — useful when another system hands you raw 16-byte IDs.
• Faster foreign-key checks — inserts that check foreign keys can be up to twice as fast, with no code change on your side.

Partition merge/split and graph queries

MERGE and SPLIT partitions

Partitioned tables (for example, one partition per month of logs) are great, but reshaping them used to mean creating new tables and moving data by hand. PostgreSQL 19 adds two commands:

CREATE TABLE metrics (
  recorded_at timestamptz NOT NULL,
  value       numeric
) PARTITION BY RANGE (recorded_at);

CREATE TABLE metrics_jan PARTITION OF metrics
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE metrics_feb PARTITION OF metrics
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Combine two small monthly partitions into one
ALTER TABLE metrics MERGE PARTITIONS (metrics_jan, metrics_feb)
  INTO metrics_jan_feb;

-- Split it back into months later
ALTER TABLE metrics SPLIT PARTITION metrics_jan_feb INTO
  (PARTITION metrics_jan FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'),
   PARTITION metrics_feb FOR VALUES FROM ('2026-02-01') TO ('2026-03-01'));

Property graph queries (SQL/PGQ)

PostgreSQL 19 supports the SQL-standard way to query relationships as a graph — think “who follows whom” or “which services call which”. You define a graph on top of normal tables, then use GRAPH_TABLE with a MATCH pattern:

CREATE TABLE people  (id int PRIMARY KEY, name text);
CREATE TABLE follows (follower int REFERENCES people, followee int REFERENCES people,
                      PRIMARY KEY (follower, followee));

INSERT INTO people VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Emma');
INSERT INTO follows VALUES (1, 2), (2, 3), (1, 3);

CREATE PROPERTY GRAPH social
  VERTEX TABLES (people KEY (id) LABEL person PROPERTIES (name))
  EDGE TABLES (follows KEY (follower, followee)
    SOURCE KEY (follower) REFERENCES people (id)
    DESTINATION KEY (followee) REFERENCES people (id)
    LABEL follows);

SELECT * FROM GRAPH_TABLE (social
  MATCH (a IS person)-[IS follows]->(b IS person)
  COLUMNS (a.name AS follower, b.name AS following));

Your data still lives in ordinary tables, so you keep transactions, indexes and backups — you just get a much more readable way to express relationship queries.

Operations: autovacuum, checksums and monitoring

Even if you are mainly an app developer, these changes make your database healthier with less effort:

• Parallel autovacuum: autovacuum can now use parallel workers for big tables. It is off by default — the server-wide limit autovacuum_max_parallel_workers starts at 0.
• Smarter vacuum order: a new scoring system decides which tables need vacuuming most urgently.
• Online data checksums: you can turn page checksums (which detect silent disk corruption) on or off without re-creating the cluster or restarting.
• New monitoring views: pg_stat_lock shows statistics per lock type, and pg_stat_recovery shows replica recovery state.
• Logical replication of sequences: sequence values are now replicated, which makes upgrades and failovers via logical replication much safer.
• EXPLAIN (ANALYZE, IO): shows statistics for the asynchronous I/O system introduced in PostgreSQL 18.

-- Allow up to 2 parallel autovacuum workers server-wide
ALTER SYSTEM SET autovacuum_max_parallel_workers = 2;
SELECT pg_reload_conf();

-- Turn on data checksums in the background (no restart needed)
SELECT pg_enable_data_checksums();
SHOW data_checksums;   -- shows the progress state, then "on"

-- Which lock types cause the most waiting?
SELECT * FROM pg_stat_lock;

Changed defaults: read this before upgrading

Some defaults changed in PostgreSQL 19. Most are improvements, but a couple can surprise you after an upgrade:

Common errors and fixes

Best practices for upgrading

1. Test on a copy first. Restore last night’s backup into a PostgreSQL 19 instance and run your test suite against it. A CI pipeline makes this painless — see our GitHub Actions CI/CD guide for Node.js to run tests against a Postgres service container.
2. Check extensions. Make sure extensions like PostGIS, pgvector or TimescaleDB have released PostgreSQL 19 builds before you upgrade.
3. Review pg_hba.conf. Remove RADIUS lines and move md5 users to scram-sha-256.
4. Benchmark analytics queries. Compare timings with JIT on and off before deciding.
5. Adopt features gradually. Swap the riskiest workaround first — usually the double-query “get or create” or a nightly VACUUM FULL job.
6. Wait for the first minor release for critical systems. Many teams upgrade production at x.1 or x.2, after early bugs are fixed.

Using Python? Pair this upgrade with the language improvements in our Python 3.15 new features guide.

FAQ

Conclusion

The PostgreSQL 19 new features are very practical. REPACK (CONCURRENTLY) removes the scariest maintenance job, ON CONFLICT DO SELECT and GROUP BY ALL make everyday SQL shorter, and WAIT FOR LSN solves the classic “I saved it but it didn’t change” bug with read replicas. Spin up the Docker container, run the examples above, and make a short upgrade checklist for your team — especially around JIT, RADIUS and your extensions.

Want to go deeper? Read the official REPACK documentation and the WAIT FOR LSN reference.