PostgreSQL 19 introduces a range of performance improvements across query execution, data loading, sorting, index maintenance, and internal database operations.
Some changes reduce CPU overhead, while others improve parallel execution, asynchronous I/O, or the efficiency of frequently executed operations. The impact depends on the workload, data distribution, hardware, and database configuration.
In this article, we explore ten important performance-related changes, explain how PostgreSQL 18 and PostgreSQL 19 differ, and provide SQL examples you can use to investigate the improvements.
We also cover practical limitations, benchmarking considerations, and upgrade checks to help DBAs and developers evaluate these changes in their own environments.
Release note: The examples and feature descriptions should be validated against the specific PostgreSQL 19 build being tested. PostgreSQL 19 RC 1 is not the same as the final general-availability release.
PG18 vs PG19 at a glance
| Area | PG18 | PG19 |
| Foreign key checks | RI trigger runs an internal query through SPI(Server Programming Interface ) for every referencing row | Direct probe of the referenced table’s unique index, bypassing SPI(Server Programming Interface ) (non-partitioned parent, non-temporal FK) |
| COPY FROM (text/CSV) | Byte-at-a-time parser state machine | SIMD (Single Instruction, Multiple Data ) scan skips whole chunks that contain no special characters |
| Row (tuple) deformation | Inlined loops and CompactAttribute from the PG18 work | Precomputed attribute offsets and better handling of tuples that contain NULLs |
| UTF-8 case operations (ICU) International Components for Unicode | Converts UTF-8 to UTF-16 and back on every call | Uses ICU’s UTF-8 case-map API; the case-map object is created once |
| Sorting | Quicksort with per-type specializations | Radix sort when the first sort key is a pass-by-value, integer-like datum; quicksort for ties |
| TID range scans | Single process only | Parallel Tid Range Scan |
| GIN cleanup/hash bulk-delete | Synchronous page-by-page reads | Streaming reads |
| PL/pgSQL SELECT expr INTO var | Always runs through SPI | Fast path for simple expressions into a single variable |
| NOTIFY | Signals all listening backends | Shared channel map: wakes only interested listeners |
| Object identifiers | 32-bit oid only | New 64-bit oid8 |
1. Faster foreign key checks
PG18: Every insert or update of a referencing row fires an internal RI trigger. That trigger ran a small internal query against the referenced table through SPI (the server’s internal SQL interface) just to confirm the parent key exists. It is correct, but each check pays executor setup and teardown costs, which adds up in bulk loads.
PG19: A new fast path (commit 2da86c1ef, with a follow-up fix in e484b0eea) skips SPI and probes the referenced table’s unique index directly. The commit message describes it as mirroring the SPI path’s behavior (snapshot, permission checks, locking). It applies when the referenced table is not partitioned and the constraint does not use temporal semantics. Everything else, including the action triggers that fire from the parent side (ON DELETE / ON UPDATE), still goes through SPI.
A note on batching. Earlier in the cycle a second commit added batched index probes, buffering FK rows and probing them in groups. That was removed before release (a “Remove batching from RI fast-path checks” commit in September 2026) because of correctness risks around nested triggers, subtransactions, deferred constraints and SET CONSTRAINTS. The PG19 release notes list only the per-row fast path, so be careful with older posts that quote batching numbers.
Run the same script on PG18 and PG19:
CREATE TABLE customers (
customer_id bigint PRIMARY KEY,
name text
);
CREATE TABLE orders (
order_id bigint PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (customer_id),
amount numeric
);
INSERT INTO customers
SELECT g, 'customer ' || g FROM generate_series(1, 100000) g;Warm the cache so you measure the check, not the disk
SELECT pg_prewarm('customers'), pg_prewarm('customers_pkey');
pg_prewarm | pg_prewarm
------------+------------
637 | 276
postgres=# EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO orders
SELECT g, (g % 100000) + 1, random() * 100
FROM generate_series(1, 1000000) g;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Insert on orders (cost=0.00..27500.00 rows=0 width=0) (actual time=3899.159..3899.160 rows=0.00 loops=1)
Buffers: shared hit=2178044 read=1 dirtied=10098 written=10101, temp read=1709 written=1709
-> Subquery Scan on unnamed_subquery (cost=0.00..27500.00 rows=1000000 width=48) (actual time=80.789..1466.521 rows=1000000.00 loops=1)
Buffers: temp read=1709 written=1709
-> Function Scan on generate_series g (cost=0.00..20000.00 rows=1000000 width=16) (actual time=80.764..276.023 rows=1000000.00 loops=1)
Buffers: temp read=1709 written=1709
Planning Time: 0.161 ms
Trigger for constraint orders_customer_id_fkey: time=2324.744 calls=1000000
Execution Time: 6294.857 ms
(9 rows)In the output, compare the line Trigger for constraint orders_customer_id_fkey: time=… calls=1000000. That trigger time is the FK-check cost. (pg_prewarm needs CREATE EXTENSION pg_prewarm; first.)
To find foreign keys that will not get the fast path because the referenced table is partitioned:
SELECT c.conname,
c.conrelid::regclass AS referencing_table,
c.confrelid::regclass AS referenced_table,
p.relkind
FROM pg_constraint c
JOIN pg_class p ON p.oid = c.confrelid
WHERE c.contype = 'f';
conname | referencing_table | referenced_table | relkind
-------------------------+-------------------+------------------+---------
orders_customer_id_fkey | orders | customers | r2. SIMD-accelerated COPY FROM
PG18: The text and CSV parser walks the input one byte at a time through a state machine, checking whether each byte is a newline, carriage return, quote, escape or delimiter. Many branches per byte means a lot of CPU work, and branch mispredictions exist on top.
PG19: Commit e0a3a3fd5 (Nazir Bilal Yavuz, Shinya Kato) uses SIMD instructions. It loads a vector of input bytes, compares them against the special characters in a few vector operations, and skips the whole chunk when none are present. When a special character shows up, the parser falls back to the scalar path for it.
What this means in practice:
- Wide rows with long runs of plain characters benefit most. Input dense with special characters (lots of quotes, escapes or very short fields) has less to skip. The patch history includes work specifically to avoid regressions there.
- It applies to FORMAT text and FORMAT csv on the way in; binary format is a different path.
- If you tested early PG19 betas on COPY … FROM PROGRAM or from a pipe, note that a read-ahead problem that could hang text-format COPY on a pipe was fixed in September 2026. Retest on RC 1 or later.
CREATE TABLE copy_bench (id bigint, payload text, created timestamptz);
INSERT INTO copy_bench
SELECT g, repeat(md5(g::text), 4), now() - (g || ' seconds')::interval
FROM generate_series(1, 5000000) g;
-- Server-side file (needs superuser or pg_write_server_files)
COPY copy_bench TO '/tmp/copy_bench.csv' (FORMAT csv);
TRUNCATE copy_bench;
\timing on
COPY copy_bench FROM '/tmp/copy_bench.csv' (FORMAT csv);
-- or from a client: \copy copy_bench FROM 'copy_bench.csv' (FORMAT csv)
COPY copy_bench FROM '/tmp/copy_bench.csv' (FORMAT csv);
COPY 5000000
Time: 12893.239 ms (00:12.893)Run it three or four times on each version, truncating in between. Try a second file with short, quote-heavy fields to see how the gain shrinks.
Other COPY changes in PG19 worth a look while you are here:
Set invalid input values to NULL instead of failing the load
COPY copy_bench FROM '/tmp/copy_bench.csv' (FORMAT csv, ON_ERROR SET_NULL);
COPY 5000000
Time: 11382.503 ms (00:11.383)JSON output (new). FORCE_ARRAY wraps the rows in one JSON array
COPY (SELECT id, payload FROM copy_bench LIMIT 3) TO STDOUT (FORMAT json, FORCE_ARRAY true);
[
{"id":1,"payload":"c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b"}
,{"id":2,"payload":"c81e728d9d4c2f636f067f89cc14862cc81e728d9d4c2f636f067f89cc14862cc81e728d9d4c2f636f067f89cc14862cc81e728d9d4c2f636f067f89cc14862c"}
,{"id":3,"payload":"eccbc87e4b5ce2fe28308fd9f2a7baf3eccbc87e4b5ce2fe28308fd9f2a7baf3eccbc87e4b5ce2fe28308fd9f2a7baf3eccbc87e4b5ce2fe28308fd9f2a7baf3"}
]
Time: 6.810 msCOPY TO now accepts a partitioned table directly (previously COPY (SELECT …) was required)
PG19 also lets COPY FROM skip more than one header line; check the COPY reference page for the exact HEADER syntax.
3. Faster internal row deformation
“Deforming” a tuple means turning the on-disk heap tuple into column values you can use. Nearly every query that reads a table does it, and it gets more expensive as rows get wider.
PG18 already included earlier work from David Rowley: CompactAttribute, faster offset alignment, and dedicated deform loops for tuples without NULLs.
PG19 (commit c456e3911) goes further:
- Attribute offsets (attcacheoff) are precalculated when the tuple descriptor is finalized, instead of being discovered while deforming.
- A dedicated loop deforms every attribute up to the first one without a cached offset.
- Tuples that do contain NULLs get better treatment: the code can deform up to the first NULL without NULL checks instead of dropping into the slow path for the whole tuple.
In the author’s proposal on the mailing list, a benchmark designed to maximize the deforming share of runtime averaged about 21% faster, with some tests more than 44% faster and no regressions. Treat that as a best case for deform-heavy workloads, not a promise for every query.
Try it. This table is wide and mostly NULL, so reading the last column forces the executor to walk the row:
DO $$
DECLARE cols text;
BEGIN
SELECT string_agg(format('c%s int', i), ', ') INTO cols FROM generate_series(1, 40) i;
EXECUTE format('CREATE TABLE wide (id bigint, %s)', cols);
END $$;
INSERT INTO wide (id, c40)
SELECT g, g FROM generate_series(1, 2000000) g;
VACUUM ANALYZE wide;
SET max_parallel_workers_per_gather = 0; -- keep runs comparable
SET jit = off;
EXPLAIN (ANALYZE, TIMING OFF) SELECT sum(c40) FROM wide;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Aggregate (cost=37739.00..37739.01 rows=1 width=8) (actual rows=1.00 loops=1)
Buffers: shared hit=12739
-> Seq Scan on wide (cost=0.00..32739.00 rows=2000000 width=4) (actual rows=2000000.00 loops=1)
Buffers: shared hit=12739
Planning:
Buffers: shared hit=7 read=1
Planning Time: 0.416 ms
Execution Time: 203.531 ms
(8 rows)Design tip that still holds: offset caching stops at the first variable-length column, so put fixed-width, NOT NULL columns first in hot, wide tables.
4. Faster UTF-8 case folding
PG18: With the ICU provider, lower(), upper(), initcap() and casefold() converted the string from UTF-8 to ICU’s UTF-16 form, applied the case mapping, and converted back, on every call. (casefold() itself was added in PG18.)
PG19: Commit c4ff35f10 (Andreas Karlsson) creates an ICU UCaseMap object once and uses ICU’s UTF-8-optimized APIs, so those round-trip conversions disappear. The release notes describe it as faster repeated UTF-8 case-folding. In the patch discussion, the author reported roughly 15-20% in the first version and up to 30-40% on short-to-medium strings in later ones; the exact gain depends on your strings and ICU version.
Where it matters: lower(col) expression indexes (the classic ORM pattern), lower(a) = lower(b) filters and joins, and then bulk normalization happens. It applies to ICU collations in UTF-8 databases; the libc provider is not affected.
Is your database using ICU?
pg18:
postgres=# \timing on
Timing is on.
postgres=# SELECT count(lower(('Åsa Öberg ' || i) COLLATE "sv-SE-x-icu"))
FROM generate_series(1, 1000000) i;
count
---------
1000000
(1 row)
Time: 793.873 ms
postgres=# CREATE INDEX customers_lower_name_idx ON customers (lower(name));
CREATE INDEX
Time: 569.498 ms
postgres=# SELECT * FROM customers WHERE lower(name) = lower('CUSTOMER 4242');
customer_id | name
-------------+---------------
4242 | customer 4242
(1 row)
Time: 1.260 mspg19:
SELECT datname, datlocprovider FROM pg_database WHERE datname = current_database();
datname | datlocprovider
----------+----------------
postgres | c
\timing on
SELECT count(lower(('Åsa Öberg ' || i) COLLATE "sv-SE-x-icu"))
FROM generate_series(1, 1000000) i;
count
---------
1000000
(1 row)
Time: 367.074 ms
Typical expression-index pattern (database with an ICU default collation)
CREATE INDEX customers_lower_name_idx ON customers (lower(name));
SELECT * FROM customers WHERE lower(name) = lower('CUSTOMER 4242');
customer_id | name
-------------+---------------
4242 | customer 4242
(1 row)
Time: 0.867 msThe benchmark results indicate that PostgreSQL 19 performs better than PostgreSQL 18 for the tested string-processing and indexed-search operations, reducing execution times from 793.873 ms to 367.074 ms and from 1.260 ms to 0.867 ms, respectively. However, the database reports libc as its default locale provider, which does not exclude the use of explicitly specified ICU collations such as "sv-SE-x-icu", PostgreSQL 19 is reported to update its Unicode tables to version 17.0.0.
5. Radix sort
PG18: tuple sorting used quicksort, with specialized comparison routines for common integer-like key types.
PG19 (commit ef3c3cf6d, John Naylor) adds a radix sort. Radix sort works on unsigned bytes, so the sort key is first “normalized”:
- Signedness: signed integers are shifted so they compare as unsigned while keeping their order.
- Direction: DESC is handled by taking the complement of the unsigned value.
- NULLs: rows are partitioned by nullness first so NULLS FIRST / NULLS LAST still work.
The non-NULL rows are then radix sorted on the first key. It applies when that first key is a pass-by-value datum that compares like an integer (abbreviated keys qualify; numeric abbreviations are the stated counterexample). Ties on later sort keys go to the usual quicksort, and small arrays still use quicksort. The implementation is an in-place radix sort in the style of American flag sort.
Per the commit message, ORDER BY queries on pre-warmed buffers are up to 2x faster on high-cardinality inputs than with the old specializations (which were removed). Moderately low-cardinality inputs gain less, and for very low-cardinality inputs it is usually equal or very close to quicksort.
The catch: tie order can change. Radix sort and quicksort are both unstable. A query like SELECT a, b FROM t ORDER BY a has no guaranteed order of b within each value of a, and PG19 may return those ties in a different order than PG18 did. The commit even had to adjust regression test output for exactly this reason. If you diff query results across an upgrade, add a tiebreaker.
CREATE TABLE sort_demo AS
SELECT (random() * 1e9)::bigint AS k, g AS id, md5(g::text) AS pad
FROM generate_series(1, 5000000) g;
SET work_mem = '512MB'; -- keep the sort in memory
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT k, id FROM sort_demo ORDER BY k;
-- high cardinality
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Sort (cost=690001.18..703217.66 rows=5286592 width=12) (actual time=7709.134..11635.391 rows=5000000.00 loops=1)
Sort Key: k
Sort Method: quicksort Memory: 352859kB
Buffers: shared hit=44589 read=46784 dirtied=29101 written=32118
-> Seq Scan on sort_demo (cost=0.00..99649.92 rows=5286592 width=12) (actual time=0.265..4003.839 rows=5000000.00 loops=1)
Buffers: shared hit=44589 read=46784 dirtied=29101 written=32118
Planning:
Buffers: shared hit=13 read=3
Planning Time: 1.080 ms
Execution Time: 14630.299 ms
(10 rows)
Time: 14631.774 ms (00:14.632)
EXPLAIN (ANALYZE, BUFFERS) SELECT k % 10 AS bucket, id FROM sort_demo ORDER BY 1; -- low cardinality
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Sort (cost=665627.70..678127.82 rows=5000048 width=12) (actual time=6662.205..10148.620 rows=5000000.00 loops=1)
Sort Key: ((k % '10'::bigint))
Sort Method: quicksort Memory: 352859kB
Buffers: shared hit=16213 read=30752 dirtied=149
-> Seq Scan on sort_demo (cost=0.00..109284.60 rows=5000048 width=12) (actual time=0.101..3228.377 rows=5000000.00 loops=1)
Buffers: shared hit=16213 read=30752 dirtied=149
Planning:
Buffers: shared hit=8 read=1
Planning Time: 0.438 ms
Execution Time: 13132.476 ms
(10 rows)
Time: 13138.401 ms (00:13.138)Deterministic version: always add a tiebreaker
pg18:
postgres=# SELECT k, id FROM sort_demo ORDER BY k, id LIMIT 10;
k | id
------+---------
65 | 4513947
74 | 4833635
295 | 753138
742 | 2302433
790 | 4712453
805 | 3477576
825 | 982721
844 | 176036
867 | 334587
1120 | 4284974
(10 rows)
Time: 753.475 mspg19:
postgres=# SELECT k, id FROM sort_demo ORDER BY k, id LIMIT 10;
k | id
------+---------
643 | 4457066
703 | 2049833
1062 | 1919569
1561 | 1530827
2242 | 740757
3013 | 4242112
3066 | 3593391
3102 | 3089690
3315 | 69382
3379 | 652928
(10 rows)
Time: 560.164 msUsing ORDER BY k, id ensures deterministic sorting by adding a tie-breaker. PostgreSQL 19 completed the query faster (560.164 ms) than PostgreSQL 18 (753.475 ms), but the differing results suggest the test data or execution conditions should be verified.
6. Parallel TID range scans
A TID range scan reads only the heap blocks inside a ctid range. It has existed since PG14 but always ran in a single process.
PG18: a big ctid range forced the planner into a dilemma. A Parallel Seq Scan could win on CPU parallelism even though it reads blocks outside your range, so the planner sometimes chose more I/O just to get parallel workers.
PG19 (commit 0ca3b1697, Cary Huang and David Rowley) adds Parallel Tid Range Scan. The block range is divided among workers, who each take a chunk and ask for another when done, the same idea as a parallel sequential scan. The planner no longer has to trade I/O for parallelism. The motivating use case was splitting a huge table into ranges for migration or export.
Numbers from the original proposal (40 million rows, two columns) showed about 10.2 s for a serial TID range scan versus 7.1 s with two workers; independent re-tests in the thread showed smaller gains, roughly 12-18%. Gains depend on how much per-row work the workers do.
Related new helpers: tid_block(tid) and tid_offset(tid) extract the block number and offset from a ctid. On PG18 the common workaround was (ctid::text::point)[0].
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM sort_demo
WHERE ctid >= '(0,0)' AND ctid < '(40000,0)';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual time=3037.323..3043.546 rows=1.00 loops=1)
Buffers: shared read=40000 written=13023
-> Gather (actual time=3028.324..3043.531 rows=5.00 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared read=40000 written=13023
-> Partial Aggregate (actual time=3017.089..3017.092 rows=1.00 loops=5)
Buffers: shared read=40000 written=13023
-> Parallel Tid Range Scan on sort_demo (actual time=0.212..1668.614 rows=856000.00 loops=5)
TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid < '(40000,0)'::tid))
Buffers: shared read=40000 written=13023
Planning:
Buffers: shared hit=5 read=3
Planning Time: 1.350 ms
Execution Time: 3043.575 ms
(15 rows)PG19 can show: Gather -> Parallel Tid Range Scan on sort_demo
New helpers
SELECT ctid, tid_block(ctid) AS blk, tid_offset(ctid) AS off
FROM sort_demo LIMIT 3;
ctid | blk | off
-------+-----+-----
(0,1) | 0 | 1
(0,2) | 0 | 2
(0,3) | 0 | 3Building 8 block ranges to export or copy a large table in chunks:
WITH t AS (
SELECT (pg_relation_size('sort_demo') / current_setting('block_size')::int) AS nblocks
)
SELECT i AS chunk,
(i * (nblocks / 8 + 1))::bigint AS start_block,
((i + 1) * (nblocks / 8 + 1))::bigint AS end_block
FROM t, generate_series(0, 7) i;
chunk | start_block | end_block
-------+-------------+-----------
0 | 0 | 5849
1 | 5849 | 11698
2 | 11698 | 17547
3 | 17547 | 23396
4 | 23396 | 29245
5 | 29245 | 35094
6 | 35094 | 40943
7 | 40943 | 46792Each worker connection then runs, with its own bounds:
-- SELECT * FROM sort_demo
-- WHERE ctid >= '(<start_block>,0)'::tid AND ctid < '(<end_block>,0)'::tid;7. Streaming reads for hash index bulk-delete and GIN vacuum
PG17 introduced the read-stream API, and PG18 added asynchronous I/O (io_method) on top of it. PG19 keeps converting synchronous read loops to use that machinery.
- GIN: ginvacuumcleanup() now scans index pages with streaming reads (commit 6c228755a). The author measured about a 5x runtime improvement under a synthetic high-latency setup (dm_delay plus debug_io_direct=data), and the committer reproduced similar numbers.
- Hash indexes: bulk deletion during VACUUM uses streaming reads (commit bfa3c4f10). A follow-up fixed an initialization-order bug and was back-patched to 19.
- bloom and pgstattuple (pgstatindex(), pgstathashindex()) got the same treatment; the bloom commit reports roughly 30% better runtime in the same kind of test.
Those test conditions emulate slow storage, so expect real gains to be largest on high-latency or cold-cache storage and smaller on fast, fully cached systems.
GIN indexes keep new entries in a small waiting area (the pending list) and merge them into the main index later, and vacuum triggers that merge. You can also trigger it manually with gin_clean_pending_list().
So if one test run starts with a full pending list and another with an empty one, vacuum timings won’t be comparable. Flush the list before each run, and keep the paragraph in your blog for that reason.
CREATE TABLE docs (id bigserial PRIMARY KEY, body text, tags text[]);
INSERT INTO docs (body, tags)
SELECT md5(g::text), ARRAY['t' || (g % 500), 't' || (g % 37), 't' || (g % 9973)]
FROM generate_series(1, 3000000) g;
CREATE INDEX docs_tags_gin ON docs USING gin (tags);
SELECT pg_size_pretty(pg_relation_size('docs_tags_gin'));
pg_size_pretty
----------------
31 MB
(1 row)
DELETE FROM docs WHERE id % 5 = 0;
\timing on
VACUUM (VERBOSE) docs; In another session while it runs, check the new columns added by PG19: started_by and mode
SELECT pid, relid::regclass, phase, started_by, mode FROM pg_stat_progress_vacuum;PG19 also adds an IO option to EXPLAIN ANALYZE to report asynchronous I/O activity:
EXPLAIN (ANALYZE, IO) SELECT count(*) FROM docs;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=52407.69..52407.70 rows=1 width=8) (actual time=1675.645..1682.105 rows=1.00 loops=1)
Buffers: shared hit=10 read=8199 written=3461
-> Gather (cost=52407.67..52407.68 rows=4 width=8) (actual time=1673.758..1682.090 rows=5.00 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=10 read=8199 written=3461
-> Partial Aggregate (cost=52407.67..52407.68 rows=1 width=8) (actual time=1662.448..1662.450 rows=1.00 loops=5)
Buffers: shared hit=10 read=8199 written=3461
-> Parallel Index Only Scan using docs_pkey on docs (cost=0.43..50907.72 rows=599976 width=0) (actual time=0.135..921.830 rows=480000.00 loops=5)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=10 read=8199 written=3461
Planning:
Buffers: shared hit=21
Planning Time: 0.134 ms
Execution Time: 1682.132 ms
(16 rows)8. PL/pgSQL: faster SELECT … INTO
PG18: SELECT expr INTO var always went through the SPI machinery, so it was much slower than the equivalent var := expr. People coming from T-SQL, who have no := assignment, hit this all the time, as does PL/SQL code converted to PL/pgSQL once FROM dual is dropped.
PG19 (commit ce8d5fe0e, Tom Lane) detects a simple expression (the same test used for :=: a scalar-expression SELECT with no tables, aggregates or qualifiers) assigned into a single variable, and uses the fast evaluation path instead.
Behavior change to know about: because the pg_stat_statements hooks are not called on this path, those evaluated expressions no longer appear in pg_stat_statements output. If a dashboard relied on seeing them, expect them to disappear after the upgrade. Anything with a FROM clause is not affected.
CREATE OR REPLACE FUNCTION bench_select_into(n int) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE v bigint := 0; x bigint;
BEGIN
FOR i IN 1..n LOOP
SELECT v + i INTO x; -- simple expression, single target
v := x;
END LOOP;
RETURN v;
END $$;
CREATE OR REPLACE FUNCTION bench_assign(n int) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE v bigint := 0; x bigint;
BEGIN
FOR i IN 1..n LOOP
x := v + i; -- the always-fast form
v := x;
END LOOP;
RETURN v;
END $$;
\timing on
SELECT bench_select_into(5000000);
bench_select_into
-------------------
12500002500000
SELECT bench_assign(5000000);
bench_assign
----------------
12500002500000On PG18 the first function is far slower than the second; on PG19 the gap should largely close.
9. Smarter NOTIFY wakeups
The NOTIFY documentation page reads the same in PG18 and PG19 because the user-visible semantics did not change: delivery between transactions, de-duplication within a transaction, payloads under 8000 bytes by default, and a notification queue of up to 8 GB (max_notify_queue_pages, already present in PG18).
What changed is the internals (commit 282b1cde9, Joel Jacobson, reviewed by Tom Lane):
PG18: a NOTIFY signaled all listening backends, whichever channel they were listening on. With many listeners and only a few interested in any given message, most wakeups were wasted work.
PG19: a shared hash table tracks which processes listen on which channel (a channel is a database OID plus a name). The notifier can tell exactly who is interested. Listeners known to be uninterested and sitting at the old queue head are not woken at all; their queue pointer is advanced directly past the new messages. The commit message reports integer-multiple improvements in throughput and lower latency when many listeners exist but few care about a given message. If every listener reads every message, there is no improvement.
A follow-up fix (in the release candidate cycle) made sure a listener that has registered but not yet committed its LISTEN does not miss messages sent in that window.
Three psql sessions:
-- Session A
LISTEN orders_eu;
-- Session B
LISTEN orders_us;
-- Session C
NOTIFY orders_eu, '{"order_id": 42}';
NOTIFY
Time: 0.194 ms
Asynchronous notification "orders_eu" with payload "{"order_id": 42}" received from server process with PID 2543056.
SELECT pg_notify('orders_eu', '{"order_id": 43}');
Only session A receives anything. On PG19 session B is not woken at all.
SELECT pg_notification_queue_usage(); -- fraction of the queue in useFor a throughput comparison, create many idle listeners on separate channels and hammer one channel:
for i in $(seq 1 300); do
(echo "LISTEN ch_$i;"; sleep 900) | psql -X -q demo >/dev/null &
Done
ubuntu@ip-172-31-7-166:/tmp$ for i in $(seq 1 300); do
(echo "LISTEN ch_$i;"; sleep 900) | psql -X -q demo >/dev/null &
done
[1] 2543376
[2] 2543378
[3] 2543380
[4] 2543382
…
[234] Stopped ( echo "LISTEN ch_$i;"; sleep 900 ) | psql -X -q demo > /dev/null
[239]+ Stopped ( echo "LISTEN ch_$i;"; sleep 900 ) | psql -X -q demo > /dev/null
echo "NOTIFY ch_1;" > notify.sql
pgbench -n -c 8 -j 8 -T 30 -f notify.sql demo10. oid8: a 64-bit object identifier
PG18: oid is an unsigned 32-bit integer. The documentation itself notes it is not large enough to guarantee uniqueness across big databases or even large individual tables.
PG19 (commit b139bd3b6, Michael Paquier) adds oid8, an unsigned 8-byte integer. It comes with casts to and from integer types and oid, btree and hash operators, and min()/max() aggregates. Like oid, it has few operations beyond comparison, and you should watch for signed-versus-unsigned confusion when casting to integers. Its main purpose today is groundwork for 64-bit identifiers inside the system, for example 64-bit TOAST value IDs, but it is a normal SQL type you can use.
Related: PG19 also adds a regdatabase alias type for casting between database names and OIDs, and the new tid_block() / tid_offset() functions live on the same documentation page as oid.
SELECT pg_typeof(42::oid8);
pg_typeof
-----------
oid8Beyond the 32-bit range: works with oid8
SELECT 5000000000::oid8;On PG18, the equivalent cast to oid raises an out-of-range error
SELECT 5000000000::oid;
SELECT min(x), max(x) FROM (VALUES (1::oid8), (5000000000::oid8)) AS t(x);
min | max
-----+------------
1 | 5000000000regdatabase (new): database name <-> OID
SELECT 'template1'::regdatabase::oid;
oid
-----
1
SELECT datname FROM pg_database WHERE oid = 'template1'::regdatabase;
datname
-----------
template1More PG19 performance changes worth knowing
| Change | What it means |
| I/O worker auto-scaling | With io_method = worker, the pool now grows and shrinks on demand: io_min_workers (default 2), io_max_workers (default 8), io_worker_idle_timeout (1 min), io_worker_launch_interval (100 ms) |
| Better AIO read-ahead | Improved read-ahead scheduling for large requests |
| Visibility map from scans | Query table scans can now mark pages all-visible; previously only VACUUM and COPY … FREEZE could |
| Parallel autovacuum | Index vacuuming can use parallel workers (autovacuum_max_parallel_workers, per-table autovacuum_parallel_workers), plus a scoring system that orders which tables autovacuum handles first |
| Planner: anti-joins | NOT IN can become an ANTI JOIN when NULLs are not present, more LEFT JOINs convert to ANTI JOINs, and Memoize is allowed for anti-joins with unique inner sides |
| Planner: aggregate before join | Some aggregation can run before the join to reduce rows |
| WAL and TOAST | wal_compression = on now picks the best available method (it was always pglz), and the default TOAST compression changes from pglz to lz4 |
| Cheaper timing | timing_clock_source can use the CPU time-stamp counter, lowering EXPLAIN (ANALYZE, TIMING) overhead |
| SIMD and CPU instructions | AVX2 page checksums, ARM CRC32C, SIMD hex_encode()/hex_decode() |
| REPACK | New command replacing VACUUM FULL and CLUSTER (which remain); REPACK (CONCURRENTLY) avoids blocking reads and writes |
| Planner control | New pg_plan_advice and pg_stash_advice extensions for steering and stabilizing plans |
Upgrade checklist: PG18 to PG19 defaults that affect performance
- JIT is now off by default. If you run large analytical queries and rely on JIT, enable it explicitly.
- default_toast_compression is now lz4. New TOASTed values use it; existing data is unchanged.
- max_locks_per_transaction default is now 128 (was 64) due to a lock-table shared memory sizing change; default users need no action, but if you set a custom value you may need to raise it (doubling is a safe start) to avoid “out of shared memory” errors.
- log_lock_waits is on by default, so expect more lock-wait lines in logs.
- Sort tie order may differ. Add tiebreakers to ORDER BY in tests and comparisons.
- SELECT simple-expr INTO var no longer shows in pg_statements-based dashboards (see section 8).
- Dumps: standard_conforming_strings is always on, so dumps made with it off from older versions will not load; use PG19’s pg_dump.
- Upgrade path is still pg_upgrade, dump/restore, or logical replication.
Benchmarking tips
- Same hardware, same data, same settings on both versions; reset pg_stat_statements between runs.
- Warm the cache (pg_prewarm) for CPU-bound tests such as radix sort, deformation and FK checks; use cold cache on purpose for I/O tests such as GIN vacuum.
- Disable parallelism (max_parallel_workers_per_gather = 0) and set jit = off when comparing single-process code paths.
- Run each test several times and compare medians, and read EXPLAIN (ANALYZE, BUFFERS, IO) rather than wall-clock time alone.
References
- FK fast path commit 2da86c1ef
- SIMD COPY FROM commit e0a3a3fd5
- Faster row deformation commit c456e3911
- UTF-8 case folding commit c4ff35f10
- Radix sort commit ef3c3cf6d
- PL/pgSQL SELECT INTO commit ce8d5fe0e
- GIN vacuum streaming read commit 6c228755a and hash bulk-delete commit bfa3c4f10
- Parallel TID range scan commit 0ca3b1697
- NOTIFY optimization commit 282b1cde9
- oid8 commit b139bd3b6
- PostgreSQL 19 documentation: Object Identifier Types
- PostgreSQL 19 documentation: NOTIFY
- PostgreSQL 19 documentation: GIN Indexes
- PostgreSQL 19 release notes
- PostgreSQL 19 RC 1 announcement