{"id":421105,"date":"2024-06-30T03:53:20","date_gmt":"2024-06-30T03:53:20","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=421105"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=421105","title":{"rendered":"PostgreSQL 16: Part 2 or CommitFest 2022-09<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

<\/p>\n

<\/p>\n

It’s official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release.<\/p>\n

<\/p>\n

Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code.<\/p>\n

<\/p>\n

If you missed the July CommitFest, our previous article<\/a> will get you up to speed in no time.<\/p>\n

<\/p>\n

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

<\/p>\n

SYSTEM_USER function<\/a>
Frozen pages\/tuples information in autovacuum’s server log<\/a>
pg_stat_get_backend_idset returns the actual backend ID<\/a>
Improved performance of ORDER BY \/ DISTINCT aggregates<\/a>
Faster bulk-loading into partitioned tables<\/a>
Optimized lookups in snapshots<\/a>
Bidirectional logical replication<\/a>
pg_auth_members: pg_auth_members: role membership granting management<\/a>
pg_auth_members: role membership and privilege inheritance<\/a>
pg_receivewal and pg_recvlogical can now handle SIGTERM<\/a><\/p>\n

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

SYSTEM_USER function<\/a><\/strong><\/p>\n

<\/p>\n

commit: 0823d061<\/a><\/p>\n

<\/p>\n

The SYSTEM_USER<\/code> function is a part of the SQL standard which has not been implemented in PostgreSQL so far. It displays information about the system user connected to the database server and the authentification method used:<\/p>\n

<\/p>\n

SELECT session_user, system_user;<\/code><\/pre>\n
  <\/p>\n
 session_user | system_user   --------------+--------------  alice        | peer:student<\/code><\/pre>\n
  <\/p>\n
The example shows that alice<\/code> has connected to the database as student<\/code> (OS username) using peer authentification. For trust authentification, the function always returns NULL.<\/p>\n
  <\/p>\n
These connection details are also obtainable from the server log (if the parameter log_connections<\/code> is on).<\/p>\n
  <\/a>  <\/p>\n
Frozen pages\/tuples information in autovacuum’s server log<\/a><\/strong><\/p>\n
  <\/p>\n
commit: d977ffd9<\/a><\/p>\n
  <\/p>\n
The autovacuum log and the VACUUM (verbose)<\/code> output now display additional information on the number of frozen pages and tuples in a given table.<\/p>\n
  <\/p>\n
CREATE TEMP TABLE t AS SELECT 42;  VACUUM (freeze, verbose) t;<\/code><\/pre>\n
  <\/p>\n
INFO:  aggressively vacuuming \"demo.pg_temp_3.t\" INFO:  finished vacuuming \"demo.pg_temp_3.t\": index scans: 0 pages: 0 removed, 1 remain, 1 scanned (100.00% of total) tuples: 0 removed, 1 remain, 0 are dead but not yet removable removable cutoff: 945, which was 0 XIDs old when operation ended new relfrozenxid: 945, which is 1 XIDs ahead of previous value frozen: 1 pages from table (100.00% of total) had 1 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 91.912 MB\/s, avg write rate: 0.000 MB\/s buffer usage: 5 hits, 4 misses, 0 dirtied WAL usage: 1 records, 0 full page images, 188 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM<\/code><\/pre>\n
  <\/p>\n
See the line that starts with frozen:<\/code>.<\/p>\n
  <\/a>  <\/p>\n
pg_stat_get_backend_idset returns the actual backend ID<\/a><\/strong>
  commit: d7e39d72<\/a><\/p>\n
  <\/p>\n
Consider a system that runs several concurrent sessions. The sessions work with temporary tables, which are stored in temporary schemas. The list of temporary schemas:<\/p>\n
  <\/p>\n
SELECT n.nspname,        regexp_replace(n.nspname, '^pg_temp_', '')::int AS backend_id FROM   pg_namespace n                                                     WHERE  n.nspname ~ '^pg_temp_';<\/code><\/pre>\n
  <\/p>\n
  nspname  | backend_id -----------+------------  pg_temp_5 |        5  pg_temp_3 |        3  pg_temp_4 |        4 (3 rows)<\/code><\/pre>\n
  <\/p>\n
Let me explain this query. The backend_id<\/code> column lists backend IDs. The system uses them to name temporary schemas. Backend IDs may be reused, and a new backend may get the same name as a recently terminated one. When a backend terminates, its temporary schemas don’t get deleted, but simply remain unused until a new backend with the same ID is created, at which point they are assigned to it.<\/p>\n
  <\/p>\n
But how do you draw a line between a temporary schema and a backend process? You may need to do that, for example, to terminate a hung up session when objects in a temporary schema prevent freezing, potentially leading to transaction ID wraparound.<\/p>\n
  <\/p>\n
The updated function pg_stat_get_backend_idset<\/code> and related statistics functions<\/a> can help you out with that. Before, the function returned indexes from the array of open sessions as the backend IDs. However, an index value for a session may change after opening or terminating a session and rebuilding statistics. The new pg_stat_get_backend_idset<\/code> now reliably returns actual backend session IDs, which can be linked to backend_id from the query above.<\/p>\n
  <\/p>\n
This allows us to retrieve a list of backend IDs and temporary schemas:<\/p>\n
  <\/p>\n
WITH tmp AS (     SELECT n.nspname,                                                        regexp_replace(n.nspname, '^pg_temp_', '')::int AS backend_id     FROM   pg_namespace n     WHERE  n.nspname ~ '^pg_temp_' ) SELECT pg_stat_get_backend_pid(backend_id) AS pid,        nspname FROM   tmp        LEFT JOIN pg_stat_get_backend_idset() AS b(backend_id)            USING (backend_id);<\/code><\/pre>\n
  <\/p>\n
  pid   |  nspname   --------+-----------  336661 | pg_temp_3  349872 | pg_temp_4         | pg_temp_5 (3 rows)<\/code><\/pre>\n
  <\/p>\n
Also note that the pg_temp_5<\/code> schema is not currently used.<\/p>\n
  <\/a>  <\/p>\n
Improved performance of ORDER BY \/ DISTINCT aggregates<\/a><\/strong>
  commit: 1349d279<\/a><\/p>\n
  <\/p>\n
Aggregates with ORDER BY<\/code> and DISTINCT<\/code> can now use indexes to skip sorting tuples when the inputs are known to be pre-sorted.<\/p>\n
  <\/p>\n
Example:<\/p>\n
  <\/p>\n
SELECT flight_no,        array_agg(status ORDER BY status),        array_agg(DISTINCT status) FROM flights GROUP BY flight_no;<\/code><\/pre>\n
  <\/p>\n
In both cases, array_agg<\/code> has to sort the statuses for each flight. Now let’s create an index:<\/p>\n
  <\/p>\n
CREATE INDEX ON flights (flight_no, status);<\/code><\/pre>\n
  <\/p>\n
The PostgreSQL 16 planner knows that the input from the index comes in pre-sorted. Therefore, the statuses from each group can be passed to the transition function right away, without the need to wait for them all to be fetched and sorted.<\/p>\n
  <\/p>\n
As a result, the operation executes on my laptop 1.5 times faster in PostgreSQL 16 than an identical operation in PostgreSQL 15.<\/p>\n
  <\/a>  <\/p>\n
Faster bulk-loading into partitioned tables<\/a><\/strong><\/p>\n
  <\/p>\n
commit: 3592e0ff<\/a><\/p>\n
  <\/p>\n
The patch increases bulk-loading and update speeds for partitioned tables. Operations that load records into the same partition will spend less time looking for specific partitions for each record. When 16 (the value is hard-coded) records in a row are loaded into the same partition, the partition’s name is cached, and if the following records must go to the same partition, they are processed faster.<\/p>\n
  <\/p>\n
The optimization works for RANGE and LIST partitions, but it’s of no use for HASH partitioned tables.<\/p>\n
  <\/p>\n
Developer testing shows a 15-20% decrease in execution time.<\/p>\n
  <\/a>  <\/p>\n
Optimized lookups in snapshots<\/a><\/strong><\/p>\n
  <\/p>\n
commit: 37a6e5df<\/a>, b6ef1675<\/a>, 82739d4a<\/a><\/p>\n
  <\/p>\n
The patch optimizes linear searches of arrays (first commit) for x86-64 (second commit) and ARM (third commit). The new algorithm using SIMD instructions was applied to snapshot->xip<\/code> array search. At a large number (hundreds) of concurrent writers, it significantly increases the visibility check speed, which is an overall performance boost.<\/p>\n
  <\/a>  <\/p>\n
Bidirectional logical replication<\/a><\/strong><\/p>\n
  <\/p>\n
commit: 36628396<\/a><\/p>\n
  <\/p>\n
Bidirectional replication occurs when server A subscribes to table changes from server B, and server B subscribes to the same table changes from server A. As a result, all changes made on either server will be applied to both.<\/p>\n
  <\/p>\n
Previous PostgreSQL releases didn’t support this replication setup. Originally, a subscriber replicated all changes made to the publisher, be it SQL commands or replicated changes. When server A runs an SQL command, server B receives and runs the command as well. The same changes, then, are sent to the first server, resulting in an infinite replication loop. To avoid it, PostgreSQL returns an error when attempting to create a second subscription that will result in a loop.<\/p>\n
  <\/p>\n
To implement bidirectional replication, the developers needed a way to apply only changes made by SQL commands but not those from replication. And they found one!<\/p>\n
  <\/p>\n
PostgreSQL can distinguish between the two sources of changes, as all logical replication changes are written into WAL together with their origins. Now, when you create a subscription, you can set the publisher to not publish any changes with replication origins.<\/p>\n
  <\/p>\n
Let’s set up bidirectional replication between node_1<\/code> and node_2<\/code>, both located on the same server, on ports 5401 and 5402.<\/p>\n
  <\/p>\n
wal_level<\/code> must allow logical replication on both nodes:<\/p>\n
  <\/p>\n
node_1=# SHOW wal_level;<\/code><\/pre>\n
  <\/p>\n
wal_level  -----------  logical (1 row)<\/code><\/pre>\n
  <\/p>\n
Let’s create a table and a publication. The node column in the primary key may look redundant, but it’s helpful for avoiding replication conflicts.<\/p>\n
  <\/p>\n
node_1=# CREATE TABLE test (              node text DEFAULT 'node_1',              id int,              PRIMARY KEY (node, id)          ); node_1=# CREATE PUBLICATION pub1 FOR TABLE test;<\/code><\/pre>\n
  <\/p>\n
Next, let’s create a table with an identical structure on the second node and a publication for it:<\/p>\n
  <\/p>\n
node_2=# CREATE TABLE test (              node text DEFAULT 'node_2',              id int,              PRIMARY KEY (node, id)          ); node_2=# CREATE PUBLICATION pub2 FOR TABLE test;<\/code><\/pre>\n
  <\/p>\n
Subscribe node_1<\/code> to the node_2<\/code> publication, and vice versa.<\/p>\n
  <\/p>\n
node_1=# CREATE SUBSCRIPTION sub1_pub2              CONNECTION 'port=5402 user=postgres dbname=postgres'              PUBLICATION pub2              WITH (copy_data = false, origin = none); node_2=# CREATE SUBSCRIPTION sub2_pub1              CONNECTION 'port=5401 user=postgres dbname=postgres'              PUBLICATION pub1              WITH (copy_data = false, origin = none);<\/code><\/pre>\n
  <\/p>\n
A few things to pay attention to:<\/p>\n
  <\/p>\n