{"id":498707,"date":"2026-10-15T09:33:40","date_gmt":"2026-10-15T06:33:40","guid":{"rendered":"https:\/\/savepearlharbor.com\/?p=498707"},"modified":"2026-10-15T09:33:40","modified_gmt":"2026-10-15T06:33:40","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=498707","title":{"rendered":"PostgreSQL 19: CommitFest 2026-03 Part 5.2 (DBA1, DBA2)"},"content":{"rendered":"<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<figure class=\"\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/webt\/eb\/b6\/01\/ebb60195ea800a8a4c158b06f15ad1a2.png\" sizes=\"(max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/webt\/eb\/b6\/01\/ebb60195ea800a8a4c158b06f15ad1a2.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/webt\/eb\/b6\/01\/ebb60195ea800a8a4c158b06f15ad1a2.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/figure>\n<p>We\u2019re continuing our review of the March CommitFest for PostgreSQL 19. This second article in the series covers the changes related to our <a href=\"https:\/\/postgrespro.com\/community\/courses\/DBA1\">DBA1<\/a> and <a href=\"https:\/\/postgrespro.com\/community\/courses\/DBA2\">DBA2<\/a> courses.<\/p>\n<p>As a reminder, here\u2019s the plan for our coverage of the final PostgreSQL 19 CommitFest:<\/p>\n<p><a href=\"https:\/\/habr.com\/ru\/companies\/postgrespro\/articles\/1093686\/\">Part 5.1 (QPT)<\/a><\/p>\n<p><em>Part 5.2 (DBA1, DBA2) \u2013 you are here<\/em> \ud83d\ude42<\/p>\n<p>Part 5.3 (DBA3, DBS)<\/p>\n<p>Part 5.4 (SQL, DEV1, DEV2)<\/p>\n<p>For the earlier CommitFests, see: <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5972724\">2025-07<\/a>, <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5972743\">2025-09<\/a>, <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5972812\">2025-11<\/a>, <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5972815\">2026-01<\/a>.<\/p>\n<p>Here\u2019s what\u2019s covered:<\/p>\n<ul>\n<li>\n<p><a href=\"#commit_ac58465e061\">REPACK: rewriting a table to reclaim disk space<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d7965d65fc5\">Autovacuum: table processing order<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_1ff3180ca01\">Autovacuum: parallel index vacuuming<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_e646450e609\">Enhanced transaction ID wraparound warnings<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_5b93a5987bd\">Checkpoint reason in the completion log message<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_a881cc9c7e8\">Less WAL and on-access visibility map updates<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ae58189a4d5\">More code paths use read streams<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_4019f725f5d\">pg_stat_lock: statistics on long lock waits<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_b33f7536128\">pg_upgrade: faster large object transfer, part 2<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_3c5ec35dea2\">oid2name: file paths relative to PGDATA<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_41d69e6dcca\">psql: comments on internal queries<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_38e0190ced7\">log_min_messages per backend type<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_723619eaa3a\">Stats reset time for pg_stat_database_conflicts and pg_statio_all_sequences<\/a><\/p>\n<\/li>\n<\/ul>\n<p><a class=\"anchor\" name=\"commit_ac58465e061\" id=\"commit_ac58465e061\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/5117\/\"><strong>REPACK: rewriting a table to reclaim disk space<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=ac58465e061\"><code>ac58465e061<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=28d534e2ae0\"><code>28d534e2ae0<\/code><\/a><\/p>\n<p>The <code>CLUSTER<\/code> and <code>VACUUM FULL<\/code> commands deal with table bloat. They rewrite a table and its indexes into new files, leaving out dead tuples, and return the freed space to the operating system. The difference is that <code>CLUSTER<\/code> also physically reorders the rows according to the specified index.<\/p>\n<p>The new <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/sql-repack.html\"><code>REPACK<\/code><\/a> command combines the functionality of <code>VACUUM FULL<\/code> and <code>CLUSTER<\/code> under a clearer name:<\/p>\n<pre><code class=\"pgsql\">REPACK bookings; -- replaces VACUUM FULLREPACK bookings USING INDEX bookings_pkey; -- replaces CLUSTER<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper\"><\/span><\/a><\/div><\/pre>\n<p>In this form, <code>REPACK<\/code>, just like <code>CLUSTER<\/code> and <code>VACUUM FULL<\/code>, holds an exclusive lock for the whole operation. On a busy production system, that\u2019s usually unacceptable.<\/p>\n<p>The second commit adds the <code>CONCURRENTLY<\/code> option to <code>REPACK<\/code>, which lets you rebuild a table without blocking the applications that write to it. In this mode, the command creates new files with a copy of the table and its indexes, and uses logical decoding to capture the changes made while it\u2019s running. At the end, it applies those changes and takes a brief exclusive lock to swap the old files for the new ones.<\/p>\n<pre><code class=\"pgsql\">REPACK(concurrently) bookings;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p><code>REPACK(concurrently)<\/code> has some serious limitations. For example, the command holds the database horizon for its entire duration, which prevents [auto]vacuum from removing dead tuples in other tables. It can\u2019t process a partitioned table as a whole, though it does work on individual partitions. The table needs a primary key or a unique index on <code>NOT NULL<\/code> columns to serve as its replica identity. And you can\u2019t repack system catalog tables or TOAST tables.<\/p>\n<p>Some of these limitations may be lifted in future versions. For now, be sure to read up on them in the <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/sql-repack.html\">documentation<\/a> before using it.<\/p>\n<p><a class=\"anchor\" name=\"commit_d7965d65fc5\" id=\"commit_d7965d65fc5\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/6114\/\"><strong>Autovacuum: table processing order<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=d7965d65fc5\"><code>d7965d65fc5<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=87f61f0c828\"><code>87f61f0c828<\/code><\/a><\/p>\n<p>An autovacuum worker has to process every relation in the database that needs vacuuming or analyzing. There are five criteria that put a relation on the list, and they haven\u2019t changed:<\/p>\n<ul>\n<li>\n<p>The age of the oldest transaction ID<\/p>\n<\/li>\n<li>\n<p>The age of the oldest multixact ID<\/p>\n<\/li>\n<li>\n<p>The number of dead tuples<\/p>\n<\/li>\n<li>\n<p>The number of inserted tuples<\/p>\n<\/li>\n<li>\n<p>The number of tuples modified since the last analyze<\/p>\n<\/li>\n<\/ul>\n<p>But in what order should the worker process the relations on this list? Until now, the order was essentially arbitrary. The worker read <code>pg_class<\/code>, evaluated the criteria, and built a list of tables and materialized views to process. Then it read <code>pg_class<\/code> a second time to add the TOAST tables that needed processing. Finally, it processed the relations in the order they had been added to the list.<\/p>\n<p>PostgreSQL 19 introduces a simple <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/routine-vacuuming.html#AUTOVACUUM-PRIORITY\">prioritization system<\/a> for processing tables. For every relation, the autovacuum worker calculates a separate score for each criterion above. The highest of the five becomes the relation\u2019s overall score.<\/p>\n<p>To keep the math simple, let\u2019s disable the parameters that specify a fraction of the table\u2019s rows (<em>autovacuum_*_scale_factor<\/em>). Then only the <em>autovacuum_*_threshold<\/em> parameters will decide whether a relation gets processed. We\u2019ll also lengthen the delay between autovacuum runs so we have time to look at the numbers:<\/p>\n<pre><code class=\"pgsql\">ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0;ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0;ALTER SYSTEM SET autovacuum_vacuum_insert_scale_factor = 0;ALTER SYSTEM SET autovacuum_naptime = '1h';SELECT pg_reload_conf();<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>Now let\u2019s create a table and insert 2000 rows into it.<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE test1 (id int);INSERT INTO test1 SELECT g.x FROM generate_series(1,2000) AS g(x);<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>The new <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/monitoring-stats.html#MONITORING-PG-STAT-AUTOVACUUM-SCORES-VIEW\"><code>pg_stat_autovacuum_scores<\/code><\/a> view shows the overall score and the individual score for each criterion. The last three columns are Boolean flags indicating whether the table needs vacuuming, analyzing, or freezing:<\/p>\n<pre><code class=\"pgsql\">SELECT relname,       round(score) score,       round(xid_score) xid_score,       round(mxid_score) mxid_score,       round(vacuum_score) vacuum_score,       round(vacuum_insert_score) vacuum_insert_score,       round(analyze_score) analyze_score,       do_vacuum,       do_analyze,       for_wraparoundFROM pg_stat_autovacuum_scoresWHERE schemaname = 'public' AND relname ~ 'test'\\gx<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>-[ RECORD 1 ]-------+------relname             | test1score               | 40xid_score           | 0mxid_score          | 0vacuum_score        | 0vacuum_insert_score | 2analyze_score       | 40do_vacuum           | tdo_analyze          | tfor_wraparound      | f<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>The table meets two criteria:<\/p>\n<ul>\n<li>\n<p><code>vacuum_insert_score = 2<\/code> 2000 rows is twice <em>autovacuum_vacuum_insert_threshold<\/em> (1000).<\/p>\n<\/li>\n<li>\n<p><code>analyze_score = 40<\/code> 2000 rows is 40 times <em>autovacuum_analyze_threshold<\/em> (50).<\/p>\n<\/li>\n<\/ul>\n<p>The overall score (the <code>score<\/code> column) is the highest of these, so it\u2019s 40.<\/p>\n<p>In another table, let\u2019s insert 1000 rows and immediately delete half of them, leaving 500 dead tuples:<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE test2 (id int);INSERT INTO test2 SELECT g.x FROM generate_series(1,1000) AS g(x);DELETE FROM test2 WHERE id &gt; 500;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>Let\u2019s compare the vacuum and analyze scores for both tables:<\/p>\n<pre><code class=\"pgsql\">SELECT relname,       round(score) score,       round(vacuum_score) vacuum_score,       round(vacuum_insert_score) vacuum_insert_score,       round(analyze_score) analyze_scoreFROM pg_stat_autovacuum_scoresWHERE schemaname = 'public' AND relname ~ 'test'ORDER BY score DESC;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code> relname | score | vacuum_score | vacuum_insert_score | analyze_score---------+-------+--------------+---------------------+--------------- test1   |    40 |            0 |                   2 |            40 test2   |    30 |           10 |                   1 |            30(2 rows)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>The second table scores 10 for dead tuples and 30 for analyze, so its overall score is lower than the first table\u2019s.<\/p>\n<p>But what if we want tables with lots of dead tuples to take priority? That\u2019s what the new <em>autovacuum_vacuum_score_weight<\/em> parameter is for. It sets a weight for this criterion:<\/p>\n<pre><code class=\"pgsql\">ALTER SYSTEM SET autovacuum_vacuum_score_weight = 10;SELECT pg_reload_conf();<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>Now <code>vacuum_score<\/code> is multiplied by 10, so <code>test2<\/code> gets processed before <code>test1<\/code>:<\/p>\n<pre><code class=\"pgsql\">SELECT relname,       round(score) score,       round(vacuum_score) vacuum_score,       round(vacuum_insert_score) vacuum_insert_score,       round(analyze_score) analyze_scoreFROM pg_stat_autovacuum_scoresWHERE schemaname = 'public' AND relname ~ 'test'ORDER BY score DESC;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code> relname | score | vacuum_score | vacuum_insert_score | analyze_score---------+-------+--------------+---------------------+--------------- test2   |   100 |          100 |                   1 |            30 test1   |    40 |            0 |                   2 |            40(2 rows)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>Each of the five criteria has its own weight, and they all default to 1:<\/p>\n<pre><code class=\"pgsql\">\\dconfig+ autovacuum_*_weight<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>                           List of configuration parameters                Parameter                 | Value | Type | Context | Access privileges------------------------------------------+-------+------+---------+------------------- autovacuum_analyze_score_weight          | 1     | real | sighup  | autovacuum_freeze_score_weight           | 1     | real | sighup  | autovacuum_multixact_freeze_score_weight | 1     | real | sighup  | autovacuum_vacuum_insert_score_weight    | 1     | real | sighup  | autovacuum_vacuum_score_weight           | 10    | real | sighup  |(5 rows)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>To disable the new prioritization system, just set all five parameters to 0.<\/p>\n<p><a class=\"anchor\" name=\"commit_1ff3180ca01\" id=\"commit_1ff3180ca01\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/5755\/\"><strong>Autovacuum: parallel index vacuuming<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=1ff3180ca01\"><code>1ff3180ca01<\/code><\/a><\/p>\n<p>Manual <code>VACUUM(PARALLEL N)<\/code> has been able to process a table\u2019s indexes in parallel since version 13, but autovacuum always used a single process per table, no matter how many indexes the table had. Now autovacuum can launch parallel workers to process the indexes of a single table.<\/p>\n<p>The new <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-vacuum.html#GUC-AUTOVACUUM-MAX-PARALLEL-WORKERS\"><em>autovacuum_max_parallel_workers<\/em><\/a> configuration parameter sets the maximum number of parallel workers per autovacuum worker:<\/p>\n<pre><code class=\"pgsql\">\\dconfig+ autovacuum_max_parallel_workers<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>                        List of configuration parameters            Parameter            | Value |  Type   | Context | Access privileges---------------------------------+-------+---------+---------+------------------- autovacuum_max_parallel_workers | 0     | integer | sighup  |(1 row)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>By default, parallel autovacuum is off, so the behavior matches previous versions.<\/p>\n<p>You can also set the number of index vacuuming workers per table:<\/p>\n<pre><code class=\"pgsql\">ALTER TABLE flights SET (autovacuum_parallel_workers = 2);<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p><a class=\"anchor\" name=\"commit_e646450e609\" id=\"commit_e646450e609\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/6222\/\"><strong>Enhanced transaction ID wraparound warnings<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=e646450e609\"><code>e646450e609<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=48f11bfa06c\"><code>48f11bfa06c<\/code><\/a><\/p>\n<p>The warning about approaching transaction ID wraparound used to kick in 40 million transactions before the limit. At today\u2019s transaction rates, that can be too late, so the warning now appears 100 million transactions ahead. In addition to the number of remaining transactions, it now also reports the percentage of transaction IDs still available:<\/p>\n<pre><code>WARNING:  database \"mydb\" must be vacuumed within 99985967 transactionsDETAIL:  Approximately 4.66% of transaction IDs are available for use.<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>The idea is that a small remaining percentage signals urgency to the DBA more clearly than a transaction count that looks reassuringly large.<\/p>\n<p><a class=\"anchor\" name=\"commit_5b93a5987bd\" id=\"commit_5b93a5987bd\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/6306\/\"><strong>Checkpoint reason in the completion log message<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=5b93a5987bd\"><code>5b93a5987bd<\/code><\/a><\/p>\n<p>Previously, the reason for a checkpoint (time-based, manual, or triggered by WAL growth) was logged only when the checkpoint started, not when it completed. That made it harder to tell why a given checkpoint had run. Now the completion message includes the reason too:<\/p>\n<pre><code class=\"pgsql\">CHECKPOINT;\\! tail -2 logfile<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>2026-09-10 13:30:05.951 MSK [22469] LOG:  checkpoint starting: fast force wait2026-09-10 13:30:05.965 MSK [22469] LOG:  checkpoint complete: fast force wait: wrote 0 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.015 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=2403 kB; lsn=F\/2F962CF0, redo lsn=F\/2F962C90<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p><a class=\"anchor\" name=\"commit_a881cc9c7e8\" id=\"commit_a881cc9c7e8\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/5841\/\"><strong>Less WAL and on-access visibility map updates<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=a881cc9c7e8\"><code>a881cc9c7e8<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=b46e1e54d07\"><code>b46e1e54d07<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=378a216187a\"><code>378a216187a<\/code><\/a><\/p>\n<p>Vacuum no longer writes a separate WAL record noting that a page has been marked all-visible\/all-frozen in the visibility map. Instead, the change is recorded in the WAL record for the heap page itself. This reduces WAL volume for bulk operations on tables.<\/p>\n<p>This also makes it possible to update the visibility map on access, not only during vacuum (and <code>COPY FREEZE<\/code>). What does \u201con access\u201d mean? Whenever a page is accessed, whether by a read or a write, <a href=\"https:\/\/edu.postgrespro.com\/16\/dba2-16-en\/dba2_04_mvcc_hot.html\">page pruning<\/a> may occur. Page pruning removes tuple versions on the page that are no longer needed. In PostgreSQL 19, pruning can also update the visibility map, setting the all-visible bit if only tuples visible to all transactions remain on the page. So ordinary read queries can now set the all-visible bit for a page as they prune it.<\/p>\n<p>The benefit is that the visibility map no longer has to wait for vacuum: regular table operations can update it page by page. And vacuum will have less work to do if some pages are already marked in the map.<\/p>\n<p><a class=\"anchor\" name=\"commit_ae58189a4d5\" id=\"commit_ae58189a4d5\"><\/a><\/p>\n<p><strong>More code paths use read streams<\/strong><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=ae58189a4d5\"><code>ae58189a4d5<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=213f0079b34\"><code>213f0079b34<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=4c910f3bbe9\"><code>4c910f3bbe9<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=d841ca2d149\"><code>d841ca2d149<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=6c228755add\"><code>6c228755add<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=bfa3c4f106b\"><code>bfa3c4f106b<\/code><\/a><\/p>\n<p>The read stream interface, introduced back in version 17, keeps spreading to more subsystems:<\/p>\n<ul>\n<li>\n<p>The <code>pgstattuple<\/code> extension: the <code>pgstattuple_approx<\/code>, <code>pgstatindex<\/code>, and <code>pgstathashindex<\/code> functions<\/p>\n<\/li>\n<li>\n<p><code>bloom<\/code> indexes: vacuum cleanup, bulk delete, and bitmap scans<\/p>\n<\/li>\n<li>\n<p>Vacuum cleanup of GIN indexes<\/p>\n<\/li>\n<li>\n<p>Bulk delete for hash indexes<\/p>\n<\/li>\n<\/ul>\n<p><a class=\"anchor\" name=\"commit_4019f725f5d\" id=\"commit_4019f725f5d\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/5942\/\"><strong>pg_stat_lock: statistics on long lock waits<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=4019f725f5d\"><code>4019f725f5d<\/code><\/a><\/p>\n<p>The <code>pg_locks<\/code> view only shows a snapshot of current locks. Once a lock is released, the only trace of it may be in the server log, and only if the wait lasted longer than <em>deadlock_timeout<\/em> and <em>log_lock_waits<\/em> is enabled (in PostgreSQL 19, it\u2019s <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5972743#commit_2aac62be8\">on by default<\/a>).<\/p>\n<p>The new <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/monitoring-stats.html#MONITORING-PG-STAT-LOCK-VIEW\"><code>pg_stat_lock<\/code><\/a> view accumulates statistics on lock waits that last longer than <em>deadlock_timeout<\/em>, aggregated by lock type.<\/p>\n<p>Here\u2019s an example. First, let\u2019s reset the lock statistics:<\/p>\n<pre><code class=\"pgsql\">SELECT pg_stat_reset_shared('lock');<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>In the first session, we lock a row in the table:<\/p>\n<pre><code class=\"pgsql\">1=# BEGIN;1=*# SELECT * FROM bookings WHERE book_ref = '2EW1SQ' FOR UPDATE;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code> book_ref |           book_date           | total_amount----------+-------------------------------+-------------- 2EW1SQ   | 2025-09-01 03:00:12.557744+03 |      8125.00(1 row)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>In the second session, we try to lock the same row:<\/p>\n<pre><code class=\"pgsql\">2=# SELECT * FROM bookings WHERE book_ref = '2EW1SQ' FOR UPDATE;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>While the command is waiting for the row to be released, we take our time switching back to the first session and committing the transaction:<\/p>\n<pre><code class=\"pgsql\">1=*# COMMIT;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>The second session can now proceed:<\/p>\n<pre><code class=\"pgsql\">2=# SELECT * FROM bookings WHERE book_ref = '2EW1SQ' FOR UPDATE;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code> book_ref |           book_date           | total_amount----------+-------------------------------+-------------- 2EW1SQ   | 2025-09-01 03:00:12.557744+03 |      8125.00(1 row)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>And <code>pg_stat_lock<\/code> now has a record of the lock wait:<\/p>\n<pre><code class=\"pgsql\">SELECT locktype, waits, wait_time, fastpath_exceededFROM pg_stat_lock;<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>     locktype     | waits | wait_time | fastpath_exceeded------------------+-------+-----------+------------------- relation         |     0 |         0 |                 0 extend           |     0 |         0 |                 0 frozenid         |     0 |         0 |                 0 page             |     0 |         0 |                 0 tuple            |     0 |         0 |                 0 transactionid    |     1 | 10060.469 |                 0 virtualxid       |     0 |         0 |                 0 spectoken        |     0 |         0 |                 0 object           |     0 |         0 |                 0 userlock         |     0 |         0 |                 0 advisory         |     0 |         0 |                 0 applytransaction |     0 |         0 |                 0(12 rows)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>Statistics cover all databases in the cluster and are grouped by <code>locktype<\/code>, which has the same values as the <code>locktype<\/code> column in <code>pg_locks<\/code>. The <code>waits<\/code> and <code>wait_time<\/code> (in milliseconds) columns only count long lock waits (longer than <em>deadlock_timeout<\/em>). The <code>fastpath_exceeded<\/code> column shows how many times a lock couldn\u2019t be acquired via the <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/explicit-locking.html#LOCKING-TABLES-FAST-PATH\">fast path<\/a>. If this number is high, consider increasing <em>max_locks_per_transaction<\/em>.<\/p>\n<p><a class=\"anchor\" name=\"commit_b33f7536128\" id=\"commit_b33f7536128\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/6471\/\"><strong>pg_upgrade: faster large object transfer, part 2<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=b33f7536128\"><code>b33f7536128<\/code><\/a><\/p>\n<p>In our review of the first CommitFest, we <a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5972724#commit_161a3e8b6\">described<\/a> how <code>pg_upgrade<\/code> now transfers large object metadata (<code>pg_largeobject_metadata<\/code>) with the faster <code>COPY<\/code> command instead of individual <code>INSERT<\/code> commands. However, this optimization only applied when upgrading from version 12 or later.<\/p>\n<p>Now that restriction is gone: <code>COPY<\/code> is used for <code>pg_largeobject_metadata<\/code> regardless of the old server\u2019s version.<\/p>\n<p><a class=\"anchor\" name=\"commit_3c5ec35dea2\" id=\"commit_3c5ec35dea2\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/6111\/\"><strong>oid2name: file paths relative to PGDATA<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=3c5ec35dea2\"><code>3c5ec35dea2<\/code><\/a><\/p>\n<p>In extended mode, the <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/oid2name.html\"><code>oid2name<\/code><\/a> utility now shows file paths relative to <code>PGDATA<\/code>:<\/p>\n<pre><code class=\"bash\">$ oid2name -d demo -t bookings --extended<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>From database \"demo\":  Filenode  Table Name    Oid    Schema  Tablespace              Path---------------------------------------------------------------------     17258    bookings  17258  bookings  pg_default  base\/16384\/17258<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>You can get the same path with the <code>pg_relation_filepath<\/code> function:<\/p>\n<pre><code class=\"bash\">$ psql -d demo -c \"SELECT pg_relation_filepath('bookings')\"<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code> pg_relation_filepath---------------------- base\/16384\/17258(1 row)<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p><a class=\"anchor\" name=\"commit_41d69e6dcca\" id=\"commit_41d69e6dcca\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/4764\/\"><strong>psql: comments on internal queries<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=41d69e6dcca\"><code>41d69e6dcca<\/code><\/a><\/p>\n<p><code>psql<\/code> runs internal queries against the system catalog to get the information it displays. In the server log or in script output, these queries used to be hard to tell apart from user queries. Now each internal query is wrapped in descriptive comments.<\/p>\n<pre><code class=\"pgsql\">\\set ECHO_HIDDEN on\\sf now<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>\/**** INTERNAL QUERY ****\/\/* Get function's OID *\/SELECT 'now'::pg_catalog.regproc::pg_catalog.oid\/************************\/\/**** INTERNAL QUERY ****\/\/* Get function's definition *\/SELECT pg_catalog.pg_get_functiondef(1299)\/************************\/CREATE OR REPLACE FUNCTION pg_catalog.now() RETURNS timestamp with time zone LANGUAGE internal STABLE PARALLEL SAFE STRICTAS $function$now$function$<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p><a class=\"anchor\" name=\"commit_38e0190ced7\" id=\"commit_38e0190ced7\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/5447\/\"><strong>log_min_messages per backend type<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=38e0190ced7\"><code>38e0190ced7<\/code><\/a><\/p>\n<p>When debugging background processes, it helps to get more detailed logs, for example by changing <em>log_min_messages<\/em> from <code>WARNING<\/code> to <code>DEBUG1<\/code>. But then the server log gets flooded with messages from all processes, especially client backends.<\/p>\n<p>In PostgreSQL 19, <a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES\"><em>log_min_messages<\/em><\/a> lets you set a separate message level for each backend type. Let\u2019s set <code>DEBUG5<\/code> for the checkpointer and keep <code>WARNING<\/code> for everything else:<\/p>\n<pre><code class=\"pgsql\">ALTER SYSTEM SET log_min_messages = 'warning, checkpointer:debug5';SELECT pg_reload_conf();<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p>Now, after a checkpoint, debug messages show up in the server log:<\/p>\n<pre><code class=\"pgsql\">CHECKPOINT;\\! tail logfile | grep DEBUG<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<pre><code>2026-09-10 14:28:56.991 MSK [22469] DEBUG:  checkpointer updated shared memory configuration values2026-09-10 14:29:05.484 MSK [22469] DEBUG:  performing replication slot checkpoint2026-09-10 14:29:05.555 MSK [22469] DEBUG:  snapshot of 0+0 running transaction ids (lsn F\/2F962E08 oldest xid 1383 latest complete 1382 next xid 1383)2026-09-10 14:29:05.561 MSK [22469] DEBUG:  attempting to remove WAL segments older than log file 000000000000000F0000002E2026-09-10 14:29:05.562 MSK [22469] DEBUG:  SlruScanDirectory invoking callback on pg_subtrans\/0000<\/code><div class=\"code-explainer\"><a href=\"https:\/\/sourcecraft.dev\/\" class=\"tm-button code-explainer__link\" style=\"visibility: hidden;\"><span class=\"sc-logo-wrapper icon-only\"><\/span><\/a><\/div><\/pre>\n<p><a class=\"anchor\" name=\"commit_723619eaa3a\" id=\"commit_723619eaa3a\"><\/a><\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/patch\/6577\/\"><strong>Stats reset time for pg_stat_database_conflicts and pg_statio_all_sequences<\/strong><\/a><br \/> commit: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=723619eaa3a\"><code>723619eaa3a<\/code><\/a>, <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=8fe315f18d4\"><code>8fe315f18d4<\/code><\/a><\/p>\n<p>The <code>pg_stat_database_conflicts<\/code> and <code>pg_statio_all_sequences<\/code> views didn\u2019t have a <code>stats_reset<\/code> column showing when the counters were last reset, unlike the other statistics views. Now they do.<\/p>\n<p><a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5972812#commit_a5b543258\">Similar work<\/a> was done earlier in this release cycle for the <code>pg_stat_{all|user}_{tables|indexes}<\/code> and <code>pg_stat_user_functions<\/code> views.<\/p>\n<hr\/>\n<p>That\u2019s all for now. Next time, we\u2019ll look at the changes related to the <a href=\"https:\/\/postgrespro.ru\/education\/courses\/DBA3\">DBA3<\/a> and <a href=\"https:\/\/postgrespro.ru\/education\/courses\/DBS\">DBS<\/a> courses.<\/p>\n<\/div>\n<p>\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\/1094472\/\">https:\/\/habr.com\/ru\/articles\/1094472\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>We\u2019re continuing our review of the March CommitFest for PostgreSQL 19. This second article in the series covers the changes related to our DBA1 and DBA2 courses.As a reminder, here\u2019s the plan for our coverage of the final PostgreSQL 19 CommitFest:Part 5.1 (QPT)Part 5.2 (DBA1, DBA2) \u2013 you are here \ud83d\ude42Part 5.3 (DBA3, DBS)Part 5.4 (SQL, DEV1, DEV2)For the earlier CommitFests, see: 2025-07, 2025-09, 2025-11, 2026-01.Here\u2019s what\u2019s covered:REPACK: rewriting a table to reclaim disk spaceAutovacuum: table processing orderAutovacuum: parallel index vacuumingEnhanced transaction ID wraparound warningsCheckpoint reason in the completion log messageLess WAL and on-access visibility map updatesMore code paths use read streamspg_stat_lock: statistics on long lock waitspg_upgrade: faster large object transfer, part 2oid2name: file paths relative to PGDATApsql: comments on internal querieslog_min_messages per backend typeStats reset time for pg_stat_database_conflicts and pg_statio_all_sequencesREPACK: rewriting a table to reclaim disk space commit: ac58465e061, 28d534e2ae0The CLUSTER and VACUUM FULL commands deal with table bloat. They rewrite a table and its indexes into new files, leaving out dead tuples, and return the freed space to the operating system. The difference is that CLUSTER also physically reorders the rows according to the specified index.The new REPACK command combines the functionality of VACUUM FULL and CLUSTER under a clearer name:REPACK bookings; &#8212; replaces VACUUM FULLREPACK bookings USING INDEX bookings_pkey; &#8212; replaces CLUSTERIn this form, REPACK, just like CLUSTER and VACUUM FULL, holds an exclusive lock for the whole operation. On a busy production system, that\u2019s usually unacceptable.The second commit adds the CONCURRENTLY option to REPACK, which lets you rebuild a table without blocking the applications that write to it. In this mode, the command creates new files with a copy of the table and its indexes, and uses logical decoding to capture the changes made while it\u2019s running. At the end, it applies those changes and takes a brief exclusive lock to swap the old files for the new ones.REPACK(concurrently) bookings;REPACK(concurrently) has some serious limitations. For example, the command holds the database horizon for its entire duration, which prevents [auto]vacuum from removing dead tuples in other tables. It can\u2019t process a partitioned table as a whole, though it does work on individual partitions. The table needs a primary key or a unique index on NOT NULL columns to serve as its replica identity. And you can\u2019t repack system catalog tables or TOAST tables.Some of these limitations may be lifted in future versions. For now, be sure to read up on them in the documentation before using it.Autovacuum: table processing order commit: d7965d65fc5, 87f61f0c828An autovacuum worker has to process every relation in the database that needs vacuuming or analyzing. There are five criteria that put a relation on the list, and they haven\u2019t changed:The age of the oldest transaction IDThe age of the oldest multixact IDThe number of dead tuplesThe number of inserted tuplesThe number of tuples modified since the last analyzeBut in what order should the worker process the relations on this list? Until now, the order was essentially arbitrary. The worker read pg_class, evaluated the criteria, and built a list of tables and materialized views to process. Then it read pg_class a second time to add the TOAST tables that needed processing. Finally, it processed the relations in the order they had been added to the list.PostgreSQL 19 introduces a simple prioritization system for processing tables. For every relation, the autovacuum worker calculates a separate score for each criterion above. The highest of the five becomes the relation\u2019s overall score.To keep the math simple, let\u2019s disable the parameters that specify a fraction of the table\u2019s rows (autovacuum_*_scale_factor). Then only the autovacuum_*_threshold parameters will decide whether a relation gets processed. We\u2019ll also lengthen the delay between autovacuum runs so we have time to look at the numbers:ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0;ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0;ALTER SYSTEM SET autovacuum_vacuum_insert_scale_factor = 0;ALTER SYSTEM SET autovacuum_naptime = &#8216;1h&#8217;;SELECT pg_reload_conf();Now let\u2019s create a table and insert 2000 rows into it.CREATE TABLE test1 (id int);INSERT INTO test1 SELECT g.x FROM generate_series(1,2000) AS g(x);The new pg_stat_autovacuum_scores view shows the overall score and the individual score for each criterion. The last three columns are Boolean flags indicating whether the table needs vacuuming, analyzing, or freezing:SELECT relname,       round(score) score,       round(xid_score) xid_score,       round(mxid_score) mxid_score,       round(vacuum_score) vacuum_score,       round(vacuum_insert_score) vacuum_insert_score,       round(analyze_score) analyze_score,       do_vacuum,       do_analyze,       for_wraparoundFROM pg_stat_autovacuum_scoresWHERE schemaname = &#8216;public&#8217; AND relname ~ &#8216;test&#8217;\\gx-[ RECORD 1 ]&#8212;&#8212;-+&#8212;&#8212;relname             | test1score               | 40xid_score           | 0mxid_score          | 0vacuum_score        | 0vacuum_insert_score | 2analyze_score       | 40do_vacuum           | tdo_analyze          | tfor_wraparound      | fThe table meets two criteria:vacuum_insert_score = 2 2000 rows is twice autovacuum_vacuum_insert_threshold (1000).analyze_score = 40 2000 rows is 40 times autovacuum_analyze_threshold (50).The overall score (the score column) is the highest of these, so it\u2019s 40.In another table, let\u2019s insert 1000 rows and immediately delete half of them, leaving 500 dead tuples:CREATE TABLE test2 (id int);INSERT INTO test2 SELECT g.x FROM generate_series(1,1000) AS g(x);DELETE FROM test2 WHERE id &gt; 500;Let\u2019s compare the vacuum and analyze scores for both tables:SELECT relname,       round(score) score,       round(vacuum_score) vacuum_score,       round(vacuum_insert_score) vacuum_insert_score,       round(analyze_score) analyze_scoreFROM pg_stat_autovacuum_scoresWHERE schemaname = &#8216;public&#8217; AND relname ~ &#8216;test&#8217;ORDER BY score DESC; relname | score | vacuum_score | vacuum_insert_score | analyze_score&#8212;&#8212;&#8212;+&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212; test1   |    40 |            0 |                   2 |            40 test2   |    30 |           10 |                   1 |            30(2 rows)The second table scores 10 for dead tuples and 30 for analyze, so its overall score is lower than the first table\u2019s.But what if we want tables with lots of dead tuples to take priority? That\u2019s what the new autovacuum_vacuum_score_weight parameter is for. It sets a weight for this criterion:ALTER SYSTEM SET autovacuum_vacuum_score_weight = 10;SELECT pg_reload_conf();Now vacuum_score is multiplied by 10, so test2 gets processed before test1:SELECT relname,       round(score) score,       round(vacuum_score) vacuum_score,       round(vacuum_insert_score) vacuum_insert_score,       round(analyze_score) analyze_scoreFROM pg_stat_autovacuum_scoresWHERE schemaname = &#8216;public&#8217; AND relname ~ &#8216;test&#8217;ORDER BY score DESC; relname | score | vacuum_score | vacuum_insert_score | analyze_score&#8212;&#8212;&#8212;+&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212; test2   |   100 |          100 |                   1 |            30 test1   |    40 |            0 |                   2 |            40(2 rows)Each of the five criteria has its own weight, and they all default to 1:\\dconfig+ autovacuum_*_weight                           List of configuration parameters                Parameter                 | Value | Type | Context | Access privileges&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;-+&#8212;&#8212;+&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- autovacuum_analyze_score_weight          | 1     | real | sighup  | autovacuum_freeze_score_weight           | 1     | real | sighup  | autovacuum_multixact_freeze_score_weight | 1     | real | sighup  | autovacuum_vacuum_insert_score_weight    | 1     | real | sighup  | autovacuum_vacuum_score_weight           | 10    | real | sighup  |(5 rows)To disable the new prioritization system, just set all five parameters to 0.Autovacuum: parallel index vacuuming commit: 1ff3180ca01Manual VACUUM(PARALLEL N) has been able to process a table\u2019s indexes in parallel since version 13, but autovacuum always used a single process per table, no matter how many indexes the table had. Now autovacuum can launch parallel workers to process the indexes of a single table.The new autovacuum_max_parallel_workers configuration parameter sets the maximum number of parallel workers per autovacuum worker:\\dconfig+ autovacuum_max_parallel_workers                        List of configuration parameters            Parameter            | Value |  Type   | Context | Access privileges&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;-+&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- autovacuum_max_parallel_workers | 0     | integer | sighup  |(1 row)By default, parallel autovacuum is off, so the behavior matches previous versions.You can also set the number of index vacuuming workers per table:ALTER TABLE flights SET (autovacuum_parallel_workers = 2);Enhanced transaction ID wraparound warnings commit: e646450e609, 48f11bfa06cThe warning about approaching transaction ID wraparound used to kick in 40 million transactions before the limit. At today\u2019s transaction rates, that can be too late, so the warning now appears 100 million transactions ahead. In addition to the number of remaining transactions, it now also reports the percentage of transaction IDs still available:WARNING:  database &#171;mydb&#187; must be vacuumed within 99985967 transactionsDETAIL:  Approximately 4.66% of transaction IDs are available for use.The idea is that a small remaining percentage signals urgency to the DBA more clearly than a transaction count that looks reassuringly large.Checkpoint reason in the completion log message&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-498707","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/498707","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=498707"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/498707\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=498707"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=498707"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=498707"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}