CODEWITHSUNDEEP
postgresqlpg-repack
nmakb
nmakb

Reputation: 1245

does pg_repack work for materialized view

Does pg_repack work for materialized views on postgres v11? I am running into issues installing pg_repack client on my server and hence unable to check myself. As per the documentation it says "pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes", so I am assuming it includes materialized views as well.

Upvotes: 3

Views: 234

Answers (1)

jian
jian

Reputation: 4877

DROP TABLE tbt CASCADE;

BEGIN;
CREATE TABLE tbt (
    a int PRIMARY KEY
)
WITH (
    fillfactor = 40,
    autovacuum_enabled = OFF
);
INSERT INTO tbt
SELECT
    g
FROM
    generate_series(1, 2000) g;
CREATE MATERIALIZED VIEW tbtmv AS
SELECT
    *
FROM
    tbt;
CREATE UNIQUE INDEX tbtmv_idx ON tbtmv (a);
COMMIT;

then

--------do the update.
UPDATE
    tbt
SET
    a = a - 10
WHERE
    a < 100;

REFRESH MATERIALIZED VIEW CONCURRENTLY tbtmv;    

SELECT
    *
FROM
    pgstattuple ('tbtmv');

return

table_len          | 73728
tuple_count        | 2000
tuple_len          | 56000
tuple_percent      | 75.95
dead_tuple_count   | 10
dead_tuple_len     | 280
dead_tuple_percent | 0.38
free_space         | 1116
free_percent       | 1.51

 /usr/local/pgsql15/bin/pg_repack -d test15 --table tbtmv

return

ERROR: pg_repack failed with error: ERROR:  relation "tbtmv" does not exist

Then

 /usr/local/pgsql15/bin/pg_repack -d test15 --table tbt

then SELECT * FROM pgstattuple('tbtmv');

-[ RECORD 1 ]------+------
table_len          | 73728
tuple_count        | 2000
tuple_len          | 56000
tuple_percent      | 75.95
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1436
free_percent       | 1.95

As you can see dead_tuple_count from 10 to 0. I also tried with delete operation, the result is the same. So does pg_repack work for materialized view? The answer is YES.
Key gotcha is that to make materialized view bloat, you need to use:

REFRESH MATERIALIZED view CONCURRENTLY

Upvotes: 0

Related Questions