Re: Allow logical replication to copy tables in binary format

Hello,

Andres Freund <andres(at)anarazel(dot)de>, 2 Eyl 2022 Cum, 01:25 tarihinde şunu
yazdı:

> It'd be good to collect some performance numbers justifying this. I'd
> expect
> decent gains if there's e.g. a bytea or timestamptz column involved.

Experimented the binary copy with a quick setup.

- Created a "temp" table with bytea and timestamptz columns

> postgres=# \d temp
> Table "public.temp"
> Column | Type | Collation | Nullable | Default
> --------+--------------------------+-----------+----------+---------
> i | integer | | |
> b | bytea | | |
> t | timestamp with time zone | | |
>

- Loaded with ~1GB data

> postgres=# SELECT pg_size_pretty( pg_total_relation_size('temp') );
> pg_size_pretty
> ----------------
> 1137 MB
> (1 row)

- Created a publication with only this "temp" table.
- Created a subscription with binary enabled on instances from master
branch and this patch.
- Timed the tablesync process by calling the following procedure:

> CREATE OR REPLACE PROCEDURE wait_for_rep() LANGUAGE plpgsql AS $$BEGIN
> WHILE (SELECT count(*) != 0 FROM pg_subscription_rel WHERE srsubstate <>
> 'r') LOOP COMMIT; END LOOP; END; $$;

Hera are averaged results of multiple consecutive runs from both master
branch and the patch:

master (binary enabled but no binary copy): 20007.7948 ms
the patch (allows binary copy): 8874,869 ms

Seems like a good improvement.
What are your thoughts on this patch?

Best,
Melih