What’s New in PostgreSQl16<\/h2>\n\n- \n
Logical Replication Enhancements<\/p>\n<\/li>\n
- \n
Performance Improvements<\/p>\n<\/li>\n
- \n
I\/O Statistics Monitoring pg_stat_io<\/code><\/p>\n<\/li>\n- \n
Authentication and Privileges Changes<\/p>\n<\/li>\n
- \n
Parallelization of Joins<\/p>\n<\/li>\n
- \n
SQL\/JSON Features<\/p>\n<\/li>\n
- \n
Data Type and Functionality Additions<\/p>\n<\/li>\n
- \n
Libpq Enhancements<\/p>\n<\/li>\n<\/ol>\n
Logical Replication<\/h3>\n
In this series covering the new features of postgresql16, we will be addressing refinements made to Logical Replication.<\/p>\n
\n- Logical replication publishers can be created from standby instances. user can also set a Logical replica from the read-only Physical standby this required
wal_level = logical<\/code> on both primary and standby.![\"\"](\"https:\/\/divaind.com\/ie1\/wp-content\/uploads\/2023\/10\/0eebf801-7598-4573-aed8-878ae0f5142d-300x98.png\")
PostgreSQL 16 introduced a new parameter, <\/strong>origin<\/code>for the CREATE SUBSCRIPTION<\/code> command. This parameter controls whether the subscription will only receive changes that do not have an associated origin, or whether it will receive all changes, regardless of origin.<\/li>\n<\/ul>\nIn other words,<\/h4>\n
the origin<\/code> parameter specifies how selective the subscription is about the changes it receives. A value origin = NONE<\/code> means that the subscription will receive all changes, regardless of origin. A value of origin = ANY<\/code> means that the subscription will only receive changes that do not have an associated origin.<\/p>\nLet’s take an example, NODE1 is primary and NODE2 is logical standby, if there are two other standbys for NODE2 – NODE3, NODE4.
\nNODE3 has the origin value ANY, so it will apply all changes in WAL logs it received from NODE2 which can have changes of NODE1, and\u00a0 NODE2.
\nNODE4 has an origin value of NONE, so it applies only to changes that are generated on the NODE2.<\/p>\n
Standard SQL<\/p>\n
CREATE PUBLICATION pub1 FOR ALL TABLES; \nCREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (origin = none);<\/pre>\nExample:-<\/h4>\n-bash-4.2$ psql -p 5432 -d test_pg_11 \npsql (16.0, server 11.21) \nType \"help\" for help.\n\ntest_pg_11=# CREATE PUBLICATION pub1 FOR ALL TABLES; \nCREATE PUBLICATION \n\n-bash-4.2$ psql -p 5440 -d test_pg_16 \npsql (16.0) \nType \"help\" for help. \n\ntest_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (origin = none); \nNOTICE: created replication slot \"sub1\" on publisher \nCREATE SUBSCRIPTION<\/pre>\n
\n- Initial table synchronization in binary format
\nThis option specifies whether the subscription requests data in binary format from the publisher (default is false). The binary format can be faster but less portable. It’s important to note that only data types with binary send and receive functions will be transferred in binary.<\/li>\n<\/ul>\nCREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (binary = true);<\/pre>\nExample:-<\/h4>\ntest_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (binary = true);\nNOTICE: created replication slot \"sub1\" on publisher\nCREATE SUBSCRIPTION<\/pre>\n
\n- Now a logical replication subscriber can use indexes other than the
PRIMARY KEY<\/code> to perform lookups during UPDATE<\/code> or DELETE<\/code> operations, Employing REPLICA IDENTITY FULL on the publisher may result in a comprehensive table scan for each tuple alteration on the subscriber, especially in cases where REPLICA IDENTITY or a primary key index is absent.<\/li>\n- Subscribers can now apply large transactions using parallel workers<\/li>\n<\/ul>\n
This setting streaming<\/code> controls how transactions are sent to subscribers. The value “on” stores changes temporary files, applying them after the transaction is committed. “Parallel” applies changes directly, using parallel workers if available, otherwise, they’re stored temporarily and applied after the transaction is committed. Note, that errors in parallel may not report the finish LSN in the server log.<\/p>\nCREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (streaming = parallel);<\/pre>\nExample:-<\/h4>\ntest_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (streaming = parallel);\nNOTICE: created replication slot \"sub1\" on publisher\nCREATE SUBSCRIPTION<\/pre>\nHaving delved into the Logical Replication enhancements in PostgreSQL 16 thus far, our journey through its exciting new features continues. In the upcoming blog post, we will delve into Performance Improvements and I\/O Statistics Monitoring. Keep an eye out for further insights into the evolving landscape of PostgreSQL 16<\/p>\n
For the latest feaures in postgresql 18 follow<\/a> our blog “New Features added in postgresql18<\/a>“.<\/p><\/blockquote>\n<\/div>\n","protected":false},"excerpt":{"rendered":"A Glimpse into the Future: Series of What’s New in PostgreSQL16 ? On September 14, 2023, the PostgreSQL Global Development Group introduced PostgreSQL16, marking the latest release of the renowned open-source database. This version brings significant enhancements in performance, particularly in query parallelism, bulk data loading, and logical replication, including expanded SQL\/JSON syntax, additional monitoring…<\/p>\n","protected":false},"author":1,"featured_media":2998,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[49,192,262,265,266,305],"class_list":["post-2581","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-postgresql16","tag-logical","tag-postgres","tag-postgres16","tag-postgresql","tag-replication","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2581","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2581"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2581\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2998"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Logical Replication Enhancements<\/p>\n<\/li>\n
Performance Improvements<\/p>\n<\/li>\n
I\/O Statistics Monitoring Authentication and Privileges Changes<\/p>\n<\/li>\n Parallelization of Joins<\/p>\n<\/li>\n SQL\/JSON Features<\/p>\n<\/li>\n Data Type and Functionality Additions<\/p>\n<\/li>\n Libpq Enhancements<\/p>\n<\/li>\n<\/ol>\n In this series covering the new features of postgresql16, we will be addressing refinements made to Logical Replication.<\/p>\n the Let’s take an example, NODE1 is primary and NODE2 is logical standby, if there are two other standbys for NODE2 – NODE3, NODE4. Standard SQL<\/p>\n This setting Having delved into the Logical Replication enhancements in PostgreSQL 16 thus far, our journey through its exciting new features continues. In the upcoming blog post, we will delve into Performance Improvements and I\/O Statistics Monitoring. Keep an eye out for further insights into the evolving landscape of PostgreSQL 16<\/p>\n For the latest feaures in postgresql 18 follow<\/a> our blog “New Features added in postgresql18<\/a>“.<\/p><\/blockquote>\n A Glimpse into the Future: Series of What’s New in PostgreSQL16 ? On September 14, 2023, the PostgreSQL Global Development Group introduced PostgreSQL16, marking the latest release of the renowned open-source database. This version brings significant enhancements in performance, particularly in query parallelism, bulk data loading, and logical replication, including expanded SQL\/JSON syntax, additional monitoring…<\/p>\n","protected":false},"author":1,"featured_media":2998,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[49,192,262,265,266,305],"class_list":["post-2581","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-postgresql16","tag-logical","tag-postgres","tag-postgres16","tag-postgresql","tag-replication","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2581","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2581"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2581\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2998"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}pg_stat_io<\/code><\/p>\n<\/li>\nLogical Replication<\/h3>\n
\n
wal_level = logical<\/code> on both primary and standby.PostgreSQL 16 introduced a new parameter, <\/strong>origin<\/code>for the CREATE SUBSCRIPTION<\/code> command. This parameter controls whether the subscription will only receive changes that do not have an associated origin, or whether it will receive all changes, regardless of origin.<\/li>\n<\/ul>\nIn other words,<\/h4>\n
origin<\/code> parameter specifies how selective the subscription is about the changes it receives. A value origin = NONE<\/code> means that the subscription will receive all changes, regardless of origin. A value of origin = ANY<\/code> means that the subscription will only receive changes that do not have an associated origin.<\/p>\n
\nNODE3 has the origin value ANY, so it will apply all changes in WAL logs it received from NODE2 which can have changes of NODE1, and\u00a0 NODE2.
\nNODE4 has an origin value of NONE, so it applies only to changes that are generated on the NODE2.<\/p>\nCREATE PUBLICATION pub1 FOR ALL TABLES; \nCREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (origin = none);<\/pre>\n
Example:-<\/h4>\n
-bash-4.2$ psql -p 5432 -d test_pg_11 \npsql (16.0, server 11.21) \nType \"help\" for help.\n\ntest_pg_11=# CREATE PUBLICATION pub1 FOR ALL TABLES; \nCREATE PUBLICATION \n\n-bash-4.2$ psql -p 5440 -d test_pg_16 \npsql (16.0) \nType \"help\" for help. \n\ntest_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (origin = none); \nNOTICE: created replication slot \"sub1\" on publisher \nCREATE SUBSCRIPTION<\/pre>\n
\n
\nThis option specifies whether the subscription requests data in binary format from the publisher (default is false). The binary format can be faster but less portable. It’s important to note that only data types with binary send and receive functions will be transferred in binary.<\/li>\n<\/ul>\nCREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (binary = true);<\/pre>\n
Example:-<\/h4>\n
test_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (binary = true);\nNOTICE: created replication slot \"sub1\" on publisher\nCREATE SUBSCRIPTION<\/pre>\n
\n
PRIMARY KEY<\/code> to perform lookups during UPDATE<\/code> or DELETE<\/code> operations, Employing REPLICA IDENTITY FULL on the publisher may result in a comprehensive table scan for each tuple alteration on the subscriber, especially in cases where REPLICA IDENTITY or a primary key index is absent.<\/li>\nstreaming<\/code> controls how transactions are sent to subscribers. The value “on” stores changes temporary files, applying them after the transaction is committed. “Parallel” applies changes directly, using parallel workers if available, otherwise, they’re stored temporarily and applied after the transaction is committed. Note, that errors in parallel may not report the finish LSN in the server log.<\/p>\nCREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (streaming = parallel);<\/pre>\n
Example:-<\/h4>\n
test_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (streaming = parallel);\nNOTICE: created replication slot \"sub1\" on publisher\nCREATE SUBSCRIPTION<\/pre>\n