PG 表膨胀回收

调整vacuum参数为默认值

select name, setting, short_desc from pg_settings
where name in (‘autovacuum’,
‘autovacuum_freeze_max_age’,
‘log_autovacuum_min_duration’,
‘vacuum_failsafe_age’,
‘vacuum_freeze_min_age’,
‘vacuum_freeze_table_age’
);

            name             |  setting   |                                   short_desc
-----------------------------+------------+--------------------------------------------------------------------------------
 autovacuum                  | off        | Starts the autovacuum subprocess.
 autovacuum_freeze_max_age   | 200000000  | Age at which to autovacuum a table to prevent transaction ID wraparound.
 log_autovacuum_min_duration | 0          | Sets the minimum execution time above which autovacuum actions will be logged.
 vacuum_failsafe_age         | 1600000000 | Age at which VACUUM should trigger failsafe to avoid a wraparound outage.
 vacuum_freeze_min_age       | 50000000   | Minimum age at which VACUUM should freeze a table row.
 vacuum_freeze_table_age     | 150000000  | Age at which VACUUM should scan whole table to freeze tuples.
复制

频繁增删改表，模拟表膨胀

call insert_trx(1500000);
call update_trx(1500000);
call delete_trx(1500000);
call update_trx(1500000);
call update_trx(1500000);
call update_trx(1500000);
call update_trx(1500000);
call update_trx(1500000);
复制

查看表膨胀率

SELECT relname AS TABLE_NAME,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
n_dead_tup,
n_live_tup,
(n_dead_tup * 100 / (n_live_tup + n_dead_tup))AS dead_tup_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup<>0 order by n_dead_tup LIMIT 10;

 table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
------------+------------+------------+------------+----------------
 test_table | 103 MB     |    2211853 |          0 |            100
复制

回收表膨胀表

pg_repack -t public.test_table -j 2 -d postgres
复制

 pg_repack -t public.test_table -j 2 -d postgres
NOTICE: Setting up workers.conns
INFO: repacking table "public.test_table"

SELECT relname AS TABLE_NAME,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
n_dead_tup,
n_live_tup,
(n_dead_tup * 100 / (n_live_tup + n_dead_tup))AS dead_tup_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup<>0 order by n_dead_tup LIMIT 10;
 table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
------------+------------+------------+------------+----------------
(0 rows)
复制

需注意的是表需要有主键

postgres=# \d+ test_table
                                                            Table "public.test_table"
 Column |       Type        | Collation | Nullable |                Default                 | Storage  | Compression | Stats
target | Description
--------+-------------------+-----------+----------+----------------------------------------+----------+-------------+-------
-------+-------------
 id     | integer           |           | not null | nextval('test_table_id_seq'::regclass) | plain    |             |
       |
 name   | character varying |           |          |                                        | extended |             |
       |
Indexes:
    "test_table_pkey" PRIMARY KEY, btree (id)
复制

如建一个无主键的表

drop table test_table;
CREATE TABLE test_table (
    id SERIAL  ,
    name VARCHAR
);

call insert_trx(3000000);
call update_trx(3000000);
call delete_trx(3000000);
call update_trx(3000000);
复制

pg_repack -t public.test_table -j 2 -d postgres

NOTICE: Setting up workers.conns
WARNING: relation "public.test_table" must have a primary key or not-null unique keys

复制