{"id":385875,"date":"2024-06-29T06:22:58","date_gmt":"2024-06-29T06:22:58","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=385875"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=385875","title":{"rendered":"PostgreSQL 16: Part 4 or CommitFest 2023-01<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

<\/p>\n

<\/p>\n

We continue to follow the news of the PostgreSQL 16 release, and today, the results of the fourth CommitFest are on the table. Let’s have a look.<\/p>\n

<\/p>\n

If you missed the previous CommitFests, check out our reviews for 2022-07<\/a>, 2022-09<\/a> and 2022-11<\/a>.<\/p>\n

<\/a> <\/p>\n

Here are the patches I want to talk about this time:<\/p>\n

<\/p>\n

New function: random_normal<\/a>
Input formats for integer literals<\/a>
Goodbye, postmaster<\/a>
Parallel execution for string_agg and array_agg<\/a>
New parameter: enable_presorted_aggregate<\/a>
Planner support function for working with window functions<\/a>
Optimized grouping of repeating columns in GROUP BY and DISTINCT<\/a>
VACUUM parameters: SKIP_DATABASE_STATS and ONLY_DATABASE_STATS<\/a>
pg_dump: lock tables in batches<\/a>
PL\/pgSQL: cursor variable initialization<\/a>
Roles with the CREATEROLE attribute<\/a>
Setting parameter values at the database and user level<\/a>
New parameter: reserved_connections<\/a>
postgres_fdw: analyzing foreign tables with TABLESAMPLE<\/a>
postgres_fdw: batch insert records during partition key updates<\/a>
pg_ident.conf: new ways to identify users in PostgreSQL<\/a>
Query jumbling for DDL and utility statements<\/a>
New function: bt_multi_page_stats<\/a>
New function: pg_split_walfile_name<\/a>
pg_walinspect, pg_waldump: collecting page images from WAL<\/a><\/p>\n

<\/a> <\/p>\n

New function: random_normal<\/a><\/strong>
commit: 38d81760<\/a><\/p>\n

<\/p>\n

The function random<\/em> returns a number between 0 and 1, and any number within the scope has an equal chance to be selected, i.e., the numbers are distributed uniformly. The patch introduces a new function random_normal<\/em> as a way to get random numbers from a normal distribution<\/a>. The function has two parameters: the mean and the standard deviation.<\/p>\n

<\/p>\n

Let’s generate 1,000,000 numbers and see if the 68-95-99.7 rule<\/a> applies. The rule states that 68% of normally distributed random numbers are within no more than one standard deviation (the second parameter) from the mean (the first parameter), 95% are within two standard deviations, and 99.7% are within three.<\/p>\n

<\/p>\n

WITH samples AS (     SELECT random_normal(0.0, 1.0) AS x FROM generate_series(1,1000000) ) SELECT count(x) FILTER (WHERE x BETWEEN -1 AND 1)\/1000000.0 AS stddev_1,        count(x) FILTER (WHERE x BETWEEN -2 AND 2)\/1000000.0 AS stddev_2,        count(x) FILTER (WHERE x BETWEEN -3 AND 3)\/1000000.0 AS stddev_3 FROM samples;<\/code><\/pre>\n
  <\/p>\n
        stddev_1        |        stddev_2        |        stddev_3         ------------------------+------------------------+------------------------  0.68391100000000000000 | 0.95473800000000000000 | 0.99724900000000000000<\/code><\/pre>\n
  <\/p>\n
We can send the output of psql to gnuplot to display the resulting distribution visually:<\/p>\n
  <\/p>\n
WITH samples AS (     SELECT random_normal(0.0, 1.0) AS x FROM generate_series(1,1000000) ) SELECT round(x::numeric,1) point, count(*) AS density FROM samples GROUP BY point ORDER BY point \\g (format=unaligned tuples_only=on fieldsep='\\t') | gnuplot -e \"set term png; set output 'data.png'; plot '<cat'\"<\/code><\/pre>\n
  <\/p>\n
I separated the values into groups in increments of 0.1 to get the distribution density. As a result, in the data.png file we get the famous bell curve:<\/p>\n
  <\/p>\n
<\/p>\n
  <\/p>\n
Note that the tablefunc extension<\/a> already has a function normal_rand<\/em> for obtaining random numbers from a normal distribution. Now, however, the functionality is available out-of-the-box, without the need for any extensions.<\/p>\n
  <\/p>\n
See also:
  Waiting for PostgreSQL 16 \u2013 Invent random_normal() to provide normally-distributed random numbers<\/a> (Hubert ‘depesz’ Lubaczewski)<\/p>\n
  <\/a>  <\/p>\n
Input formats for integer literals<\/a><\/strong>
  commit: 6fcda9ab<\/a>, 6dfacbf7<\/a>, faff8f8e<\/a><\/p>\n
  <\/p>\n
The latest changes to the SQL standard allow declaring integer literals not only in decimal, but also in hexadecimal, octal and binary formats.<\/p>\n
  <\/p>\n
SELECT 0x2A hex_int, 0o52 oct_int, 0b101010 bin_int;<\/code><\/pre>\n
  <\/p>\n
 hex_int | oct_int | bin_int ---------+---------+---------       42 |      42 |      42<\/code><\/pre>\n
  <\/p>\n
If the resulting number doesn’t fit into bigint, it is transformed into numeric type (the second commit). In addition, for large integer values, an underscore can be used to visually separate groups of digits (the third commit):<\/p>\n
  <\/p>\n
SELECT count(*) FROM generate_series(1, 1_000_000);<\/code><\/pre>\n
  <\/p>\n
  count   ---------  1000000<\/code><\/pre>\n
  <\/p>\n
See also:
  Waiting for PostgreSQL 16 \u2013 Non-decimal integer literals<\/a> (Hubert ‘depesz’ Lubaczewski)
  Waiting for PostgreSQL 16 \u2013 Allow underscores in integer and numeric constants<\/a> (Hubert ‘depesz’ Lubaczewski)
  hex, oct, bin integers in PostgreSQL 16<\/a> (Pavlo Golub)<\/p>\n
  <\/a>  <\/p>\n
Goodbye, postmaster<\/a><\/strong>
  commit: 81266442<\/a>, 37e26733<\/a><\/p>\n
  <\/p>\n
On startup, the server starts a process traditionally referred to as postmaster…<\/em><\/p>\n
  <\/p>\n
Many a course on PostgreSQL architecture begin with a variation of this sentence. But in today’s Postgres, postmaster is just a 15-years-old moniker of the postgres process. You can still find the name in the documentation, and the bin catalog contains a postmaster file with a symlink to postgres.<\/p>\n
  <\/p>\n
The PostgreSQL 16 release finally says goodbye to postmaster, wiping every mention of it.<\/p>\n
  <\/a>  <\/p>\n
Parallel execution for string_agg and array_agg<\/a><\/strong>
  commit: 16fd03e9<\/a><\/p>\n
  <\/p>\n
Prior to PostgreSQL 16, all queries containing string_agg<\/em> and array_agg<\/em> always executed sequentially:<\/p>\n
  <\/p>\n
15=# EXPLAIN (costs off) SELECT fare_conditions, array_agg(flight_id), string_agg(ticket_no, ',') FROM ticket_flights GROUP BY fare_conditions;<\/code><\/pre>\n
  <\/p>\n
            QUERY PLAN             ----------------------------------  HashAggregate    Group Key: fare_conditions    ->  Seq Scan on ticket_flights<\/code><\/pre>\n
  <\/p>\n
Now, parallel execution is also possible:<\/p>\n
  <\/p>\n
16=# EXPLAIN (costs off) SELECT fare_conditions, array_agg(flight_id), string_agg(ticket_no, ',') FROM ticket_flights GROUP BY fare_conditions;<\/code><\/pre>\n
  <\/p>\n
                         QUERY PLAN                           -------------------------------------------------------------  Finalize GroupAggregate    Group Key: fare_conditions    ->  Gather Merge          Workers Planned: 2          ->  Sort                Sort Key: fare_conditions                ->  Partial HashAggregate                      Group Key: fare_conditions                      ->  Parallel Seq Scan on ticket_flights<\/code><\/pre>\n
  <\/a>  <\/p>\n
New parameter: enable_presorted_aggregate<\/strong>
  commit: 4a29eabd<\/a>, 3226f472<\/a><\/p>\n
  <\/p>\n
The first commit reduces the incremental sort cost estimate. This makes the planner choose it more often, specifically for optimizing ORDER BY and DISTINCT aggregates<\/a>.<\/p>\n
  <\/p>\n
However, in some cases<\/a> of non-uniform distribution of data in groups, ORDER BY and DISTINCT aggregates will perform slower with incremental sort. Disabling the new parameter enable_presorted_aggregate<\/em> (the second commit) will use the old version planning behavior.<\/p>\n
  <\/a>  <\/p>\n
Planner support functions for working with window functions<\/a><\/strong>
  commit: ed1a88dd<\/a><\/p>\n
  <\/p>\n
Unless a window function frame is defined explicitly, RANGE<\/em> UNBOUNDED PRECEDING is used by default.<\/p>\n
  <\/p>\n
15=# EXPLAIN (costs off, analyze, timing off) SELECT row_number() OVER (ORDER BY ticket_no) FROM tickets;<\/code><\/pre>\n
  <\/p>\n
                                    QUERY PLAN                                      -----------------------------------------------------------------------------------  WindowAgg (actual rows=2949857 loops=1)    ->  Index Only Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)          Heap Fetches: 0  Planning Time: 0.098 ms  Execution Time: 682.466 ms<\/code><\/pre>\n
  <\/p>\n
For this query, another frame can be specified: ROWS<\/em> UNBOUNDED PRECEDING. This will not change the result, but will boost performance:<\/p>\n
  <\/p>\n
15=# EXPLAIN (costs off, analyze, timing off) SELECT row_number() OVER (ORDER BY ticket_no ROWS UNBOUNDED PRECEDING) FROM tickets;<\/code><\/pre>\n
  <\/p>\n
                                    QUERY PLAN                                      -----------------------------------------------------------------------------------  WindowAgg (actual rows=2949857 loops=1)    ->  Index Only Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)          Heap Fetches: 0  Planning Time: 0.100 ms  Execution Time: 483.560 ms<\/code><\/pre>\n
  <\/p>\n
It’s faster because with RANGE you have to check all peer rows while with ROWS you don’t. Therefore, the default behavior can end up being slower.<\/p>\n
  <\/p>\n
In the 16th release, planner support functions<\/a> were added for window functions row_number<\/em>, rank<\/em>, dense_rank<\/em>, percent_rank<\/em>, cume_dist<\/em> and ntile<\/em>. With their help, the planner will always use the most optimal way to define a window frame.<\/p>\n
  <\/a>  <\/p>\n
Optimized grouping of repeating columns in GROUP BY and DISTINCT<\/a><\/strong>
  commit: 8d83a5d0<\/a><\/p>\n
  <\/p>\n
If multiple columns containing the same values are put into a group, it is sufficient to group by any one of them.<\/p>\n
  <\/p>\n
The optimization is illustrated well by the following example:<\/p>\n
  <\/p>\n
SET max_parallel_workers_per_gather = 0; SET jit = off;  15=# EXPLAIN (costs off, analyze) SELECT b.seat_no, s.seat_no FROM boarding_passes b      JOIN seats s ON b.seat_no = s.seat_no GROUP BY b.seat_no, s.seat_no;<\/code><\/pre>\n
  <\/p>\n
                                               QUERY PLAN                                                 ---------------------------------------------------------------------------------------------------------  Group (actual time=3537.559..10758.307 rows=461 loops=1)    Group Key: b.seat_no, s.seat_no ...<\/code><\/pre>\n
  <\/p>\n
The rest of the plan is omitted as irrelevant.<\/p>\n
  <\/p>\n
Here are the first two lines of the plan of the same query after the patch:<\/p>\n
  <\/p>\n
                                                QUERY PLAN                                                  -----------------------------------------------------------------------------------------------------------  HashAggregate (actual time=8201.412..8201.440 rows=461 loops=1)    Group Key: b.seat_no ...<\/code><\/pre>\n
  <\/p>\n
Note the Group Key line. In PostgreSQL 16, the planner knows that grouping by one column is enough and selects one plan based on that. In the older version, the grouping is performed on both columns, with a worse plan overall (see the execution time).<\/p>\n
  <\/p>\n
The same optimization is done for DISTINCT.<\/p>\n
  <\/p>\n
The idea of grouping by two columns containing identical values may seem strange at first, but I assume that automatic query generation systems (like various ORMs) may do that and may benefit from this bit of optimization.<\/p>\n
  <\/a>  <\/p>\n
VACUUM parameters: SKIP_DATABASE_STATS and ONLY_DATABASE_STATS<\/a><\/strong>
  commit: a46a7011<\/a><\/p>\n
  <\/p>\n
After each vacuuming, the vacuuming process updates database stats: the frozen transactions counter (pg_database.datfrozenxid<\/em>) and the multitransaction counter (pg_database.datminmxid<\/em>). These operations require a full scan of pg_class<\/em> in search of minimal relfrozenxid<\/em> and relminmxid<\/em> values. If either value is found that is greater than the current database value, the stats are updated. The more there are tables in the database, the longer this step takes.<\/p>\n
  <\/p>\n
When multiple VACUUM commands for multiple tables are executed in sequence, each one will update the counters, while it would be sufficient to only update them after the last vacuum in the sequence.<\/p>\n
  <\/p>\n
The vacuumdb<\/em> utility (in PostgreSQL 12 through 15) runs VACUUM for each table in the database. With a high enough table count (thousands, tens of thousands) it becomes extremely inefficient due to the issues mentioned above. Even parallelization (-j<\/em> key) doesn’t save the day, because only one process can update the database counters at a given time.<\/p>\n
  <\/p>\n
PostgreSQL 16 supplements the VACUUM command<\/a> with two logical parameters:<\/p>\n
  <\/p>\n