Re: Allow logical replication to copy tables in binary format
Melih Mutlu <m.melihmutlu@gmail.com>
From: Melih Mutlu <m.melihmutlu@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-09-07T11:51:25Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Allow logical replication to copy tables in binary format.
- ecb696527c01 16.0 landed
-
Common function for percent placeholder replacement
- c96de2ce1782 16.0 cited
-
Doc: add XML ID attributes to <sectN> and <varlistentry> tags.
- 78ee60ed84bb 16.0 cited
-
Re-order disable_on_error in tab-complete.
- d547f7cf5efc 16.0 cited
-
Make subscription tests pass with log_error_verbosity=verbose
- 19408aae7fa2 15.0 cited
-
Weaken type-OID-matching checks in array_recv and record_recv.
- 670c0a1d474b 14.0 cited
-
Allow logical replication to transfer data in binary format.
- 9de77b545313 14.0 cited
Hello,
Andres Freund <andres@anarazel.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