PostgreSQL 19 new features: what to test before you upgrade

PostgreSQL 19 new features have been written about since the first beta in June, and a fair share of those articles now describe a release that will not ship. The first release candidate came out on October 15, general availability is planned for November 19, 2026, and between the betas and RC1 the project pulled several large features, including SQL/PGQ graph queries. If you run a SaaS backend on Postgres, now is the right time to test, and the right list to test against is the one in RC1.

This guide covers the PostgreSQL 19 release date and status, the new features that matter to application developers (each one run on PostgreSQL 19 RC1 against a small table of daily app and extension stats), the operational improvements, and the compatibility changes that can break an app on upgrade. It ends with a checklist you can work through before November 19.

Key takeaways

• PostgreSQL 19 RC1 shipped on October 15, 2026. RC2 is planned for November 12 and general availability for November 19, 2026.
• SQL/PGQ property graph queries were reverted in September and are not in PostgreSQL 19. GROUP BY ALL is not in RC1 either.
• The developer features worth testing now: IGNORE NULLS in window functions, INSERT ... ON CONFLICT DO SELECT ... RETURNING, COPY TO in JSON, COPY FROM with ON_ERROR SET_NULL, and REPACK (CONCURRENTLY).
• The changes most likely to bite: JIT is now off by default, max_locks_per_transaction doubled with a new meaning, standard_conforming_strings can no longer be turned off, and an empty json_array() subquery returns [] instead of NULL.

PostgreSQL 19 release date and status#

The PostgreSQL 19 RC1 announcement sets out the remaining schedule. Because the next scheduled update release for supported versions, on November 12, 2026, "will include a large number of fixes", the release team will ship RC2 on the same day, and "unless an issue is discovered that requires a delay or an additional release candidate, PostgreSQL 19 should be made generally available on November 19, 2026."

That is later than usual. Recent major versions have shipped in late September or early October, and The Register reports that the delay came with an unusually large set of reverts: between eight and ten significant features moved to PostgreSQL 20. According to the article, part of the reason is that LLM-assisted code review found many edge-case bugs late in the cycle, and fixing a large batch of those reports took more time than the schedule allowed. Committer Peter Eisentraut described them as "relatively harmless issues involving various edge cases" that review now finds much faster. Tomas Vondra's post Are we reverting patches because of bugs found by AI? gives a committer's view of the same question.

What is not in PostgreSQL 19#

The most visible removal is SQL/PGQ, the SQL-standard syntax for property graph queries. It was in Beta 1 and was reverted on September 7, 2026 in a commit that rolls back about 60 earlier commits. Several early "what's new" articles also list GROUP BY ALL. Neither is in RC1: on our RC1 install, CREATE PROPERTY GRAPH and GROUP BY ALL both failed with a syntax error.

If you planned around a feature you read about in June, check it against the current PostgreSQL 19 release notes. They are marked "as of 2026-10-09" and can still change before release.

PostgreSQL 19 new features for application developers#

We installed the official PGDG packages for 19 RC1 and tested each feature below against a table of the kind an analytics backend keeps: one row per listing per day, with gaps on days when a store did not return data.

CREATE TABLE listing_snapshots (
  listing_id text NOT NULL,
  day        date NOT NULL,
  users      integer,
  rating     numeric(3,2),
  version    text,
  PRIMARY KEY (listing_id, day)
);

IGNORE NULLS in window functions#

PostgreSQL 19 lets lead(), lag(), first_value(), last_value() and nth_value() skip NULLs with IGNORE NULLS. This is the feature we expect most analytics code to use, because it solves the "compare with the last known value" problem without a self-join or a subquery:

SELECT day, users,
       lag(users)              OVER w AS prev_plain,
       lag(users) IGNORE NULLS OVER w AS prev_known,
       users - lag(users) IGNORE NULLS OVER w AS change
FROM listing_snapshots
WINDOW w AS (PARTITION BY listing_id ORDER BY day)
ORDER BY day;

On RC1, with missing values on October 16 and 18:

Plain lag() returns NULL on the day after a gap, so a day-over-day change disappears. IGNORE NULLS compares each reading with the last real one, which is exactly what a change alert needs. Our guide to PostgreSQL time series without TimescaleDB solves the same problem on older versions with more SQL; on 19 it is one keyword.

INSERT ... ON CONFLICT DO SELECT ... RETURNING#

Idempotent inserts are everywhere in a backend that ingests data on a schedule. Until now, ON CONFLICT DO NOTHING returned no row on conflict, so code that needed the existing row ran a second query. PostgreSQL 19 adds ON CONFLICT DO SELECT, which returns the conflicting row and can lock it with FOR UPDATE or FOR SHARE:

INSERT INTO listing_snapshots (listing_id, day, users)
VALUES ('ext-a', '2026-10-19', 99999)
ON CONFLICT (listing_id, day) DO SELECT
RETURNING listing_id, day, users;

On RC1 this returned the existing row, ext-a | 2026-10-19 | 10190, and left it unchanged. For a new key it inserts and returns the new row as usual. This simplifies "get or create" code for daily snapshots, job runs and webhook deduplication.

COPY TO in JSON#

COPY TO can now write JSON, one object per line, and FORCE_ARRAY wraps the output in a single JSON array:

COPY (SELECT day, users FROM listing_snapshots WHERE users IS NOT NULL ORDER BY day LIMIT 2)
TO STDOUT (FORMAT json, FORCE_ARRAY);

[
 {"day":"2026-10-15","users":10200}
,{"day":"2026-10-17","users":10350}
]

NULLs come out as null and numerics keep their scale (4.60, not 4.6). An export endpoint or a nightly backup to object storage no longer needs a script to turn rows into JSON. One RC1 fix matters here: JSON keys now use the query's column names, including aliases.

COPY FROM with ON_ERROR SET_NULL#

Imports of messy CSV no longer have to fail on one bad cell. ON_ERROR SET_NULL sets the offending column to NULL and keeps the row:

COPY import_stage FROM STDIN (FORMAT csv, ON_ERROR set_null);
-- ext-a,10200,4.6
-- ext-b,n/a,4.1
-- ext-c,880,unknown

RC1 loaded all three rows, set users to NULL for ext-b and rating to NULL for ext-c, and reported: "in 2 rows, columns were set to null due to data type incompatibility". COPY FROM can also skip more than one header line now.

Smaller additions worth knowing#

• Casts between uuid and bytea, handy when IDs are stored in binary in one place and as UUIDs in another.
• random(min, max) for date, timestamp and timestamptz, useful for seeding test data.
• encode() and decode() in base64url, the format used in JWTs and URL-safe tokens.
• New jsonpath string methods: lower(), upper(), replace(), split_part(), btrim() and others.
• error_on_null(), which returns its argument or raises an error on NULL.

Operational improvements#

These need no code changes, but they change how you run Postgres:

• REPACK. A new REPACK command replaces VACUUM FULL and CLUSTER (both remain for compatibility), and REPACK (CONCURRENTLY) rebuilds a table without blocking reads and writes. On our RC1 install, both forms ran on a default configuration with wal_level = replica. For append-heavy stats tables that bloat after you delete old rows, it can replace a separate pg_repack setup.
• Smarter autovacuum. Autovacuum can vacuum a table's indexes with parallel workers, and a new scoring system decides which tables to process first. A new pg_stat_autovacuum_scores view shows the scores.
• Cheaper NOTIFY. NOTIFY now wakes only the backends listening to that channel. Apps that use LISTEN/NOTIFY for job queues or cache invalidation with many connections should see less overhead.
• Read-your-writes on replicas. A new WAIT command lets a standby wait until it has replayed changes up to a given point, so you can read from a replica right after a write without seeing stale data.
• Logical replication without a restart. With wal_level = replica, logical replication is enabled automatically when needed, and sequence values now replicate. Both make blue-green upgrades through logical replication easier.
• lz4 by default. default_toast_compression is now lz4, and wal_compression = on picks the best available method.
• Better EXPLAIN. EXPLAIN (ANALYZE, IO) reports asynchronous I/O activity, and the I/O worker pool now scales between io_min_workers and io_max_workers.

Changes that can break your app on upgrade#

The release notes list the incompatibilities in their migration section. These are the ones most likely to affect a typical SaaS backend:

We also confirmed one more on RC1: SET standard_conforming_strings = off now fails with "non-standard string literals are not supported".

PostgreSQL 19 upgrade checklist for a SaaS backend#

1. Run your test suite against RC1 now. The PGDG apt repository carries RC1 packages, which is what we used. Problems found now can still be reported and fixed before general availability.
2. Check every extension. Each one needs a build for 19. Some already have one: pg_hint_plan 1.9.0, released on October 18, "adds compatibility with PostgreSQL 19". If you use pgvector for similarity search, check its release notes for 19 support before you schedule the upgrade.
3. Run pg_upgrade --check on a copy of production. It catches the btree_gist and naming problems above before the real run.
4. Re-dump with the new tools. If you migrate with dump and restore, use the PostgreSQL 19 pg_dump, as the release notes recommend.
5. Benchmark the queries behind your dashboards with JIT off. Daily rollups over months of snapshots are the kind of query JIT used to kick in for.
6. Update monitoring. Fix queries that read renamed columns, and add pg_stat_autovacuum_scores and pg_stat_lock to what you watch.
7. Test your driver and ORM. Run the app's integration tests against 19, not only the SQL. If you are also moving the runtime, our Node.js 26 LTS upgrade checklist covers that side; doing both at once makes failures harder to attribute.
8. Decide when, not just whether. Managed providers often add a new major version some weeks after release, and many teams wait for the first minor release before upgrading production. Use the time to adopt one feature, such as IGNORE NULLS in your change detection, in a branch that is ready to merge.

What this means for app and extension analytics#

Most of what an analytics backend does is append a row per item per day and compare it with the previous one. PostgreSQL 19 makes both halves simpler: ON CONFLICT DO SELECT for idempotent ingestion, IGNORE NULLS for comparisons across gaps, COPY ... FORMAT json for exports, and REPACK (CONCURRENTLY) for keeping a large history table compact. A table like the one in our guide to App Store version history, keyed on app, storefront and version, benefits from the first two directly. If you store the same data in MongoDB instead, our post on MongoDB time series collections for extension stats covers that route.

If you would rather not run that pipeline at all, Extenify does it for you. The app and extension analytics dashboard records installs, users, ratings, reviews and versions for Chrome Web Store, Edge Add-ons, Firefox Add-ons, Google Play and App Store listings every day, compares each day with the last real reading, and sends alerts when something moves past a threshold. Every plan starts with a 7-day free trial with no credit card required; see Extenify pricing for history length and CSV export. For deciding which numbers deserve a chart in the first place, our list of Chrome extension metrics to track every week is a good starting point.

Frequently asked questions#

When is PostgreSQL 19 released?#

General availability is planned for November 19, 2026, according to the RC1 announcement. RC1 came out on October 15, and RC2 is planned for November 12, alongside the next update release for supported versions. The date can move if another release candidate is needed.

Is SQL/PGQ in PostgreSQL 19?#

No. SQL Property Graph Queries were reverted on September 7, 2026, after the third beta, and are not in RC1. The Register reports that the reverted features are expected in PostgreSQL 20.

Does PostgreSQL 19 support GROUP BY ALL?#

Not in RC1. On our RC1 install, GROUP BY ALL without a column list returned a syntax error, and it is not in the current release notes.

What is REPACK in PostgreSQL 19?#

A new command that reclaims space and reorders a table, replacing VACUUM FULL and CLUSTER, which still work for compatibility. REPACK (CONCURRENTLY) does it without blocking reads and writes to the table.

Is JIT disabled in PostgreSQL 19?#

Yes, by default. The release notes say JIT's costing "has been determined to be unreliable", so sites that run many large analytical queries have to turn it on themselves.

Can I upgrade from PostgreSQL 18 to 19 with pg_upgrade?#

Yes. A major upgrade needs pg_upgrade, a dump and restore, or logical replication. Run pg_upgrade --check first: PostgreSQL 19 refuses to upgrade clusters with btree_gist inet/cidr indexes or with carriage returns or line feeds in database, role or tablespace names.

Summary#

PostgreSQL 19 is due on November 19, 2026, later than usual and smaller than the early previews promised: SQL/PGQ is gone, and GROUP BY ALL is not in RC1. What remains is practical. IGNORE NULLS, ON CONFLICT DO SELECT, JSON output for COPY and ON_ERROR SET_NULL remove boilerplate from every ingestion and reporting job, and REPACK (CONCURRENTLY), parallel autovacuum and cheaper NOTIFY make large tables easier to run. Before you upgrade, test on RC1, check your extensions, benchmark analytical queries with JIT off, double any custom max_locks_per_transaction, and run pg_upgrade --check on a copy of production.

Image credits#

• Cover photo: African bull elephant on a dirt track by Mutebi Ivan Junior on the WordPress Photo Directory, CC0.