#Article
Recent posts
May 25
  •  
PostgreSQL
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)
...
March 7
  •  
PostgreSQL
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
December 31, 2022
  •  
PostgreSQL
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
November 14, 2022
  •  
PostgreSQL
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
October 6, 2022
  •  
PostgreSQL

Queries in PostgreSQL: 7. Sort and merge
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 17, 2022
  •  
PostgreSQL
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!