PostgreSQL 14 – In progress transaction in logical replication
Per PostgreSQL 14 release notes “When using logical replication, PostgreSQL can now stream in-progress transactions to subscribers, with significant performance benefits for applying large transactions on subscribers. PostgreSQL 14 also adds several other performance enhancements to the logical decoding system that powers logical replication.”
What is in-progress transactions streaming?
As per the logical replication architecture, the data streamed only when it is committed at primary. When there is a big transaction, it leads to apply-lag.
The in-progress transactions streaming feature streams changes to standby during the transaction thus reduces overhead during commit time.
[postgres@pg14 tmp]$ /usr/local/pgsql/bin/psql
psql (14.0)
Type “help” for help.
postgres=#
How this can be achieved?
The in-progress transactions streaming is achieved by changing subscription mode.
For example,
When in-progress transactions streaming is off
[PRIMARY-PUBLISHER]
When in-progress transactions streaming is enabled
[STANDBY-SUBSCRIBER]
postgres=# ALTER SUBSCRIPTION my_subscription SET(STREAMING = ON);
ALTER SUBSCRIPTION
Here the commit time has reduced to some extent.
PostgreSQL 14 has also added a dictionary view to keep track of the in-progress transactions streaming
when in-progress streaming is not enabled
postgres=# select * from pg_stat_replication_slots ;
-[ RECORD 1 ]+——————————–
slot_name | my_subscription
spill_txns | 1
spill_count | 2
spill_bytes | 71501504
stream_txns | 0
stream_count | 0
stream_bytes | 0
total_txns | 1
total_bytes | 71501504
stats_reset | 2021-09-30 19:38:51.47429+05:30
when in-progress streaming is enabled
postgres=# select * from pg_stat_replication_slots ;
-[ RECORD 1 ]+———————————
slot_name | my_subscription
spill_txns | 0
spill_count | 0
spill_bytes | 0
stream_txns | 1
stream_count | 2
stream_bytes | 71501504
total_txns | 1
total_bytes | 71501504
stats_reset | 2021-09-30 19:40:24.322549+05:30
Answers to below questions with a case study is updated soon.
- what is stream_bytes
- What is logical decoding system?
- What is output plugin?
- When is the replication slot is created in logical replication?
- Can I create a manual replication slot?
- How actually logical replication works?
Reference to this article can be found here.
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
