Horton

Re: First draft of the PG 15 release notes

Euler Taveira <euler@eulerto.com>

View thread View on postgr.es
From: "Euler Taveira" <euler@eulerto.com>
To: "Bruce Momjian" <bruce@momjian.us>
Cc: "Ajin Cherian" <itsajin@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-05-13T00:31:20Z
Lists: pgsql-hackers 
On Thu, May 12, 2022, at 11:22 AM, Bruce Momjian wrote:
> On Thu, May 12, 2022 at 11:12:54AM -0300, Euler Taveira wrote:
> OB> On Thu, May 12, 2022, at 11:03 AM, Bruce Momjian wrote:
> > 
> >     I looked at that but thought that everyone would already assume we
> >     skipped replication of empty transactions, and I didn't see much impact
> >     for the user, so I didn't include it.
> > 
> > It certainly has an impact on heavy workloads that replicate tables with few
> > modifications. It receives a high traffic of 'begin' and 'commit' messages that
> > the previous Postgres versions have to handle (discard). I would classify it as
> > a performance improvement for logical replication. Don't have a strong opinion
> > if it should be mentioned or not.
> 
> Oh, so your point is that a transaction that only has SELECT would
> previously send an empty transaction?  I thought this was only for apps
> that create literal empty transactions, which seem rare.
No. It should be a write transaction. If you have a replication setup that
publish only table foo (that isn't modified often) and most of your
workload does not contain table foo, Postgres sends 'begin' and 'commit'
messages to subscriber even if there is no change to replicate.

Let me show you an example:

postgres=# CREATE TABLE foo (a integer primary key, b text);
CREATE TABLE
postgres=# CREATE TABLE bar (c integer primary key, d text);
CREATE TABLE
postgres=# CREATE TABLE baz (e integer primary key, f text);
CREATE TABLE
postgres=# CREATE PUBLICATION pubfoo FOR TABLE foo;
CREATE PUBLICATION
postgres=# SELECT pg_create_logical_replication_slot('slotfoo', 'pgoutput');
pg_create_logical_replication_slot 
------------------------------------
(slotfoo,0/E709AC50)
(1 row)

Let's create a transaction without table foo:

postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO bar (c, d) VALUES(1, 'blah');
INSERT 0 1
postgres=*# INSERT INTO baz (e, f) VALUES(2, 'xpto');
INSERT 0 1
postgres=*# COMMIT;
COMMIT

As you can see, the replication slot contains messages for that transaction.
Although, table bar and baz are NOT published, the begin (B) and commit (C)
messages that refers to this transaction are sent to subscriber.

postgres=# SELECT chr(get_byte(data, 0)) FROM 
pg_logical_slot_peek_binary_changes('slotfoo', NULL, NULL, 
'proto_version', '1', 'publication_names', 'pubfoo');
chr 
-----
B
C
(2 rows)

If you execute another transaction without table foo, there will be another B/C
pair.

postgres=# DELETE FROM baz WHERE e = 2;
DELETE 1
postgres=# SELECT chr(get_byte(data, 0)) FROM 
pg_logical_slot_peek_binary_changes('slotfoo', NULL, NULL, 
'proto_version', '1', 'publication_names', 'pubfoo');
chr 
-----
B
C
B
C
(4 rows)

Let's create a transaction that uses table foo but also table bar:

postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO foo (a, b) VALUES(100, 'asdf');
INSERT 0 1
postgres=*# INSERT INTO bar (c, d) VALUES(200, 'qwert');
INSERT 0 1
postgres=*# COMMIT;
COMMIT

In this case, there will be other messages since the publication pubfoo
publishes table foo. ('I' means there is an INSERT for table foo).

postgres=# SELECT chr(get_byte(data, 0)), length(data) FROM 
pg_logical_slot_peek_binary_changes('slotfoo', NULL, NULL, 
'proto_version', '1', 'publication_names', 'pubfoo');
chr | length 
-----+--------
B   |     21
C   |     26
B   |     21
C   |     26
B   |     21
R   |     41
I   |     25
C   |     26
(8 rows)


In summary, a logical replication setup sends 47 bytes per skipped transaction.
v15 won't send the first 2 B/C pairs. Discussion started here [1].

[1] https://postgr.es/m/CAMkU=1yohp9-dv48FLoSPrMqYEyyS5ZWkaZGD41RJr10xiNo_Q@mail.gmail.com


--
Euler Taveira
EDB   https://www.enterprisedb.com/

Commits

  1. Doc: last minute adjustment to the release notes

  2. Doc: more tweaking of v15 release notes.

  3. Doc: further adjust notes about pg_upgrade_output.d.

  4. Doc: add list of major features to the v15 release notes.

  5. relnotes: update item about public schema permission change

  6. relnotes: update ordered partition scan item

  7. relnotes: add Heikki to UTF8 item

  8. relnotes: improve UTF8 text item in relation to ASCII

  9. relnotes: add null logical replication item

  10. relnotes: adjust several logical replication items and FK text

  11. relnotes: mention non-exclusive backup mode was deprecated

  12. relnotes: add author to in-memory sorts item

  13. relnotes: update for non-exclusive backup mode removal

  14. relnote: improve sorting entries

  15. relnotes: adjustments from Álvaro Herrera

  16. relnotes: update foreign key partition and add sort items

  17. relnotes: more adjustments

  18. relnotes: logical replication permissions checked by subscrib.

  19. relnotes: adjustments

  20. relnotes: remove sequence replication and update 'postgres -C'

  21. relnote: extensive updates

  22. relnotes: "training" -> "trailing"

  23. Standardize references to Zstandard as <productname>

  24. pgstat: Update docs to match the shared memory stats reality.

  25. Raise a WARNING for missing publications.

  26. Skip empty transactions for logical replication.

  27. Optionally disable subscriptions on error.

  28. Allow root-owned SSL private keys in libpq, not only the backend.

  29. Use COPY FREEZE in pgbench for faster benchmark table population.