details.txt

text/plain

Download

Some parameters specified in postgresql.conf
=====================================================
shared_buffers = 8GB
checkpoint_timeout = 30min
max_wal_size = 20GB
min_wal_size = 10GB
autovacuum = off

Steps
=====================================================
-- create table in publisher and subscriber
CREATE TABLE large_test (
     id serial primary key,
     num1 bigint,
     num2 double precision,
     num3 double precision
);


-- create publication
create publication pub for table large_test;

-- create subscription
CREATE SUBSCRIPTION sub CONNECTION 'port=5432 dbname=postgres' PUBLICATION pub with(streaming);


Then set synchronous replication and restart publisher server.

-- case: bulk insert
EXPLAIN (ANALYZE, BUFFERS) INSERT INTO large_test (num1, num2, num3)
     SELECT round(random()*10), random(), random()*142
     FROM generate_series(1, 5000000) s(i);

-- case: rollback to savepoint
INSERT INTO large_test (num1, num2, num3)
     SELECT round(random()*10), random(), random()*142
     FROM generate_series(1, 600000) s(i);"

BEGIN;
UPDATE large_test SET num1 = num1 + 1, num2 = num2 + 1, num3 = num3 + 1;
SAVEPOINT s1;
UPDATE large_test SET num1 = num1 + 1, num2 = num2 + 1, num3 = num3 + 1;
SAVEPOINT s2;
UPDATE large_test SET num1 = num1 + 1, num2 = num2 + 1, num3 = num3 + 1;
ROLLBACK TO SAVEPOINT s2;
END;