Horton

Re: row filtering for logical replication

Peter Smith <smithpb2250@gmail.com>

View thread
From: Peter Smith <smithpb2250@gmail.com>
To: Ajin Cherian <itsajin@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Amit Kapila <amit.kapila16@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Euler Taveira <euler@eulerto.com>, Greg Nancarrow <gregn4422@gmail.com>, vignesh C <vignesh21@gmail.com>, "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>, Dilip Kumar <dilipbalaut@gmail.com>, Rahila Syed <rahilasyed90@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Önder Kalacı <onderkalaci@gmail.com>, japin <japinli@hotmail.com>, Michael Paquier <michael@paquier.xyz>, David Steele <david@pgmasters.net>, Craig Ringer <craig@2ndquadrant.com>, Amit Langote <amitlangote09@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-02-04T00:27:39Z
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 →

  1. Release cache tuple when no longer needed

  2. Add some additional tests for row filters in logical replication.

  3. Fix one of the tests introduced in commit 52e4f0cd47.

  4. Allow specifying row filters for logical replication of tables.

  5. Move scanint8() to numutils.c

  6. Replace Test::More plans with done_testing

  7. Reduce relcache access in WAL sender streaming logical changes

  8. Small cleanups related to PUBLICATION framework code

  9. Add a view to show the stats of subscription workers.

  10. Allow publishing the tables of schema.

  11. Doc: improve documentation of CREATE/ALTER SUBSCRIPTION.

  12. Add PublicationTable and PublicationRelInfo structs

  13. Remove unused argument "txn" in maybe_send_schema().

  14. Add prepare API support for streaming transactions in logical replication.

  15. Unify PostgresNode's new() and get_new_node() methods

  16. Use l*_node() family of functions where appropriate

  17. Add support for prepared transactions to built-in logical replication.

  18. Restore the portal-level snapshot after procedure COMMIT/ROLLBACK.

  19. Rename a parse node to be more general

  20. Remove unused column atttypmod from initial tablesync query

  21. SEARCH and CYCLE clauses

Attachments

On Fri, Feb 4, 2022 at 2:26 AM Ajin Cherian <itsajin@gmail.com> wrote:
>
> On Sat, Jan 29, 2022 at 11:31 AM Andres Freund <andres@anarazel.de> wrote:
> >
> > Hi,
> >
> > Are there any recent performance evaluations of the overhead of row filters? I
> > think it'd be good to get some numbers comparing:
> >
> > 1) $workload with master
> > 2) $workload with patch, but no row filters
> > 3) $workload with patch, row filter matching everything
> > 4) $workload with patch, row filter matching few rows
> >
> > For workload I think it'd be worth testing:
> > a) bulk COPY/INSERT into one table
> > b) Many transactions doing small modifications to one table
> > c) Many transactions targetting many different tables
> > d) Interspersed DDL + small changes to a table
> >
>
> Here's the performance data results for scenario d:
>
> HEAD   "with patch no row filter" "with patch 0%" "row-filter-patch
> 25%" "row-filter-patch v74 50%" "row-filter-patch 75%"
> "row-filter-patch v74 100%"
> 1 65.397639 64.414034 5.919732 20.012096 36.35911 49.412548 64.508842
> 2 65.641783 65.255775 5.715082 20.157575 36.957403 51.355821 65.708444
> 3 65.096526 64.795163 6.146072 21.130709 37.679346 49.568513 66.602145
> 4 65.173569 64.644448 5.787197 20.784607 34.465133 55.397313 63.545337
> 5 65.791092 66.000412 5.642696 20.258802 36.493626 52.873252 63.511428
>
> The performance is similar to the other scenarios.
> The script used is below:
>
> CREATE TABLE test (key int, value text, value1 text, data jsonb,
> PRIMARY KEY(key, value));
>
> CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 0); -- 100% allowed
> CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 250000); -- 75% allowed
> CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 500000); -- 50% allowed
> CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 750000); -- 25% allowed
> CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 1000000); -- 0% allowed
>
> DO
> $do$
> BEGIN
> FOR i IN 1..1000001 BY 4000 LOOP
> Alter table test alter column value1 TYPE varchar(30);
> INSERT INTO test VALUES(i,'BAH', row_to_json(row(i)));
> Alter table test ALTER COLUMN value1 TYPE text;
> UPDATE test SET value = 'FOO' WHERE key = i;
> COMMIT;
> END LOOP;
> END
> $do$;
>

Just for completeness, I have shown Ajin's workload "d" test results
as a bar chart same as for the previous perf test posts:

HEAD 65.40
v74 no filters 64.90
v74 allow 100% 64.59
v74 allow 75% 51.27
v74 allow 50% 35.97
v74 allow 25% 20.40
v74 allow 0% 5.78

PSA.

------
Kind Regards,
Peter Smith.
Fujitsu Australia