{"id":433487,"date":"2024-09-27T15:00:20","date_gmt":"2024-09-27T15:00:20","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=433487"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=433487","title":{"rendered":"<span>PostgreSQL 17: Part 5 or CommitFest 2024-03<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-1\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/webt\/ef\/-6\/4r\/ef-64ruepz4sc0vyhzwym3erxy0.png\" data-src=\"https:\/\/habrastorage.org\/webt\/ef\/-6\/4r\/ef-64ruepz4sc0vyhzwym3erxy0.png\"\/><\/p>\n<p> <\/p>\n<p>Since the PostgreSQL 17 RC1 came out, we are <a href=\"https:\/\/wiki.postgresql.org\/wiki\/PostgreSQL_17_Open_Items#Important_Dateshttps:\/\/wiki.postgresql.org\/wiki\/PostgreSQL_17_Open_Items#Important_Dates\">on a home run<\/a> towards the official PostgreSQL release, scheduled for September 26, 2024.<\/p>\n<p> <\/p>\n<p>Let&#8217;s take a look at the patches that came in during the March CommitFest.<a name=\"habracut\"><\/a> Previous articles about PostgreSQL 17 CommitFests:\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970285\">2023-07<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970391\">2023-09<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970508\">2023-11<\/a>, <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970866\">2024-01<\/a>.<\/p>\n<p> <\/p>\n<p>Together, these give an idea of what the new PostgreSQL will look like.<\/p>\n<p> <\/p>\n<h3 id=\"reverts-after-code-freeze\"><strong>Reverts after code freeze<\/strong><\/h3>\n<p> <\/p>\n<p>Unfortunately, some previously accepted patches didn&#8217;t make it in after all. Some of the notable ones:<\/p>\n<p> <\/p>\n<ul>\n<li><a href=\"https:\/\/commitfest.postgresql.org\/47\/3659\/\">ALTER TABLE\u2026 MERGE\/SPLIT PARTITION<\/a> (revert: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/3890d90c\">3890d90c<\/a>)<\/li>\n<li><a href=\"https:\/\/commitfest.postgresql.org\/47\/4308\/\">Temporal primary, unique and foreign keys<\/a> (revert: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/8aee330a\">8aee330a<\/a>)<\/li>\n<li><a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970508#commit_d3d55ce5\">Planner: remove redundant self-joins<\/a> (revert: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d1d286d8\">d1d286d8<\/a>)<\/li>\n<li><a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970391#b0e96f31\">pg_constraint: NOT NULL constraints<\/a> (revert: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6f8bb7c\">16f8bb7c1<\/a>)<\/li>\n<\/ul>\n<p> <\/p>\n<p>Now, let&#8217;s get to the new stuff.<\/p>\n<p> <\/p>\n<h3 id=\"sql-commands\"><strong>SQL commands<\/strong><\/h3>\n<p> <\/p>\n<p><a href=\"#commit_5f2e179b\">New features of the MERGE command<\/a><br \/> <a href=\"#commit_f5a22789\">COPY\u2026 FROM: messages about discarded rows<\/a><br \/> <a href=\"#commit_6185c973\">The SQL\/JSON standard support<\/a><\/p>\n<p> <\/p>\n<h3 id=\"performance\"><strong>Performance<\/strong><\/h3>\n<p> <\/p>\n<p><a href=\"#commit_53c2a97a\">SLRU cache configuration<\/a><br \/> <a href=\"#commit_66c0185a\">Planner: Merge Append for the UNION implementation<\/a><br \/> <a href=\"#commit_a65724df\">Planner: materialized CTE statistics (continued)<\/a><br \/> <a href=\"#commit_7e0ade0f\">Optimization of parallel plans with DISTINCT<\/a><br \/> <a href=\"#commit_5bf748b8\">Optimizing B-tree scans for sets of values<\/a><br \/> <a href=\"#commit_30e14428\">VACUUM: new dead tuples storage<\/a><br \/> <a href=\"#commit_f83d7097\">VACUUM: combine WAL records for vacuuming and freezing<\/a><br \/> <a href=\"#commit_0075d789\">Functions with subtransactions in parallel processes<\/a><\/p>\n<p> <\/p>\n<h3 id=\"monitoring-and-management\"><strong>Monitoring and management<\/strong><\/h3>\n<p> <\/p>\n<p><a href=\"#commit_06286709\">EXPLAIN (analyze, serialize): data conversion costs<\/a><br \/> <a href=\"#commit_fd0398fc\">EXPLAIN: improved display of SubPlan and InitPlan nodes<\/a><br \/> <a href=\"#commit_13453eed\">pg_buffercache: eviction from cache<\/a><\/p>\n<p> <\/p>\n<h3 id=\"server\"><strong>Server<\/strong><\/h3>\n<p> <\/p>\n<p><a href=\"#commit_e6341323\">random: a random number in the specified range<\/a><br \/> <a href=\"#commit_51efe38c\">transaction_timeout: session termination when the transaction timeout is reached<\/a><br \/> <a href=\"#commit_d3ae2a24\">Prohibit the use of ALTER SYSTEM<\/a><br \/> <a href=\"#commit_ecb0fd33\">The MAINTAIN privilege and the pg_maintain predefined role<\/a><br \/> <a href=\"#commit_f69319f2\">Built-in locale provider for C.UTF8<\/a><br \/> <a href=\"#commit_d1162cfd\">pg_column_toast_chunk_id: ID of the TOAST value<\/a><br \/> <a href=\"#commit_b154d8a6\">pg_basetype function: basic domain type<\/a><br \/> <a href=\"#commit_e5da0fe3\">pg_constraint: NOT NULL restrictions for domains<\/a><br \/> <a href=\"#commit_1218ca99\">New function to_regtypemod<\/a><br \/> <a href=\"#commit_485f0aa8\">Hash indexes for ltree<\/a><\/p>\n<p> <\/p>\n<h3 id=\"replication\"><strong>Replication<\/strong><\/h3>\n<p> <\/p>\n<p><a href=\"#commit_d44032d0\">pg_createsubscriber: quickly create a logical replica from a physical one<\/a><br \/> <a href=\"#commit_007693f2\">Logical slots: tracking the causes of replication conflicts<\/a><br \/> <a href=\"#commit_a145f424\">pg_basebackup -R: dbname in primary_conninfo<\/a><br \/> <a href=\"#commit_c393308b\">Synchronization of logical replication slots between the primary server and replicas<\/a><br \/> <a href=\"#commit_5bec1d6b\">Logical decoding optimization for subtransactions<\/a><\/p>\n<p> <\/p>\n<h3 id=\"client-applications\"><strong>Client applications<\/strong><\/h3>\n<p> <\/p>\n<p><a href=\"#commit_61461a30\">libpq: non-locking query cancellation<\/a><br \/> <a href=\"#commit_d39a49c1\">libpq: direct connection via TLS<\/a><br \/> <a href=\"#commit_648928c7\">vvacuumdb, clusterdb, reindexdb: processing individual objects in multiple databases<\/a><br \/> <a href=\"#commit_47f99a40\">reindexdb: &#8212;jobs and &#8212;index at the same time<\/a><br \/> <a href=\"#commit_90f51782\">psql: new implementation of FETCH_COUNT<\/a><br \/> <a href=\"#commit_522ed12f\">pg_dump &#8212;exclude-extension<\/a><br \/> <a href=\"#commit_a45c78e3\">Backup and restore large objects<\/a><\/p>\n<p> <a name=\"commit_5f2e179b\"><\/a> <\/p>\n<h2 id=\"new-features-of-the-merge-command\"><strong>New features of the MERGE command<\/strong><\/h2>\n<p> <\/p>\n<p>commit:<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/5f2e179b\">5f2e179b<\/a>,<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/0294df2f\">0294df2f<\/a>,<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/c649fa24\">c649fa24<\/a><\/p>\n<p> <\/p>\n<p>The <code>MERGE<\/code> command, first introduced in PostgreSQL 15, receives a bunch of improvements.<\/p>\n<p> <\/p>\n<p>Here are two tables:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT * FROM source;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id | descr ----+-------   0 | Zero   1 | First (2 rows)<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT * FROM target;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id | descr ----+-------   1 | One   2 | Two (2 rows)<\/code><\/pre>\n<p> <\/p>\n<p>The first improvement is for <em><code>target<\/code><\/em>. Now the target can be <a href=\"https:\/\/commitfest.postgresql.org\/47\/4076\/\">not just a table, but also a view<\/a>. The view must be updatable or must have INSTEAD OF triggers defined for it.<\/p>\n<p> <\/p>\n<p>Let&#8217;s run MERGE so that <em><code>target<\/code><\/em> becomes the same as <em><code>source<\/code><\/em>. To do this, add the row with id=0 to target, update the row with id=1 and delete the row with id=2. Before PostgreSQL 17, the MERGE command couldn&#8217;t process target relationship rows that are not in the source. Now, there is the condition <a href=\"https:\/\/commitfest.postgresql.org\/47\/4092\/\"><code>WHEN NOT MATCHED BY SOURCE<\/code><\/a> for such rows:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">MERGE INTO target AS t      USING source AS s      ON (s.id = t.id) WHEN matched AND s.descr IS DISTINCT FROM t.descr THEN     UPDATE SET descr = s.descr WHEN NOT MATCHED BY TARGET THEN     INSERT (id, descr) VALUES (s.id, s.descr) WHEN NOT MATCHED BY SOURCE THEN     DELETE RETURNING merge_action(), s.*, t.*;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> merge_action | id | descr | id | descr --------------+----+-------+----+-------  INSERT       |  0 | Zero  |  0 | Zero  UPDATE       |  1 | First |  1 | One  DELETE       |    |       |  2 | Two (3 rows)  MERGE 3<\/code><\/pre>\n<p> <\/p>\n<p>The third improvement is in the output. It adds the <a href=\"https:\/\/commitfest.postgresql.org\/47\/4168\/\"><code>RETURNING<\/code> clause support<\/a>, namely, the <code>merge_action<\/code> function that tells you which rows were added, updated or deleted. Note that within <code>RETURNING<\/code> you can reference both source and target rows.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT * FROM target;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id | descr ----+-------   0 | Zero   1 | One (2 rows)<\/code><\/pre>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/www.depesz.com\/2024\/03\/19\/waiting-for-postgresql-17-add-returning-support-to-merge\/\">Waiting for PostgreSQL 17 \u2014 Add RETURNING support to MERGE<\/a> (Hubert &#8216;depesz&#8217; Lubaczewski)<\/p>\n<p> <a name=\"commit_f5a22789\"><\/a> <\/p>\n<h2 id=\"copy-from-messages-about-discarded-rowshttpscommitfestpostgresqlorg474798\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4798\/\"><strong>COPY\u2026 FROM: messages about discarded rows<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/f5a22789\">f5a22789<\/a><\/p>\n<p> <\/p>\n<p>In the January CommitFest article I talked about how the <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970866#commit_9e2d8701\">COPY command can now ignore format conversion errors<\/a>.<\/p>\n<p> <\/p>\n<p>This patch adds a new parameter <em><code>log_verbosity<\/code><\/em>. If set to <em><code>verbose<\/code><\/em>, the COPY output will include NOTICE-level messages for every discarded row.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE TABLE t (id int PRIMARY KEY);  COPY t FROM STDIN (on_error 'ignore', log_verbosity verbose);<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. &gt;&gt; 1 &gt;&gt; two &gt;&gt; three &gt;&gt; 4 &gt;&gt; \\. NOTICE:  skipping row due to data type incompatibility at line 2 for column id: \"two\" NOTICE:  skipping row due to data type incompatibility at line 3 for column id: \"three\" NOTICE:  2 rows were skipped due to data type incompatibility COPY 2<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT * FROM t;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id ----   1   4 (2 rows)<\/code><\/pre>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/www.depesz.com\/2024\/04\/11\/waiting-for-postgresql-17-add-new-copy-option-log_verbosity\/\">Waiting for PostgreSQL 17 \u2014 Add new COPY option LOG_VERBOSITY<\/a> (Hubert &#8216;depesz&#8217; Lubaczewski)\\<br \/> <a href=\"https:\/\/databaserookies.wordpress.com\/2024\/07\/17\/exploring-postgresql-17-a-developers-guide-to-new-features-part-3-the-copy-command-gets-more-user-friendly\/\">Exploring PostgreSQL 17: A Developer&#8217;s Guide to New Features \u2014 Part 3: The COPY Command Gets More User-Friendly<\/a> (Deepak Mahto)<\/p>\n<p> <a name=\"commit_6185c973\"><\/a> <\/p>\n<h2 id=\"the-sqljson-standard-supporthttpscommitfestpostgresqlorg474377\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4377\/\"><strong>The SQL\/JSON standard support<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6185c973\">6185c973<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/de360045\">de360045<\/a><\/p>\n<p> <\/p>\n<p>The SQL standard defines how to work with JSON using dedicated constructors, predicates and functions. All of them were <a href=\"https:\/\/commitfest.postgresql.org\/37\/2901\/\">implemented and included<\/a> in PostgreSQL 15 before its code freeze. However, just before the release, SQL\/JSON support was retracted due to performance concerns.<\/p>\n<p> <\/p>\n<p>The issue is with the need for some of the functions to be able to process incorrect arguments when they are passed to them: for example, when a function expects a JSON but gets a non-JSON string instead. Such errors were processed by means of savepoints and rollbacks. This approach hit performance hard, but was the only way to catch an error and not interrupt the whole function.<\/p>\n<p> <\/p>\n<p>In PostgreSQL 16, <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970086#commit_b8da37b3\"><code>the pg_input_error_info<\/code> and <code>pg_input_is_valid<\/code> functions were added<\/a> for soft type conversion error handling. They enabled the SQL\/JSON functionality to be refined and, ultimately, <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970086#commit_7081ac46\">get into the 16th release<\/a>.<\/p>\n<p> <\/p>\n<p>Now, most of the standard is implemented, with a few exceptions (see the second commit description.)<\/p>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/www.depesz.com\/2024\/04\/12\/waiting-for-sql-json-is-coming-back-hopefully\/\">Waiting for &#8230;: SQL\/JSON is coming back. Hopefully<\/a> (Hubert &#8216;depesz&#8217; Lubaczewski)<\/p>\n<p> <a name=\"commit_bcdfa5f2\"><\/a> <\/p>\n<h2 id=\"slru-cache-configurationhttpscommitfestpostgresqlorg474626\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4626\/\"><strong>SLRU cache configuration<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/bcdfa5f2\">bcdfa5f2<\/a><\/p>\n<p> <\/p>\n<p>In high-load systems, the size of SLRU caches, specifically the inability to increase the size of a particular cache, can become a bottleneck.<\/p>\n<p> <\/p>\n<p>In PostgreSQL 17, each cache gets a configuration parameter (with the <em><code>_buffers<\/code><\/em> suffix in the name), which allows users to fine-tune the system to a specific load. In addition, the cache lookup has been significantly optimized.<\/p>\n<p> <\/p>\n<p>New SLRU cache configuration parameters:<\/p>\n<p> <\/p>\n<ul>\n<li>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-resource.html#GUC-COMMIT-TIMESTAMP-BUFFERS\">commit_timestamp_buffers<\/a><\/p>\n<p> <\/li>\n<li>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-resource.html#GUC-MULTIXACT-MEMBER-BUFFERS\">multixact_member_buffers<\/a><\/p>\n<p> <\/li>\n<li>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-resource.html#GUC-MULTIXACT-OFFSET-BUFFERS\">multixact_offset_buffers<\/a><\/p>\n<p> <\/li>\n<li>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-resource.html#GUC-NOTIFY-BUFFERS\">notify_buffers<\/a><\/p>\n<p> <\/li>\n<li>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-resource.html#GUC-SERIALIZABLE-BUFFERS\">serializable_buffers<\/a><\/p>\n<p> <\/li>\n<li>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-resource.html#GUC-SUBTRANSACTION-BUFFERS\">subtransaction_buffers<\/a><\/p>\n<p> <\/li>\n<li>\n<p><a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-resource.html#GUC-TRANSACTION-BUFFERS\">transaction_buffers<\/a><\/p>\n<p> <\/li>\n<\/ul>\n<p> <\/p>\n<p>The names of the caches in <em><code>pg_stat_slru<\/code><\/em> view were adjusted slightly (the second commit) to correlate with the parameter names.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT name FROM pg_stat_slru;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">       name        ------------------  commit_timestamp  multixact_member  multixact_offset  notify  serializable  subtransaction  transaction  other (8 rows)<\/code><\/pre>\n<p> <a name=\"commit_66c0185a\"><\/a> <\/p>\n<h2 id=\"planner-merge-append-for-the-union-implementationhttpscommitfestpostgresqlorg474642\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4642\/\"><strong>Planner: Merge Append for the UNION implementation<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/66c0185a\">66c0185a<\/a><\/p>\n<p> <\/p>\n<p>In queries with <code>UNION<\/code>, the planner has always used the Append node to combine the results of subqueries. At the next step, either Sort or Hash Aggregate was applied to the results, then duplicates were eliminated with Unique.<\/p>\n<p> <\/p>\n<p>Here&#8217;s an impractical but simple example:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">16=# EXPLAIN (costs off) SELECT book_ref FROM bookings UNION SELECT book_ref FROM bookings;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">                    QUERY PLAN                      ---------------------------------------------------  Unique    -&gt;  Sort          Sort Key: bookings.book_ref          -&gt;  Append                -&gt;  Seq Scan on bookings                -&gt;  Seq Scan on bookings bookings_1 (6 rows)<\/code><\/pre>\n<p> <\/p>\n<p>However, if there are indexes or an <code>ORDER BY<\/code> clause, the results of subqueries may come in sorted even before they are combined. So, if Merge Append is used in place of Append, the full sort can be avoided and you can go straight to deduplication.<\/p>\n<p> <\/p>\n<p>Here&#8217;s the plan of the same query after the patch:<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">                               QUERY PLAN                                ------------------------------------------------------------------------  Unique    -&gt;  Merge Append          Sort Key: bookings.book_ref          -&gt;  Index Only Scan using bookings_pkey on bookings          -&gt;  Index Only Scan using bookings_pkey on bookings bookings_1 (5 rows)<\/code><\/pre>\n<p> <a name=\"commit_a65724df\"><\/a> <\/p>\n<h2 id=\"planner-materialized-cte-statistics-continuedhttpscommitfestpostgresqlorg474667\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4667\/\"><strong>Planner: materialized CTE statistics (continued)<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a65724df\">a65724df<\/a><\/p>\n<p> <\/p>\n<p>The fact that the planner has learned to use some column statistics from CTE to build the outer parts of the plan has already been mentioned in <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970508#commit_f7816aec\">the November commitfest article<\/a>. Now the planner will take into account the sorting of the rows returned from CTE. This avoids unnecessary repeat sorting for some queries.<\/p>\n<p> <\/p>\n<p>In this example, index access to the <em><code>bookings<\/code><\/em> table is used inside CTE, which means the output is returned already sorted. But in old versions, the planner does not know that, so it adds the Sort node anyway.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">16=# EXPLAIN (costs off) WITH b AS MATERIALIZED (     SELECT * FROM bookings ORDER BY book_ref ) SELECT * FROM b ORDER BY book_ref;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">                     QUERY PLAN                      ----------------------------------------------------  Sort    Sort Key: b.book_ref    CTE b      -&gt;  Index Scan using bookings_pkey on bookings    -&gt;  CTE Scan on b (5 rows)<\/code><\/pre>\n<p> <\/p>\n<p>In PostgreSQL 17, no additional sorting is required.<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">                      QUERY PLAN                      ----------------------------------------------------  CTE Scan on b    CTE b      -&gt;  Index Scan using bookings_pkey on bookings (3 rows)<\/code><\/pre>\n<p> <a name=\"commit_7e0ade0f\"><\/a> <\/p>\n<h2 id=\"optimization-of-parallel-plans-with-distincthttpscommitfestpostgresqlorg474724\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4724\/\"><strong>Optimization of parallel plans with DISTINCT<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/7e0ade0f\">7e0ade0f<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/902900b3\">902900b3<\/a><\/p>\n<p> <\/p>\n<p>The parallel execution of <code>DISTINCT<\/code> <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/22c4e88e\">was introduced<\/a> in PostgreSQL 15. Each worker processes its portion of data then passes the result to the leader process into the Gather node.<\/p>\n<p> <\/p>\n<p>The new patch allows the planner to choose between Gather and Gather Merge as the collection point. Gather Merge may be more efficient in cases where unique values need to be sorted.<\/p>\n<p> <\/p>\n<p>In the next example, Gather still collects results from workers. Here, parallelization stops, PostgreSQL sorts all the rows and returns the first ten. (While PostgreSQL is very efficient at sorting and returning the first several rows, it still needs to sort the whole set.)<\/p>\n<p> <\/p>\n<p>PostgreSQL 16 plan:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">16=# EXPLAIN (costs off) SELECT DISTINCT flight_id FROM ticket_flights ORDER BY 1 LIMIT 10;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">                            QUERY PLAN                              -------------------------------------------------------------------  Limit    -&gt;  Sort          Sort Key: flight_id          -&gt;  HashAggregate                Group Key: flight_id                -&gt;  Gather                      Workers Planned: 2                      -&gt;  HashAggregate                            Group Key: flight_id                            -&gt;  Parallel Seq Scan on ticket_flights (10 rows)<\/code><\/pre>\n<p> <\/p>\n<p>In PostgreSQL 17, workers sort their batches before feeding them to Gather Merge. Since the leader process receives the rows already sorted, all it has to do is deduplicate (Unique) and return the first 10 rows, saving time on sorting the whole set.<\/p>\n<p> <\/p>\n<p>PostgreSQL 17 plan:<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">                            QUERY PLAN                              -------------------------------------------------------------------  Limit    -&gt;  Unique          -&gt;  Gather Merge                Workers Planned: 2                -&gt;  Sort                      Sort Key: flight_id                      -&gt;  HashAggregate                            Group Key: flight_id                            -&gt;  Parallel Seq Scan on ticket_flights (9 rows)<\/code><\/pre>\n<p> <a name=\"commit_5bf748b8\"><\/a> <\/p>\n<h2 id=\"optimizing-b-tree-scans-for-sets-of-valueshttpscommitfestpostgresqlorg474455\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4455\/\"><strong>Optimizing B-tree scans for sets of values<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/5bf748b8\">5bf748b8<\/a><\/p>\n<p> <\/p>\n<p>This patch optimizes searches for sets of values, especially over multicolumn indexes. Here&#8217;s an example for the <em><code>ticket_flights<\/code><\/em> table, which has a unique index for two columns: <em><code>ticket_no<\/code><\/em> and <em><code>flight_id<\/code><\/em>.<\/p>\n<p> <\/p>\n<p>Let&#8217;s look up records matching a set of tickets and flights.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">16=# EXPLAIN (analyze, buffers, costs off, timing off, summary off) SELECT * FROM ticket_flights WHERE ticket_no IN ('123456789012', '234567890123', '345678901234')   AND flight_id IN (12345, 23456, 34567);<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">                                                                   QUERY PLAN                                                                      ---------------------------------------------------------------------------------------------------------------------------------------------------  Index Scan using ticket_flights_pkey on ticket_flights (actual rows=0 loops=1)    Index Cond: ((ticket_no = ANY ('{123456789012,234567890123,345678901234}'::bpchar[])) AND (flight_id = ANY ('{12345,23456,34567}'::integer[])))    Buffers: shared hit=36<\/code><\/pre>\n<p> <\/p>\n<p>The query plan in PostgreSQL 17 does not change, but only four buffers have been read instead of 36:<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">                                                                    QUERY PLAN                                                                      ---------------------------------------------------------------------------------------------------------------------------------------------------  Index Scan using ticket_flights_pkey on ticket_flights (actual rows=0 loops=1)    Index Cond: ((ticket_no = ANY ('{123456789012,234567890123,345678901234}'::bpchar[])) AND (flight_id = ANY ('{12345,23456,34567}'::integer[])))    Buffers: shared hit=4<\/code><\/pre>\n<p> <a name=\"commit_30e14428\"><\/a> <\/p>\n<h2 id=\"vacuum-new-dead-tuples-storagehttpscommitfestpostgresqlorg473687\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/3687\/\"><strong>VACUUM: new dead tuples storage<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/30e14428\">30e14428<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/667e65aa\">667e65aa<\/a><\/p>\n<p> <\/p>\n<p>VACUUM operates by scanning a table (with regards to the visibility map) for any tuples that are no longer needed. Identifiers for these tuples (TIDs) are stored in a dedicated array. The array is then used first to free up space in all indexes (which have to be scanned in full), and then in the table itself. If the TIDs do not fit into the array, the process will have to be repeated. This is bad and should be avoided by configuring autovacuuming accordingly.<\/p>\n<p> <\/p>\n<p>Before this patch, the storage array was the bottleneck of the process. Its size is defined by the <em><code>maintenance_work_mem<\/code><\/em> parameter, 64MB by default. The memory is allocated all at once, even if the dead tuples are few. On the other hand, the array size cannot exceed 1GB, even if a higher value is set.<\/p>\n<p> <\/p>\n<p>What has changed? The first commit implements a memory structure and an interface for working with TIDs based on a compressed prefix tree (radix tree). According to the patch author&#8217;s assessment (link below), TIDs take up about twenty times less space in the new storage than in the array, while the scan speed becomes several times faster. In addition, there is no longer a 1GB limit (although with such a compact layout, this is no longer so relevant), and memory is allocated as needed, rather than exactly in the amount defined by <em><code>maintenance_work_mem<\/code><\/em>.<\/p>\n<p> <\/p>\n<p>All this should help avoid most situations when one pass is not enough to vacuum a table.<\/p>\n<p> <\/p>\n<p>The example below demonstrates the results.<\/p>\n<p> <\/p>\n<p>First, prepare a table with one index:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE TABLE t(id integer) WITH (autovacuum_enabled = off); INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id); CREATE INDEX t_id ON t(id); VACUUM;<\/code><\/pre>\n<p> <\/p>\n<p>Update the table and set a low <em><code>maintenance_work_mem<\/code><\/em>, then run vacuuming. Watch how many passes it takes to vacuum the indexes.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">UPDATE t SET id = id + 1; SET maintenance_work_mem = '1MB'; VACUUM VERBOSE t;<\/code><\/pre>\n<p> <\/p>\n<p>The <code>VACUUM VERBOSE<\/code> output in PostgreSQL 16 starts with the lines:<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">INFO:  vacuuming \"postgres.public.t\" INFO:  finished vacuuming \"postgres.public.t\": index scans: 21 ...<\/code><\/pre>\n<p> <\/p>\n<p>The rest of the output is irrelevant. The dead TIDs array size of 1MB is too low, so index vacuuming was done 21 times.<\/p>\n<p> <\/p>\n<p>PostgreSQL 17 output:<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">INFO:  vacuuming \"postgres.public.t\" INFO:  finished vacuuming \"postgres.public.t\": index scans: 1 ...<\/code><\/pre>\n<p> <\/p>\n<p>All the TIDs fit into the new storage at once.<\/p>\n<p> <\/p>\n<p>Another change was made to the <em><code>pg_stat_progress_vacuum<\/code><\/em> view. The columns <em><code>max_dead_tuples<\/code><\/em> and <em><code>num_dead_tuples<\/code><\/em> have been replaced with <em><code>max_dead_tuple_bytes<\/code><\/em> and <em><code>dead_tuple_bytes<\/code><\/em>. As the column names suggest, vacuum progress is now tracked in bytes rather than in tuples, as there is no longer a predictable relationship between the number of dead tuples and the storage space occupied by their TIDs.<\/p>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/www.pgevents.ca\/events\/pgconfdev2024\/schedule\/session\/50-postgresql-meets-art-using-adaptive-radix-tree-to-speed-up-vacuuming\/\">PostgreSQL meets ART \u2014 Using Adaptive Radix Tree to speed up vacuuming<\/a> (Masahiko Sawada)<\/p>\n<p> <a name=\"commit_f83d7097\"><\/a> <\/p>\n<h2 id=\"vacuum-combine-wal-records-for-vacuuming-and-freezinghttpscommitfestpostgresqlorg474793\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4793\/\"><strong>VACUUM: combine WAL records for vacuuming and freezing<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/f83d7097\">f83d7097<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6dbb4902\">6dbb4902<\/a><\/p>\n<p> <\/p>\n<p>If rows are frozen during vacuuming, <code>VACUUM<\/code> will add one combined record to WAL instead of two separate ones. This decreases the size of WAL, which helps not only the server, but also replicas and the WAL archive.<\/p>\n<p> <\/p>\n<p>We can use <code>VACUUM VERBOSE<\/code> to assess the optimization, since it reports WAL usage.<\/p>\n<p> <\/p>\n<p>Freeze all pages in the test table:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE TABLE t(id integer) WITH (autovacuum_enabled = off); INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id); VACUUM FREEZE t;<\/code><\/pre>\n<p> <\/p>\n<p>Update the rows and run vacuum with freeze. Watch the WAL usage stats:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">UPDATE t SET id = id + 1; VACUUM FREEZE VERBOSE t;<\/code><\/pre>\n<p> <\/p>\n<p>PostgreSQL 16:<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">INFO:  aggressively vacuuming \"postgres.public.t\" ... WAL usage: 77437 records, 28718 full page images, 142916493 bytes ...<\/code><\/pre>\n<p> <\/p>\n<p>PostgreSQL 17:<\/p>\n<p> <\/p>\n<pre><code class=\"plaintext\">INFO:  aggressively vacuuming \"postgres.public.t\" ... WAL usage: 61949 records, 27227 full page images, 116712997 bytes ...<\/code><\/pre>\n<p> <\/p>\n<p>~62 thousand WAL records versus~77 thousand is very significant.<\/p>\n<p> <a name=\"commit_0075d789\"><\/a> <\/p>\n<h2 id=\"functions-with-subtransactions-in-parallel-processeshttpscommitfestpostgresqlorg474776\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4776\/\"><strong>Functions with subtransactions in parallel processes<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/0075d789\">0075d789<\/a><\/p>\n<p> <\/p>\n<p>This is a bug fix. The interesting part here is that functions with subtransactions (with the <code>EXCEPTION<\/code> block in plpgsql) can now be marked as <code>PARALLEL SAFE<\/code>. You still have to consider other restrictions, such as modifying the database.<\/p>\n<p> <\/p>\n<p>For example, if the <code>EXCEPTION<\/code> is used to catch a division by zero, and at the same time nothing is written to the database, the function can be marked safe for parallelization.<\/p>\n<p> <\/p>\n<p>However, using <code>EXCEPTION<\/code> in such situations is not the best idea. If the function doesn&#8217;t write anything to the database, then there is nothing to roll back to the savepoint in the <code>EXCEPTION<\/code> block, and creating a savepoint isn&#8217;t free by any means.<\/p>\n<p> <a name=\"commit_06286709\"><\/a> <\/p>\n<h2 id=\"explain-analyze-serialize-data-conversion-costshttpscommitfestpostgresqlorg474852\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4852\/\"><strong>EXPLAIN (analyze, serialize): data conversion costs<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/06286709\">06286709<\/a><\/p>\n<p> <\/p>\n<p><code>EXPLAIN ANALYZE<\/code> executes a query and shows the actual time spent. However, not all actions are not performed during this execution.<\/p>\n<p> <\/p>\n<p>If you compare the execution time in the plan:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">=# EXPLAIN (costs off, analyze) SELECT * FROM tickets;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">                              QUERY PLAN                                -----------------------------------------------------------------------  Seq Scan on tickets (actual time=0.080..212.350 rows=2949857 loops=1)  Planning Time: 0.060 ms  Execution Time: 275.396 ms (3 rows)<\/code><\/pre>\n<p> <\/p>\n<p>\u2026 to the actual execution time, you will see a significant difference:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">\\timing on<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">Timing is on.<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">=# SELECT * FROM tickets\\g \/dev\/null<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">Time: 1853,612 ms (00:01,854)<\/code><\/pre>\n<p> <\/p>\n<p><code>EXPLAIN ANALYZE<\/code> does not convert the query result into text or binary form for sending to the client and saves on assembling TOASTed values from individual pieces. Therefore, the more rows the query returns, the greater the margin of error.<\/p>\n<p> <\/p>\n<p>The new parameter <code>SERIALIZE<\/code> forces <code>EXPLAIN<\/code> to do these tasks as well. The cost of decompression and conversion is displayed under Serialization:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">=# EXPLAIN (costs off, analyze, serialize) SELECT * FROM tickets;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">                              QUERY PLAN                                -----------------------------------------------------------------------  Seq Scan on tickets (actual time=0.074..232.690 rows=2949857 loops=1)  Planning Time: 0.057 ms  Serialization: time=1215.195 ms  output=338792kB  format=text  Execution Time: 1568.133 ms (4 rows)<\/code><\/pre>\n<p> <\/p>\n<p>The difference between calculated and actual time is significantly lower. It&#8217;s still not exact, since <code>EXPLAIN<\/code> cannot track the time spent sending data from server to client and writing to disk.<\/p>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/www.depesz.com\/2024\/04\/11\/waiting-for-postgresql-17-invent-serialize-option-for-explain\/\">Waiting for PostgreSQL 17 \u2014 Invent SERIALIZE option for EXPLAIN<\/a> (Hubert &#8216;depesz&#8217; Lubaczewski)<\/p>\n<p> <a name=\"commit_fd0398fc\"><\/a> <\/p>\n<h2 id=\"explain-improved-display-of-subplan-and-initplan-nodeshttpscommitfestpostgresqlorg474782\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4782\/\"><strong>EXPLAIN: improved display of SubPlan and InitPlan nodes<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/fd0398fc\">fd0398fc<\/a><\/p>\n<p> <\/p>\n<p>The <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/using-explain.html#USING-EXPLAIN-BASICS\">EXPLAIN BASICS<\/a> section of the documentation is supplemented with examples of plans with SubPlan, hashed SubPlan and InitPlan nodes. Let&#8217;s consider the changes by using the following query as an example:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">EXPLAIN (costs off) SELECT * FROM bookings WHERE book_date &gt; (SELECT current_date) AND       total_amount NOT IN (VALUES (1),(2));<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">PostgreSQL 16 plan:                          QUERY PLAN                          -----------------------------------------------------------  Seq Scan on bookings    Filter: ((book_date &gt; $0) AND (NOT (hashed SubPlan 2)))    InitPlan 1 (returns $0)      -&gt;  Result    SubPlan 2      -&gt;  Values Scan on \"*VALUES*\" (6 rows)<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">PostgreSQL 17 plan:                                                     QUERY PLAN                                                     -----------------------------------------------------------------------------------------------------------------  Seq Scan on bookings    Filter: ((book_date &gt; (InitPlan 1).col1) AND (NOT (ANY (total_amount = ((hashed SubPlan 2).col1)::numeric))))    InitPlan 1      -&gt;  Result    SubPlan 2      -&gt;  Values Scan on \"*VALUES*\" (6 rows)<\/code><\/pre>\n<p> <\/p>\n<p>Look at the two differences in the second line.<\/p>\n<p> <\/p>\n<ul>\n<li>\n<p>The output parameters of the InitPlan and SubPlan nodes are now displayed not with the dollar sign (\\$0, \\$1,.., \\$n) but as (InitPlan N).colX or (SubPlan N).colX, where N is the ordinal number of the InitPlan or SubPlan node, and X is the ordinal number of the returned column.<\/p>\n<p> <\/li>\n<li>\n<p>The second condition is displayed more clearly. The uninformative <code>NOT (hashed SubPlan 2)<\/code> has been replaced by <code>NOT (ANY (total_amount = ((hashed SubPlan 2).col1)::numeric))<\/code>. Now it correlates with the condition in the query.<\/p>\n<p> <\/li>\n<\/ul>\n<p> <a name=\"commit_13453eed\"><\/a> <\/p>\n<h2 id=\"pg_buffercache-eviction-from-cachehttpscommitfestpostgresqlorg474426\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4426\/\"><strong>pg_buffercache: eviction from cache<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/13453eed\">13453eed<\/a><\/p>\n<p> <\/p>\n<p>The new function <em>pg_buffercache_evict<\/em> evicts a buffer from the buffer cache.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE EXTENSION pg_buffercache;<\/code><\/pre>\n<p> <\/p>\n<p>The test table will take up one block. Autovacuum is disabled for clarity of the experiment.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE TABLE t (id int) WITH (autovacuum_enabled=off); INSERT INTO t VALUES (1);<\/code><\/pre>\n<p> <\/p>\n<p>Find the buffer related to the table:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT bufferid, isdirty, pinning_backends FROM pg_buffercache WHERE relfilenode = (         SELECT relfilenode         FROM pg_class         WHERE oid = 't'::regclass );<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> bufferid | isdirty | pinning_backends ----------+---------+------------------       824 | t       |                0 (1 row)<\/code><\/pre>\n<p> <\/p>\n<p>Evict the buffer by feeding the new buffer number to <code>pg_buffercache_evict<\/code>:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT pg_buffercache_evict(824);<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> pg_buffercache_evict ----------------------  t (1 row)<\/code><\/pre>\n<p> <\/p>\n<p>Verify that there are no buffers for the table left in the cache.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT bufferid, isdirty, pinning_backends FROM pg_buffercache WHERE relfilenode = (         SELECT relfilenode         FROM pg_class         WHERE oid = 't'::regclass );<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> bufferid | isdirty | pinning_backends ----------+---------+------------------ (0 rows)<\/code><\/pre>\n<p> <a name=\"commit_e6341323\"><\/a> <\/p>\n<h2 id=\"random-a-random-number-in-the-specified-rangehttpscommitfestpostgresqlorg474722\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4722\/\"><strong>random: a random number in the specified range<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/e6341323\">e6341323<\/a><\/p>\n<p> <\/p>\n<p>The function <code>random<\/code> returns a random number in the range from 0 to 1. However, sometimes you want a random number within an arbitrary range. Now you can get just that!<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT random(2,5), random(0,3.14) FROM generate_series(1,5);<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> random | random --------+--------       2 |   0.43       5 |   0.98       3 |   0.12       5 |   2.18       2 |   2.43 (5 rows)<\/code><\/pre>\n<p> <\/p>\n<p>The function <code>random<\/code> now has two parameters to specify the minimum and maximum values of the returned random number. The range can be specified for int, bigint, and numeric types.<\/p>\n<p> <a name=\"commit_51efe38c\"><\/a> <\/p>\n<h2 id=\"transaction_timeout-session-termination-when-the-transaction-timeout-is-reachedhttpscommitfestpostgresqlorg474040\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4040\/\"><strong>transaction_timeout: session termination when the transaction timeout is reached<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/51efe38c\">51efe38c<\/a><\/p>\n<p> <\/p>\n<p>We already have two parameters to control long transactions: <em><code>statement_timeout<\/code><\/em> and <em><code>idle_in_transaction_session_timeout<\/code><\/em>. However, a transaction can last indefinitely if it executes sufficiently short commands that do not exceed <em><code>statement_timeout<\/code><\/em>, and the pauses between commands fit into <em><code>idle_in_transaction_session_timeout<\/code><\/em>.<\/p>\n<p> <\/p>\n<p>In such situations, <em><code>transaction_timeout<\/code><\/em> ensures that the duration of the transaction does not exceed the specified timeout. If exceeded, the transaction and the session that executed it will be terminated.<\/p>\n<p> <a name=\"commit_d3ae2a24\"><\/a> <\/p>\n<h2 id=\"prohibit-the-use-of-alter-systemhttpscommitfestpostgresqlorg474805\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4805\/\"><strong>Prohibit the use of ALTER SYSTEM<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d3ae2a24\">d3ae2a24<\/a><\/p>\n<p> <\/p>\n<p>Cloud providers usually come with their own configuration management tools, which often prohibit changing certain parameters or only accept certain parameter values. This is important for the proper service operation. For example, if the client administrator sets the <code>jsonlog<\/code> server log format instead of the expected <code>csvlog<\/code>, the log analysis tools will stop working.<\/p>\n<p> <\/p>\n<p>To avoid the temptation to edit <em><code>postgresql.conf<\/code><\/em>, access to the server file system can be restricted. However, the <code>ALTER SYSTEM<\/code> command can still modify <em><code>postgresql.auto.conf<\/code><\/em> without access to the file system.<\/p>\n<p> <\/p>\n<p>The new configuration parameter <em><code>allow_alter_system<\/code><\/em> can be used to restrict the use of <code>ALTER SYSTEM<\/code> for everyone, including superusers:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">=# SHOW allow_alter_system;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> allow_alter_system --------------------  off (1 row)<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">=# ALTER SYSTEM SET wal_level = minimal;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">ERROR:  ALTER SYSTEM is not allowed in this environment<\/code><\/pre>\n<p> <\/p>\n<p>Important note: this is not a server security feature. A superuser has multiple ways to change the server configuration and perform other potentially dangerous actions. The parameter is there to help prevent unintentional, non-malicious configuration changes.<\/p>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/lwn.net\/Articles\/968300\/\">The PostgreSQL community debates ALTER SYSTEM<\/a> (Jonathan Corbet)<\/p>\n<p> <a name=\"commit_ecb0fd33\"><\/a> <\/p>\n<h2 id=\"the-maintain-privilege-and-the-pg_maintain-predefined-rolehttpscommitfestpostgresqlorg474836\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4836\/\"><strong>The MAINTAIN privilege and the pg_maintain predefined role<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ecb0fd33\">ecb0fd33<\/a><\/p>\n<p> <\/p>\n<p>This patch has already been <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969929#commit_60684dd8\">discussed<\/a> in the PostgreSQL 16 CommitFest articles, but after the code freeze it has been rolled back due to security concerns. The concerns have been resolved, and PostgreSQL 17 brings a new privilege <code>MAINTAIN<\/code> for tables and materialized views.<\/p>\n<p> <\/p>\n<p>The privilege allows users to execute the following commands: <code>ANALYZE<\/code>, <code>VACUUM<\/code> (including <code>VACUUM FULL<\/code>), <code>CLUSTER<\/code>, <code>REINDEX<\/code>, <code>REFRESH MATERIALIZED VIEW<\/code> and <code>LOCK TABLE<\/code>.<\/p>\n<p> <\/p>\n<p>Membership in the new role <em><code>pg_maintain<\/code><\/em> grants the <code>MAINTAIN<\/code> privilege for all relations within a database.<\/p>\n<p> <a name=\"commit_f69319f2\"><\/a> <\/p>\n<h2 id=\"built-in-locale-provider-for-cutf8httpscommitfestpostgresqlorg474365\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4365\/\"><strong>Built-in locale provider for C.UTF8<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/f69319f2\">f69319f2<\/a><\/p>\n<p> <\/p>\n<p>For a long time, libc was the only provider in PostgreSQL. It provided locales of the C system library. In PostgreSQL 10, icu was added, using the external library ICU. PostgreSQL brings to the table the built-in provider with support for C and C.UTF8 locales.<\/p>\n<p> <\/p>\n<p>Functionally, nothing has changed. Both locales have been around and provided by libc for a long time. The ascetic locale of C still understands only the Latin alphabet, but C.UTF8 is a bit more interesting.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE DATABASE test_builtin_provider   LOCALE_PROVIDER = 'builtin'   BUILTIN_LOCALE = 'C.UTF8'   TEMPLATE = 'template0';<\/code><\/pre>\n<p> <\/p>\n<p>In the C.UTF8 locale, character classification works correctly for any language (which characters are letters, are they upper or lower case), but letters are sorted by character codes, disregarding any linguistic nuances. For example, the sorting for the Cyrillic letter \\&#187;\u0451\\&#187; is incorrect:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">WITH russian_alphabet AS (   SELECT regexp_split_to_table('\u0430\u0431\u0432\u0433\u0434\u0435\u0451\u0436\u0437\u0438\u0439\u043a\u043b\u043c\u043d\u043e\u043f\u0440\u0441\u0442\u0443\u0444\u0445\u0446\u0447\u0448\u0449\u044a\u044b\u044c\u044d\u044e\u044f', '') AS letter ) SELECT string_agg(lower(letter), '' ORDER BY lower(letter)) AS lower,        string_agg(upper(letter), '' ORDER BY upper(letter)) AS upper FROM russian_alphabet\\gx<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">-[ RECORD 1 ]---------------------------- lower | \u0430\u0431\u0432\u0433\u0434\u0435\u0436\u0437\u0438\u0439\u043a\u043b\u043c\u043d\u043e\u043f\u0440\u0441\u0442\u0443\u0444\u0445\u0446\u0447\u0448\u0449\u044a\u044b\u044c\u044d\u044e\u044f\u0451 upper | \u0401\u0410\u0411\u0412\u0413\u0414\u0415\u0416\u0417\u0418\u0419\u041a\u041b\u041c\u041d\u041e\u041f\u0420\u0421\u0422\u0423\u0424\u0425\u0426\u0427\u0428\u0429\u042a\u042b\u042c\u042d\u042e\u042f<\/code><\/pre>\n<p> <\/p>\n<p>If this sorting is acceptable, the use of C.UTF8 from the built-in provider gives an advantage:<\/p>\n<p> <\/p>\n<ul>\n<li>\n<p>Sorting and case change are quicker due to internal optimizations. See the link below for some timings.<\/p>\n<p> <\/li>\n<li>\n<p>The locale is independent from external libraries and works identically on any platform on the same major PostgreSQL version.<\/p>\n<p> <\/li>\n<\/ul>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/postgresql.verite.pro\/blog\/2024\/07\/01\/pg17-utf8-collation.html\">Looking at the new built-in collation provider in PostgreSQL 17<\/a> (Daniel V\u00e9rit\u00e9)<\/p>\n<p> <a name=\"commit_d1162cfd\"><\/a> <\/p>\n<h2 id=\"pg_column_toast_chunk_id-id-of-the-toast-valuehttpscommitfestpostgresqlorg474267\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4267\/\"><strong>pg_column_toast_chunk_id: ID of the TOAST value<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d1162cfd\">d1162cfd<\/a><\/p>\n<p> <\/p>\n<p>The values of columns with variable length types can be transferred to a TOAST table. But which values didn&#8217;t fit into the main table and were TOASTed?<\/p>\n<p> <\/p>\n<p>For the demonstration, let&#8217;s create a table with three rows containing oversized numeric values.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE TABLE t(     id integer,     n numeric );<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">INSERT INTO t VALUES     (1,    123456789::numeric ^ 123::numeric),     (2,    123456789::numeric ^ 1234::numeric),     (3,    123456789::numeric ^ 12345::numeric);<\/code><\/pre>\n<p> <\/p>\n<p>Are these values stored in the main table or in the TOAST table? The new function <code>pg_column_toast_chunk_id<\/code> helps answer this question: it returns <em><code>chunk_id<\/code><\/em>, the identifier of a large value in the TOAST table.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT id, pg_column_toast_chunk_id(n) FROM t;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id | pg_column_toast_chunk_id ----+--------------------------   1 |                            2 |                            3 |                    24587 (3 rows)<\/code><\/pre>\n<p> <\/p>\n<p>As you can see, only the value from the third row did not fit in the main table.<\/p>\n<p> <a name=\"commit_b154d8a6\"><\/a> <\/p>\n<h2 id=\"pg_basetype-function-basic-domain-typehttpscommitfestpostgresqlorg474568\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4568\/\"><strong>pg_basetype function: basic domain type<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/b154d8a6\">b154d8a6<\/a><\/p>\n<p> <\/p>\n<p>The <code>pg_basetype<\/code> function returns the base domain type. In the case of nested domains, the function will recursively scan them in search of the base type:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE DOMAIN posint AS integer CHECK (VALUE &gt; 0); CREATE DOMAIN dozen AS posint CHECK (VALUE &lt; 13);  SELECT pg_basetype('dozen'::regtype);<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> pg_basetype -------------  integer (1 row)<\/code><\/pre>\n<p> <a name=\"commit_e5da0fe3\"><\/a> <\/p>\n<h2 id=\"pg_constraint-not-null-restrictions-for-domainshttpscommitfestpostgresqlorg474678\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4678\/\"><strong>pg_constraint: NOT NULL restrictions for domains<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/e5da0fe3\">e5da0fe3<\/a><\/p>\n<p> <\/p>\n<p>In the September CommitFest article I mentioned how <em><code>pg_constraint<\/code><\/em> now records <code>NOT NULL<\/code> constraints for table columns. Unfortunately, that patch was <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6f8bb7c1\">reverted<\/a>.<\/p>\n<p> <\/p>\n<p>But the <code>NOT NULL<\/code> constraints will still appear in <em><code>pg_constraint<\/code><\/em>, if only for domains.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">CREATE DOMAIN int_nn AS integer NOT NULL;  SELECT * FROM pg_constraint WHERE conname ~ 'int_nn'\\gx<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">-[ RECORD 1 ]--+---------------- oid            | 16573 conname        | int_nn_not_null connamespace   | 2200 contype        | n condeferrable  | f condeferred    | f convalidated   | t conrelid       | 0 contypid       | 16572 conindid       | 0 conparentid    | 0 confrelid      | 0 confupdtype    |   confdeltype    |   confmatchtype  |   conislocal     | t coninhcount    | 0 connoinherit   | f conkey         | confkey        | conpfeqop      | conppeqop      | conffeqop      | confdelsetcols | conexclop      | conbin         |<\/code><\/pre>\n<p> <a name=\"commit_1218ca99\"><\/a> <\/p>\n<h2 id=\"new-function-to_regtypemodhttpscommitfestpostgresqlorg474807\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4807\/\"><strong>New function to_regtypemod<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1218ca99\">1218ca99<\/a><\/p>\n<p> <\/p>\n<p>A combination of the already existing functions <code>format_type<\/code> and <code>to_regtype<\/code>, together with the new function <code>to_regtypemod<\/code>, will help convert the name of a data type specified in any valid form to the canonicalized form.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">WITH types (alias) AS (     VALUES ('timestamp(5)'),            ('char(10)'),            ('varchar(30)') ) SELECT alias,        format_type(to_regtype(alias), to_regtypemod(alias)) FROM types;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">    alias     |          format_type            --------------+--------------------------------  timestamp(5) | timestamp(5) without time zone  char(10)     | character(10)  varchar(30)  | character varying(30) (3 rows)<\/code><\/pre>\n<p> <a name=\"commit_485f0aa8\"><\/a> <\/p>\n<h2 id=\"hash-indexes-for-ltreehttpscommitfestpostgresqlorg474375\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4375\/\"><strong>Hash indexes for ltree<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/485f0aa8\">485f0aa8<\/a><\/p>\n<p> <\/p>\n<p>The ltree data type (of the ltree extension) now supports hash indexes.<\/p>\n<p> <a name=\"commit_d44032d0\"><\/a> <\/p>\n<h2 id=\"pg_createsubscriber-quickly-create-a-logical-replica-from-a-physical-onehttpscommitfestpostgresqlorg474637\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4637\/\"><strong>pg_createsubscriber: quickly create a logical replica from a physical one<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d44032d0\">d44032d0<\/a><\/p>\n<p> <\/p>\n<p>The usual logical replication setup process for a large database can take a long time due to the initial data synchronization. It&#8217;s even worse if the database is actively updated.<\/p>\n<p> <\/p>\n<p>If that is the case, you can now use a physical replica as a foundation, since it already has all the tables synchronized. If you switch the physical replica to read\/write mode, create publication\/subscription pairs in one or more databases on both servers, and specify the correct position in the subscription properties from which to continue receiving changes, then a logical replica will be created fairly quickly. This is a simplified description of what the new <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/app-pgcreatesubscriber.html\">pg_createsubscriber<\/a> server utility does. See the link below for a full description.<\/p>\n<p> <\/p>\n<p>Initialize the primary server on port 5400. Set <em><code>wal_level<\/code><\/em> to <code>logical<\/code>. Test it with a single table in the <em><code>postgres<\/code><\/em> database.<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ PGPORT=5400; initdb -U postgres -D .\/primary -c port=5400 -c wal_level=logical $ pg_ctl start -D .\/primary -l .\/primary\/log<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">primary=# CREATE TABLE test(id int); primary=# INSERT INTO test (id) VALUES (1);<\/code><\/pre>\n<p> <\/p>\n<p>The physical replica <em><code>standby<\/code><\/em> will run on port 5401.<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ pg_basebackup -d 'port=5400 user=postgres' -c fast -R -D .\/standby $ echo 'port=5401' &gt;&gt;.\/standby\/postgresql.auto.conf $ pg_ctl start -D .\/standby -l .\/standby\/log<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">standby=# SELECT pg_is_in_recovery();<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">-------------------  t (1 row)<\/code><\/pre>\n<p> <\/p>\n<p>The replica must be stopped before conversion.<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ pg_ctl stop -D .\/standby<\/code><\/pre>\n<p> <\/p>\n<p>The <code>pg_createsubscriber<\/code> tool runs on the physical replica server.<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ pg_createsubscriber -d postgres -U postgres -D .\/standby -P 'port=5400 user=postgres'<\/code><\/pre>\n<p> <\/p>\n<p>As it works, the tool outputs a lot of diagnostics data about the actions being performed. If the conversion completes without errors, the logical replica is ready.<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ pg_ctl start -D .\/standby -l .\/standby\/log<\/code><\/pre>\n<p> <\/p>\n<p>A publication has been created in the <em><code>postgres<\/code><\/em> database on the primary server for all tables.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">primary=# \\x \\dRp<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">Expanded display is on. List of publications -[ RECORD 1 ]------------------------------ Name       | pg_createsubscriber_5_d3ba035a Owner      | postgres All tables | t Inserts    | t Updates    | t Deletes    | t Truncates  | t Via root   | f<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">primary=# INSERT INTO test (id) VALUES (2);<\/code><\/pre>\n<p> <\/p>\n<p>And a subscription to the publication has been created on the <em><code>standby<\/code><\/em> server in the same database.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">standby=# SELECT * FROM test;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id ----   1   2 (2 rows)<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">standby=# \\x \\dRp<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">Expanded display is on. List of subscriptions -[ RECORD 1 ]--------------------------------- Name        | pg_createsubscriber_5_dec1faeb Owner       | postgres Enabled     | t Publication | {pg_createsubscriber_5_dec1faeb}<\/code><\/pre>\n<p> <a name=\"commit_007693f2\"><\/a> <\/p>\n<h2 id=\"logical-slots-tracking-the-causes-of-replication-conflictshttpscommitfestpostgresqlorg464758\"><a href=\"https:\/\/commitfest.postgresql.org\/46\/4758\/\"><strong>Logical slots: tracking the causes of replication conflicts<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/007693f2\">007693f2<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6ae701b4\">6ae701b4<\/a><\/p>\n<p> <\/p>\n<p>In the <em><code>pg_stat_replication<\/code><\/em> view, in the <em><code>conflicting<\/code><\/em> column, you can get information that the logical slot is invalid, and in the <em><code>invalidation_reason<\/code><\/em> column, you can find out why. The following reasons can currently be monitored: <code>wal_removed<\/code>, <code>rows_removed<\/code>, <code>wal_level_insufficient<\/code>. See the <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/view-pg-replication-slots.html\">documentation<\/a> for more.<\/p>\n<p> <a name=\"commit_a145f424\"><\/a> <\/p>\n<h2 id=\"pg_basebackup--r-dbname-in-primary_conninfohttpscommitfestpostgresqlorg474841\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4841\/\"><strong>pg_basebackup -R: dbname in primary_conninfo<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a145f424\">a145f424<\/a><\/p>\n<p> <\/p>\n<p>In the September CommitFest article I talked about <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970391#cca97ce6\">the new ability<\/a> to specify the dbname parameter when launching <code>pg_basebackup<\/code> and <code>pg_receivewal<\/code>.<\/p>\n<p> <\/p>\n<p>Now, if a database was specified in the connection string when running <code>pg_basebackup<\/code> with the <code>-R<\/code> parameter, dbname will be included in the <em><code>primary_conninfo<\/code><\/em> parameter in <em><code>postgresql.auto.conf<\/code><\/em>. This helps when configuring a replica with replication slots synchronization.<\/p>\n<p> <a name=\"commit_c393308b\"><\/a> <\/p>\n<h2 id=\"synchronization-of-logical-replication-slots-between-the-primary-server-and-replicashttpscommitfestpostgresqlorg474423\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4423\/\"><strong>Synchronization of logical replication slots between the primary server and replicas<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/c393308b\">c393308b<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/73292404\">73292404<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/776621a5\">776621a5<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ddd5f4f5\">ddd5f4f5<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/93db6cbd\">93db6cbd<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/bf279ddd\">bf279ddd<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a11f330b\">a11f330b<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/2ec005b4\">2ec005b4<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6d49c8d4\">6d49c8d4<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6f132ed6\">6f132ed6<\/a><\/p>\n<p> <\/p>\n<p>This set of patches aims to ensure smooth logical replication when switching from the publisher to a replica. The problem: logical slots are not transferred to replicas, so when a failure or a planned switchover occurs and a replica becomes the publisher, logical replication on subscribers stops.<\/p>\n<p> <\/p>\n<p>The solution: to transfer the logical slots to the replica and <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS-SYNCHRONIZATION\">keep them up to date<\/a>. Then, after switching to the replica, all you need to do is to set the connection string to the new server in the subscription settings and continue receiving changes without data loss.<\/p>\n<p> <\/p>\n<p>Let&#8217;s set up three local servers on different ports:<\/p>\n<p> <\/p>\n<ul>\n<li>\n<p>pub_primary \u2015 publishing server (port 5400)<\/p>\n<p> <\/li>\n<li>\n<p>pub_standby \u2015 physical replica of the publishing server (port 5401)<\/p>\n<p> <\/li>\n<li>\n<p>sub_server \u2015 subscriber to the publication on pub_primary (port 5402)<\/p>\n<p> <\/li>\n<\/ul>\n<p> <\/p>\n<p>After switchover from <em><code>pub_primary<\/code><\/em> to <em><code>pub_standby<\/code><\/em>, we want <em><code>sub_server<\/code><\/em> to start listening to updates from <em><code>pub_standby<\/code><\/em> with no data loss.<\/p>\n<p> <\/p>\n<h5 id=\"pub_primary\"><strong>pub_primary<\/strong><\/h5>\n<p> <\/p>\n<p>Initialize <em><code>pub_primary<\/code><\/em> and <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970086#commit_3e51b278\">immediately set<\/a> the <em><code>wal_level<\/code><\/em> required for logical replication.<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ PGPORT=5400; initdb -U postgres -D .\/pub_primary -c port=5400 -c wal_level=logical -c cluster_name=pub_primary $ pg_ctl start -D .\/pub_primary -l .\/pub_primary\/log<\/code><\/pre>\n<p> <\/p>\n<p>The future replica <em><code>pub_standby<\/code><\/em> must operate through a physical slot. Set it up manually:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_primary=# SELECT pg_create_physical_replication_slot('standby_slot');<\/code><\/pre>\n<p> <\/p>\n<p>The publication consists of a single table in the schema <em><code>public<\/code><\/em> in the database <em><code>postgres<\/code><\/em>. The <em><code>descr<\/code><\/em> column is filled with the name of the current cluster by default.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_primary=# CREATE TABLE test(     id int,     descr text DEFAULT current_setting('cluster_name') ); pub_primary=# INSERT INTO test (id) VALUES (1) RETURNING *;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id |    descr     ----+-------------   1 | pub_primary (1 row)<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_primary=# CREATE PUBLICATION test_pub FOR TABLE test;<\/code><\/pre>\n<p> <\/p>\n<h5 id=\"pub_standby\"><strong>pub_standby<\/strong><\/h5>\n<p> <\/p>\n<p>Create a replica from a physical backup of <em><code>pub_primary<\/code><\/em>, which was created with <code>pg_basebackup<\/code>. Use the slot created earlier.<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ pg_basebackup -d 'port=5400 user=postgres dbname=postgres' -S standby_slot -R -D .\/pub_standby<\/code><\/pre>\n<p> <\/p>\n<p><code>pg_basebackup<\/code> with the flag <code>-R<\/code> will create a <em><code>postgresql.auto.conf<\/code><\/em> file with parameters required to run the backup as a replica. Adjust the configuration:<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ echo 'port=5401' &gt;&gt;.\/pub_standby\/postgresql.auto.conf $ echo 'cluster_name=pub_standby' &gt;&gt;.\/pub_standby\/postgresql.auto.conf $ echo 'sync_replication_slots=on' &gt;&gt;.\/pub_standby\/postgresql.auto.conf $ echo 'hot_standby_feedback=on' &gt;&gt;.\/pub_standby\/postgresql.auto.conf<\/code><\/pre>\n<p> <\/p>\n<p>The parameters <em><code>port<\/code><\/em> and <em><code>cluster_name<\/code><\/em> are self-explanatory. The new parameter <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-replication.html#GUC-SYNC-REPLICATION-SLOTS\">sync_replication_slots<\/a> deserves a closer look. It initiates a dedicated process <code>slotsync worker<\/code> on the replica. This process creates logical slots on <em><code>pub_standby<\/code><\/em> mirroring those on <em><code>pub_primary<\/code><\/em> and ensures their ongoing synchronization. (The slots still can be synchronized manually by running the function <code>pg_sync_replication_slots<\/code> on the replica.)<\/p>\n<p> <\/p>\n<p>Note the <em><code>dbname<\/code><\/em> parameter in the <code>pg_basebackup<\/code> connection string. It is <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5970391#cca97ce6\">not necessary<\/a> to specify a database to create a copy of the cluster. The parameter <em><code>dbname<\/code><\/em> is there in order for <code>pg_basebackup<\/code> to transfer it to the <em><code>primary_conninfo<\/code><\/em> connection string. This is necessary to synchronize logical slots that are defined at the database level.<\/p>\n<p> <\/p>\n<p>In addition to that, the replica must have feedback enabled, hence the parameter <em><code>hot_standby_feedback<\/code><\/em>.<\/p>\n<p> <\/p>\n<p>Start the replica and check the configuration.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pg_ctl start -D .\/pub_standby -l .\/pub_standby\/log<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_standby=# \\dconfig primary_*|hot_*|sync_*|wal_level<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">                                                                                                                                                            List of configuration parameters        Parameter        |                                                                                                                                                             Value                                                                                                                                                               ------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  hot_standby            | on  hot_standby_feedback   | on  primary_conninfo       | user=postgres passfile='\/home\/pal\/.pgpass' channel_binding=disable port=5400 sslmode=disable sslnegotiation=postgres sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable dbname=postgres  primary_slot_name      | standby_slot  sync_replication_slots | on  wal_level              | logical (6 rows)<\/code><\/pre>\n<p> <\/p>\n<p>The new process <code>slotsync worker<\/code> is already running:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">SELECT datname, pid, wait_event_type, wait_event, state FROM pg_stat_activity WHERE backend_type = 'slotsync worker' \\gx<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">-[ RECORD 1 ]---+------------------------ datname         | postgres pid             | 171422 wait_event_type | Activity wait_event      | ReplicationSlotsyncMain state           |<\/code><\/pre>\n<p> <\/p>\n<p>There are no logical replication slots on <em><code>pub_primary<\/code><\/em> yet, so there&#8217;s nothing to sync. Now is the time to set up the subscription server.<\/p>\n<p> <\/p>\n<h5 id=\"sub_server\"><strong>sub_server<\/strong><\/h5>\n<p> <\/p>\n<p>Cluster initialization and startup:<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ PGPORT=5402; initdb -U postgres -D .\/sub_server -c port=5402 -c cluster_name=sub_server $ pg_ctl start -D .\/sub_server -l .\/sub_server\/log<\/code><\/pre>\n<p> <\/p>\n<p>Create a table and subscribe to updates from <em><code>pub_primary<\/code><\/em>:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">sub_server=# CREATE TABLE test(     id int,     descr text DEFAULT current_setting('cluster_name') ); sub_server=# CREATE SUBSCRIPTION test_sub     CONNECTION 'port=5400 user=postgres dbname=postgres'     PUBLICATION test_pub WITH (failover = true);<\/code><\/pre>\n<p> <\/p>\n<p>Enable the new subscription parameter <code>failover<\/code>. It will add the attribute <code>failover<\/code> to the logical replication slot we are going to create on <em><code>pub_primary<\/code><\/em>. Only slots with this attribute will synchronize with the replica. (The function <code>pg_create_logical_replication_slot<\/code> also gets the same parameter to create failover slots.)<\/p>\n<p> <\/p>\n<h5 id=\"verify-that-the-slots-synchronize\"><strong>Verify that the slots synchronize<\/strong><\/h5>\n<p> <\/p>\n<p>Check that the created logical slot on <em><code>pub_primary<\/code><\/em> has the <code>failover<\/code> attribute:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_primary=# SELECT slot_name, slot_type, database, failover, synced, inactive_since FROM pg_replication_slots WHERE slot_name = 'test_sub'<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">-[ RECORD 1 ]--+--------- slot_name      | test_sub slot_type      | logical database       | postgres failover       | t synced         | f inactive_since |<\/code><\/pre>\n<p> <\/p>\n<p>The slot on the replica exists and synchronizes with <em><code>pub_primary<\/code><\/em> (<code>synced<\/code>). The last sync time is listed under <em><code>inactive_since<\/code><\/em>.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_standby=# SELECT slot_name, slot_type, database, failover, synced, inactive_since FROM pg_replication_slots<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">-[ RECORD 1 ]--+------------------------------ slot_name      | test_sub slot_type      | logical database       | postgres failover       | t synced         | t inactive_since | 2024-08-12 14:20:07.351486+03<\/code><\/pre>\n<p> <\/p>\n<p>There&#8217;s one more thing to be taken care of. An asynchronous replica may receive updates later than a subscriber does because of lag or other reasons. If the publisher crashes and switches over to the replica, the subscriber will no longer receive updates from the replica, since it is now ahead of it. To ensure that the subscriber does not get ahead, the name of the physical slot is set in the new parameter <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-replication.html#GUC-SYNCHRONIZED-STANDBY-SLOTS\">synchronized_standby_slots<\/a> on the publication server:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_primary=# ALTER SYSTEM SET synchronized_standby_slots = 'standby_slot'; pub_primary=# SELECT pg_reload_conf();<\/code><\/pre>\n<p> <\/p>\n<p>The parameter stores a list of physical replication slots. WAL sender processes serving logical replication will send data to subscribers only after all slots from this list confirm receipt of WAL records. In case of lag on the replica, subscribers will not receive records ahead of it.<\/p>\n<p> <\/p>\n<p>Now all the preparations have been completed. Add a second row to the table at the publisher:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_primary=# INSERT INTO test (id) VALUES (2) RETURNING *;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id |    descr     ----+-------------   2 | pub_primary (1 row)<\/code><\/pre>\n<p> <\/p>\n<h5 id=\"crash-and-switchover-to-replica\"><strong>Crash and switchover to replica<\/strong><\/h5>\n<p> <\/p>\n<p>To simulate a crash, stop <em><code>pub_primary<\/code><\/em> in <code>immediate<\/code> mode:<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ pg_ctl stop -m immediate -D .\/pub_primary<\/code><\/pre>\n<p> <\/p>\n<p>Before switching to the replica, the subscription should be disabled.<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">sub_server=# ALTER SUBSCRIPTION test_sub DISABLE;<\/code><\/pre>\n<p> <\/p>\n<p>Switch the replica to read\/write mode:<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ pg_ctl promote  -D .\/pub_standby<\/code><\/pre>\n<p> <\/p>\n<p>Change the connection string in the subscription and enable it:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">sub_server=# ALTER SUBSCRIPTION test_sub     CONNECTION 'port=5401 user=postgres dbname=postgres'; sub_server=# ALTER SUBSCRIPTION test_sub ENABLE;<\/code><\/pre>\n<p> <\/p>\n<h5 id=\"verify-that-the-logical-replication-works\"><strong>Verify that the logical replication works<\/strong><\/h5>\n<p> <\/p>\n<p>On the new publisher, add a third row to the table:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">pub_standby=# INSERT INTO test (id) VALUES (3) RETURNING *;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id |    descr     ----+-------------   3 | pub_standby (1 row)<\/code><\/pre>\n<p> <\/p>\n<p>The subscriber receives updates:<\/p>\n<p> <\/p>\n<pre><code class=\"pgsql\">sub_server=# SELECT * FROM test;<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\"> id |    descr     ----+-------------   1 | pub_primary   2 | pub_primary   3 | pub_standby (3 rows)<\/code><\/pre>\n<p> <\/p>\n<p>Conclusion. In order for the subscriber to continue receiving updates after switchover to a physical replica, you need to do the following:<\/p>\n<p> <\/p>\n<ul>\n<li>\n<p>Add the <code>failover<\/code> attribute to the logical slot at the publisher by setting the corresponding parameter in the subscription configuration.<\/p>\n<p> <\/li>\n<li>\n<p>The replica must run on a physical slot with feedback enabled.<\/p>\n<p> <\/li>\n<li>\n<p>The replica must have the parameter <em><code>sync_replication_slots<\/code><\/em> enabled to start the <code>slotsync worker<\/code> process. This process synchronizes slots with the main publishing server.<\/p>\n<p> <\/li>\n<li>\n<p>On the publishing server, the physical slot should be added to <em><code>synchronized_standby_slots<\/code><\/em>. This ensures that subscribers will not apply updates before the replica does.<\/p>\n<p> <\/li>\n<li>\n<p>After switchover, you just need to change the connection string in the subscription configuration.<\/p>\n<p> <\/li>\n<\/ul>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/bdrouvot.github.io\/2024\/03\/16\/postgres-17-highlight-logical-replication-slots-synchronization\/\">Postgres 17 highlight: Logical replication slots synchronization<\/a> (Bertrand Drouvot)<\/p>\n<p> <a name=\"commit_5bec1d6b\"><\/a> <\/p>\n<h2 id=\"logical-decoding-optimization-for-subtransactionshttpscommitfestpostgresqlorg474699\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4699\/\"><strong>Logical decoding optimization for subtransactions<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/5bec1d6b\">5bec1d6b<\/a><\/p>\n<p> <\/p>\n<p>Logical decoding and, consequently, logical replication have been optimized significantly when it comes to processing a lot of subtransactions.<\/p>\n<p> <\/p>\n<p>Developers&#8217; own tests on a transaction containing 100,000 subtransactions show an increase in performance by over 30 times.<\/p>\n<p> <a name=\"commit_61461a30\"><\/a> <\/p>\n<h2 id=\"libpq-non-locking-query-cancellationhttpscommitfestpostgresqlorg473511\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/3511\/\"><strong>libpq: non-locking query cancellation<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/61461a30\">61461a30<\/a><\/p>\n<p> <\/p>\n<p>libpq gets a new interface for non-locking query cancellation. The existing <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/libpq-cancel.html#LIBPQ-PQCANCEL\">PQcancel<\/a> call has been deprecated because it not only makes the application wait for the query to complete, but is also not secure.<\/p>\n<p> <\/p>\n<p>The new interface can be used by drivers working through libpq. In particular, there are plans to implement non-locking query cancellation in psycopg 3.2.<\/p>\n<p> <\/p>\n<p>See also:<br \/> <a href=\"https:\/\/blog.dalibo.com\/2024\/04\/15\/improved-query-cancellation-in-postgresql-17-and-psycopg-3.2.html\">Improved query cancellation in PostgreSQL 17 and Psycopg 3.2<\/a> (Denis Laxalde)<\/p>\n<p> <a name=\"commit_d39a49c1\"><\/a> <\/p>\n<h2 id=\"libpq-direct-connection-via-tlshttpscommitfestpostgresqlorg474742\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4742\/\"><strong>libpq: direct connection via TLS<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d39a49c1\">d39a49c1<\/a><\/p>\n<p> <\/p>\n<p>Client applications running through libpq can use the new parameter <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/libpq-connect.html#LIBPQ-CONNECT-SSLNEGOTIATION\">sslnegotiation<\/a> in the connection string.<\/p>\n<p> <\/p>\n<p>With <code>sslnegotiation=direct<\/code>, when connecting to servers running PostgreSQL 17 or newer that support <a href=\"https:\/\/ru.wikipedia.org\/wiki\/ALPN\">ALPN<\/a>, the client and server can forego requesting and approving the use of TLS, which reduces the connection time.<\/p>\n<p> <\/p>\n<p>Direct connection is disabled by default.<\/p>\n<p> <a name=\"commit_648928c7\"><\/a> <\/p>\n<h2 id=\"vacuumdb-clusterdb-reindexdb-processing-individual-objects-in-multiple-databaseshttpscommitfestpostgresqlorg474417\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4417\/\"><strong>vacuumdb, clusterdb, reindexdb: processing individual objects in multiple databases<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/648928c7\">648928c7<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1b49d56d\">1b49d56d<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/24c928ad\">24c928ad<\/a><\/p>\n<p> <\/p>\n<p>If there are identical objects in different databases in the same cluster, then they can be processed in one call by the <code>vacuumdb<\/code>, <code>clusterdb<\/code> and <code>reindexdb<\/code> utilities. For example, vacuum all <em><code>pg_catalog<\/code><\/em> schemas:<\/p>\n<p> <\/p>\n<pre><code class=\"bash\">$ vacuumdb --all --schema=pg_catalog<\/code><\/pre>\n<p> <\/p>\n<pre><code class=\"plaintext\">vacuumdb: vacuuming database \"demo\" vacuumdb: vacuuming database \"postgres\" vacuumdb: vacuuming database \"template1\"<\/code><\/pre>\n<p> <a name=\"commit_47f99a40\"><\/a> <\/p>\n<h2 id=\"reindexdb---jobs-and---index-at-the-same-timehttpscommitfestpostgresqlorg474740\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4740\/\"><strong>reindexdb: &#8212;jobs and &#8212;index at the same time<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/47f99a40\">47f99a40<\/a><\/p>\n<p> <\/p>\n<p><code>reindexdb<\/code> had a restriction on working in parallel mode (<code>--jobs<\/code>) when trying to reindex individual indexes specified in several <code>--index<\/code> parameters, because indexes belonging to the same table cannot be rebuild in parallel. However, the utility couldn&#8217;t rebuild indexes from different tables in parallel mode either.<\/p>\n<p> <\/p>\n<p>Now the restriction has been lifted. <code>reindexdb<\/code> can be run with the <code>--jobs<\/code> and <code>--index<\/code> parameters simultaneously, while the indexes belonging to the same table will be reindexed by the same process.<\/p>\n<p> <a name=\"commit_90f51782\"><\/a> <\/p>\n<h2 id=\"psql-new-implementation-of-fetch_counthttpscommitfestpostgresqlorg474233\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4233\/\"><strong>psql: new implementation of FETCH_COUNT<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/90f51782\">90f51782<\/a><\/p>\n<p> <\/p>\n<p>The stock psql variable <code>FETCH_COUNT<\/code> can be used to get a batch of rows from a query at a time, rather than the whole output at once.<\/p>\n<p> <\/p>\n<p>How did it work? Query execution (<code>SELECT<\/code>) began with the cursor declaration and the subsequent series of <code>FETCH FORWARD<\/code> commands with the number of rows specified in <code>FETCH_COUNT<\/code>.<\/p>\n<p> <\/p>\n<p>However, not all queries that return sets of rows support cursors. One example is DML commands with the keyword <code>RETURNING<\/code>, which always returned the full output, regardless of the <code>FETCH_COUNT<\/code> value.<\/p>\n<p> <\/p>\n<p>In the new implementation, <code>FETCH_COUNT<\/code> uses libpq&#8217;s <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/libpq-single-row-mode.html\">single-row mode<\/a> instead of cursors, which works with any type of query.<\/p>\n<p> <a name=\"commit_522ed12f\"><\/a> <\/p>\n<h2 id=\"pg_dump---exclude-extensionhttpscommitfestpostgresqlorg474721\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4721\/\"><strong>pg_dump &#8212;exclude-extension<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/522ed12f\">522ed12f<\/a><\/p>\n<p> <\/p>\n<p>In PostgreSQL 14, <code>pg_dump<\/code> got the <code>--extension<\/code> parameter. It defines which extensions should be included into the archive. PostgreSQL 17 adds the reverse parameter <code>--exclude-extension<\/code>: extensions that do not need to be included.<\/p>\n<p> <a name=\"commit_a45c78e3\"><\/a> <\/p>\n<h2 id=\"backup-and-restore-large-objectshttpscommitfestpostgresqlorg474713\"><a href=\"https:\/\/commitfest.postgresql.org\/47\/4713\/\"><strong>Backup and restore large objects<\/strong><\/a><\/h2>\n<p> <\/p>\n<p>commit: <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a45c78e3\">a45c78e3<\/a>, <a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/959b38d7\">959b38d7<\/a><\/p>\n<p> <\/p>\n<p>Most system catalog objects are designed to define a data schema: tables, views, sequences, data types, functions, etc. The number of these objects in production systems can be quite large, but not unimaginably so.<\/p>\n<p> <\/p>\n<p>The large objects stand out among them. These unusual system catalog objects are, in fact, user data. And there can be a lot of them. The discussion around this patch was initiated by an email about someone unable to use pg_upgrade on a database containing~200 million large objects. The <code>pg_restore<\/code> utility, which restores system catalog objects in a new cluster, failed. Each large object is restored by a separate command, and therefore by a separate transaction. This makes the system rapidly increment transaction counter, which may lead to wraparound.<\/p>\n<p> <\/p>\n<p>Besides, even if <code>pg_dump<\/code>\/<code>pg_restore<\/code> completes without errors, its speed goes down as the number of large objects goes up. <code>pg_dump<\/code> includes every large object in the archive table of contents, bloating the archive and slowing down table of contents access. And <code>pg_restore<\/code> not only blasts through the transaction counter, but also writes each transaction to WAL with subsequent synchronization to disk (<code>fsync<\/code>).<\/p>\n<p> <\/p>\n<p>To optimize the operation of <code>pg_dump<\/code> and <code>pg_restore<\/code>, developers took the following steps. <code>pg_dump<\/code> groups large objects in the archive table of contents in chunks 1000 objects each (the first commit).<\/p>\n<p> <\/p>\n<p><code>pg_restore<\/code> gets a new parameter <code>--transaction-size<\/code> (second commit). As the name implies, it defines the number of SQL commands that are grouped into a single transaction during recovery. This reduces WAL and disk sync overhead, increasing the recovery speed, and also goes easier on the transaction counter. In PostgreSQL 16, by default, each command is executed in a separate transaction, unless the <code>--single-transaction<\/code> flag is set.<\/p>\n<p> <\/p>\n<p>With the new patch, <code>pg_upgrade<\/code> will call <code>pg_restore<\/code> with a hard-coded value of <code>--transaction-size=1000<\/code>.<\/p>\n<p> <\/p>\n<hr\/>\n<p> <\/p>\n<p>That&#8217;s all for the upcoming PostgreSQL 17 release. Hopefully, the release will come out on time, with no unexpected delays.<\/p>\n<p> <\/p>\n<p>Meanwhile, further development is already under way: the first, July CommitFest for PostgreSQL 18 has concluded and the second, September one, has begun. More to research and share.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/846388\/\"> https:\/\/habr.com\/ru\/articles\/846388\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-1\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/webt\/ef\/-6\/4r\/ef-64ruepz4sc0vyhzwym3erxy0.png\" data-src=\"https:\/\/habrastorage.org\/webt\/ef\/-6\/4r\/ef-64ruepz4sc0vyhzwym3erxy0.png\"\/><\/p>\n<p> <\/p>\n<p>Since the PostgreSQL 17 RC1 came out, we are <a href=\"https:\/\/wiki.postgresql.org\/wiki\/PostgreSQL_17_Open_Items#Important_Dateshttps:\/\/wiki.postgresql.org\/wiki\/PostgreSQL_17_Open_Items#Important_Dates\">on a home run<\/a> towards the official PostgreSQL release, scheduled for September 26, 2024.<\/p>\n<p> <\/p>\n<p>Let&#8217;s take a look at the patches that came in during the March CommitFest.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-433487","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/433487","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=433487"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/433487\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=433487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=433487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=433487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}