PostgreSQL 19: the new features for analysts and data engineers

PostgreSQL 19 is in its final testing stage: Beta 4 was released on 24 September 2026, and the final release is expected later in 2026. PostgreSQL 18 remains the current stable version, so this is the right moment to see what is coming and plan your upgrade. Here are the features that matter most if you write SQL for analytics or move data around.

IGNORE NULLS in window functions

A long-awaited one for analysts. Window functions such as lag(), lead(), first_value(), last_value() and nth_value() can now skip missing values. The classic use is carrying the last known value forward, for example a sensor reading or a price that is only recorded when it changes.

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

Before, this needed a subquery that counted groups of rows, which was hard to read and easy to get wrong. RESPECT NULLS stays the default, so existing queries behave exactly as before. New to window functions? Start with SQL window functions, explained.

COPY straight to JSON

COPY can now export tables as JSON. By default it writes one JSON object per line (NDJSON), the format most data tools and APIs expect; FORCE_ARRAY wraps everything in a single JSON array instead.

-- One JSON object per line
COPY users TO STDOUT WITH (FORMAT JSON);

-- A single JSON array
COPY users TO STDOUT WITH (FORMAT JSON, FORCE_ARRAY);

COPY also works directly on partitioned tables now, so exporting a large partitioned fact table no longer needs a SELECT wrapped around it.

Get-or-create in one statement: ON CONFLICT DO SELECT

Loading dimension data often needs "insert this customer if new, otherwise give me the existing row". Until now, the usual trick was a do-nothing DO UPDATE, which writes to the table for no reason. PostgreSQL 19 adds a cleaner option:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO SELECT
RETURNING *;

It returns the existing row when the email already exists, without updating anything. Benchmarks reported by Neon show it nearly four times faster than the old workaround.

Time-aware updates and deletes

For tables that store values over time, such as prices, contracts or exchange rates, UPDATE ... FOR PORTION OF changes only part of a period. PostgreSQL splits the row automatically, so the dates before and after keep their original values.

UPDATE product_prices
FOR PORTION OF valid_range FROM '2025-07-01' TO '2025-10-01'
SET price = 34.99
WHERE product_id = 1;

This builds on the temporal constraints added in PostgreSQL 18, and removes a lot of fiddly logic from slowly changing dimension and pricing pipelines. If those terms are new, see Star schema data modelling.

Better for big tables and busy databases

• Parallel autovacuum: clean-up of large tables can use several workers. It is off by default; enable it with autovacuum_max_parallel_workers.
• Online table rebuilds: a new REPACK command can rebuild bloated tables while they stay in use, something that used to need an extension.
• Plan advice: the new pg_plan_advice module lets you capture and reuse query plans, useful when one important report query suddenly changes plan.
• DDL extraction: functions such as pg_get_role_ddl() return the SQL to recreate databases and roles, handy for documentation and migrations.
• Logical replication can now include sequences, which makes major-version upgrades with minimal downtime simpler.

Changes to check before upgrading

• JIT is off by default. According to Neon's feature guide, the jit setting now defaults to off. Heavy analytical queries that benefited from JIT should be tested, and JIT re-enabled if they are slower.
• LZ4 is the default compression for large values stored out of line (TOAST).
• Graph queries were pulled. SQL/PGQ graph queries (GRAPH_TABLE) were in early betas but were reverted on 7 September 2026, and will not ship in PostgreSQL 19.

When to upgrade

Do not run a beta in production. Once the final release is out, the usual safe path is to wait for the first minor release (19.1), test your heaviest queries and extensions on a copy of production, and then upgrade. If you are still on PostgreSQL 14, note that it stops receiving fixes on 12 November 2026, so plan that upgrade first.

Choosing between PostgreSQL and other databases? See Choosing the right database. To practise the window functions above, try the SQL puzzles.

Sources: postgresql.org release news and beta announcements, Neon's PostgreSQL 19 feature guide and Bytebase's PostgreSQL 19 overview. Features can still change before the final release.

Alessandro Ecclesie AgazziWritten by Alessandro Ecclesie Agazzi, analytics engineer in London. Updated 13 October 2026. About me