PostgreSQL 16 DBMS is released
September 15, 2023
The major changes and improvements in PostgreSQL 16, according to OpenNET:
- added a logical replication mechanism that allows changes made to the database during record additions, deletions or updates to be broadcast to another server, expanded with the ability to replicate changes from a standby server (standby). For example, in case of a high load on the primary server, the standby server can be used to transfer changes to other secondary systems;
- added support for bidirectional logical table replication, which allows synchronizing changes in two tables on different servers. Replication capabilities added to PostgreSQL 16 allow you to create configurations with several active servers (active-active mode), on which you can perform INSERT, UPDATE, DELETE operations simultaneously;
- the performance of logical replication is improved. For example, the possibility of using parallel handlers when subscribers use large transactions has been implemented. Added the ability to use B-tree indexes for tables without primary keys on the subscriber's side, which allows to avoid sequential scanning when searching for records. Accelerated execution of initial synchronization operations for some tables in binary format;
- means of controlling access to logical replication have been extended. For example, a new predefined role "pg_create_subscription" has been added to give users the right to create new subscriptions;
- added support for load balancing on the client side using the libpq library. Load balancing allows a client to connect to different hosts, which can be selected either in a specific or random order. If a host is unavailable, an attempt is made to connect to another server. Once the connection is established, requests within the session are sent to the selected server;
- optimizations were made and the work of the request scheduler was improved;
- implemented support for the "IS JSON" operation to check the JSON expression type defined in SQL:2023 standard. Added JSON_ARRAY() and JSON_ARRAYAGG() functions for creating arrays in JSON format;
- Allowed the use of the underscore character in numbers to increase the clarity of numeric literals. For example "SELECT ... WHERE a > 1_000_000";
- added the ability to specify hexadecimal, binary and octal literals. For example "SELECT 0x1538, 0o12450, 0b1010100111001";
- a new command "\bind" was added to the psql utility, which allows to form parameterized queries and use variables in queries. For example, "SELECT $1::int + $2::int \bind 1 2 \g";
- extended support for the "Collation" locale properties, which allow you to set sorting rules and comparison methods taking into account the meaning of characters (for example, when sorting numeric values, the presence of minus and dot before the number and different types of spelling are taken into account, while the comparison does not take into account the case of characters and the presence of the accent mark). By default, now builds with ICU locale ("ICU Collation") instead of libc locale;
- extended DBMS performance monitoring capabilities. Added "pg_stat_io" service view with statistics reflecting the I/O load on the system by various backends (background worker, autovacuum) and objects;
- a new field with data on the time of the last table or index scan was added to the "pg_stat_all_tables" service view;
- support for journaling of values passed to parameterized queries was added to the "auto_explain" module. The accuracy of the query tracking algorithm used in pg_stat_statements and pg_stat_activity views has been improved;
- in pg_hba.conf and pg_ident.conf files containing access and authentication settings, the possibility of using regular expressions for user and database names has been provided, and "include", "include_if_exists" and "include_dir" directives have been added to include the contents of other configuration files;
- added additional parameters to manage client connection protection: require_auth, to specify authentication parameters allowed when connecting to the server; sslrootcert="system" to use the storage of root certificates of certification centers provided by the client's operating system. Added support for Kerberos delegated credentials mechanism, which can be used for authenticated connection to external services using postgres_fdw and dblink modules.