PostgreSQL New Feature - 16 Release (1)

Performance Improvements

• Better PostgreSQL performance through new query planner optimizations
• Parallel execution of FULL OUTER JOIN and RIGHT OUTER JOIN
• More optimized query plans for aggregate functions (e.g. string_agg, array_agg) used with DISTINCT or ORDER BY
• Controlled by the enable_presorted_aggregate parameter
• Incremental sort for SELECT DISTINCT queries
• Window function optimizations
• Improved anti-joins (RIGHT, OUTER)

• Improved bulk loading with COPY → up to 300% faster in some cases

• Vacuum improvements that reduce the need for full-table freezes

• Features such as RANGE caching and LIST partition lookup make it possible to load large volumes of data into partitioned tables and to control shared buffer usage more efficiently through Vacuum and Analyze
• Faster RANGE and LIST partition lookups
• Shared buffer usage can be analyzed through Vacuum and Analyze
• Added VACUUM BUFFER_USAGE_LIMIT and ANALYZE BUFFER_USAGE_LIMIT options
• vacuumdb --buffer-usage-limit
• The default is set by vacuum_buffer_usage_limit, which also controls autovacuum

• CPU acceleration using SIMD on x86 and ARM architectures
• Faster processing of ASCII and JSON strings and faster array and subtransaction searches

Logical Replication

• Logical replication from standby servers
• Logical replication supported on physical replication standby servers
• New pg_log_standby_snapshot() function
• Changes can be published to other standby servers from a standby server instead of the primary server

• Logical replication performance improvements
• Subscriptions can apply large transactions using parallel workers
• For tables without a primary key, subscriptions can look up rows using a B-tree index instead of a sequential scan
• Under certain conditions, initial table synchronization can be sped up using binary format

• New pg_create_subscription role
• Non-superusers with CREATE privilege can run CREATE SUBSCRIPTION

• Support for bidirectional logical replication
• Data can be replicated between two tables in different publications

Usability

• More standard SQL/JSON syntax, including constructors and predicates such as JSON_ARRAY(), JSON_ARRAYAGG(), and IS JSON

• Subquery aliases are no longer required

• Underscores allowed as thousands separators (e.g. 5_432_000)

• New psql \bind command to run queries with parameters

• Improved support for text collation

• Load balancing for clients using libpq
• Added load_balance_hosts option (Value : random / disable)

Monitoring

• New pg_stat_io view for detailed analysis of I/O access patterns

• New column in the pg_stat_all_tables view recording when a table or index was last scanned
• last_seq_scan
• last_idx_scan

• The pg_stat_all_tables view records statistics on updated rows that move to a new page
• n_tup_newpage_upd

• Logs the values passed to parameterized statements, which makes auto_explain easier to read and improves the accuracy of the query tracking algorithm used by pg_stat_statements and pg_stat_activity

• Speculative lock information added to the pg_locks view
• A speculative insertion lock consists of a transaction ID and a speculative insertion token
• The transaction ID is shown in the transactionid column
• The speculative insertion token is shown in the objid column

Security

• Improved management of pg_hba.conf and pg_ident.conf files

• Security improvements

• GRANT group_name TO user_name can be run with ADMIN OPTION
• Previously required the CREATEROLE privilege

• WITH ADMIN TRUE/FALSE syntax allowed in GRANT
• Previously only WITH ADMIN OPTION was supported
• Administrators can define more specific roles and assign privileges accordingly, which simplifies privilege management

PostgreSQL 16 Incompatibilities

• NULL NOT DISTINCT indexes are not allowed for primary keys

• REINDEX DATABASE and reindexdb changed so they do not process indexes on system catalogs
• REINDEX SYSTEM and reindexdb --system can still be used

• pg_get_wal_records_info_till_end_of_wal() and pg_get_wal_stats_till_end_of_wal() functions removed from pg_walinspect

• force_parallel_mode parameter renamed to debug_parallel_query

• Removed the ability to manually CREATE VIEW using ON SELECT

• vacuum_defer_cleanup_age parameter removed
• No longer needed since hot_standby_feedback and replication slots were added

• promote_trigger_file parameter removed
• No longer needed since pg_ctl promote or pg_promote() can be used

• Read-only server variables lc_collate and lc_ctype removed
• Collation and locale can differ per database, so exposing them as read-only server variables is inappropriate

PostgreSQL New Feature - 16 Release (2) | EXEM

Learn about the PostgreSQL 16 release

https://ex-em.com/en/academy/PostgreSQL-NewFeature-2

PostgreSQL New Feature - 16 Release (3) | EXEM

Learn about the PostgreSQL 16 release

https://ex-em.com/en/academy/PostgreSQL-NewFeature-3