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/JSON standard functions, changes to the Incremental Sort feature, and the establishment of Custom 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 new libpq 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.
- 10.27.62.66 - Primary
- 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.
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.
Now, attempt to connect to the server and check if it connects to the same host or a different one.
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.
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.
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-writeinstance always.
As you see in the above example, we specified read-write option to the target_session_attrs connection option. To connect to the read only instances, then we have to use the read-only option to target_session_attrs option as like below.
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.
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 in SSL, SCRAM-SHA-256, the SASL authentication is enforced.
In simpler terms, SASL enables 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 during channel_binding with SSL and SCRAM-SHA-256. Now, non-SSL authentications like md5, password, gss, and scram-sha-256 follow a similar approach with the help of require_auth.
To keep this simple, let’s configure pg_hba.conf entry 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.
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 password in require_auth mode from the application side, as shown below.
As shown above, the connection fails due to the password authentication mode. The server only accepts scram-sha-256, adding an extra layer of security to application connections. Here, SASL validates the authentication mode against the entries in pg_hba.conf before allowing the application to send all its data back to the server. The require_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 the pg_hba.conf file. If one of these modes is present in the pg_hba.conf entries, 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 the pg_hba.conf
Please note that if pg_hba.conf uses md5 and the user's password is generated by SCRAM, then the user's SCRAM will be verified instead of md5.
The negate operator 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 disallow password and md5, but support all other authentication methods, require_auth can be set as !password,!md5.
In summary, the require_auth connection 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 the EXPLAIN plan. 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 the EXPLAIN command.
Consider the example below, where GENERIC_PLAN is used to obtain the execution plan of a SQL query.
In the example above, we obtained the index plan of the submitted SQL query. Here, an index scan is performed on the pg_class table as to retrieve the relname records.
Without GENERIC_PLAN, we would be getting error message like below as the placeholder value is required to generate the plan.
This option is useful for debugging SQL queries, which are logged into the database logs. Here, we only see queries with placeholders like $1, $2 instead of the actual values. This option also applies to SQL queries without query placeholders. For instance, consider the usecase below.
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.
As seen in the above output, we have created a logical replication slot on the replica node. To enable this logical replication, ensure that the wal_level on the replica node is set to logical.
Next, start the pg_recvlogical process to retrieve these streams from the replica node, as shown below.
Then, go to the primary node, create a test table, and perform some insert operations into this table.
Finally, observe the pg_recvlogical process, which retrieves these values via the logical replication slot.
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 the replication slot we created is inactive or its subscriber is inactive, the replica node will retain all necessary WAL files in the pg_wal directory. The primary will not have this information and will not maintain these WAL data.
Another added feature is that SUBSCRIBERS can now utilize parallel workers. This set of parallel workers is designed to consume data from PUBLISHERS, significantly improving the handling of large data streams from PUBLISHER nodes.
New I/O catalog - pg_stat_io
In PostgreSQL, there are several dedicated worker processes. For instance, autovacuum worker is a process that logically reclaims disk space from underlying tables. Another process, checkpointer worker, flushes dirty buffers from memory to disk. The background worker process manages shared_buffers by 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 the checkpointer worker is causing heavy writes into the system, we may decide to adjust this setting to conduct longer checkpoint operations. 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 simple pgbench test and observe the IOPS footprint on the system. We can use the pg_stat_io catalog table to see how each backend behaves.
Start by restarting the database to flush all the shared_buffers and then run the pgbench command.
Before running the benchmark, reset all the previous IO related statuses using the SQL query below.
Let's confirm that all the read and write counters are set to 0 before we run the benchmark.
Run the benchmark with the following command:
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.
As you can see, the test ran for 100 seconds and produced a total of 2476 reads and 580 write operations. This totals to 3056 IOPS for 100 seconds, which means we produced 30 IOPS on this system with 20 concurrent users. Please note that these are approximate calculations.
The pg_stat_io view only calculates the IO actions performed on the database files. It does not account for the IO used when writing the temp files. Hence, consider this IOPS value as an approximate rather than a complete IOPS value.
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 used gzipcompression algorithm.
There are a few new roles introduced as well:
pg_create_subscriptions- Users or groups with this permission can now create subscriptions in the database.
pg_use_reserved_connections- Users or groups with this privilege can now utilize connections defined in the
reserved_connectionsnumber of connections.
In VACUUM, there are three additional flags that manage the updating of database statistics about the oldest unfrozen xids. The SKIP_DATABASE_STATS and ONLY_DATABASE_STATS flags allow you to control when these statistics are updated by the VACUUM job. The BUFFER_USAGE_LIMIT flag lets you allocate a portion of shared_buffers for the VACUUM job.
In summary, PostgreSQL 16 introduces several new features and improvements. The load_balance_hosts connection option allows applications to connect to multiple target nodes randomly, improving load balancing. The require_authoption enhances security by enabling SASL for non SSL, SCRAM-SHA-256 authentication modes. The GENERIC_PLANoption in the EXPLAIN plan 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 table pg_stat_io tracks 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 in VACUUM for managing database statistics.
