#Article

The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let’s take a look at some exciting new updates it introduced. I hope that this review together with the previous articles in the series ( 2022-07 , 2022-09 , 2022-11 , 2023-01 ) will give you a coherent idea of the new features of PostgreSQL 16. As usual, the March Commitfest introduces a ton of new changes. I’ve split them into several sections for convenience. Monitoring pg_stat_io: input/output statistics Counter for new row versions moved to another page when performing an UPDATE pg_buffercache: new pg_buffercache_usage_counts function Normalization of DDL and service commands, continued EXPLAIN (generic_plan): generic plan of a parameterized query auto_explain: logging the query ID PL/pgSQL: GET DIAGNOSTICS .. PG_ROUTINE_OID Client applications psql: variables SHELL_ERROR and SHELL_EXIT_CODE psql: \watch and the number of repetitions psql:\df+ does not show the source code of functions pg_dump: support for LZ4 and zstd compression methods pg_dump and partitioned tables pg_verifybackup --progress libpq: balancing connections Server administration and maintenance initdb: setting configuration parameters during cluster initialization Autovacuum: balancing I/O impact on the fly Managing the size of shared memory for vacuum and analyze VACUUM for TOAST tables only The vacuum_defer_cleanup_age parameter has been removed pg_walinspect: interpretation of the end_lsn parameter pg_walinspect: pg_get_wal_fpi_info → pg_get_wal_block_info Localization ICU: UNICODE collation ICU: Canonization of locales ICU: custom rules for customizing the sorting algorithm Security libpq: new parameter require_auth scram_iterations: iteration counter for password encryption using SCRAM-SHA-256 SQL functions and commands SQL/JSON standard support New functions pg_input_error_info and pg_input_is_valid The Daitch-Mokotoff Soundex New functions array_shuffle and array_sample New aggregate function any_value COPY: inserting default values timestamptz: adding and subtracting time intervals XML: formatting values pg_size_bytes: support for "B" New functions: erf, erfc Performance Parallel execution of full and right hash joins Options for the right antijoin Relation extension mechanism rework Don’t block HOT update by BRIN index postgres_fdw: aborting transactions on remote servers in parallel mode force_parallel_mode → debug_parallel_query Direct I/O (for developers only) Logical replication Logical replication from a physical replica Using non-unique indexes with REPLICA IDENTITY FULL Initial synchronization in binary format Privileges for creating subscriptions and applying changes Committing changes in parallel mode (for developers only) ...

We continue to follow the news of the PostgreSQL 16 release, and today, the results of the fourth CommitFest are on the table. Let’s have a look. If you missed the previous CommitFests, check out our reviews for 2022-07 , 2022-09 and 2022-11 . Here are the patches I want to talk about this time: New function: random_normal Input formats for integer literals Goodbye, postmaster Parallel execution for string_agg and array_agg New parameter: enable_presorted_aggregate Planner support function for working with window functions Optimized grouping of repeating columns in GROUP BY and DISTINCT VACUUM parameters: SKIP_DATABASE_STATS and ONLY_DATABASE_STATS pg_dump: lock tables in batches PL/pgSQL: cursor variable initialization Roles with the CREATEROLE attribute Setting parameter values at the database and user level New parameter: reserved_connections postgres_fdw: analyzing foreign tables with TABLESAMPLE postgres_fdw: batch insert records during partition key updates pg_ident.conf: new ways to identify users in PostgreSQL Query jumbling for DDL and utility statements New function: bt_multi_page_stats New function: pg_split_walfile_name pg_walinspect, pg_waldump: collecting page images from WAL

We continue to follow the news of the upcoming PostgreSQL 16. The third CommitFest concluded in early December. Let's look at the results. If you missed the previous CommitFests, check out our reviews: 2022-07 , 2022-09 . Here are the patches I want to talk about: meson: a new source code build system Documentation: a new chapter on transaction processing psql: \d+ indicates foreign partitions in a partitioned table psql: extended query protocol support Predicate locks on materialized views Tracking last scan time of indexes and tables pg_buffercache: a new function pg_buffercache_summary walsender displays the database name in the process status Reducing the WAL overhead of freezing tuples Reduced power consumption when idle postgres_fdw: batch mode for COPY Modernizing the GUC infrastructure Hash index build optimization MAINTAIN ― a new privilege for table maintenance SET ROLE: better role change management Support for file inclusion directives in pg_hba.conf and pg_ident.conf Regular expressions support in pg_hba.conf

It’s official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release. Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code. If you missed the July CommitFest, our previous article will get you up to speed in no time. Here are the patches I want to talk about: SYSTEM_USER function Frozen pages/tuples information in autovacuum's server log pg_stat_get_backend_idset returns the actual backend ID Improved performance of ORDER BY / DISTINCT aggregates Faster bulk-loading into partitioned tables Optimized lookups in snapshots Bidirectional logical replication pg_auth_members: pg_auth_members: role membership granting management pg_auth_members: role membership and privilege inheritance pg_receivewal and pg_recvlogical can now handle SIGTERM

In the previous articles, covered query execution stages , statistics , sequential and index scan , and two of the three join methods: nested loop and hash join . This last article of the series will cover the merge algorithm and sorting . I will also demonstrate how the three join methods compare against each other.

August is a special month in PostgreSQL release cycle. PostgreSQL 15 isn't even officially out yet, but the first CommitFest for the 16th release has already been held. Let's compile the server and check out the cool new stuff!