为物化视图执行pg_repack工作

Question

pg_repack是否适用于postgres v11的物化视图?我在服务器上安装pg_repack客户端时遇到了问题，因此无法自己检查。根据文档，它说"pg_repack是一个PostgreSQL扩展，它可以让你从表和索引中删除膨胀"，所以我假设它也包括物化视图。

Answer 1

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;

然后

--------do the update.
UPDATE
tbt
SET
a = a - 10
WHERE
a < 100;
REFRESH MATERIALIZED VIEW CONCURRENTLY tbtmv;    

---

SELECT
*
FROM
pgstattuple ('tbtmv');

返回

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

返回

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

然后

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

则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

可以看到dead_tuple_count从10到0。我也尝试过删除操作，结果是一样的。那么pg_repack是否适用于物化视图呢?答案是肯定的。
关键的问题是，要使物化视图膨胀，你需要使用:

REFRESH MATERIALIZED view CONCURRENTLY