Replica identity full uses index

test_1.sql

-- 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)

test_2.sql

-- 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)

test_3.sql

-- 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)

test_4.sql

-- 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)

test_5.sql

-- 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)

test_6.sql

-- 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)