postgres=# select * from pg_stat_user_tables where relname = 'test';
-[ RECORD 1 ]-------+------------------------------
relid | 23400
schemaname | public
relname | test
seq_scan | 12
seq_tup_read | 49290
idx_scan |
idx_tup_fetch |
n_tup_ins | 22916
n_tup_upd | 25249
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 22916
n_dead_tup | 2333
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum | 2019-10-09 09:15:52.549356+08
last_analyze |
last_autoanalyze | 2019-10-09 09:15:55.279256+08
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2 postgres=# vacuum landing_person_cdl_02; #执行手工的清理
VACUUM
postgres=# select * from pg_stat_user_tables where relname = 'landing_person_cdl_02'; #查看清理结果
-[ RECORD 1 ]-------+------------------------------
relid | 23400
schemaname | public
relname | landing_person_cdl_02
seq_scan | 12
seq_tup_read | 49290
idx_scan |
idx_tup_fetch |
n_tup_ins | 22916
n_tup_upd | 25249
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 21923
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum | 2019-10-10 09:57:09.651626+08
last_autovacuum | 2019-10-09 09:15:52.549356+08
last_analyze |
last_autoanalyze | 2019-10-09 09:15:55.279256+08
vacuum_count | 1
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2
说明
n_live_tup的数量是当前表的数据量。
n_dead_tup的数据量是未回收的空间。
查看表的大小
select pg_size_pretty(pg_relation_size('test'));