PostgreSQL 16 is released, with a lot of content such as performance improvements such as up to 300% improvement in COPY performance and enhancement of logical replication function.

Version 16 of the open source database 'PostgreSQL' has been released. It has so much content that it's hard to believe that 35 years have passed since development began, including performance improvements and logical replication enhancements, as well as improvements to developer quality of life and security-related updates.

PostgreSQL: PostgreSQL 16 Released!

https://www.postgresql.org/about/news/postgresql-16-released-2715/

◆Query planner is improved
・FULL and RIGHT JOIN can be parallelized
・Generate more efficient plans for queries that use aggregate functions at the same time as 'DISTINCT' and 'ORDER BY' clauses
- Use incremental sort for ' SELECT DISTINCT ' queries
・Optimized window function
・Improved anti-join of RIGHT and OUTER

◆Other performance improvements
- Improved bulk loading using COPY, increasing performance by up to 300%
- Supports client load balancing using libpq
Improved VACUUM strategy to reduce the need to FREEZE the entire table
- Introduced CPU acceleration using SIMD on both x86 and ARM architectures

◆ Logical replication
- Logical replication from standby instance is now possible
- Subscribers can now apply large transactions using parallel workers
- Subscribers can now use B-tree indexes on tables that do not have a primary key .
- New predefined roles related to logical replication have been added to improve access control.
・Supports bidirectional logical replication

◆Improved developer experience
- Added SQL/JSON standard syntax
'JSON_ARRAY()', 'JSON_ARRAYAGG()', 'IS JSON', etc. have been added.

・Supports various number expressions
Underscores can now be used as delimiters, such as ``5_432_000'', as well as expressions other than decimal numbers, such as ``0x1538'', ``0o12470'', and ``0b1010100111000''.

- \bind can now be used with psql
When using psql, it is now possible to use \bind to substitute variables in parameterized queries, such as 'SELECT $1::int + $2::int \bind 1 2 \g'. Ta.

Improved general support for string collations
PostgreSQL 16 is built with standard ICU support, determines the ICU locale based on the environment, and allows users to define their own ICU collations.

◆Monitoring
・Introduced pg_stat_io indicator for analysis of I/O access patterns
- Added last scan date and time to pg_stat_all_tables
・Improved readability of auto_explain
- Improved query tracking algorithms for pg_stat_statements and pg_stat_activity

◆Access control and security
- Added functionality to pg_hba.conf and pg_ident.conf files
Regular expression matching between user names and database names and include directives for external configuration files can now be used.

- Added client connection parameters to improve security
Added parameters such as ``require_auth'', which allows the client to specify authentication parameters to accept from the server, and sslrootcert='system', which indicates that PostgreSQL should use the trusted certificate authority store provided by the client. Ta.

・Supports delegation of Kerberos authentication information
Extensions such as postgres_fdw and dblink can now connect to trusted services using authenticated credentials.

The support period for PostgreSQL is 5 years from release, so version 16 is scheduled to be supported until November 2028.

If you would like to check the update details in more detail, please check the release notes .