In this post, we test some of the major changes in the PostgreSQL 16 release listed earlier.
[Performance] Parallel FULL OUTER JOIN and RIGHT OUTER JOIN
Starting with PostgreSQL 16, FULL OUTER JOIN and RIGHT OUTER JOIN can run in parallel, so the query planner can build more efficient execution plans than in earlier versions.
📢 Parallel processing is controlled by the
enable_parallel_hash parameter.[Performance] Parallel aggregate functions
Aggregate functions on large data sets can be processed in a parallel, distributed way to speed up query execution. Because aggregate functions can now run in parallel, the query planner can build more efficient execution plans than in earlier versions.
Comparing the cost of the same SQL on PostgreSQL 15 and 16, the final cost is 133493.19 on PostgreSQL 16 and 242301.64 on PostgreSQL 15. This shows a performance improvement in terms of cost.
[Usability] Subquery alias no longer required
Up to PostgreSQL 15, omitting a subquery alias raised an error. Starting with PostgreSQL 16, no error occurs even without an alias.
[Usability] Thousands separators in numeric literals
Starting with PostgreSQL 16, you can use underscores (_) as thousands separators in numeric literals.
[Usability] New \bind command in psql
The new psql
\bind command lets you run queries with parameters.📢 If you connect to PostgreSQL 15 with psql 16, you can use the \bind command.
[Usability] Load balancing for clients using libpq
Since PostgreSQL 10, every client using libpq has been able to connect to multiple hosts.
In this example, connections are attempted in the order 10.10.45.240:5432, 10.10.45.241:5433, 10.10.45.242:5434.
PostgreSQL 16 adds the load_balance_hosts parameter and the PGLOADBALANCEHOSTS environment variable, which let you attempt connections in random order.
random: tries the listed connections in random order
disable: tries connections the existing way (in order)
If you want to give a server more weight, you can list it multiple times.
[Monitoring] New last_seq_scan & last_idx_scan columns
PostgreSQL 16 records when the last sequential scan and last index scan were performed on a table. The last_seq_scan and last_idx_scan columns were added to pg_stat_*_tables, and the last_idx_scan column was added to pg_stat_*_indexes. To minimize overhead, these columns are updated only at transaction commit. The last_idx_scan column helps identify unnecessary indexes, and the last_seq_scan column helps identify where a new index may be needed.
[Monitoring] pg_stat_io Catalog
Up to PostgreSQL 15, you could check and analyze I/O statistics through catalogs such as
pg_stat_database, pg_statio_all_tables, pg_statio_all_indexes, pg_stat_bgwriter, and pg_stat_statements. However, these catalogs cannot measure I/O accurately, for the following reasons.- What writes include (Writes = Flushes + extends)
- Reads and writes cannot be broken down by backend type (all backend types are shown combined)
- Statistics cannot be broken down by context (I/O operation) and object (storage type) (all contexts and objects are shown combined)
PostgreSQL 16 introduces
pg_stat_io, which lets you analyze I/O access patterns in detail. With track_io_timing = on, the pg_stat_io system view makes it easier to track down I/O performance problems.
Column | Description |
backend_type | Backend Type. (e.g. background worker, client backend, walsender, standalone backend, autovacuum worker, autovacuum launcher, background writer, startup, checkpointer) |
object | I/O target (storage type). relation, temp relation |
context | I/O operation. normal, vacuum, bulkread, bulkwrite |
reads | Number of read operations (size in bytes = reads * op_bytes) |
read_time | Time spent on read operations (ms) → requires track_io_timing = on (0 if off) |
writes | Number of write operations (size in bytes = writes * op_bytes) |
write_time | Time spent on write operations (ms) → requires track_io_timing = on (0 if off) |
writebacks | Number of times data was sent from the kernel to the OS (size in bytes = writebacks * op_bytes) |
writeback_time | Time spent on writeback operations (ms) → requires track_io_timing = on (0 if off) |
extends | Number of relation extends (size in bytes = extends * op_bytes) |
extend_time | Time spent on relation extend operations (ms) → requires track_io_timing = on (0 if off) |
op_bytes | Size per I/O unit. Determined by the block_size parameter; default = 8192 (8k) |
hits | Number of times the desired block was found in shared buffers |
evictions | Number of times space was freed up for a new block |
reuses | |
fsyncs | Number of fsync calls. Tracked only for the normal context |
fsync_time | Time spent on fsync calls (ms) → requires track_io_timing = on (0 if off) |
stats_reset | Time the statistics were last reset |
In this example, the test01 table had 87 extends, totaling 712,704 bytes (extends * op_bytes = 87 * 8192 = 712,704). The 87 extends took 1.076ms. The actual table size is shown below.
You can see that the size calculated from extends differs from the actual table size by 24,576 bytes (712,704 - 688,128 = 24,576). This difference is the size of the Free Space Map (FSM): the size calculated from extends in pg_stat_io includes the FSM.
In addition, pg_stat_io lets you check the cache hit ratio, which you can use to size shared_buffers. The cache hit ratio is also available through the pg_stat_database catalog, but the key difference with pg_stat_io is that it can be broken down by backend type.
[Security] WITH ADMIN OPTION required to grant roles
Starting with PostgreSQL 16, you cannot grant roles without WITH ADMIN OPTION, even if you have the CREATEROLE attribute. Up to PostgreSQL 15, having CREATEROLE was enough to grant roles.
Related articles
