What's New in PostgreSQL 18

What's New for Analytics in PostgreSQL 18

PostgreSQL 18 1,289 (released 25 September 2025; 18.6 is current) changed little analytical syntax but much underneath. demos/ch04/scripts/pg17_vs_18.sh runs this script on 18.6 and on a throwaway 17.11 container with the same orders; the query filters on the second column of an index.

432-new18.sql: I/O settings and a query that needs a skip scanSQL

SELECT split_part(version(), ' ', 2) AS version,
       current_setting('io_method', true) AS io_method,
       current_setting('effective_io_concurrency') AS io_concurrency;
CREATE TABLE ev AS SELECT order_id, channel, order_ts FROM orders;
CREATE INDEX ON ev (channel, order_ts);
VACUUM ANALYZE ev;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM ev WHERE order_ts >= '2026-06-30' AND order_ts < '2026-07-01';
DROP TABLE ev;

Output

--- l2-pg17
 17.11   |           | 1
...
   Buffers: shared hit=576
   ->  Seq Scan on ev (actual rows=195 loops=1)
...
--- l2-pg
 18.6    | worker    | 16
...
   Buffers: shared hit=8 read=7
   ->  Index Only Scan using ev_channel_order_ts_idx on ev (actual rows=195.00 loops=1)
         Index Searches: 4

PostgreSQL 17 scanned all 576 pages. PostgreSQL 18's B-tree skip scan ran one small range scan per channel value (Index Searches: 4) and read 15 pages. The first rows show the new asynchronous I/O subsystem (io_method = worker), which queues several reads at once for sequential and bitmap heap scans.

Analytics-relevant changes in PostgreSQL 18 (from the release notes)
Change in PostgreSQL 18	Why analytics cares
Asynchronous I/O (io_method)	Faster sequential and bitmap scans on cold data
B-tree skip scan	Multicolumn indexes serve filters on later columns
Faster, leaner hash joins and GROUP BY	Less memory for large aggregates
HAVING on grouping sets pushed to WHERE	Earlier filtering; some wrong grouping-set results fixed
Virtual generated columns by default	Derived columns computed on read, no storage
pg_upgrade keeps planner statistics	No slow plans right after a major upgrade

The SQL in PostgreSQL 18 for Analytics to Views, Partitions, Parallelism is older and runs on any supported release: GROUPING SETS, ROLLUP and CUBE arrived in 9.5 (2016), FILTER in 9.4, GROUPS frames and EXCLUDE in 11 (2018), LATERAL in 9.3.

MENU