Introduction:
In the ever-evolving world of relational databases, PostgreSQL continues to shine as a beacon of innovation and reliability. With each new release, it unveils a treasure trove of features that cater to the needs of modern applications and enterprises. PostgreSQL 16, the latest installment in this remarkable journey, promises to be no different. Packed with enhancements and capabilities, it sets the stage for a more powerful, efficient, and user-friendly database management experience. In this blog post, we embark on a thrilling journey to explore some of the most exciting features that PostgreSQL 16 brings to the table. From performance improvements to advanced data processing, and everything in between, join us as we unveil the future of PostgreSQL.
We’ll be doing a series of blogs around the super cool features introduced in PG16 and some of the features enhanced in PG16.
To start with, one of the most important ones in our view is the ability to perform logical replication from the standby node, which we discuss below.
Logical replication from a physical standby instance
At a broad level, PostgreSQL offers support for two primary types of replication: streaming/physical replication and logical replication. Streaming/physical replication involves transmitting the entire collection of physical files over a connection, effectively replicating the entire on-disk database. In contrast, logical replication provides a more granular approach, allowing you to specify specific database objects, be it tables or even individual rows, for replication to a remote server.
One notable benefit of logical replication is its capability to subscribe to changes from PostgreSQL servers across various versions! This flexibility proves invaluable when dealing with applications that employ different versions of PostgreSQL. Now, we can setup a Logical replica from the readonly Physical standby PostgreSQL instance seamlessly. This operation would previously fail before Postgres 16 (screenshot below).
Data can be filtered based on origin during replication.
In primary:
CREATE PUBLICATION mypub FOR ALL TABLES;In Standby:
Perform the operation on standby if the server running on pg 15 you will get the below error:
Perform the operation on pg 16:
osdb=# CREATE SUBSCRIPTION mysub_standby
CONNECTION 'host=192.168.113.209 port=5432 user=repuser password=postgres dbname=osdb'
PUBLICATION mypub WITH (origin = none);
NOTICE: created replication slot "mysub_standby" on publisher
CREATE SUBSCRIPTION
Postgres 16, setting a bi-directional or logical replication among nodes was difficult, because if we set up replication for a table, it would lead to an infinite loop. By adding the ability to filter data based on origin, we can set up n-way logical replication, and that will prevent loops when performing bi-directional replication.
Logical replication parallel apply of transactions:
Large transactions can be applied in parallel.
osdb=# CREATE SUBSCRIPTION mysub_streming_parallel
CONNECTION 'host=192.168.113.209 port=5432 user=repuser password=postgres dbname=osdb'
PUBLICATION mypub WITH (streaming = parallel);
NOTICE: created replication slot "mysub_streming_parallel" on publisher
CREATE SUBSCRIPTION
Performance improvement in the range of 25-40% has been observed.
Each large transaction is assigned to one of the available workers, which improves lag by immediately applying instead of waiting till the whole transaction is received by the subscriber. The worker remains assigned until the transaction is completed.
max_parallel_apply_workers_per_subscription sets the maximum number of parallel apply workers per subscription.
osdb=# show max_parallel_apply_workers_per_subscription ;
max_parallel_apply_workers_per_subscription
*** *** *** *** *** *** *** *** *** *** ***
The apply process can be configured to perform operations with the table owner’s privileges instead of the subscription owner’s privileges.
Perform the operation on pg 16:
CREATE SUBSCRIPTION mysub_standby_2
CONNECTION 'host=192.168.113.209 port=5432 user=repuser dbname=osdb'
PUBLICATION mypub WITH (run_as_owner = false);
CREATE SUBSCRIPTION
Non-superusers can create subscriptions.
The non-superusers must have been granted a pg_create_subscription role, and are required to specify a password for authentication.
Superusers can set password_required = false for non-superusers who own the subscription.
A monitoring boon – pg_stat_io
The “pg_stat_io” is a system view in PostgreSQL that provides statistics about input and output (I/O) operations at the database level. It can be used to monitor and analyze the I/O activity of your PostgreSQL database, which can be valuable for performance tuning and troubleshooting.
PostgreSQL 16 records statistics on the last sequential and index scans on tables, adds speculative lock information to the pg_locks view and makes several improvements to wait events that make monitoring of PostgreSQL more comprehensive than ever.
New pg_stat_io view: Offering comprehensive I/O statistics for troubleshooting performance issues and database optimization. With this view in place, we’ll be able to:
- Monitoring system-wide buffer cache hit ratio calculation precisely
- Accumulating system-wide I/O times, providing a comprehensive view beyond the existing I/O counts in pg_stat_io.
- Enhancing cumulative WAL statistics, surpassing the capabilities of pg_stat_wal.
- Expanding I/O tracking to include tables and indexes, offering a more comprehensive overview.
Here is an example of the statistics you can see in pg_stat_io as following:
As we continue this exploration of PostgreSQL 16’s exciting new features, we’ve only looked at the Logical replication features in this blog post. From performance enhancements to improved data management and security features, PostgreSQL 16 is set to empower developers, administrators, and businesses alike. In our next blog post, we’ll dive deeper into specific use cases and practical examples to showcase how these features can transform your database operations. Stay tuned for more insights into the world of PostgreSQL 16!
Leave a Reply