{"id":403047,"date":"2024-06-29T16:52:59","date_gmt":"2024-06-29T16:52:59","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=403047"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=403047","title":{"rendered":"<span>PostgreSQL 16: Part 5 or CommitFest 2023-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-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/ea1\/adf\/77a\/ea1adf77a205f7356dfb4309f9890bd7.png\" width=\"1920\" height=\"1080\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/ea1\/adf\/77a\/ea1adf77a205f7356dfb4309f9890bd7.png\"\/><\/figure>\n<p>The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let\u2019s take a look at some exciting new updates it introduced.<\/p>\n<p>I hope that this review together with the previous articles in the series (<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969676\">2022-07<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969859\">2022-09<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969929\">2022-11<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969981\">2023-01<\/a>) will give you a coherent idea of the new features of PostgreSQL 16.<\/p>\n<p>As usual, the March Commitfest introduces a ton of new changes. I\u2019ve split them into several sections for convenience.<\/p>\n<h4>Monitoring<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_a9c70b46\">pg_stat_io: input\/output statistics<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ae4fdde1\">Counter for new row versions moved to another page when performing an UPDATE<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_f3fa3132\">pg_buffercache: new pg_buffercache_usage_counts function<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_daa8365a\">Normalization of DDL and service commands, continued<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_3c05284d\">EXPLAIN (generic_plan): generic plan of a parameterized query<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9d2d9728\">auto_explain: logging the query ID<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d3d53f95\">PL\/pgSQL: GET DIAGNOSTICS .. PG_ROUTINE_OID<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Client applications<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_b0d8f2d9\">psql: variables SHELL_ERROR and SHELL_EXIT_CODE<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_00beecfe\">psql: \\watch and the number of repetitions<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_3dfae91f\">psql:\\df+ does not show the source code of functions<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_0da243fe\">pg_dump: support for LZ4 and zstd compression methods<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_a563c24c\">pg_dump and partitioned tables<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d07c2948\">pg_verifybackup &#8212;progress<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_7f5b1981\">libpq: balancing connections<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Server administration and maintenance<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_3e51b278\">initdb: setting configuration parameters during cluster initialization<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_7d71d3dd\">Autovacuum: balancing I\/O impact on the fly<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_1cbbee03\">Managing the size of shared memory for vacuum and analyze<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_4211fbd8\">VACUUM for TOAST tables only<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_be504a3e\">The vacuum_defer_cleanup_age parameter has been removed<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_5c1b6628\">pg_walinspect: interpretation of the end_lsn parameter<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9ecb134a\">pg_walinspect: pg_get_wal_fpi_info \u2192 pg_get_wal_block_info<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Localization<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_0d21d4b9\">ICU: UNICODE collation<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ea1db8ae\">ICU: Canonization of locales<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_30a53b79\">ICU: custom rules for customizing the sorting algorithm<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Security<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_3a465cc6\">libpq: new parameter require_auth<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_b5777430\">scram_iterations: iteration counter for password encryption using SCRAM-SHA-256<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>SQL functions and commands<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_7081ac46\">SQL\/JSON standard support<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_b8da37b3\">New functions pg_input_error_info and pg_input_is_valid<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_a290378a\">The Daitch-Mokotoff Soundex<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_888f2ea0\">New functions array_shuffle and array_sample<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_2ddab010\">New aggregate function any_value<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9f8377f7\">COPY: inserting default values<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_75bd846b\">timestamptz: adding and subtracting time intervals<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_483bdb2a\">XML: formatting values<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ce1215d9\">pg_size_bytes: support for &#171;B&#187;<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d5d57414\">New functions: erf, erfc<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Performance<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_11c2d6fd\">Parallel execution of full and right hash joins<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_16dc2703\">Options for the right antijoin<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_31966b15\">Relation extension mechanism rework<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_19d8e230\">Don\u2019t block HOT update by BRIN index<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_983ec230\">postgres_fdw: aborting transactions on remote servers in parallel mode<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_5352ca22\">force_parallel_mode \u2192 debug_parallel_query<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_faeedbce\">Direct I\/O (for developers only)<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Logical replication<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_0fdab27a\">Logical replication from a physical replica<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_89e46da5\">Using non-unique indexes with REPLICA IDENTITY FULL<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ecb69652\">Initial synchronization in binary format<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_c3afe8cf\">Privileges for creating subscriptions and applying changes<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9f2213a7\">Committing changes in parallel mode (for developers only)<\/a><\/p>\n<\/li>\n<\/ul>\n<h3>Monitoring<\/h3>\n<h4>pg_stat_io: input\/output statistics<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a9c70b46\">a9c70b46<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ac8d53da\">ac8d53da<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/8aaa04b3\">8aaa04b3<\/a><\/p>\n<p>The new\u00a0<code>pg_stat_io<\/code>\u00a0view displays input\/output statistics.<\/p>\n<p>To be clear, this is disk input\/output as seen by PostgreSQL. Lower level caching (OS, disk controller) is not taken into account here. In addition, only buffer cache operations are currently monitored. Transferring tables and indexes to another tablespace or WAL operations may be added in the future.<\/p>\n<p>Each row of the view is identified by three columns:<\/p>\n<ul>\n<li>\n<p><code>backend_type<\/code>\u00a0\u2014 process type, same as in\u00a0<code>pg_stat_activity.backend_type<\/code>,<\/p>\n<\/li>\n<li>\n<p><code>object<\/code>\u00a0\u2015 object type, such as relation (permanent tables, indexes, &#8230;) or temp relation,<\/p>\n<\/li>\n<li>\n<p><code>context<\/code>\u00a0\u2015 type of operation performed on the object: normal, vacuum, bulkread, bulkwrite.<\/p>\n<\/li>\n<\/ul>\n<p>The names of the operations are intuitive, and details are listed in the documentation:\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW\">pg_stat_io<\/a>.<\/p>\n<p>Let\u2019s have a look at one of the rows:<\/p>\n<pre><code class=\"pgsql\">SELECT * FROM pg_stat_io WHERE backend_type = 'client backend' AND       object = 'relation' AND       context = 'bulkread' \\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]+------------------------------ backend_type | client backend object       | relation context      | bulkread reads        | 427497 read_time    | 752.489 writes       | 77220 write_time   | 215.93 extends      | extend_time  | op_bytes     | 8192 hits         | 38683 evictions    | 94210 reuses       | 330437 fsyncs       | fsync_time   | stats_reset  | 2023-04-29 09:13:58.798952+03<\/code><\/pre>\n<p>The row shows cumulative statistics for all client backend processes that performed bulk read operations on permanent relations. Such operations include sequential scanning of large tables using a ring buffer.<\/p>\n<p>Displayed statistics include buffer cache hits, evictions, and ring buffer reuses. You can also see that clients had to flush most of the buffer evictions to disk (writes).<\/p>\n<p>The reads, writes and extends (relation file extension) columns show the number of performed operations. To convert to memory units, multiply the values by\u00a0<code>op_bytes<\/code>. Since only buffer cache operations are monitored so far,\u00a0<code>op_bytes<\/code>\u00a0is always equal to the page size (8 kB). To collect execution time statistics (<code>*_time<\/code>\u00a0columns), the\u00a0<em>track_io_timing<\/em>\u00a0parameter must be enabled.<\/p>\n<p>As another example, let\u2019s look at which processes wrote buffers from the cache to disk and compare their impacts:<\/p>\n<pre><code class=\"pgsql\">SELECT backend_type, SUM(writes) blocks,        pg_size_pretty(SUM(writes*op_bytes)) size,        round(SUM(write_time)) \"time, ms\" FROM pg_stat_io WHERE writes > 0 GROUP BY ROLLUP (backend_type) ORDER BY blocks;<\/code><\/pre>\n<pre><code>   backend_type    | blocks |  size   | time, ms -------------------+--------+---------+----------  background writer |  17198 | 134 MB  |      187  checkpointer      |  30436 | 238 MB  |      139  background worker |  76929 | 601 MB  |      213  autovacuum worker |  88870 | 694 MB  |      528  client backend    | 369031 | 2883 MB |     1055                    | 582464 | 4551 MB |     2122 (6 rows)<\/code><\/pre>\n<p>The two main disk writing processes, background writer and checkpointer, wrote the least. This is a clear sign that the system configuration is suboptimal. Perhaps, the buffer cache size should be increased and background writer should be set up more aggressively.<\/p>\n<p>The\u00a0<code>pg_stat_bgwriter<\/code>\u00a0view shows similar data. The\u00a0<code>buffers_clean<\/code>\u00a0and\u00a0<code>buffers_checkpoint<\/code>\u00a0columns display how many buffers the background writer and checkpointer processes have written to disk. However, the\u00a0<code>buffers_backend<\/code>\u00a0column isn\u2019t really representative. It collects data from not only client processes, but also others, including autovacuum. In addition to that, relation file extensions aren\u2019t tracked separately (as in\u00a0<code>pg_stat_io<\/code>), but rather are simply added to\u00a0<code>buffers_backend<\/code>, despite the operation having nothing to do with flushing cache to disk.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.depesz.com\/2023\/02\/27\/waiting-for-postgresql-16-add-pg_stat_io-view-providing-more-detailed-io-statistics\/\">Waiting for PostgreSQL 16 \u2013 Add pg_stat_io view, providing more detailed IO statistics<\/a>\u00a0(Hubert &#8216;depesz&#8217; Lubaczewski)<\/p>\n<p><a href=\"https:\/\/pganalyze.com\/blog\/pg-stat-io\">Waiting for Postgres 16: Cumulative I\/O statistics with pg_stat_io<\/a>\u00a0(Lukas Fittl)<\/p>\n<h4>Counter for new row versions moved to another page when performing an UPDATE<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ae4fdde1\">ae4fdde1<\/a><\/p>\n<p>A new column has appeared in the\u00a0<code>pg_stat_*_tables<\/code>\u00a0family of statistical views:\u00a0<code>n_tup_newpage_upd<\/code>. Together with the existing columns\u00a0<code>n_tup_hot_upd<\/code>\u00a0and\u00a0<code>n_tup_upd<\/code>, it helps evaluate the effectiveness of HOT optimization.<\/p>\n<p>HOT optimization is done when updating non-indexed table columns. A new row version is created in the page, but no new records are created in the table indexes. This is the essence of HOT optimization. But in order for it to work, you need to have enough space in the page to place a new row version there. If the space is insufficient, the row version is created in another page and new entries are also created in all indexes. It is these row changes that are tracked by the new\u00a0<code>n_tup_newpage_upd<\/code>\u00a0counter.<\/p>\n<p>If the counter value is too high, you may want to consider reducing the\u00a0<code>fillfactor<\/code>\u00a0value for the table. By reserving more page space, you increase the chances for HOT optimization to work.<\/p>\n<p>Let\u2019s create a separate database and set up pgbench:<\/p>\n<pre><code class=\"pgsql\">=# CREATE DATABASE test_fillfactor; =# \\c test_fillfactor<\/code><\/pre>\n<pre><code class=\"bash\">$ pgbench -i test_fillfactor<\/code><\/pre>\n<p>Run pgbench for 10 seconds and look at row change statistics for the pgbench_accounts table:<\/p>\n<pre><code class=\"bash\">$ pgbench -T 10 test_fillfactor<\/code><\/pre>\n<pre><code class=\"pgsql\">SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]-----+----------------- relname           | pgbench_accounts n_tup_upd         | 11307 n_tup_hot_upd     | 9644 n_tup_newpage_upd | 1663<\/code><\/pre>\n<p>The sum of\u00a0<code>n_tup_hot_upd<\/code>\u00a0and\u00a0<code>n_tup_newpage_upd<\/code>\u00a0equals\u00a0<code>n_tup_upd<\/code>. This means that during the test, all\u00a0<code>UPDATE<\/code>\u00a0commands did not update indexed columns, and therefore HOT optimization was possible. But in about 15% of cases, HOT optimization did not work because there was not enough space in the page for a new row version.<\/p>\n<p>Let\u2019s reset the statistics, decrease the\u00a0<code>fillfactor<\/code>\u00a0value and try again.<\/p>\n<pre><code class=\"pgsql\">ALTER TABLE pgbench_accounts SET (fillfactor = 80); SELECT pg_stat_reset_single_table_counters('pgbench_accounts'::regclass);<\/code><\/pre>\n<pre><code class=\"bash\">pgbench -T 10 test_fillfactor<\/code><\/pre>\n<pre><code class=\"pgsql\">SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]-----+----------------- relname           | pgbench_accounts n_tup_upd         | 11707 n_tup_hot_upd     | 11704 n_tup_newpage_upd | 3<\/code><\/pre>\n<p>The result is radically different. There were only three instances where there was not enough space in the page.<\/p>\n<p>Curiously, the\u00a0<code>pg_statistic<\/code>\u00a0system table is a candidate for decreasing\u00a0<code>fillfactor<\/code>:<\/p>\n<pre><code class=\"pgsql\">VACUUM ANALYZE; SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd FROM pg_stat_all_tables WHERE relname = 'pg_statistic'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]-----+------------- relname           | pg_statistic n_tup_upd         | 458 n_tup_hot_upd     | 182 n_tup_newpage_upd | 276<\/code><\/pre>\n<h4>pg_buffercache: new pg_buffercache_usage_counts function<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/f3fa3132\">f3fa3132<\/a><\/p>\n<p>A\u00a0<code>pg_buffercache_summary<\/code>\u00a0function was added to the\u00a0<code>pg_buffercache<\/code>\u00a0extension during the\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969929#commit_2589434a\">November Commitfest<\/a>.<\/p>\n<p>Now, another function is added. It shows aggregated buffer cache data subdivided by usage counts.<\/p>\n<pre><code class=\"pgsql\">SELECT * FROM pg_buffercache_usage_counts();<\/code><\/pre>\n<pre><code> usage_count | buffers | dirty | pinned -------------+---------+-------+-------- 0 |   15791 |     0 |      0 1 |     105 |     1 |      0 2 |      89 |     4 |      0 3 |      22 |     1 |      0 4 |      32 |     3 |      0 5 |     345 |    27 |      0 (6 rows)<\/code><\/pre>\n<p>The main advantage of\u00a0<code>pg_buffercache_summary<\/code>\u00a0and\u00a0<code>pg_buffercache_usage_counts<\/code>\u00a0over the\u00a0<code>pg_buffercache<\/code>\u00a0view is speed. The new functions do not require buffer locks, so they perform much faster.<\/p>\n<h4>Normalization of DDL and service commands, continued<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/daa8365a\">daa8365a<\/a><\/p>\n<p>This is a continuation of the\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969981#commit_3db72ebc\">query normalization effort<\/a>\u00a0in PostgreSQL\u00a016.<\/p>\n<p>This time, the affected commands are\u00a0<code>DECLARE<\/code>,\u00a0<code>EXPLAIN<\/code>,\u00a0<code>CREATE MATERIALIZED VIEW<\/code>\u00a0and\u00a0<code>CREATE TABLE AS<\/code>. An especially notable feature is that normalization now takes into account any constants in the query text and substitutes them with parameters.<\/p>\n<p>Let\u2019s create three temporary tables with a constant in the\u00a0<code>AS SELECT<\/code>\u00a0statement:<\/p>\n<pre><code class=\"pgsql\">SELECT format('create temp table tmp on commit drop as select %s', g.i) FROM generate_series(1,3) as g(i)\\gexec<\/code><\/pre>\n<pre><code>SELECT 1 SELECT 1 SELECT 1<\/code><\/pre>\n<p>Now, verify that\u00a0<code>pg_stat_statements<\/code>\u00a0counts the three commands as a single one.<\/p>\n<pre><code class=\"pgsql\">SELECT queryid, query, calls FROM pg_stat_statements WHERE query ILIKE 'create temp table%';<\/code><\/pre>\n<pre><code>       queryid       |                       query                       | calls ---------------------+---------------------------------------------------+------- 2417319575362882285 | create temp table tmp on commit drop as select $1 |     3 (1 row)<\/code><\/pre>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/paquier.xyz\/postgresql-2\/postgres-16-pgstatstatements-norm\/\">Postgres 16 highlight &#8212; Normalization of utilities in pg_stat_statements<\/a>\u00a0(Michael Paquier)<\/p>\n<h4>EXPLAIN (generic_plan): generic plan of a parameterized query<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/3c05284d\">3c05284d<\/a><\/p>\n<p>The idea behind this tool is as follows.<\/p>\n<p>Queries with long execution times are recorded into the server log for analysis.<\/p>\n<pre><code class=\"pgsql\">SHOW log_min_duration_statement;<\/code><\/pre>\n<pre><code> log_min_duration_statement 100ms<\/code><\/pre>\n<p>An application uses the extended query protocol to send queries with parameters to the server. To imitate that, use the new command\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969929#commit_5b66de34\">\\bind<\/a>\u00a0in psql:<\/p>\n<pre><code class=\"pgsql\">\\bind ABCDEF SELECT count(*) FROM tickets WHERE book_ref = $1;<\/code><\/pre>\n<pre><code> count  0  (1 row)<\/code><\/pre>\n<p>We don\u2019t care about the result here, just that it took over 100\u00a0ms to execute and therefore was recorded in the server log.<\/p>\n<pre><code class=\"bash\">$ tail -2 logfile<\/code><\/pre>\n<pre><code>LOG:  duration: 172.195 ms  execute : SELECT count() FROM tickets WHERE book_ref = $1; DETAIL:  parameters: $1 = 'ABCDEF'<\/code><\/pre>\n<p><em>Now, we can start figuring out why the query took so long to execute. To do that, we need the query plan. However, simply adding the\u00a0<\/em><code>EXPLAIN<\/code>\u00a0word to the query text returns an error:<\/p>\n<pre><code class=\"sql\">EXPLAIN SELECT count() FROM tickets WHERE book_ref = $1;<\/code><\/pre>\n<pre><code>ERROR:  there is no parameter $1 LINE 1: EXPLAIN SELECT count() FROM tickets WHERE book_ref = $1;^<\/code><\/pre>\n<p><em>The problem is that\u00a0<\/em><code>EXPLAIN<\/code>\u00a0cannot build plans for queries with parameters, since different parameter values can result in completely different plans.<\/p>\n<p><em>Of course, you can manually insert the parameter value from the log and get a plan, but there could be many such queries in the log and with multiple parameters each. Automating the substitution process can be time-consuming, too.<\/em><\/p>\n<p><em>Here is where the generic plan comes in. A query\u2019s generic plan does not depend on the query\u2019s parameter values. And now you can get one for your query:<\/em><\/p>\n<pre><code class=\"sql\">EXPLAIN (generic_plan) SELECT count() FROM tickets WHERE book_ref = $1;<\/code><\/pre>\n<pre><code>                                   QUERY PLAN Aggregate  (cost=65779.07..65779.08 rows=1 width=8) ->  Gather  (cost=1000.00..65779.07 rows=2 width=0) Workers Planned: 2 ->  Parallel Seq Scan on tickets  (cost=0.00..64778.87 rows=1 width=0) Filter: (book_ref = $1)<\/code><\/pre>\n<p>The planner wants to scan the table sequentially by several processes. This means that the table is large. However, only one record will be selected. Probably, the\u00a0<code>book_ref<\/code>\u00a0column is missing an index that would otherwise expedite the query.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.cybertec-postgresql.com\/en\/explain-generic-plan-postgresql-16\/\">EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16<\/a>\u00a0(Laurenz Albe)<\/p>\n<h4>auto_explain: logging the query ID<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/9d2d9728\">9d2d9728<\/a><\/p>\n<p>According to the documentation, logging queries with the\u00a0<em>auto_explain.log_verbose<\/em>\u00a0parameter enabled is equivalent to\u00a0<code>EXPLAIN<\/code>\u00a0output with the\u00a0<code>VERBOSE<\/code>\u00a0option. However, the query ID was not output, which was fixed:<\/p>\n<pre><code class=\"pgsql\">SET compute_query_id = on; LOAD 'auto_explain'; SET auto_explain.log_min_duration = 0; SET auto_explain.log_verbose = on; SET auto_explain.log_level = 'NOTICE'; SELECT 1;<\/code><\/pre>\n<pre><code>NOTICE:  duration: 0.009 ms  plan: Query Text: SELECT 1; Result  (cost=0.00..0.01 rows=1 width=4) Output: 1 Query Identifier: -7037075969593950510 ?column?     1  (1 row)<\/code><\/pre>\n<p>The feature is not ported into previous PostgreSQL versions, because it might affect server log analysis tools.<\/p>\n<h4>PL\/pgSQL: GET DIAGNOSTICS .. PG_ROUTINE_OID<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d3d53f95\">d3d53f95<\/a><\/p>\n<p>You can now get a function\u2019s ID from its code.<\/p>\n<pre><code class=\"pgsql\">CREATE FUNCTION f (OUT func_oid oid) AS 'BEGIN GET DIAGNOSTICS func_oid = PG_ROUTINE_OID; END;' LANGUAGE plpgsql; SELECT f() oid, f()::regprocedure signature;<\/code><\/pre>\n<pre><code>  oid  | signature -------+----------- 16528 | f()<\/code><\/pre>\n<h3>Client applications<\/h3>\n<h4>psql: variables SHELL_ERROR and SHELL_EXIT_CODE<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/b0d8f2d9\">b0d8f2d9<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/31ae2aa9\">31ae2aa9<\/a><\/p>\n<p>Two new variables show how the last OS command has completed:<\/p>\n<pre><code class=\"pgsql\">16=# ! pwd<\/code><\/pre>\n<pre><code>\/home\/pal\/pg16<\/code><\/pre>\n<pre><code class=\"pgsql\">16=# \\echo SHELL_ERROR: :SHELL_ERROR, SHELL_EXIT_CODE: :SHELL_EXIT_CODE<\/code><\/pre>\n<pre><code>SHELL_ERROR: false, SHELL_EXIT_CODE: 0<\/code><\/pre>\n<p>The first commit adds these variables for the\u00a0<code>!<\/code>\u00a0command and commands in back quotes (<code>``<\/code>). The second one adds them to\u00a0<code>\\g<\/code>,\u00a0<code>\\o<\/code>,\u00a0<code>\\w<\/code>\u00a0and\u00a0<code>\\copy<\/code>.<\/p>\n<p>In some cases, the variables are intentionally left unset. These include: calling the pager program, calling the editor (<code>\\e<\/code>) and setting the prompt with a command in back quotes (<code>\\set PROMPT1%command<\/code>).<\/p>\n<h4>psql: \\watch and the number of repetitions<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/00beecfe\">00beecfe<\/a><\/p>\n<p>The\u00a0<code>\\watch<\/code>\u00a0command repeats the query until terminated. The new patch allows you to specify the number of repetitions.<\/p>\n<pre><code class=\"pgsql\">16=# SELECT 1 \\watch interval=1 count=2<\/code><\/pre>\n<pre><code>Fri 14 Apr 2023 02:47:28 PM MSK (every 1s) ?column?     1  (1 row) Fri 14 Apr 2023 02:47:29 PM MSK (every 1s) ?column?     1  (1 row) 16=#<\/code><\/pre>\n<h4>psql:\\df+ does not show the source code of functions<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/3dfae91f\">3dfae91f<\/a><\/p>\n<p>The\u00a0<code>\\df+<\/code>\u00a0command, among other things, outputs the source code of functions, which can take up a lot of space. This makes it difficult to view the list of functions.<\/p>\n<p>In PostgreSQL 16,\u00a0<code>\\df+<\/code>\u00a0will no longer show the source code of all functions except internal ones. Internal function code is a C language function name and always fits in one line.<\/p>\n<p>You can still view function source codes with other commands:\u00a0<code>\\sf<\/code>\u00a0and\u00a0<code>\\ef<\/code>.<\/p>\n<h4>pg_dump: support for\u00a0LZ4\u00a0and\u00a0zstd\u00a0compression methods<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/0da243fe\">0da243fe<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/84adc8e2\">84adc8e2<\/a><\/p>\n<p>Before PostgreSQL 16, the only compression method supported by pg_dump was gzip. The two commits add the support for LZ4 and zstd, respectively.<\/p>\n<p>Backup compression is available for all formats except tar.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.cybertec-postgresql.com\/en\/lz4-zstd-pg_dump-compression-postgresql-16\/\">LZ4 and ZSTD pg_dump compression in PostgreSQL 16<\/a>\u00a0(Pavlo Golub)<\/p>\n<h4>pg_dump and partitioned tables<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a563c24c\">a563c24c<\/a><\/p>\n<p>Dumping individual tables is done with the\u00a0<code>--table<\/code>\u00a0parameter. But if you want to dump a partitioned table with all its partitions, then you either need to explicitly list all the partitions, or use the\u00a0<code>%<\/code>\u00a0sign to specify a table name template that all partitions will fit. Both options are imperfect. The number of partitions may change, and partitions themselves may fit into a template poorly or the template might accidentally include other tables.<\/p>\n<p>In PostgreSQL 16, the new pg_dump parameter\u00a0<code>--table-and-children<\/code>\u00a0allows you to leave the old approach behind. The parameter works not only for partitioned tables, but also for table inheritance.<\/p>\n<p>For table exclusion, similar parameters are added:\u00a0<code>--exclude-table-and-children<\/code>\u00a0and\u00a0<code>--exclude-table-data-and-children<\/code>.<\/p>\n<h4>pg_verifybackup &#8212;progress<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d07c2948\">d07c2948<\/a><\/p>\n<p>The pg_basebackup utility with the\u00a0<code>--progress<\/code>\u00a0parameter shows the backup creation progress.<\/p>\n<p>The same parameter has been added to the backup verification utility pg_verifybackup. During verification, the status is updated every second, and at completion the output looks like this:<\/p>\n<pre><code class=\"bash\">$ pg_verifybackup --progress \/home\/pal\/pg16\/backup\/<\/code><\/pre>\n<pre><code>2723991\/2723991 kB (100%) verified backup successfully verified<\/code><\/pre>\n<h4>libpq: balancing connections<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/7f5b1981\">7f5b1981<\/a><\/p>\n<p>The ability to specify multiple PostgreSQL instances during connection is not new. You can list multiple replicas under the\u00a0<code>host<\/code>,\u00a0<code>hostaddr<\/code>\u00a0and\u00a0<code>port<\/code>\u00a0parameters in the connection string. Clients will try to connect to the replicas in the specified order.<\/p>\n<pre><code class=\"bash\">psql \"host=replica1,replica2,replica3\"<\/code><\/pre>\n<p>However, with a large number of connections, the first replica in the list will experience more load than the others, and the last one may be idle at all.<\/p>\n<p>To distribute connections between replicas evenly, you can shuffle the list of replicas on the application side when forming the connection string. Or you can use the new connection parameter\u00a0<code>load_balance_hosts<\/code>:<\/p>\n<pre><code class=\"bash\">psql \"host=replica1,replica2,replica3 load_balance_hosts=random\"<\/code><\/pre>\n<p><code>load_balance_hosts=random<\/code>\u00a0indicates that the list of nodes will be shuffled before attempting to connect.<\/p>\n<h3>Server administration and maintenance<\/h3>\n<h4>initdb: setting configuration parameters during cluster initialization<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/3e51b278\">3e51b278<\/a><\/p>\n<p>The new initdb key\u00a0<code>-c<\/code>\u00a0(or\u00a0<code>--set<\/code>) will override the value of any configuration parameter.<\/p>\n<p>For the purpose of the experiment, let\u2019s create a second instance of the server on a free port and with the\u00a0<em>wal_level<\/em>\u00a0parameter set to logical:<\/p>\n<pre><code class=\"bash\">$ initdb -k -U postgres \"-c wal_level=logical\" \"-c port=5402\" -D ~\/pg16\/data2 $ pg_ctl start -D ~\/pg16\/data2 -l logfile2 $ psql -p 5402 -c \"SHOW wal_level\"<\/code><\/pre>\n<pre><code>wal_level logical<\/code><\/pre>\n<p>The parameters specified during initialization are stored at the end of the postgresql.conf file:<\/p>\n<pre><code class=\"bash\">$ tail -2 ~\/pg16\/data2\/postgresql.conf<\/code><\/pre>\n<pre><code>wal_level = logical port = 5402<\/code><\/pre>\n<h4>Autovacuum: balancing I\/O impact on the fly<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/7d71d3dd\">7d71d3dd<\/a><\/p>\n<p>When the autovacuum takes too long to vacuum a particularly large table, the process can be accelerated on the fly. This requires modifying the parameters\u00a0<em>autovacuum_vacuum_cost_limit<\/em>\u00a0and\/or\u00a0<em>autovacuum_vacuum_cost_delay<\/em>\u00a0and updating the configuration.<\/p>\n<p>In previous PostgreSQL versions, autovacuum processes could read configuration files only after they finish processing the current table. With the new patch, this is done before every pause check (<em>autovacuum_vacuum_cost_delay<\/em>).<\/p>\n<p>See also:<\/p>\n<p><a href=\"http:\/\/rhaas.blogspot.com\/2023\/04\/updating-cost-limit-on-fly.html\">Updating the Cost Limit On The Fly<\/a>\u00a0(Robert Haas)<\/p>\n<h4>Managing the size of shared memory for vacuuming and analysis<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1cbbee03\">1cbbee03<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/4830f102\">4830f102<\/a><\/p>\n<p>In order to protect the entire shared memory pool from bulk eviction, table vacuuming uses only a fraction of it to form a ring buffer. The size of the ring buffer is relatively tiny at only 256\u00a0kB. However, when a large table needs vacuuming, performance of the whole server may suffer. For the pages in the small ring buffer to be reused, dirty pages and WAL records have to be constantly flushed to disk. Frequently synchronizing WAL to disk may become a bottleneck for the I\/O system.<\/p>\n<p>The first commit introduces a new parameter\u00a0<em>vacuum_buffer_usage_limit<\/em>, which can be used to control the size of the ring buffer. The default value remains the same at 256\u00a0kB, but the parameter can accept values from 12\u00a0kB to 16\u00a0GB (but no more than \u215b of the buffer cache can be used).<\/p>\n<p>When running\u00a0<code>VACUUM<\/code>\u00a0and\u00a0<code>ANALYZE<\/code>\u00a0manually, you can specify the size with the\u00a0<code>BUFFER_USAGE_LIMIT<\/code>\u00a0option. If you set it to 0, vacuuming will be allowed to use the whole buffer cache:<\/p>\n<pre><code class=\"pgsql\">VACUUM(ANALYZE, BUFFER_USAGE_LIMIT 0); SELECT buffers_used, buffers_unused FROM pg_buffercache_summary();<\/code><\/pre>\n<pre><code> buffers_used | buffers_unused --------------+---------------- 16384 |              0<\/code><\/pre>\n<p>This may be useful after uploading a large amount of new data or after bulk changes to existing tables.<\/p>\n<p>When vacuuming aggressively to avoid transaction counter wraparound (the second commit), the process must execute as quickly as possible, so the shared memory limitation is disabled by default.<\/p>\n<p>The ring buffer operation is now explained in the glossary under the term &#171;<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY\">Buffer Access Strategy<\/a>&#171;.<\/p>\n<h4>VACUUM for TOAST tables only<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/4211fbd8\">4211fbd8<\/a><\/p>\n<p>Vacuuming just a TOAST table requires the\u00a0<code>USAGE<\/code>\u00a0privilege for the\u00a0<code>pg_toast<\/code>\u00a0schema. You also have to know the name of the relevant TOAST table.<\/p>\n<pre><code class=\"pgsql\">SELECT reltoastrelid::regclass FROM pg_class WHERE oid = 'tickets'::regclass;<\/code><\/pre>\n<pre><code>      reltoastrelid pg_toast.pg_toast_16443<\/code><\/pre>\n<pre><code class=\"pgsql\">VACUUM pg_toast.pg_toast_16443;<\/code><\/pre>\n<p>The new option\u00a0<code>PROCESS_MAIN<\/code>\u00a0lets you do all that without looking up the table name.<\/p>\n<pre><code class=\"pgsql\">VACUUM (PROCESS_MAIN false) tickets;<\/code><\/pre>\n<p>In the same vein, the vacuumdb tool has received a new parameter\u00a0<code>--no-process-main<\/code>.<\/p>\n<h4>The vacuum_defer_cleanup_age parameter has been removed<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/be504a3e\">be504a3e<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1118cd37\">1118cd37<\/a><\/p>\n<p>Setting the\u00a0<em>vacuum_defer_cleanup_age<\/em>\u00a0value higher than the current transaction ID may result in corruption of data. See the first commit for details.<\/p>\n<p>Developing comprehensive tests and porting them to previous releases is a difficult task that may just be unnecessary. After all, the\u00a0<em>vacuum_defer_cleanup_age<\/em>\u00a0parameter is probably rarely used.\u00a0<em>hot_standby_feedback<\/em>\u00a0and replication slots that appeared later are much more convenient alternatives.<\/p>\n<p>Therefore, the second commit removes the\u00a0<em>vacuum_defer_cleanup_age<\/em>\u00a0parameter altogether. It wasn\u2019t a very convenient tool in the first place: too high values would result in bloating, and too low values wouldn\u2019t do much to avoid conflicts while applying WAL records on replicas.<\/p>\n<p>Even if you don\u2019t plan on upgrading to PostgreSQL\u00a016 any time soon, it would still be wise to take stock of all your\u00a0<em>vacuum_defer_cleanup_age<\/em>\u00a0in the configuration files and prepare to get rid of them.<\/p>\n<h4>pg_walinspect: interpretation of the end_lsn parameter<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/5c1b6628\">5c1b6628<\/a><\/p>\n<p>The interpretation of the\u00a0<code>end_lsn<\/code>\u00a0parameter has been changed for\u00a0<code>pg_get_wal_records_info<\/code>,\u00a0<code>pg_get_wal_stats<\/code>\u00a0and\u00a0<code>pg_get_wal_block_info<\/code>. If no value is specified, the functions return data from\u00a0<code>start_lsn<\/code>\u00a0till the end of WAL.<\/p>\n<p>This made it possible to remove the duplicate functions\u00a0<code>pg_get_wal_records_info_till_end_of_wal<\/code>\u00a0and\u00a0<code>pg_get_wal_stats_till_end_of_wal<\/code>.<\/p>\n<h4>pg_walinspect: pg_get_wal_fpi_info \u2192 pg_get_wal_block_info<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/9ecb134a\">9ecb134a<\/a><\/p>\n<p>The\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969981#commit_c31cf1c0\">previous article<\/a>\u00a0mentioned the new\u00a0<code>pg_get_wal_fpi_info<\/code>\u00a0function of the\u00a0<code>pg_walinspect<\/code>\u00a0extension. The function has been significantly redesigned and renamed to\u00a0<code>pg_get_wal_block_info<\/code>.<\/p>\n<h3>Localization<\/h3>\n<h4>ICU: UNICODE collation<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/0d21d4b9\">0d21d4b9<\/a><\/p>\n<p>In accordance with the SQL standard, the UNICODE collation for the ICU provider has been implemented in PostgreSQL\u00a016.<\/p>\n<pre><code class=\"pgsql\">\\dO unicode<\/code><\/pre>\n<pre><code>                                     List of collations Schema   |  Name   | Provider | Collate | Ctype | ICU Locale | ICU Rules | Deterministic? ------------+---------+----------+---------+-------+------------+-----------+---------------- pg_catalog | unicode | icu      |         |       | und        |           | yes <\/code><\/pre>\n<p>The collation uses the default sorting algorithm as described in the\u00a0<a href=\"https:\/\/unicode.org\/reports\/tr10\/\">UNICODE technical standard<\/a>.<\/p>\n<h4>ICU: Canonization of locales<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ea1db8ae\">ea1db8ae<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1671f990\">1671f990<\/a><\/p>\n<p>Locale names for the ICU provider will be converted to the canonical form in accordance with BCP\u00a047 rules. Coincidentally, this will prevent creation of invalid collations.<\/p>\n<pre><code class=\"pgsql\">CREATE COLLATION test_icu(provider=icu, locale='invalid_locale');<\/code><\/pre>\n<pre><code>NOTICE:  using standard form \"invalid-locale\" for locale \"invalid_locale\" ERROR:  ICU locale \"invalid-locale\" has unknown language \"invalid\" HINT:  To disable ICU locale validation, set parameter icu_validation_level to DISABLED.<\/code><\/pre>\n<p>To disable locale validation, you can use the new\u00a0<em>icu_validation_level<\/em>\u00a0parameter (second commit).<\/p>\n<h4>ICU: custom rules for customizing the sorting algorithm<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/30a53b79\">30a53b79<\/a><\/p>\n<p>The new rules option of the\u00a0<code>CREATE COLLATION<\/code>\u00a0command allows you to customize the sorting algorithm for specific needs.<\/p>\n<p>Let\u2019s create a collation where the seasons of the year are sorted starting from winter:<\/p>\n<pre><code class=\"pgsql\">CREATE COLLATION seasons ( provider = icu, locale = 'en', rules = '&amp; W &lt; Sp &lt; Su &lt; A' ); WITH seasons(name) AS ( VALUES ('Summer'), ('Winter'), ('Spring'), ('Autumn') ) SELECT * FROM seasons ORDER BY name COLLATE seasons;<\/code><\/pre>\n<pre><code> name Winter Spring Summer Autumn (4 rows)<\/code><\/pre>\n<p>Custom rules can also be specified in the\u00a0<code>CREATE DATABASE<\/code>\u00a0command and when running createdb and initdb.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/unicode-org.github.io\/icu\/userguide\/collation\/customization\/\">ICU Documentation: Collation Customization<\/a><\/p>\n<p><a href=\"http:\/\/peter.eisentraut.org\/blog\/2023\/03\/14\/how-collation-works\">How collation works<\/a>\u00a0(Peter Eisentraut)<\/p>\n<p><a href=\"http:\/\/peter.eisentraut.org\/blog\/2023\/04\/12\/how-collation-of-punctuation-and-whitespace-works\">How collation of punctuation and whitespace works<\/a>\u00a0(Peter Eisentraut)<\/p>\n<h3>Security<\/h3>\n<h4>libpq: new parameter require_auth<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/3a465cc6\">3a465cc6<\/a><\/p>\n<p>The pg_hba.conf file is configured as follows:<\/p>\n<pre><code class=\"pgsql\">SELECT rule_number rule, type, database, user_name, auth_method FROM pg_hba_file_rules WHERE database != '{replication}';<\/code><\/pre>\n<pre><code> rule | type  | database | user_name |  auth_method ------+-------+----------+-----------+--------------- 1 | local | {all}    | {all}     | trust 2 | host  | {all}    | {all}     | scram-sha-256<\/code><\/pre>\n<p>The new libpq parameter\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/libpq-connect.html#LIBPQ-CONNECT-REQUIRE-AUTH\">require_auth<\/a>\u00a0allows you to specify the authentication method that the server should use. You can list several methods to allow the server to use any of them:<\/p>\n<pre><code class=\"bash\">$ psql 'host=localhost require_auth=md5,scram-sha-256' -c 'SELECT system_user' Password for user postgres:<\/code><\/pre>\n<pre><code>      system_user scram-sha-256:postgres (1 row)<\/code><\/pre>\n<p>In this example, the second rule with the scram-sha-256 method should be used for TCP\/IP connection. Since this method, along with md5, is listed in the client\u2019s\u00a0<code>require_auth<\/code>\u00a0parameter, it is applied. (You can read more about the new\u00a0<code>system_user<\/code>\u00a0function in PostgreSQL\u00a016 in the\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969859#commit_0823d061\">September Commitfest article<\/a>.)<\/p>\n<p>If the authentication method in\u00a0<code>require_auth<\/code>\u00a0does not match the method from pg_hba.conf, then you will not be able to connect:<\/p>\n<pre><code class=\"bash\">$ psql 'host=localhost require_auth=md5' -c 'SELECT system_user'<\/code><\/pre>\n<pre><code>psql: error: connection to server at \"localhost\" (127.0.0.1), port 5416 failed: auth method \"md5\" requirement failed: server requested SASL authentication<\/code><\/pre>\n<p>You can forbid specific methods from being used by listing them with a preceding exclamation mark.<\/p>\n<pre><code class=\"bash\">$ psql 'host=localhost require_auth=!md5,!password' -c 'SELECT system_user' Password for user postgres:<\/code><\/pre>\n<pre><code>      system_user scram-sha-256:postgres (1 row)<\/code><\/pre>\n<p>The special value\u00a0<code>none<\/code>\u00a0is used when a connection without authentication is required. In our case, this will work for the local connection under the first rule in pg_hba.conf:<\/p>\n<pre><code class=\"bash\">$ psql 'require_auth=none' -c 'SELECT system_user'<\/code><\/pre>\n<pre><code> system_user (1 row)<\/code><\/pre>\n<p>Instead of the\u00a0<code>require_auth<\/code>\u00a0parameter, you can use the\u00a0<code>PGREQUIREAUTH<\/code>\u00a0environment variable.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/paquier.xyz\/postgresql-2\/2023-04-13-postgres-16-require-auth\/\">Postgres 16 highlight &#8212; require_auth for libpq<\/a>\u00a0(Michael Paquier)<\/p>\n<h4>scram_iterations: iteration counter for password encryption using SCRAM-SHA-256<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/b5777430\">b5777430<\/a><\/p>\n<p>In previous versions, the counter value was hard-coded as 4096.<\/p>\n<p>Now, more resilient passwords can be generated by specifying a higher counter value under the new parameter\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-connection.html#GUC-SCRAM-ITERATIONS\"><em>scram_iterations<\/em><\/a>.\u00a0<a href=\"https:\/\/www.rfc-editor.org\/rfc\/rfc7677\">RFC\u00a07677<\/a>\u00a0recommends to set it to 15,000 or more.<\/p>\n<p>Keep in mind that higher counter values increase security, but also the time it takes to authenticate the user.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/paquier.xyz\/postgresql-2\/postgres-16-scram-iterations\/\">Postgres 16 highlight &#8212; Control of SCRAM iterations<\/a>\u00a0(Michael Paquier)<\/p>\n<h3>SQL functions and commands<\/h3>\n<h4>SQL\/JSON standard support<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/7081ac46\">7081ac46<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6ee30209\">6ee30209<\/a><\/p>\n<p>This big update was accepted a year ago, but was rolled back shortly before the PostgreSQL\u00a015 release.<\/p>\n<p>The patch for PostgreSQL 16 introduces support for constructor functions (<code>JSON_ARRAY<\/code>,\u00a0<code>JSON_ARRAYAGG<\/code>,\u00a0<code>JSON_OBJECT<\/code>,\u00a0<code>JSON_OBJECTAGG<\/code>) and predicates (<code>IS JSON [VALUE]<\/code>,\u00a0<code>IS JSON ARRAY<\/code>,\u00a0<code>IS JSON OBJECT<\/code>,\u00a0<code>IS JSON SCALAR<\/code>).<\/p>\n<p>The work will continue with the next major release.<\/p>\n<h4>New functions pg_input_error_info and pg_input_is_valid<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/b8da37b3\">b8da37b3<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1939d262\">1939d262<\/a><\/p>\n<p>The\u00a0<code>pg_input_is_valid<\/code>\u00a0function checks whether the first argument is a valid input value for the data type from the second argument:<\/p>\n<pre><code class=\"pgsql\">SELECT pg_input_is_valid('value', 'jsonb');<\/code><\/pre>\n<pre><code> pg_input_is_valid f<\/code><\/pre>\n<p>The\u00a0<code>pg_input_error_info<\/code>\u00a0function returns detailed information about the error:<\/p>\n<pre><code class=\"pgsql\">SELECT * FROM pg_input_error_info('value', 'jsonb')\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]--+----------------------------------- message        | invalid input syntax for type json detail         | Token \"value\" is invalid. hint           | sql_error_code | 22P02<\/code><\/pre>\n<p>The functions do a \u201csoft\u201d check of input values. The lack of such a function in the last release cycle is what prevented the SQL\/JSON patch from releasing with PostgreSQL\u00a015.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.depesz.com\/2022\/12\/12\/waiting-for-postgresql-16-add-test-scaffolding-for-soft-error-reporting-from-input-functions\/\">Waiting for PostgreSQL 16 \u2013 Add test scaffolding for soft error reporting from input functions<\/a>\u00a0(Hubert &#8216;depesz&#8217; Lubaczewski)<\/p>\n<h4>The Daitch-Mokotoff Soundex<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a290378a\">a290378a<\/a><\/p>\n<p>Daitch-Mokotoff Soundex support is\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/fuzzystrmatch.html#FUZZYSTRMATCH-DAITCH-MOKOTOFF\">added<\/a>\u00a0to the fuzzystrmatch extension.<\/p>\n<pre><code class=\"pgsql\">CREATE EXTENSION fuzzystrmatch; SELECT daitch_mokotoff('Holubica'), daitch_mokotoff('Golubitsa'), daitch_mokotoff('Holubica') &amp;&amp; daitch_mokotoff('Golubitsa');<\/code><\/pre>\n<pre><code> daitch_mokotoff | daitch_mokotoff | ?column? -----------------+-----------------+---------- {587500,587400} | {587400}        | t<\/code><\/pre>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.avotaynu.com\/soundex.htm\">Soundexing and Genealogy<\/a>\u00a0by Gary Mokotoff<\/p>\n<h4>New functions array_shuffle and array_sample<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/888f2ea0\">888f2ea0<\/a><\/p>\n<p>The\u00a0<code>array_shuffle<\/code>\u00a0function shuffles the elements of the input array, and\u00a0<code>array_sample<\/code>\u00a0returns an array with the specified number of randomly selected elements of the input array:<\/p>\n<pre><code class=\"pgsql\">WITH a(digits) AS ( SELECT '{1,2,3,4,5,6,7,8,9,0}'::int[] ) SELECT array_shuffle(a.digits), array_sample(a.digits, 3) FROM a;<\/code><\/pre>\n<pre><code>     array_shuffle     | array_sample -----------------------+-------------- {1,0,9,8,2,3,5,7,4,6} | {9,1,4}<\/code><\/pre>\n<p>The functions can be helpful when using the\u00a0<a href=\"https:\/\/en.wikipedia.org\/wiki\/Monte_Carlo_method\">Monte Carlo method<\/a>.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.depesz.com\/2023\/04\/18\/waiting-for-postgresql-16-add-array_sample-and-array_shuffle-functions\/\">Waiting for PostgreSQL 16 \u2013 Add array_sample() and array_shuffle() functions<\/a>\u00a0(Hubert &#8216;depesz&#8217; Lubaczewski)<\/p>\n<h4>New aggregate function any_value<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/2ddab010\">2ddab010<\/a><\/p>\n<p>An SQL standard aggregate function\u00a0<code>any_value<\/code>\u00a0returns an arbitrary non-empty value for a group of rows.<\/p>\n<p>Consider a table without a unique key in which some rows are duplicated.<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE t (id int); INSERT INTO t VALUES(1),(1),(2),(3),(3) RETURNING *;<\/code><\/pre>\n<pre><code> id 1 1 2 3 3 (5 rows)<\/code><\/pre>\n<p>You need to delete all duplicates in one query. (Similar tasks are common in job interviews.)<\/p>\n<p>To select one of two duplicates, the functions min and max are commonly applied to the system column ctid. This works, but is a bit misleading, because we don\u2019t really need a minimum or a maximum value. Any of the two will do, and the new function\u00a0<code>any_value<\/code>\u00a0does just that:<\/p>\n<pre><code class=\"pgsql\">DELETE FROM t WHERE ctid IN (SELECT any_value(ctid) FROM t GROUP BY id HAVING count(*) = 2 );<\/code><\/pre>\n<pre><code class=\"pgsql\">DELETE 2<\/code><\/pre>\n<pre><code class=\"pgsql\">SELECT * FROM t;<\/code><\/pre>\n<pre><code> id 1 2 3 (3 rows)<\/code><\/pre>\n<h4>COPY: inserting default values<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/9f8377f7\">9f8377f7<\/a><\/p>\n<p>Consider a table with two mandatory date columns. If a value is missing, a date from the distant past or future is used as a placeholder.<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE periods ( id int PRIMARY KEY, date_from date NOT NULL DEFAULT '0001-01-01', date_to   date NOT NULL DEFAULT '3999-12-31' );<\/code><\/pre>\n<p>The table is populated from external files by means of the\u00a0<code>COPY<\/code>\u00a0command. The providers of the external files use\u00a0<code>'N\/A'<\/code>\u00a0for missing date values. You cannot affect how the files are formed.<\/p>\n<pre><code class=\"pgsql\">! cat data.txt<\/code><\/pre>\n<pre><code>1    2023-04-01    2023-05-01 2    2023-04-08    N\/A 3    N\/A    2023-04-22<\/code><\/pre>\n<p>Importing such a file into the table is now made easier by the\u00a0<code>DEFAULT<\/code>\u00a0option of the\u00a0<code>COPY<\/code>\u00a0command. When a value specified under\u00a0<code>DEFAULT<\/code>\u00a0is matched in the data, the default value for the column is used instead:<\/p>\n<pre><code class=\"pgsql\">\\COPY periods FROM 'data.txt' (DEFAULT 'N\/A'); SELECT * FROM periods;<\/code><\/pre>\n<pre><code> id | date_from  |  date_to ----+------------+------------ 1 | 2023-04-01 | 2023-05-01 2 | 2023-04-08 | 3999-12-31 3 | 0001-01-01 | 2023-04-22 (3 rows)<\/code><\/pre>\n<h4>timestamptz: adding and subtracting time intervals<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/75bd846b\">75bd846b<\/a><\/p>\n<p>The\u00a0<code>+<\/code>\u00a0and\u00a0<code>-<\/code>\u00a0operators can be used to add and subtract time intervals from values of the timestamp with time zone type (<code>timestamptz<\/code>). The result of such operations depends on the time zone set in the\u00a0<em>timezone<\/em>\u00a0parameter.<\/p>\n<p>To make the result independent of the current time zone, you can use the new functions\u00a0<code>date_add<\/code>\u00a0and\u00a0<code>date_subtract<\/code>. The last parameter of these functions allows you to explicitly specify the time zone for calculations:<\/p>\n<pre><code class=\"pgsql\">SET timezone = 'UTC'; SELECT '2021-10-31 00:00:00+02'::timestamptz + '1 day'::interval, date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe\/Warsaw');<\/code><\/pre>\n<pre><code>        ?column?        |        date_add ------------------------+------------------------ 2021-10-31 22:00:00+00 | 2021-10-31 23:00:00+00<\/code><\/pre>\n<p>The time zone parameter is also added to the\u00a0<code>generate_series<\/code>\u00a0function.<\/p>\n<p>Independence from session parameters allowed to declare all three functions as immutable in the time zone variant.<\/p>\n<h4>XML: formatting values<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/483bdb2a\">483bdb2a<\/a><\/p>\n<p>The\u00a0<code>XMLSERIALIZE<\/code>\u00a0function now has a parameter\u00a0<code>INDENT<\/code>\u00a0for formatting output:<\/p>\n<pre><code class=\"pgsql\">SELECT XMLSERIALIZE( DOCUMENT 'Support [NO] INDENT option in XMLSERIALIZE()Jim JonesPeter Smith and Tom LaneTom Lane' AS TEXT INDENT);<\/code><\/pre>\n<pre><code>                        xmlserialize &lt;feature>                                                  + &lt;name>Support [NO] INDENT option in XMLSERIALIZE()&lt;\/name>+ &lt;author>Jim Jones&lt;\/author>                               + &lt;reviewers>Peter Smith and Tom Lane&lt;\/reviewers>          + &lt;committer>Tom Lane&lt;\/committer>                          + &lt;\/feature>                                                 +<\/code><\/pre>\n<h4>pg_size_bytes: support for &#171;B&#187;<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ce1215d9\">ce1215d9<\/a><\/p>\n<p>Some parameter values can be set in bytes.<\/p>\n<pre><code class=\"pgsql\">SHOW log_parameter_max_length;<\/code><\/pre>\n<pre><code> log_parameter_max_length 512B<\/code><\/pre>\n<p>You can use\u00a0<code>pg_size_bytes<\/code>\u00a0to get the number of bytes:<\/p>\n<pre><code class=\"pgsql\">SELECT pg_size_bytes(current_setting('log_parameter_max_length'));<\/code><\/pre>\n<pre><code> pg_size_bytes        512<\/code><\/pre>\n<p>In previous versions, the\u00a0<code>pg_size_bytes<\/code>\u00a0function will throw an error because it understands\u00a0<code>bytes<\/code>, but not\u00a0<code>B<\/code>.<\/p>\n<h4>New functions: erf, erfc<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d5d57414\">d5d57414<\/a><\/p>\n<p>Together with\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969981#commit_38d81760\">random_normal<\/a>, the PostgreSQL 16 release introduces the\u00a0<a href=\"https:\/\/en.wikipedia.org\/wiki\/Error_function\">error function<\/a>\u00a0<code>erf<\/code>\u00a0and the complementary error function\u00a0<code>erfc<\/code>. The\u00a0<a href=\"https:\/\/en.wikipedia.org\/wiki\/68%E2%80%9395%E2%80%9399.7_rule\">68-95-99.7 rule<\/a>\u00a0can now be viewed like this:<\/p>\n<pre><code class=\"pgsql\">SELECT erf(1 \/ sqrt(2)) AS erf_1, erf(2 \/ sqrt(2)) AS erf_2, erf(3 \/ sqrt(2)) AS erf_3;<\/code><\/pre>\n<pre><code>       erf_1        |       erf_2        |       erf_3 --------------------+--------------------+-------------------- 0.6826894921370859 | 0.9544997361036416 | 0.9973002039367398<\/code><\/pre>\n<p>The\u00a0<code>erfc<\/code>\u00a0function returns a value close to\u00a0<code>1 - erf()<\/code>.<\/p>\n<h3>Performance<\/h3>\n<h4>Parallel execution of full and right hash joins<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/11c2d6fd\">11c2d6fd<\/a><\/p>\n<p>In previous versions, the query below was always executed sequentially. In PostgreSQL\u00a016, parallel execution is now possible:<\/p>\n<pre><code class=\"pgsql\">EXPLAIN (costs off) SELECT count(*) FROM bookings b FULL OUTER JOIN tickets t USING (book_ref);<\/code><\/pre>\n<pre><code>                          QUERY PLAN Finalize Aggregate ->  Gather Workers Planned: 2 ->  Partial Aggregate ->  Parallel Hash Full Join Hash Cond: (t.book_ref = b.book_ref) ->  Parallel Seq Scan on tickets t ->  Parallel Hash ->  Parallel Seq Scan on bookings b<\/code><\/pre>\n<h4>Options for the right antijoin<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/16dc2703\">16dc2703<\/a><\/p>\n<p>Let\u2019s look at the plan of a query that lists all the flights for which no tickets have been issued yet.<\/p>\n<pre><code class=\"pgsql\">15=# EXPLAIN SELECT * FROM flights f WHERE NOT EXISTS (SELECT 1 FROM ticket_flights t WHERE t.flight_id = f.flight_id);<\/code><\/pre>\n<pre><code>                                      QUERY PLAN Hash Anti Join  (cost=291530.67..338842.08 rows=133627 width=63) Hash Cond: (f.flight_id = t.flight_id) ->  Seq Scan on flights f  (cost=0.00..4772.67 rows=214867 width=63) ->  Hash  (cost=153851.52..153851.52 rows=8391852 width=4) ->  Seq Scan on ticket_flights t  (cost=0.00..153851.52 rows=8391852 width=4)<\/code><\/pre>\n<p>PostgreSQL 15, the query is executed using the antijoin (the Hash Anti Join node). The hash table is built for the\u00a0<code>ticket_flights<\/code>\u00a0table, the larger of the two tables from the query. Creation of a hash table requires some time and memory, which is reflected in the high startup cost of the query relative to the total cost.<\/p>\n<p>In PostgreSQL 16, the planner also considers the antijoin where the table positions are reversed. If the reverse plan turns out to be cheaper, it will be selected instead.<\/p>\n<pre><code class=\"pgsql\">16=# EXPLAIN SELECT * FROM flights f WHERE NOT EXISTS (SELECT 1 FROM ticket_flights t WHERE t.flight_id = f.flight_id);<\/code><\/pre>\n<pre><code>                                   QUERY PLAN Hash Right Anti Join  (cost=9767.51..288502.03 rows=134347 width=63) Hash Cond: (t.flight_id = f.flight_id) ->  Seq Scan on ticket_flights t  (cost=0.00..153850.08 rows=8391708 width=4) ->  Hash  (cost=4772.67..4772.67 rows=214867 width=63) ->  Seq Scan on flights f  (cost=0.00..4772.67 rows=214867 width=63)<\/code><\/pre>\n<p>The modified plan node is now called Hash Right Anti Join.<\/p>\n<p>The Merge Join has received a similar modification.<\/p>\n<h4>Relation extension mechanism rework<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/31966b15\">31966b15<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/00d1e02b\">00d1e02b<\/a><\/p>\n<p>When records are added into a table frequently, the table files have to be extended by appending new pages. To prevent multiple clients from adding new pages at the same time, a relation extension lock is used. Such locks may significantly affect performance when multiple clients are actively inserting records.<\/p>\n<p>The idea behind this update is to have only the bare minimum of operations done while the lock is held, and perform the rest without the lock.<\/p>\n<p>Behind this modest summary is a tremendous amount of work which resulted in 13 separate patches, of which I\u2019ve listed just two.<\/p>\n<p>As a result, not only applications with frequent record insertions by multiple clients will benefit, but also single thread insertion which will be performed at a lower cost.<\/p>\n<h4>BRIN indexes don\u2019t block HOT updates<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/19d8e230\">19d8e230<\/a><\/p>\n<p>HOT updates are not applied if at least one column modified by the\u00a0<code>UPDATE<\/code>\u00a0command is indexed by any type of index.<\/p>\n<p>However, BRIN indexes do not contain references to table rows, therefore it is safe to use HOT updates on columns with such indexes. With the new patch, BRIN indexes no longer affect HOT optimization:<\/p>\n<pre><code class=\"pgsql\">CREATE INDEX flights_bi ON flights USING brin(actual_departure); SELECT pg_stat_reset_single_table_counters('flights'::regclass); UPDATE flights SET actual_departure = actual_departure + '5 min'::interval WHERE flight_id = 1; SELECT n_tup_hot_upd, n_tup_newpage_upd FROM pg_stat_all_tables WHERE relname = 'flights';<\/code><\/pre>\n<pre><code> n_tup_hot_upd | n_tup_newpage_upd ---------------+------------------- 1 |                 0<\/code><\/pre>\n<p>One of the counters ticks up.<\/p>\n<p>The patch\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/e3fcca0d\">has been accepted<\/a>\u00a0into the PostgreSQL\u00a015 already before, but was rolled back before the release. This is the second attempt.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/42\/3392\/\">postgres_fdw: aborting transactions on remote servers in parallel mode<\/a><\/p>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/983ec230\">983ec230<\/a><\/p>\n<p>PostgreSQL 15\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/04e706d4\">introduced<\/a>\u00a0the ability to asynchronously commit transactions affecting foreign tables connected via\u00a0<code>postgres_fdw<\/code>.<\/p>\n<p>Now, with the new\u00a0<code>parallel_abort<\/code>\u00a0parameter for the external server, transactions can be aborted asynchronously as well.<\/p>\n<h4>force_parallel_mode \u2192 debug_parallel_query<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/5352ca22\">5352ca22<\/a><\/p>\n<p>The parameter\u00a0<em>force_parallel_mode<\/em>\u00a0is now called\u00a0<em>debug_parallel_query<\/em>.<\/p>\n<p>This is done because many users mistakingly enable this parameter, thinking that it will increase query execution speed. In fact, the result is quite the opposite!<\/p>\n<p>When switched on, the parameter makes the server always prefer the parallel plan, even if it is the slower one. The parameter should be used for query debugging, whenever the developer wants to figure out why the server prefers a sequential plan over its parallel equivalent. The new name reflects what the parameter does more precisely and should not provoke users to switch it on based on the name alone.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.cybertec-postgresql.com\/en\/debug_parallel_query-setting-postgresql-16\/\">New old \u201cdebug_parallel_query\u201d setting in PostgreSQL 16<\/a>\u00a0(Pavlo Golub)<\/p>\n<h4>Direct I\/O (for developers only)<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/faeedbce\">faeedbce<\/a><\/p>\n<p>PostgreSQL works with files through the operating system, not directly. This is an effort to introduce the ability to input\/output data and WAL directly. The work continues, so no useful new features so far.<\/p>\n<p>There is a new configuration parameter\u00a0<em>io_direct<\/em>, but you should avoid switching it on. The documentation honestly\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-developer.html#GUC-IO-DIRECT\">states<\/a>\u00a0that doing so will decrease performance, because it would disable the operating system\u2019s prefetching, and PostgreSQL\u2019s own prefetching has not been developed yet. The parameter is here for the developers and is intended for testing purposes only.<\/p>\n<h3>Logical replication<\/h3>\n<h4>Logical replication from a physical replica<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/0fdab27a\">0fdab27a<\/a><\/p>\n<p>This patch makes it possible to subscribe to a physical replica of a publication server rather than to the server itself.<\/p>\n<p>The\u00a0<em>wal_level<\/em>\u00a0parameter must be set to logical both on the primary server and the physical replica for this to work:<\/p>\n<pre><code class=\"bash\">replica=# SELECT pg_is_in_recovery(), current_setting('wal_level');<\/code><\/pre>\n<pre><code> pg_is_in_recovery | current_setting -------------------+----------------- t                 | logical<\/code><\/pre>\n<p>Create a publication on the primary server:<\/p>\n<pre><code class=\"pgsql\">primary=# CREATE TABLE t (id int primary key); primary=# INSERT INTO t VALUES (42); primary=# CREATE PUBLICATION pub FOR TABLE t;<\/code><\/pre>\n<p>Make sure that both the table and the publication are present on the replica:<\/p>\n<pre><code class=\"bash\">replica=# SELECT * FROM t;<\/code><\/pre>\n<pre><code> id 42 (1 row)<\/code><\/pre>\n<pre><code class=\"bash\">replica=# \\dRp+<\/code><\/pre>\n<pre><code>                              Publication pub Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f          | t       | t       | t       | t         | f Tables: \"public.t\"<\/code><\/pre>\n<p>Now, on a separate server (subscriber), subscribe to the publication. In the connection string, specify the replica that is located on port 5401:<\/p>\n<pre><code class=\"bash\">subscriber=# CREATE TABLE t (id int primary key); subscriber=# CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres port=5401' PUBLICATION pub;<\/code><\/pre>\n<p>This command should create a replication slot on the physical replica. If the primary server is inactive at this moment, it might take some time. You can speed things up by executing the following command on the primary server:<\/p>\n<pre><code class=\"bash\">primary=# SELECT pg_log_standby_snapshot();<\/code><\/pre>\n<p>The replica will \u201cwake up\u201d and the slot will be created just on it:<\/p>\n<pre><code class=\"bash\">replica=# SELECT slot_name, active FROM pg_replication_slots WHERE slot_name = 'sub';<\/code><\/pre>\n<pre><code> slot_name | active -----------+-------- sub       | t (1 row)<\/code><\/pre>\n<pre><code class=\"bash\">primary=# SELECT slot_name, active FROM pg_replication_slots WHERE slot_name = 'sub';<\/code><\/pre>\n<pre><code> slot_name | active -----------+-------- (0 rows)<\/code><\/pre>\n<p>As for logical replication, it is already working:<\/p>\n<pre><code class=\"bash\">subscriber=# SELECT * FROM t;<\/code><\/pre>\n<pre><code> id 42 (1 row)<\/code><\/pre>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/bdrouvot.github.io\/2023\/04\/19\/postgres-16-highlight-logical-decoding-on-standby\/\">Postgres 16 highlight: Logical decoding on standby<\/a>\u00a0(Bertrand Drouvot)<\/p>\n<h4>Using non-unique indexes with REPLICA IDENTITY FULL<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/89e46da5\">89e46da5<\/a><\/p>\n<p>If a table in a publication does not have a primary or a unique key, then\u00a0<code>REPLICA IDENTITY FULL<\/code>\u00a0is used as the replication ID. On the subscriber side, the search for modified and deleted rows is done via sequential scan.<\/p>\n<p>With the new patch, logical replication will be able to use the non-unique index to identify rows on the subscriber that have been modified or deleted. Index selection cannot be controlled. If the table has several indexes, one of them will be selected arbitrarily. Only indexes of B-tree type which are not partial and include at least one column of the table (not entirely functional) can be used.<\/p>\n<h4>Initial synchronization in binary format<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ecb69652\">ecb69652<\/a><\/p>\n<p>To speed up replication, you can enable the parameter binary for the subscription so that the publishing server sends changes in binary format:<\/p>\n<pre><code class=\"pgsql\">CREATE SUBSCRIPTION .. WITH (binary);<\/code><\/pre>\n<p>Before PostgreSQL 16, this affected just the format the changes were replicated in, while during initial synchronization, only the textual format was used to transmit the data. Now the\u00a0<code>COPY<\/code>\u00a0command that performs the initial synchronization will also work in binary format.<\/p>\n<h4>Privileges for\u00a0creating subscriptions\u00a0and\u00a0applying changes<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/c3afe8cf\">c3afe8cf<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1e10d49b\">1e10d49b<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/48267598\">48267598<\/a><\/p>\n<p>The first commit creates a predefined role\u00a0<code>pg_create_subscription<\/code>. Members of this role can create subscriptions (and replication slots on the publishing server) without superuser privileges.<\/p>\n<p>For security reasons, the subscription owner must switch to the table owner role for any\u00a0<code>INSERT<\/code>,\u00a0<code>UPDATE<\/code>,\u00a0<code>DELETE<\/code>\u00a0and\u00a0<code>TRUNCATE<\/code>\u00a0operations (second commit). Therefore, the subscription owner must have the right to execute the necessary\u00a0<code>SET ROLE<\/code>\u00a0commands.<\/p>\n<p>The old behavior, when the subscription owner has the rights to apply changes, can be enabled by the new subscription parameter\u00a0<code>run_as_owner<\/code>\u00a0(third commit):<\/p>\n<pre><code class=\"pgsql\">CREATE SUBSCRIPTION .. WITH (run_as_owner);<\/code><\/pre>\n<h4>Committing changes in parallel mode (for developers only)<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/c3afe8cf\">c3afe8cf<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/1e10d49b\">1e10d49b<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/9f2213a7\">9f2213a7<\/a><\/p>\n<p>Multiple processes will be able to apply changes on the subscriber at the same time. This will speed up the processing of large transactions received from the publication server.<\/p>\n<p>The feature is not implemented in full yet, so no details so far. However, there is a hint in the patch: a new parameter\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/runtime-config-developer.html#GUC-LOGICAL-REPLICATION-MODE\">logical_replication_mode<\/a>\u00a0in the development section.<\/p>\n<hr\/>\n<p>That\u2019s all for now. Nothing new will get into PostgreSQL\u00a016 at this point. Looking forward to the release in the Autumn!<\/p>\n<\/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\/739226\/\"> https:\/\/habr.com\/ru\/articles\/739226\/<\/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-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<figure class=\"full-width\"><\/figure>\n<p>The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let\u2019s take a look at some exciting new updates it introduced.<\/p>\n<p>I hope that this review together with the previous articles in the series (<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969676\">2022-07<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969859\">2022-09<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969929\">2022-11<\/a>,\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969981\">2023-01<\/a>) will give you a coherent idea of the new features of PostgreSQL 16.<\/p>\n<p>As usual, the March Commitfest introduces a ton of new changes. I\u2019ve split them into several sections for convenience.<\/p>\n<h4>Monitoring<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_a9c70b46\">pg_stat_io: input\/output statistics<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ae4fdde1\">Counter for new row versions moved to another page when performing an UPDATE<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_f3fa3132\">pg_buffercache: new pg_buffercache_usage_counts function<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_daa8365a\">Normalization of DDL and service commands, continued<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_3c05284d\">EXPLAIN (generic_plan): generic plan of a parameterized query<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9d2d9728\">auto_explain: logging the query ID<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d3d53f95\">PL\/pgSQL: GET DIAGNOSTICS .. PG_ROUTINE_OID<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Client applications<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_b0d8f2d9\">psql: variables SHELL_ERROR and SHELL_EXIT_CODE<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_00beecfe\">psql: \\watch and the number of repetitions<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_3dfae91f\">psql:\\df+ does not show the source code of functions<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_0da243fe\">pg_dump: support for LZ4 and zstd compression methods<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_a563c24c\">pg_dump and partitioned tables<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d07c2948\">pg_verifybackup &#8212;progress<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_7f5b1981\">libpq: balancing connections<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Server administration and maintenance<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_3e51b278\">initdb: setting configuration parameters during cluster initialization<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_7d71d3dd\">Autovacuum: balancing I\/O impact on the fly<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_1cbbee03\">Managing the size of shared memory for vacuum and analyze<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_4211fbd8\">VACUUM for TOAST tables only<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_be504a3e\">The vacuum_defer_cleanup_age parameter has been removed<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_5c1b6628\">pg_walinspect: interpretation of the end_lsn parameter<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9ecb134a\">pg_walinspect: pg_get_wal_fpi_info \u2192 pg_get_wal_block_info<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Localization<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_0d21d4b9\">ICU: UNICODE collation<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ea1db8ae\">ICU: Canonization of locales<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_30a53b79\">ICU: custom rules for customizing the sorting algorithm<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Security<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_3a465cc6\">libpq: new parameter require_auth<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_b5777430\">scram_iterations: iteration counter for password encryption using SCRAM-SHA-256<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>SQL functions and commands<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_7081ac46\">SQL\/JSON standard support<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_b8da37b3\">New functions pg_input_error_info and pg_input_is_valid<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_a290378a\">The Daitch-Mokotoff Soundex<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_888f2ea0\">New functions array_shuffle and array_sample<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_2ddab010\">New aggregate function any_value<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9f8377f7\">COPY: inserting default values<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_75bd846b\">timestamptz: adding and subtracting time intervals<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_483bdb2a\">XML: formatting values<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ce1215d9\">pg_size_bytes: support for &#171;B&#187;<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d5d57414\">New functions: erf, erfc<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Performance<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_11c2d6fd\">Parallel execution of full and right hash joins<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_16dc2703\">Options for the right antijoin<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_31966b15\">Relation extension mechanism rework<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_19d8e230\">Don\u2019t block HOT update by BRIN index<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_983ec230\">postgres_fdw: aborting transactions on remote servers in parallel mode<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_5352ca22\">force_parallel_mode \u2192 debug_parallel_query<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_faeedbce\">Direct I\/O (for developers only)<\/a><\/p>\n<\/li>\n<\/ul>\n<h4>Logical replication<\/h4>\n<ul>\n<li>\n<p><a href=\"#commit_0fdab27a\">Logical replication from a physical replica<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_89e46da5\">Using non-unique indexes with REPLICA IDENTITY FULL<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_ecb69652\">Initial synchronization in binary format<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_c3afe8cf\">Privileges for creating subscriptions and applying changes<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_9f2213a7\">Committing changes in parallel mode (for developers only)<\/a><\/p>\n<\/li>\n<\/ul>\n<h3>Monitoring<\/h3>\n<h4>pg_stat_io: input\/output statistics<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a9c70b46\">a9c70b46<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ac8d53da\">ac8d53da<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/8aaa04b3\">8aaa04b3<\/a><\/p>\n<p>The new\u00a0<code>pg_stat_io<\/code>\u00a0view displays input\/output statistics.<\/p>\n<p>To be clear, this is disk input\/output as seen by PostgreSQL. Lower level caching (OS, disk controller) is not taken into account here. In addition, only buffer cache operations are currently monitored. Transferring tables and indexes to another tablespace or WAL operations may be added in the future.<\/p>\n<p>Each row of the view is identified by three columns:<\/p>\n<ul>\n<li>\n<p><code>backend_type<\/code>\u00a0\u2014 process type, same as in\u00a0<code>pg_stat_activity.backend_type<\/code>,<\/p>\n<\/li>\n<li>\n<p><code>object<\/code>\u00a0\u2015 object type, such as relation (permanent tables, indexes, &#8230;) or temp relation,<\/p>\n<\/li>\n<li>\n<p><code>context<\/code>\u00a0\u2015 type of operation performed on the object: normal, vacuum, bulkread, bulkwrite.<\/p>\n<\/li>\n<\/ul>\n<p>The names of the operations are intuitive, and details are listed in the documentation:\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/devel\/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW\">pg_stat_io<\/a>.<\/p>\n<p>Let\u2019s have a look at one of the rows:<\/p>\n<pre><code class=\"pgsql\">SELECT * FROM pg_stat_io WHERE backend_type = 'client backend' AND       object = 'relation' AND       context = 'bulkread' \\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]+------------------------------ backend_type | client backend object       | relation context      | bulkread reads        | 427497 read_time    | 752.489 writes       | 77220 write_time   | 215.93 extends      | extend_time  | op_bytes     | 8192 hits         | 38683 evictions    | 94210 reuses       | 330437 fsyncs       | fsync_time   | stats_reset  | 2023-04-29 09:13:58.798952+03<\/code><\/pre>\n<p>The row shows cumulative statistics for all client backend processes that performed bulk read operations on permanent relations. Such operations include sequential scanning of large tables using a ring buffer.<\/p>\n<p>Displayed statistics include buffer cache hits, evictions, and ring buffer reuses. You can also see that clients had to flush most of the buffer evictions to disk (writes).<\/p>\n<p>The reads, writes and extends (relation file extension) columns show the number of performed operations. To convert to memory units, multiply the values by\u00a0<code>op_bytes<\/code>. Since only buffer cache operations are monitored so far,\u00a0<code>op_bytes<\/code>\u00a0is always equal to the page size (8 kB). To collect execution time statistics (<code>*_time<\/code>\u00a0columns), the\u00a0<em>track_io_timing<\/em>\u00a0parameter must be enabled.<\/p>\n<p>As another example, let\u2019s look at which processes wrote buffers from the cache to disk and compare their impacts:<\/p>\n<pre><code class=\"pgsql\">SELECT backend_type, SUM(writes) blocks,        pg_size_pretty(SUM(writes*op_bytes)) size,        round(SUM(write_time)) \"time, ms\" FROM pg_stat_io WHERE writes > 0 GROUP BY ROLLUP (backend_type) ORDER BY blocks;<\/code><\/pre>\n<pre><code>   backend_type    | blocks |  size   | time, ms -------------------+--------+---------+----------  background writer |  17198 | 134 MB  |      187  checkpointer      |  30436 | 238 MB  |      139  background worker |  76929 | 601 MB  |      213  autovacuum worker |  88870 | 694 MB  |      528  client backend    | 369031 | 2883 MB |     1055                    | 582464 | 4551 MB |     2122 (6 rows)<\/code><\/pre>\n<p>The two main disk writing processes, background writer and checkpointer, wrote the least. This is a clear sign that the system configuration is suboptimal. Perhaps, the buffer cache size should be increased and background writer should be set up more aggressively.<\/p>\n<p>The\u00a0<code>pg_stat_bgwriter<\/code>\u00a0view shows similar data. The\u00a0<code>buffers_clean<\/code>\u00a0and\u00a0<code>buffers_checkpoint<\/code>\u00a0columns display how many buffers the background writer and checkpointer processes have written to disk. However, the\u00a0<code>buffers_backend<\/code>\u00a0column isn\u2019t really representative. It collects data from not only client processes, but also others, including autovacuum. In addition to that, relation file extensions aren\u2019t tracked separately (as in\u00a0<code>pg_stat_io<\/code>), but rather are simply added to\u00a0<code>buffers_backend<\/code>, despite the operation having nothing to do with flushing cache to disk.<\/p>\n<p>See also:<\/p>\n<p><a href=\"https:\/\/www.depesz.com\/2023\/02\/27\/waiting-for-postgresql-16-add-pg_stat_io-view-providing-more-detailed-io-statistics\/\">Waiting for PostgreSQL 16 \u2013 Add pg_stat_io view, providing more detailed IO statistics<\/a>\u00a0(Hubert &#8216;depesz&#8217; Lubaczewski)<\/p>\n<p><a href=\"https:\/\/pganalyze.com\/blog\/pg-stat-io\">Waiting for Postgres 16: Cumulative I\/O statistics with pg_stat_io<\/a>\u00a0(Lukas Fittl)<\/p>\n<h4>Counter for new row versions moved to another page when performing an UPDATE<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/ae4fdde1\">ae4fdde1<\/a><\/p>\n<p>A new column has appeared in the\u00a0<code>pg_stat_*_tables<\/code>\u00a0family of statistical views:\u00a0<code>n_tup_newpage_upd<\/code>. Together with the existing columns\u00a0<code>n_tup_hot_upd<\/code>\u00a0and\u00a0<code>n_tup_upd<\/code>, it helps evaluate the effectiveness of HOT optimization.<\/p>\n<p>HOT optimization is done when updating non-indexed table columns. A new row version is created in the page, but no new records are created in the table indexes. This is the essence of HOT optimization. But in order for it to work, you need to have enough space in the page to place a new row version there. If the space is insufficient, the row version is created in another page and new entries are also created in all indexes. It is these row changes that are tracked by the new\u00a0<code>n_tup_newpage_upd<\/code>\u00a0counter.<\/p>\n<p>If the counter value is too high, you may want to consider reducing the\u00a0<code>fillfactor<\/code>\u00a0value for the table. By reserving more page space, you increase the chances for HOT optimization to work.<\/p>\n<p>Let\u2019s create a separate database and set up pgbench:<\/p>\n<pre><code class=\"pgsql\">=# CREATE DATABASE test_fillfactor; =# \\c test_fillfactor<\/code><\/pre>\n<pre><code class=\"bash\">$ pgbench -i test_fillfactor<\/code><\/pre>\n<p>Run pgbench for 10 seconds and look at row change statistics for the pgbench_accounts table:<\/p>\n<pre><code class=\"bash\">$ pgbench -T 10 test_fillfactor<\/code><\/pre>\n<pre><code class=\"pgsql\">SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]-----+----------------- relname           | pgbench_accounts n_tup_upd         | 11307 n_tup_hot_upd     | 9644 n_tup_newpage_upd | 1663<\/code><\/pre>\n<p>The sum of\u00a0<code>n_tup_hot_upd<\/code>\u00a0and\u00a0<code>n_tup_newpage_upd<\/code>\u00a0equals\u00a0<code>n_tup_upd<\/code>. This means that during the test, all\u00a0<code>UPDATE<\/code>\u00a0commands did not update indexed columns, and therefore HOT optimization was possible. But in about 15% of cases, HOT optimization did not work because there was not enough space in the page for a new row version.<\/p>\n<p>Let\u2019s reset the statistics, decrease the\u00a0<code>fillfactor<\/code>\u00a0value and try again.<\/p>\n<pre><code class=\"pgsql\">ALTER TABLE pgbench_accounts SET (fillfactor = 80); SELECT pg_stat_reset_single_table_counters('pgbench_accounts'::regclass);<\/code><\/pre>\n<pre><code class=\"bash\">pgbench -T 10 test_fillfactor<\/code><\/pre>\n<pre><code class=\"pgsql\">SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]-----+----------------- relname           | pgbench_accounts n_tup_upd         | 11707 n_tup_hot_upd     | 11704 n_tup_newpage_upd | 3<\/code><\/pre>\n<p>The result is radically different. There were only three instances where there was not enough space in the page.<\/p>\n<p>Curiously, the\u00a0<code>pg_statistic<\/code>\u00a0system table is a candidate for decreasing\u00a0<code>fillfactor<\/code>:<\/p>\n<pre><code class=\"pgsql\">VACUUM ANALYZE; SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd FROM pg_stat_all_tables WHERE relname = 'pg_statistic'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]-----+------------- relname           | pg_statistic n_tup_upd         | 458 n_tup_hot_upd     | 182 n_tup_newpage_upd | 276<\/code><\/pre>\n<h4>pg_buffercache: new pg_buffercache_usage_counts function<\/h4>\n<p>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/f3fa3132\">f3fa3132<\/a><\/p>\n<p>A\u00a0<code>pg_buffercache_summary<\/code>\u00a0function was added to the\u00a0<code>pg_buffercache<\/code>\u00a0extension during the\u00a0<a href=\"https:\/\/postgrespro.com\/blog\/pgsql\/5969929#commit_2589434a\">November Commitfest<\/a>.<\/p>\n<p>Now, another function is added. It shows aggregated buffer cache data subdivided by usage counts.<\/p>\n<pre><code class=\"pgsql\">SELECT * FROM pg_buffercache_usage_counts();<\/code><\/pre>\n<pre><code> usage_count | buffers | dirty | pinned -------------+---------+-------+-------- 0 |   15791 |     0 |      0 1 |     105 |     1 |      0 2 |      89 |     4 |      0 3 |      22 |     1 |      0 4 |      32 |     3 |      0 5 |     345 |    27 |      0 (6<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\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-403047","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/403047","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=403047"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/403047\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=403047"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=403047"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=403047"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}