Re: row filtering for logical replication

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