Skip to content

Instantly share code, notes, and snippets.

@onderkalaci
Last active June 22, 2022 12:37
Show Gist options
  • Save onderkalaci/ccd716c54e6544be8f5fe6b3c2b9582f to your computer and use it in GitHub Desktop.
Save onderkalaci/ccd716c54e6544be8f5fe6b3c2b9582f to your computer and use it in GitHub Desktop.
Replica identity full uses index
-- very basic test
-- single column table on the source with rep. identity full
CREATE TABLE test(a int);
ALTER TABLE test REPLICA IDENTITY FULL;
INSERT INTO test SELECT i FROM generate_series(0,1000000)i;
CREATE PUBLICATION pub_test_1 FOR ALL TABLES;
-- on the target, same table with an index
CREATE TABLE test(a int);
CREATE INDEX i1 ON test(a);
CREATE SUBSCRIPTION sub_test_1
CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
PUBLICATION pub_test_1;
-- on the source
UPDATE test SET a = a + 1 WHERE a = 15;
UPDATE 1
-- on the target
select * from pg_stat_all_indexes where indexrelname = 'i1';
┌───────┬────────────┬────────────┬─────────┬──────────────┬──────────┬──────────────┬───────────────┐
│ relid │ indexrelid │ schemaname │ relname │ indexrelname │ idx_scan │ idx_tup_read │ idx_tup_fetch │
├───────┼────────────┼────────────┼─────────┼──────────────┼──────────┼──────────────┼───────────────┤
│ 16388 │ 16391 │ public │ test │ i1 │ 1 │ 1 │ 1 │
└───────┴────────────┴────────────┴─────────┴──────────────┴──────────┴──────────────┴───────────────┘
(1 row)
-- another simple test that we show multiple
-- rows can be updated via the index
CREATE TABLE test(a int);
ALTER TABLE test REPLICA IDENTITY FULL;
INSERT INTO test SELECT i%1000 FROM generate_series(0,1000000)i;
CREATE PUBLICATION pub_test_1 FOR ALL TABLES;
-- on the target, same table with an index
CREATE TABLE test(a int);
CREATE INDEX i1 ON test(a);
CREATE SUBSCRIPTION sub_test_1
CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
PUBLICATION pub_test_1;
-- on the source
UPDATE test SET a = a + 1 WHERE a = 15;
UPDATE 1000
-- on the target
select * from pg_stat_all_indexes where indexrelname = 'i1';
┌───────┬────────────┬────────────┬─────────┬──────────────┬──────────┬──────────────┬───────────────┐
│ relid │ indexrelid │ schemaname │ relname │ indexrelname │ idx_scan │ idx_tup_read │ idx_tup_fetch │
├───────┼────────────┼────────────┼─────────┼──────────────┼──────────┼──────────────┼───────────────┤
│ 16393 │ 16396 │ public │ test │ i1 │ 1000 │ 500500 │ 1000 │
└───────┴────────────┴────────────┴─────────┴──────────────┴──────────┴──────────────┴───────────────┘
(1 row)
-- multi-column index, and update multiple values & rows
CREATE TABLE test(a int, b text);
ALTER TABLE test REPLICA IDENTITY FULL;
INSERT INTO test SELECT (i%1000), (i%1000)::text FROM generate_series(0,1000000)i;
CREATE PUBLICATION pub_test_1 FOR ALL TABLES;
-- on the target, same table with an index
CREATE TABLE test(a int, b text);
CREATE INDEX i1 ON test(a, b);
CREATE SUBSCRIPTION sub_test_1
CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
PUBLICATION pub_test_1;
-- on the source
UPDATE test SET a = a + 1 WHERE a IN (15, 16);
UPDATE 2000
SELECT sum(a + b::int) FROM test;
┌───────────┐
│ sum │
├───────────┤
│ 999002000 │
└───────────┘
(1 row)
-- on the target
select * from pg_stat_all_indexes where indexrelname = 'i1';
┌───────┬────────────┬────────────┬─────────┬──────────────┬──────────┬──────────────┬───────────────┐
│ relid │ indexrelid │ schemaname │ relname │ indexrelname │ idx_scan │ idx_tup_read │ idx_tup_fetch │
├───────┼────────────┼────────────┼─────────┼──────────────┼──────────┼──────────────┼───────────────┤
│ 16398 │ 16403 │ public │ test │ i1 │ 2000 │ 1001000 │ 2000 │
└───────┴────────────┴────────────┴─────────┴──────────────┴──────────┴──────────────┴───────────────┘
(1 row)
SELECT sum(a + b::int) FROM test;
┌───────────┐
│ sum │
├───────────┤
│ 999002000 │
└───────────┘
(1 row)
-- dropped columns
CREATE TABLE test(drop_1 jsonb, a int, drop_2 point, b text, drop_3 timestamptz);
ALTER TABLE test DROP COLUMN drop_1;
ALTER TABLE test DROP COLUMN drop_2;
ALTER TABLE test DROP COLUMN drop_3;
ALTER TABLE test REPLICA IDENTITY FULL;
INSERT INTO test SELECT (i%1000), (i%1000)::text FROM generate_series(0,1000000)i;
CREATE PUBLICATION pub_test_1 FOR ALL TABLES;
-- on the target, same table with an index
CREATE TABLE test(drop_1 jsonb, a int, drop_2 point, b text, drop_3 timestamptz);
ALTER TABLE test DROP COLUMN drop_1;
ALTER TABLE test DROP COLUMN drop_2;
ALTER TABLE test DROP COLUMN drop_3;
CREATE INDEX i1 ON test(a, b);
CREATE SUBSCRIPTION sub_test_1
CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
PUBLICATION pub_test_1;
-- on the source
UPDATE test SET a = a + 1, b = (b::int+10)::text WHERE a IN (15, 16, 100);
UPDATE 3000
SELECT sum(a + b::int) FROM test;
┌───────────┐
│ sum │
├───────────┤
│ 999033000 │
└───────────┘
(1 row)
-- on the target
┌───────┬────────────┬────────────┬─────────┬──────────────┬──────────┬──────────────┬───────────────┐
│ relid │ indexrelid │ schemaname │ relname │ indexrelname │ idx_scan │ idx_tup_read │ idx_tup_fetch │
├───────┼────────────┼────────────┼─────────┼──────────────┼──────────┼──────────────┼───────────────┤
│ 16413 │ 16418 │ public │ test │ i1 │ 3000 │ 1501500 │ 3000 │
└───────┴────────────┴────────────┴─────────┴──────────────┴──────────┴──────────────┴───────────────┘
(1 row)
SELECT sum(a + b::int) FROM test;
┌───────────┐
│ sum │
├───────────┤
│ 999033000 │
└───────────┘
(1 row)
-- partitioned tables, index on parent, move between partitions
CREATE TABLE users_table_part(user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_1);
CREATE TABLE users_table_part_0 PARTITION OF users_table_part FOR VALUES FROM (0) TO (1);
CREATE TABLE users_table_part_1 PARTITION OF users_table_part FOR VALUES FROM (1) TO (2);
CREATE TABLE users_table_part_2 PARTITION OF users_table_part FOR VALUES FROM (2) TO (3);
CREATE TABLE users_table_part_3 PARTITION OF users_table_part FOR VALUES FROM (3) TO (4);
CREATE TABLE users_table_part_4 PARTITION OF users_table_part FOR VALUES FROM (4) TO (5);
CREATE TABLE users_table_part_5 PARTITION OF users_table_part FOR VALUES FROM (5) TO (6);
CREATE TABLE users_table_part_6 PARTITION OF users_table_part FOR VALUES FROM (6) TO (7);
CREATE TABLE users_table_part_7 PARTITION OF users_table_part FOR VALUES FROM (7) TO (8);
CREATE TABLE users_table_part_8 PARTITION OF users_table_part FOR VALUES FROM (8) TO (9);
INSERT INTO users_table_part SELECT i, i %9, i %5090 FROM generate_series(0, 1000000) i;
ALTER TABLE users_table_part REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_0 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_1 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_2 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_3 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_4 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_5 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_6 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_7 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_8 REPLICA IDENTITY FULL;
CREATE PUBLICATION pub_test_1 FOR TABLE users_table_part;
-- on the target, same table with an index
CREATE TABLE users_table_part(user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_1);
CREATE TABLE users_table_part_0 PARTITION OF users_table_part FOR VALUES FROM (0) TO (1);
CREATE TABLE users_table_part_1 PARTITION OF users_table_part FOR VALUES FROM (1) TO (2);
CREATE TABLE users_table_part_2 PARTITION OF users_table_part FOR VALUES FROM (2) TO (3);
CREATE TABLE users_table_part_3 PARTITION OF users_table_part FOR VALUES FROM (3) TO (4);
CREATE TABLE users_table_part_4 PARTITION OF users_table_part FOR VALUES FROM (4) TO (5);
CREATE TABLE users_table_part_5 PARTITION OF users_table_part FOR VALUES FROM (5) TO (6);
CREATE TABLE users_table_part_6 PARTITION OF users_table_part FOR VALUES FROM (6) TO (7);
CREATE TABLE users_table_part_7 PARTITION OF users_table_part FOR VALUES FROM (7) TO (8);
CREATE TABLE users_table_part_8 PARTITION OF users_table_part FOR VALUES FROM (8) TO (9);
CREATE INDEX i1 ON users_table_part(value_1);
CREATE SUBSCRIPTION sub_test_1
CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
PUBLICATION pub_test_1;
-- on the source, delete
DELETE FROM users_table_part WHERE user_id IN (50, 79);
DELETE 2
-- on the source, move partitions
UPDATE users_table_part SET value_1 = 6 WHERE value_2 = 25;
UPDATE 197
SELECT sum(user_id + value_1 + value_2) FROM users_table_part;
┌──────────────┐
│ sum │
├──────────────┤
│ 502545781079 │
└──────────────┘
(1 row)
select distinct(value_1) FROM users_table_part WHERE value_2 = 25;
┌─────────┐
│ value_1 │
├─────────┤
│ 6 │
└─────────┘
(1 row)
-- on the target
select indexrelname, idx_scan from pg_stat_all_indexes where indexrelname ilike 'users_table%';
┌────────────────────────────────┬──────────┐
│ indexrelname │ idx_scan │
├────────────────────────────────┼──────────┤
│ users_table_part_0_value_1_idx │ 22 │
│ users_table_part_1_value_1_idx │ 22 │
│ users_table_part_2_value_1_idx │ 21 │
│ users_table_part_3_value_1_idx │ 22 │
│ users_table_part_4_value_1_idx │ 22 │
│ users_table_part_5_value_1_idx │ 23 │
│ users_table_part_6_value_1_idx │ 22 │
│ users_table_part_7_value_1_idx │ 23 │
│ users_table_part_8_value_1_idx │ 22 │
└────────────────────────────────┴──────────┘
(9 rows)
SELECT sum(user_id + value_1 + value_2) FROM users_table_part;
┌──────────────┐
│ sum │
├──────────────┤
│ 502545781079 │
└──────────────┘
(1 row)
select distinct(value_1) FROM users_table_part WHERE value_2 = 25;
┌─────────┐
│ value_1 │
├─────────┤
│ 6 │
└─────────┘
(1 row)
-- multi-level partitioned tables, index on a leaf partition only
CREATE TABLE users_table_part(user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_1);
CREATE TABLE users_table_part_0 (user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_2);
ALTER TABLE users_table_part ATTACH PARTITION users_table_part_0 FOR VALUES FROM (0) TO (1);
CREATE TABLE users_table_part_0_0 PARTITION OF users_table_part_0 FOR VALUES FROM (0) TO (1);
CREATE TABLE users_table_part_0_1 PARTITION OF users_table_part_0 FOR VALUES FROM (1) TO (2);
INSERT INTO users_table_part SELECT i%500, i %1, i %2 FROM generate_series(0, 1000000) i;
ALTER TABLE users_table_part REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_0 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_0_0 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_0_1 REPLICA IDENTITY FULL;
CREATE PUBLICATION pub_test_1 FOR TABLE users_table_part;
-- on the target, same table with an index
CREATE TABLE users_table_part(user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_1);
CREATE TABLE users_table_part_0 (user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_2);
ALTER TABLE users_table_part ATTACH PARTITION users_table_part_0 FOR VALUES FROM (0) TO (1);
CREATE TABLE users_table_part_0_0 PARTITION OF users_table_part_0 FOR VALUES FROM (0) TO (1);
CREATE TABLE users_table_part_0_1 PARTITION OF users_table_part_0 FOR VALUES FROM (1) TO (2);
CREATE INDEX i1 ON users_table_part(user_id);
CREATE SUBSCRIPTION sub_test_1
CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
PUBLICATION pub_test_1;
-- on the source, delete
DELETE FROM users_table_part WHERE user_id IN (50, 79);
DELETE 4000
-- on the source, move partitions
UPDATE users_table_part SET value_1 =0 WHERE value_2 = 1 and user_id IN (51, 88);
UPDATE 2000
SELECT sum(user_id + value_1 + value_2) FROM users_table_part;
┌───────────┐
│ sum │
├───────────┤
│ 249740000 │
└───────────┘
(1 row)
-- on the target
select indexrelname, idx_scan from pg_stat_all_indexes where indexrelname ilike 'users_table%';
┌─[ RECORD 1 ]─┬──────────────────────────────────┐
│ indexrelname │ users_table_part_0_0_user_id_idx │
│ idx_scan │ 2000 │
├─[ RECORD 2 ]─┼──────────────────────────────────┤
│ indexrelname │ users_table_part_0_1_user_id_idx │
│ idx_scan │ 4000 │
└──────────────┴──────────────────────────────────┘
Time: 1.020 ms
SELECT sum(user_id + value_1 + value_2) FROM users_table_part;
┌───────────┐
│ sum │
├───────────┤
│ 249740000 │
└───────────┘
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment