performance_test_using_sync_replication.txt

Note that in scenario b and c, the amount of data is ten times smaller than
scenario a and d, or it would take very long time.


PG configurations
=======================
shared_buffers = 2GB
checkpoint_timeout = 30min
max_wal_size = 20GB
min_wal_size = 10GB
autovacuum = off


Steps
=======================
(1) create table in publisher side and subscriber side, create publication in
publisher side, create subscription in subscriber side.
(2) modify the postgresql.conf in publisher side and reload.
(3) Do the changes(like INSERT, UPDATE ...) in publisher side, and record the
time spent.


Details - workload "a"
=======================
-- create table in publisher side and subscriber side
CREATE TABLE test (key int, value text, data jsonb, PRIMARY KEY(key, value));

-- create publication in publisher side, specify different filters in different cases
CREATE PUBLICATION pub_1 FOR TABLE test;
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

-- create subscription in subscriber side
CREATE SUBSCRIPTION sync_sub CONNECTION 'host=127.0.0.1 port=5432 dbname=postgres application_name=sync_sub' PUBLICATION pub_1;

-- do the changes in publisher side
INSERT INTO test SELECT i, i::text, row_to_json(row(i)) FROM generate_series(1,1000001)i;


Details - workload "b"
======================
-- create table in publisher side and subscriber side
CREATE TABLE test (key int, value text, data jsonb, PRIMARY KEY(key, value));

-- create publication in publisher side, specify different filters in different cases
CREATE PUBLICATION pub_1 FOR TABLE test;
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 0); -- 100% allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 25000); -- 75% allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 50000); -- 50% allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 75000); -- 25% allowed
CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 100000); -- 0% allowed

-- create subscription in subscriber side
CREATE SUBSCRIPTION sync_sub CONNECTION 'host=127.0.0.1 port=5432 dbname=postgres application_name=sync_sub' PUBLICATION pub_1;

-- do the changes in publisher side
DO
$do$
BEGIN
FOR i IN 0..100000 BY 10 LOOP
INSERT INTO test VALUES(i,'BAH', row_to_json(row(i)));
UPDATE test SET value = 'FOO' WHERE key = i;
IF I % 1000 = 0 THEN
COMMIT;
END IF;
END LOOP;
END
$do$;


Details - workload "c"
======================
-- create table in publisher side and subscriber side
CREATE TABLE test1 (key int, value text, data jsonb, PRIMARY KEY(key, value));
CREATE TABLE test2 (key int, value text, data jsonb, PRIMARY KEY(key, value));
CREATE TABLE test3 (key int, value text, data jsonb, PRIMARY KEY(key, value));
CREATE TABLE test4 (key int, value text, data jsonb, PRIMARY KEY(key, value));
CREATE TABLE test5 (key int, value text, data jsonb, PRIMARY KEY(key, value));

-- create publication in publisher side, specify different filters in different cases
CREATE PUBLICATION pub_1 FOR TABLE test1, test2, test3, test4, test5;
CREATE PUBLICATION pub_1 FOR TABLE test1 WHERE (key > 0), test2 WHERE(key > 0), test3 WHERE (key > 0), test4 WHERE (key > 0), test5 WHERE(key > 0);
CREATE PUBLICATION pub_1 FOR TABLE test1 WHERE (key > 25000), test2 WHERE (key > 25000), test3 WHERE (key > 25000), test4 WHERE (key > 25000), test5 WHERE (key > 25000);
CREATE PUBLICATION pub_1 FOR TABLE test1 WHERE (key > 50000), test2 WHERE (key > 50000), test3 WHERE (key > 50000), test4 WHERE (key > 50000), test5 WHERE (key > 50000);
CREATE PUBLICATION pub_1 FOR TABLE test1 WHERE (key > 75000), test2 WHERE (key > 75000), test3 WHERE (key > 75000), test4 WHERE (key > 75000), test5 WHERE (key > 75000);
CREATE PUBLICATION pub_1 FOR TABLE test1 WHERE (key > 100000), test2 WHERE (key > 100000), test3 WHERE (key > 100000), test4 WHERE (key > 100000), test5 WHERE (key > 100000);

-- create subscription in subscriber side
CREATE SUBSCRIPTION sync_sub CONNECTION 'host=127.0.0.1 port=5432 dbname=postgres application_name=sync_sub' PUBLICATION pub_1;

-- do the changes in publisher side
DO
$do$
BEGIN
FOR i IN 0..100000  BY 10 LOOP
INSERT INTO test1 VALUES(i,'BAH', row_to_json(row(i)));
UPDATE test1 SET value = 'FOO' WHERE key = i;
INSERT INTO test2 VALUES(i,'BAH', row_to_json(row(i)));
UPDATE test2 SET value = 'FOO' WHERE key = i;
INSERT INTO test3 VALUES(i,'BAH', row_to_json(row(i)));
UPDATE test3 SET value = 'FOO' WHERE key = i;
INSERT INTO test4 VALUES(i,'BAH', row_to_json(row(i)));
UPDATE test4 SET value = 'FOO' WHERE key = i;
INSERT INTO test5 VALUES(i,'BAH', row_to_json(row(i)));
UPDATE test5 SET value = 'FOO' WHERE key = i;
IF I % 1000 = 0 THEN
COMMIT;
END IF;
END LOOP;
END
$do$;

Details - workload "d"
======================
-- create table in publisher side and subscriber side
CREATE TABLE test (key int, value text, value1 text, data jsonb, PRIMARY KEY(key, value));

-- create publication in publisher side, specify different filters in different cases
CREATE PUBLICATION pub_1 FOR TABLE test;
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

-- create subscription in subscriber side
CREATE SUBSCRIPTION sync_sub CONNECTION 'host=127.0.0.1 port=5432 dbname=postgres application_name=sync_sub' PUBLICATION pub_1;

-- do the changes in publisher side
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$;