PostgreSQL 18: Part 1 or CommitFest 2024-07

This article is the first in the series about the upcoming PostgreSQL 18 release. Let us take a look at the features introduced in the July CommitFest.

Planner: Hash Right Semi Join support
Planner: materializing an internal row set for parallel nested loop join
Planner support functions for generate_series
EXPLAIN (analyze): statistics for Parallel Bitmap Heap Scan node workers
Functions min and max for composite types
Parameter names for regexp* functions
Debug mode in pgbench
pg_get_backend_memory_contexts: column path instead of parent, new column type
Function pg_get_acl
pg_upgrade: pg_dump optimization
Predefined role pg_signal_autovacuum_worker

Planner: Hash Right Semi Join support
commit: aa86129e1

The plan below in PostgreSQL 17 employs hash join and sequential scan of both tables.

17=# EXPLAIN (costs off, analyze) SELECT * FROM flights WHERE flight_id IN (SELECT flight_id FROM ticket_flights);

                                           QUERY PLAN                                            ------------------------------------------------------------------------------------------------  Hash Join (actual time=2133.122..2195.619 rows=150588 loops=1)    Hash Cond: (flights.flight_id = ticket_flights.flight_id)    ->  Seq Scan on flights (actual time=0.018..10.301 rows=214867 loops=1)    ->  Hash (actual time=2132.969..2132.970 rows=150588 loops=1)          Buckets: 262144 (originally 131072)  Batches: 1 (originally 1)  Memory Usage: 7343kB          ->  HashAggregate (actual time=1821.476..2114.218 rows=150588 loops=1)                Group Key: ticket_flights.flight_id                Batches: 5  Memory Usage: 10289kB  Disk Usage: 69384kB                ->  Seq Scan on ticket_flights (actual time=7.200..655.356 rows=8391852 loops=1)  Planning Time: 0.325 ms  Execution Time: 2258.237 ms (11 rows)

However, the hash is built on the larger table ticket_flights which obviously requires more resources compared to the smaller table flights.

In PostgreSQL 18, it becomes possible to choose which table is hashed. Here, in line 4, we can see that the planner goes with the Hash Right Semi Join, and even in parallel mode. As a result, the execution time is noticeably reduced:

                                            QUERY PLAN                                              ---------------------------------------------------------------------------------------------------  Gather (actual time=56.771..943.233 rows=150590 loops=1)    Workers Planned: 2    Workers Launched: 2    ->  Parallel Hash Right Semi Join (actual time=41.754..909.894 rows=50197 loops=3)          Hash Cond: (ticket_flights.flight_id = flights.flight_id)          ->  Parallel Seq Scan on ticket_flights (actual time=0.047..221.511 rows=2797284 loops=3)          ->  Parallel Hash (actual time=40.309..40.309 rows=71622 loops=3)                Buckets: 262144  Batches: 1  Memory Usage: 23808kB                ->  Parallel Seq Scan on flights (actual time=0.008..6.631 rows=71622 loops=3)  Planning Time: 0.555 ms  Execution Time: 949.831 ms (11 rows)

Planner: materializing an internal row set for parallel nested loop join
commit: 22d946b0f

Before PostgreSQL 18, the planner never considered materializing an internal row set for parallel nested loop joins.

17=# EXPLAIN (costs off) SELECT * FROM ticket_flights tf      JOIN flights f USING (flight_id) WHERE f.flight_id = 12345;

                     QUERY PLAN                      ----------------------------------------------------  Nested Loop    ->  Index Scan using flights_pkey on flights f          Index Cond: (flight_id = 12345)    ->  Gather          Workers Planned: 2          ->  Parallel Seq Scan on ticket_flights tf                Filter: (flight_id = 12345) (7 rows)

The plan for the same query in PostgreSQL 18 materializes the flights:

                          QUERY PLAN                           --------------------------------------------------------------  Gather    Workers Planned: 2    ->  Nested Loop          ->  Parallel Seq Scan on ticket_flights tf                Filter: (flight_id = 12345)          ->  Materialize                ->  Index Scan using flights_pkey on flights f                      Index Cond: (flight_id = 12345) (8 rows)

Notably, the performance does not change much with the new plan. In both plans, the same actions are performed, but the planner has more opportunities to choose the optimal plan in other situations, which is good news.

Planner support functions for generate_series
commit: 036bdcec9

The new support function tells the planner how many rows generate_series will return for a range of dates and times:

EXPLAIN SELECT * FROM generate_series(current_date, current_date + '1 day'::interval, '1 hour');

                             QUERY PLAN                               ---------------------------------------------------------------------  Function Scan on generate_series  (cost=0.01..0.26 rows=25 width=8) (1 row)

Previously, the number of rows was always estimated at 1000 (the default ROWS value for functions).

Note that a similar support function for integers has long existed:

EXPLAIN SELECT * FROM generate_series(1, 42, 2);

                             QUERY PLAN                               ---------------------------------------------------------------------  Function Scan on generate_series  (cost=0.00..0.21 rows=21 width=4) (1 row)

EXPLAIN (analyze): statistics for Parallel Bitmap Heap Scan node workers
commit: 5a1e6df3b

The EXPLAIN command now shows statistics (the number of exact and lossy fragments) for each worker involved in a parallel bitmap scan. In the example below, these are lines starting with Worker 0 and Worker 1.

EXPLAIN (analyze, costs off, timing off, summary off) SELECT count(*) FROM bookings   WHERE total_amount < 20000 AND book_date > '2017-07-15';

                                             QUERY PLAN                                               -----------------------------------------------------------------------------------------------------  Finalize Aggregate (actual rows=1 loops=1)    ->  Gather (actual rows=3 loops=1)          Workers Planned: 2          Workers Launched: 2          ->  Partial Aggregate (actual rows=1 loops=3)                ->  Parallel Bitmap Heap Scan on bookings (actual rows=6415 loops=3)                      Recheck Cond: (total_amount < '20000'::numeric)                      Filter: (book_date > '2017-07-15 00:00:00+03'::timestamp with time zone)                      Rows Removed by Filter: 67072                      Heap Blocks: exact=6345                      Worker 0:  Heap Blocks: exact=3632                      Worker 1:  Heap Blocks: exact=3470                      ->  Bitmap Index Scan on bookings_total_amount_idx (actual rows=220460 loops=1)                            Index Cond: (total_amount < '20000'::numeric)

Functions min and max for composite types
commit: a0f1fce80

Composite type values can be compared with each other and have been for a while now. The comparison is done element-by-element. For example, let us sort the following rows:

CREATE TABLE seats(     line text,     number integer );  INSERT INTO seats VALUES     ('A', 42), ('B',  1), ('C', 27);  SELECT * FROM seats s ORDER BY s DESC;

 line | number ------+--------  C    |     27  B    |      1  A    |     42 (3 rows)

However, there were no aggregate functions min and max for composite types. Until now.

SELECT min(s.*), max(s.*) FROM seats s;

  min   |  max    --------+--------  (A,42) | (C,27) (1 row)

Parameter names for regexp* functions
commit: 580f8727

Functions for regular expressions have got parameter names for improved clarity:

SELECT regexp_replace(     pattern => '$',     replacement => 'Postgres!',     string => 'All You Need Is ' );

      regexp_replace        ---------------------------  All You Need Is Postgres!

The parameter names are listed in the documentation or in the \df command output in psql. Previously, the parameters could be passed only positionally.

Debug mode in pgbench
commit: 3ff01b2b

Most server utilities use the -d option to specify the database to connect to. However, in pgbench, this option entered the debug mode. The parameter names have been changed for consistency: now you specify the database with -d or --dbname and enter the debug more with the full-name option --debug.

The change has been implemented in PostgreSQL 17 already, although it was formally introduced only in the PostgreSQL 18 CommitFest in July.

pg_get_backend_memory_contexts: column path instead of parent, new column type
commit: 32d3ed81, f0d11275, 12227a1d

Core developers and enthusiasts researching memory usage will appreciate the changes in the pg_backend_memory_contexts view.

The patch makes linking child and parent contexts more convenient and reliable. There is a new column path, which holds an array of context IDs. The first element of the array is always the top level context TopMemoryContext, and the last element is the current row context ID. In addition, the numbering in the level column now starts with 1 instead of 0, which is better for writing queries.

Below is an example of a query showing the memory context of TopPortalContext and all its child contexts.

WITH memory_contexts AS (     SELECT * FROM pg_backend_memory_contexts ) SELECT child.name, child.type, child.level, child.path, child.total_bytes FROM memory_contexts parent, memory_contexts child WHERE parent.name = 'TopPortalContext' AND       child.path[parent.level] = parent.path[parent.level];

              name              |    type    | level |         path          | total_bytes --------------------------------+------------+-------+-----------------------+-------------  TopPortalContext               | AllocSet   |     2 | {1,20}                |        8192  PortalContext                  | AllocSet   |     3 | {1,20,31}             |        1024  ExecutorState                  | AllocSet   |     4 | {1,20,31,136}         |       65632  tuplestore tuples              | Generation |     5 | {1,20,31,136,138}     |       32768  printtup                       | AllocSet   |     5 | {1,20,31,136,139}     |        8192  ExprContext                    | AllocSet   |     5 | {1,20,31,136,140}     |        8192  ExprContext                    | AllocSet   |     5 | {1,20,31,136,141}     |        8192  tuplestore tuples              | Generation |     5 | {1,20,31,136,142}     |        8192  ExprContext                    | AllocSet   |     5 | {1,20,31,136,143}     |        8192  Table function arguments       | AllocSet   |     5 | {1,20,31,136,144}     |        8192  ExprContext                    | AllocSet   |     5 | {1,20,31,136,145}     |       32768  pg_get_backend_memory_contexts | AllocSet   |     6 | {1,20,31,136,145,146} |       16384 (12 rows)

Previously, the contexts could be joined by linking the parent column (removed by the second commit) to the name column. This approach required a more complex, recursive query. More importantly, this sort of linking was less reliable, as the context names were not guaranteed to be unique.

Lastly, the third commit adds the type column. Currently, four types of context are used: AllocSet, Generation, Slab, Bump. For more information about memory contexts, see the source code: src/backend/utils/mmgr/README.

Function pg_get_acl
commit: 4564f1ce, e311c6e5, d898665b

The following commands make the table test depend on the role alice.

CREATE ROLE alice; CREATE TABLE test (id int); GRANT SELECT ON test TO alice;

Now the role alice cannot be deleted without detaching the dependant table:

DROP ROLE alice;

ERROR:  role "alice" cannot be dropped because some objects depend on it DETAIL:  privileges for table test

The object dependency data is stored in two system catalogs: pg_depend (between objects within the same database) and pg_shdepend for shared cluster objects, such as roles.

Currently, pg_shdepend contains just the record of the dependency we just created.

SELECT * FROM pg_shdepend\gx

-[ RECORD 1 ]----- dbid       | 5 classid    | 1259 objid      | 16528 objsubid   | 0 refclassid | 1260 refobjid   | 16527 deptype    | a

This information is not easily readable since the objects are represented as identifiers: the system catalog table ID (classid), the row ID in the system catalog (objid) and the ordinal column number for tables (objsubid). The referenced objects are represented by refclassid and refobjid, respectively (there are no references to table columns, so there is no refobjsubid column).

There is a number of convenient functions for accessing objects by their IDs. For example, the function pg_identify_object returns the object record:

SELECT * FROM pg_identify_object(1259,16528,0);

 type  | schema | name |  identity    -------+--------+------+-------------  table | public | test | public.test

The object on which the table depends:

SELECT * FROM pg_identify_object(1260,16527,0);

 type | schema | name  | identity ------+--------+-------+----------  role |        | alice | alice

There is a new addition to this set of functions. The new function pg_get_acl returns the object access rights without the need to query a specific system catalog.

This query shows what privileges the role alice has:

SELECT *,     pg_identify_object(classid, objid, objsubid) AS object,     pg_identify_object(refclassid, refobjid, 0) AS ref_object,     pg_get_acl(classid, objid, objsubid) FROM pg_shdepend\gx

-[ RECORD 1 ]--------------------------------------------- dbid       | 5 classid    | 1259 objid      | 16528 objsubid   | 0 refclassid | 1260 refobjid   | 16527 deptype    | a object     | (table,public,test,public.test) ref_object | (role,,alice,alice) pg_get_acl | {postgres=arwdDxtm/postgres,alice=r/postgres}

pg_upgrade: pg_dump optimization
commit: 6e16b1e4, 64f34eb2e, 2329cad1b, 23687e925, 68e962998, c8b06bb96, bd15b7db4, 6ebeeae29

There are several new optimizations for the server version upgrade process. Let us consider them together, since they all are more or less related to pg_dump, the tool that dumps the content of the system catalogs during upgrade.

The first optimization is that pg_upgrade will run pg_dump with the --no-sync key. When upgrading the server, the guarantee that the files are flushed to disk is redundant: if something goes wrong, the whole upgrade process needs to be started from scratch anyway, while disk sync time is time to be saved.

The second optimization makes database creation commands use the FILE_COPY strategy instead of the default WAL_LOG:

CREATE DATABASE .. STRATEGY = FILE_COPY;

With this strategy, the contents of the template database are not logged, and durability is guaranteed by performing checkpoints before and after copying the files. In normal operation, this is not the most efficient approach, but during binary upgrade the server will not perform extra checkpoints anyway (there will be just one, after creating the template database template0).

The effectiveness of these two optimizations will increase with the number of databases in the upgraded cluster.

The third optimization speeds up the dumping of table descriptions in pg_dump --binary-upgrade mode. In this mode, pg_upgrade starts pg_dump. When it starts, pg_dump fetches information about all tables with a single query to pg_class and stores it in an ordered array in memory, saving time. Before this patch, there was a separate query to pg_class for each table, which increased the costs considerably.

The fourth optimization speeds up the dumping of sequences in the same way the previous patch does with tables. When pg_dump starts, it puts all the sequences data into an ordered array, eliminating the need to access the system catalog every time in the future. Notably, this optimization is not restricted to --binary-upgrade mode and works just as fine in the regular mode.

The third and fourth optimizations come with a small tradeoff in the form of the RAM required to store the table and sequence information, but the developers behind the patches believe that this is absolutely worth it.

In databases with a small number of tables and sequences, the last two optimizations will not have much of an effect. On the other hand, in databases with tens and hundreds of thousands of tables and sequences, the speed increase is actually noticeable.

Let us test it. Create a database in PostgreSQL 17 with 100 000 tables, each with a column linked to a sequence. Then do the same in PostgreSQL 18.

$ psql -d test -o /dev/null <<EOF SELECT format('CREATE TABLE t%s (id int GENERATED ALWAYS AS IDENTITY)', g.i) FROM generate_series(1,100_000) AS g(i) \gexec EOF

Now, time how long pg_dump --binary-upgrade takes.

PostgreSQL 17:

$ time pg_dump -d test --schema-only --binary-upgrade > /dev/null

real    1m40,677s user    0m5,403s sys    0m3,265s

PostgreSQL 18:

$ time pg_dump -d test --schema-only --binary-upgrade > /dev/null

real    0m7,427s user    0m2,220s sys    0m0,264s

The patches make it run more than ten times faster.

Predefined role pg_signal_autovacuum_worker
commit: ccd38024b

Members of the predefined role pg_signal_backend can send the query termination signal (pg_cancel_backend) and process termination signal (pg_terminate_backend) to server backend processes. With one exception: the target process may not belong to a superuser.

However, there may be maintenance scenarios when you need to interrupt an autovacuum process. Members of the new predefined role pg_signal_autovacuum_worker can, while not being superusers, send autovacuum workers signals to terminate the current table vacuuming job or to terminate the process entirely by using the same two functions.

---

This is all for now. The news of the September CommitFest are soon to follow!