PostgreSQL 16, What's New - Part 2

In the first part of our series on the features of PostgreSQL 16, we delved into a detailed discussion about the significant upgrades that have been made to PostgreSQL. These improvements included the implementation of Effective Parallel Joins, the introduction of SQL/JSONstandard functions, changes to the Incremental Sortfeature, and the establishment ofCustom Collation Rules. These enhancements have significantly boosted the performance and functionality of PostgreSQL, solidifying its place as a leading choice for database management. This blog post serves as a continuation to the previous discussion, and in it, we will be exploring additional major improvements that have been integrated into the PostgreSQL 16 release.

PostgreSQL 16 introduces a newlibpq connection option,load_balance_hosts. This feature is particularly useful when an application needs to connect to multiple target nodes randomly to execute SQL queries. For instance, consider running a series of SQL queries on a group of read replicas synchronized with the primary node. This approach will yield better results, as it prevents all application queries from hitting the same replica or a limited set of servers. We can also balance the report-related SQL queries by using one primary and one replica node, instead of always relying on the reporting server to produce the data.

Consider below example, where we are trying make a random connections to the primary, replica node by using the load_balance_hosts connection option.

Below are the server details.

1. 10.27.62.66 - Primary

2. 10.27.62.150 - Replica

Now, let's randomly establish a connection to these servers using the load_balance_hosts connection option. We'll use the client psql to do this. Alternatively, we can use the latest JDBC or Python drivers to connect to the specified hosts.

Set the environment variables that psql recognises.

[postgres@ip-10-27-62-66 ~]$ export PGDATABASE=db1
[postgres@ip-10-27-62-66 ~]$ export PGHOSTS=10.27.62.150,10.27.62.66
[postgres@ip-10-27-62-66 ~]$ export PGUSER=master
[postgres@ip-10-27-62-66 ~]$ export PGPASSWORD=Tessell123

Now, let's establish a connection to one of the PGHOSTS using the load_balance_hosts connection option. Try executing the query below with psql which connects to postgres and prints the server IP address. Note that the connection is made randomly to the server.

 
[postgres@ip-10-27-62-66 ~]$ psql -h $PGHOSTS -c "select inet_server_addr()" 'load_balance_hosts=random'
inet_server_addr
------------------
10.27.62.66
(1 row)

Now, attempt to connect to the server and check if it connects to the same host or a different one.
 
[postgres@ip-10-27-62-66 ~]$ psql -h $PGHOSTS -c "select inet_server_addr()" 'load_balance_hosts=random'
inet_server_addr
------------------
10.27.62.150
(1 row)

As you see above, the client made a connection to the different instance and it gave a different ip address.

Now, try by shutting down one of the pg instance and see how the connections are making to the servers.
 
[postgres@ip-10-27-62-66 ~]$ pg_ctl -D /pgsql/data/data/ stop -mf
waiting for server to shut down.... done
server stopped

[postgres@ip-10-27-62-66 ~]$ psql -h $PGHOSTS -c "select inet_server_addr()" 'load_balance_hosts=random'
inet_server_addr
------------------
10.27.62.150
(1 row)

[postgres@ip-10-27-62-66 ~]$ psql -h $PGHOSTS -c "select inet_server_addr()" 'load_balance_hosts=random'
inet_server_addr
------------------
10.27.62.150
(1 row)

As you can see, even after the primary node is shut down, psql can still route its request to the next available Postgres instance. The above demonstration provides a basic understanding of the load_balance_hosts operation. We can apply this example to real-world applications where we have a group of read-write and read-only instances.

Consider a scenario with multiple logically sharded PG instances. Some servers handle both read and write requests, while others only handle read requests. If two services are constantly communicating with these two groups of instances, you can use the target_session_attrs connection option. Used with the load_balance_hosts option, it always directs the connection to the correct group of nodes.
PGHOSTS=ReadWrite1, ReadWrite2, ReadWrite3, ReadOnly1, ReadOnly2, ReadOnly3
PGDATABASE=db1
PGUSER=master
PGPASSWORD=Tessell123

Now, we can configure one service to randomly communicate with an available read-write instance by specifying target_session_attrs=read-write. Similarly, we can configure another service to consistently connect with an available read-only instance by specifying target_session_attrs=read-only.

For example, consider below psql connection, where we always connect to a randomly available read-write instance always.
 
[postgres@ip-10-27-62-66 ~]$ psql -h $PGHOSTS -c "select inet_server_addr()" 'load_balance_hosts=random target_session_attrs=read-write'
inet_server_addr
------------------
10.27.62.66
(1 row)

As you see in the above example, we specifiedread-writeoption to the target_session_attrs connection option. To connect to the read only instances, then we have to use theread-onlyoption to target_session_attrs option as like below.
 
[postgres@ip-10-27-62-66 ~]$ psql -h $PGHOSTS -c "select inet_server_addr()" 'load_balance_hosts=random target_session_attrs=read-only'
inet_server_addr
------------------
10.27.62.150
(1 row)

If there are no available read-write or read-only servers to establish a connection, the client will throw the following error message. This error occurs, for example, when all read-only instances are shut down and a database connection is attempted.
 
[postgres@ip-10-27-62-66 ~]$ psql -h $PGHOSTS -c "select inet_server_addr()" 'load_balance_hosts=random target_session_attrs=read-write'
psql: error: connection to server at "10.27.62.66", port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "10.27.62.150", port 5432 failed: session is read-only

In conclusion, the newly introduced load_balance_hosts connection option, when used with target_session_attrs, effectively routes connections to a target group of servers. This allows the application to direct database connections to target servers randomly, eliminating the need for a middle proxy. This impressive feature, provided by PostgreSQL, enables JDBC apps to randomly route their requests to a cluster of instances.

Require auth

In PostgreSQL 16, a new client connection option,require_auth, has been introduced. This option allows for enabling SASL (Simple Authentication Secure Layer) for non SSL,SCRAM-SHA-256 authentication modes. If channel_binding is used inSSL,SCRAM-SHA-256, the SASL authentication is enforced.

In simpler terms,SASLenables the server and client to reach a common agreement. The server first approves the type of authentication sent by the client before exchanging authorization details, such as the password. This usually happens duringchannel_bindingwithSSLandSCRAM-SHA-256. Now, non-SSL authentications like md5, password, gss, and scram-sha-256 follow a similar approach with the help ofrequire_auth.

To keep this simple, let’s configurepg_hba.confentry as to allow all the connections from all the users has to be authenticated by scram-sha-256 password. Below is the entry, which we configure in the database systems.  
 
host    all             all             0.0.0.0/0              scram-sha-256

If an application tries to connect using the password authentication mode (plain text), the server should reject it. This is because the server is configured to accept connections that authenticate solely by scram-sha-256, not by any other authentication modes. Now, attempt to connect to this server using the passwordinrequire_auth mode from the application side, as shown below.
 
[postgres@ip-10-27-62-66 data]$ psql -h 10.27.62.66 -c "SELECT 1"  'dbname=postgres user=master require_auth=password'
psql: error: connection to server at "10.27.62.66", port 5432 failed: authentication method requirement "password" failed: server requested SASL authentication

As shown above, the connection fails due to thepasswordauthentication mode. The server only accepts scram-sha-256, adding an extra layer of security to application connections. Here,SASLvalidates the authentication mode against the entries inpg_hba.confbefore allowing the application to send all its data back to the server. Therequire_authconnection option triggers authentication mode validation in the PostgreSQL server, and only the server sends a response back to the client. This ensures that the server validates client authentication, preventing potential client request hijacking.

The client connection can specify a list of authentication modes to be evaluated from thepg_hba.conffile. If one of these modes is present in thepg_hba.confentries, the server will send a SASL acceptance. For example, in the below case, we listed the list of authentications which server has to validate against thepg_hba.conf
[postgres@ip-10-27-62-66 data]$ psql -h 10.27.62.66 -c "select inet_server_addr()"  'dbname=postgres user=master require_auth=scram-sha-256,md5'
Password for user master:
inet_server_addr
------------------
10.27.62.66
(1 row)

Please note that ifpg_hba.confusesmd5and the user's password is generated by SCRAM, then the user'sSCRAMwill be verified instead ofmd5.

Thenegateoperator can be used to deny specific authentication modes, allowing only those that the server is configured to accept. For instance, if a user is set to disallowpasswordandmd5, but support all other authentication methods,require_authcan be set as!password,!md5.
 
[postgres@ip-10-27-62-66 data]$ psql -h 10.27.62.66 -c "select inet_server_addr()"  'dbname=postgres user=master require_auth=!password,!md5'
Password for user master:
inet_server_addr
------------------
10.27.62.66
(1 row)

In summary, therequire_authconnection significantly enhances the security of application connections by verifying the authentication mode with the server.

Explain generic

In PostgreSQL 16, a new option,GENERIC_PLAN, is added to theEXPLAINplan. This allows us to generate execution plans for queries that include parameter placeholders like$1,$2. Previously, generating the execution plan for such queries was a bit cumbersome, requiring us to replace all query placeholders with actual values before using theEXPLAINcommand.

Consider the example below, whereGENERIC_PLANis used to obtain the execution plan of a SQL query.
 
postgres=# EXPLAIN (GENERIC_PLAN ON) SELECT * FROM pg_class WHERE relname=$1;
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.27..8.29 rows=1 width=273)
  Index Cond: (relname = $1)
(2 rows)

In the example above, we obtained the index plan of the submitted SQL query. Here, an index scan is performed on thepg_classtable as to retrieve therelnamerecords.

Without GENERIC_PLAN, we would be getting error message like below as the placeholder value is required to generate the plan.
postgres=# EXPLAIN (GENERIC_PLAN OFF) SELECT * FROM pg_class WHERE relname=$1;
ERROR:  there is no parameter $1
LINE 1: ... (GENERIC_PLAN OFF) SELECT * FROM pg_class WHERE relname=$1;

This option is useful for debugging SQL queries, which are logged into the database logs. Here, we only see queries with placeholders like$1,$2instead of the actual values. This option also applies to SQL queries without query placeholders. For instance, consider the usecase below.

postgres=# EXPLAIN (GENERIC_PLAN ON) SELECT * FROM pg_class WHERE relname='test';
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.27..8.29 rows=1 width=273)
  Index Cond: (relname = 'test'::name)
(2 rows)

Logical replication - replica

PostgreSQL 16 introduces a remarkable feature allowing users to obtain the logical stream of changes from the replica node. In other words, the physical replica node can stream logical changes to the downstream using logical replication slots. This is not to be confused with cascade replication, where one replica transfers its changes, received from the primary node, to another replica. In this instance, we are streaming the logical changes via the logical replication slots.

This feature allows us to create a PUBLICATION on the primary node and subscribe to the replica to receive these changes. Alternatively, we can also create a dedicated logical replication slot, usually only found on the primary node, on the replica node. This feature can help offload work from the primary node, which is responsible for processing and sending the stream of changes to subscribers.

Consider below example, where we create the logical replication slot on replica node, and subscriber to it.
 
postgres=# SELECT pg_create_logical_replication_slot('logical_replica', 'test_decoding');
pg_create_logical_replication_slot
------------------------------------
(logical_replica,5/8B0001B0)
(1 row)

postgres=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

postgres=# SHOW wal_level;
wal_level
-----------
logical
(1 row)

As seen in the above output, we have created a logical replication slot on the replica node. To enable this logical replication, ensure that thewal_levelon the replica node is set tological.

Next, start thepg_recvlogicalprocess to retrieve these streams from the replica node, as shown below.

[postgres@ip-10-27-62-150 ~]$ pg_recvlogical -h -d postgres -S logical_replica -f - --start

Then, go to the primary node, create atesttable, and perform someinsertoperations into this table.
 
postgres=# CREATE TABLE test(t INT);
CREATE TABLE
postgres=# INSERT INTO test VALUES(2);
INSERT 0 1
postgres=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)

Finally, observe thepg_recvlogicalprocess, which retrieves these values via thelogicalreplication slot.
 
[postgres@ip-10-27-62-150 ~]$ pg_recvlogical -h -d postgres -S logical_replica -f - --no-loop --start
BEGIN 1936
COMMIT 1936
BEGIN 1937
table public.test: INSERT: t[integer]:2
COMMIT 1937

As demonstrated above, we can fetch the stream of changes that occurred on the primary.

However, the replication slot is actually created on the replica. Please note, if thereplication slotwe created is inactive or its subscriber is inactive, the replica node will retain all necessary WAL files in thepg_waldirectory. Theprimarywill not have this information and will not maintain these WAL data.

Another added feature is thatSUBSCRIBERScan now utilize parallel workers. This set of parallel workers is designed to consume data fromPUBLISHERS, significantly improving the handling of large data streams fromPUBLISHERnodes.

New I/O catalog - pg_stat_io

In PostgreSQL, there are several dedicated worker processes. For instance,autovacuum workeris a process that logically reclaims disk space from underlying tables. Another process,checkpointer worker, flushes dirty buffers from memory to disk. Thebackground workerprocess managesshared_buffersby routinely cleaning up memory. This includes flushing dirty buffers and determining which buffers need to be cleared for memory reuse.

The role of these worker processes is to maintain the stability of the Database system. They achieve this by storing data in memory and sometimes on persistent disks. All these workers will be busy, either reading from the disk or writing into it, depending on the database request. Essentially, what all these workers are doing is I/O. That is, read data from disk or write data into the disk.

In PostgreSQL 16, there's a new catalog table,pg_stat_io, that tracks all the I/O requests made by each worker process. This is extremely useful for analyzing which backend worker is generating more I/O in the system, allowing for more informed decision-making based on trends.

For instance, if thecheckpointer workeris causing heavy writes into the system, we may decide to adjust this setting to conduct longercheckpointoperations. This I/O tracking is particularly beneficial in cloud environments, where services are billed based on IOPS. We can use it to determine which backend is generating more IOPS in the system.

Let's run a simplepgbenchtest and observe theIOPSfootprint on the system. We can use thepg_stat_iocatalog table to see how each backend behaves.

Start by restarting the database to flush all theshared_buffersand then run thepgbenchcommand.
 
[postgres@ip-10-27-62-66 ~]$ pg_ctl -D /pgsql/data/data/ restart
waiting for server to shut down...... done
server stopped
waiting for server to start....2024-02-15 15:43:10.325 GMT [283792] LOG:  redirecting log output to logging collector process
2024-02-15 15:43:10.325 GMT [283792] HINT:  Future log output will appear in directory "log".
done
server started

Before running the benchmark, reset all the previousIOrelated statuses using the SQL query below.
postgres=# SELECT pg_stat_reset_shared('io');
pg_stat_reset_shared
----------------------

(1 row)

Let's confirm that all the read and write counters are set to0before we run the benchmark.
 
postgres=# SELECT SUM(reads) as reads, SUM(writes+extends) as writes FROM pg_stat_io;
reads | writes
-------+--------
    0 |      0
(1 row)

Run the benchmark with the following command:
 
[postgres@ip-10-27-62-66 ~]$ pgbench -c 20 -T 100
pgbench (16.1)
starting vacuum...end.
transaction type:
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
duration: 100 s
number of transactions actually processed: 81992
number of failed transactions: 0 (0.000%)
latency average = 24.399 ms
initial connection time = 44.747 ms
tps = 819.708802 (without initial connection time)

After the test completes, let's see what kind of I/O PostgreSQL performed on this database system. Use the same SQL query above to calculate the IOPS in the database system.
 
[postgres@ip-10-27-62-66 ~]$ psql
psql (16.1)
Type "help" for help.

postgres=# SELECT SUM(reads) as reads, SUM(writes+extends) as writes FROM pg_stat_io;
reads | writes
-------+--------
 2476 |    580
(1 row)

As you can see, the test ran for100seconds and produced a total of2476reads and580write operations. This totals to3056IOPS for 100 seconds, which means we produced30 IOPSon this system with20concurrent users. Please note that these are approximate calculations.

Thepg_stat_ioview only calculates theIOactions performed on the database files. It does not account for theIOused when writing thetempfiles. Hence, consider thisIOPSvalue as an approximate rather than a completeIOPSvalue.

Apart from the above major features what we discussed, there are other major improvements in the logical dumps (pg_dump), where PostgreSQL started supporting the ZSTD, LZ4 compression algorithms for taking the logical dumps. This significantly improves the compression and the logical dump time when compared to the default widely usedgzipcompression algorithm.

There are a few new roles introduced as well:

1. pg_create_subscriptions

2. Users or groups with this permission can now create subscriptions in the database.

3. pg_use_reserved_connections

4. Users or groups with this privilege can now utilize connections defined in thereserved_connectionsnumber of connections.

InVACUUM, there are three additional flags that manage the updating of database statistics about the oldest unfrozen xids. TheSKIP_DATABASE_STATSandONLY_DATABASE_STATSflags allow you to control when these statistics are updated by theVACUUMjob. TheBUFFER_USAGE_LIMITflag lets you allocate a portion ofshared_buffersfor theVACUUMjob.

In summary, PostgreSQL 16 introduces several new features and improvements. Theload_balance_hostsconnection option allows applications to connect to multiple target nodes randomly, improving load balancing. Therequire_authoption enhances security by enabling SASL for non SSL, SCRAM-SHA-256 authentication modes. TheGENERIC_PLANoption in theEXPLAINplan allows for generating execution plans for queries with parameter placeholders. Logical replication can now be obtained from the replica node, offloading work from the primary node. The new catalog tablepg_stat_iotracks all I/O requests made by each worker process, aiding in performance analysis. Other improvements include support for ZSTD, LZ4 compression algorithms in logical dumps, new roles for creating subscriptions and using reserved connections, and additional flags inVACUUMfor managing database statistics.