postgresql 需要清理的库，表，索引_postgresql 清空schema下表和索引

生产上的 autovacuum 一般是要打开的，除此之外，还需要定时监控业务数据库的表。

库 age

不能大于20亿

select max(age(datfrozenxid)) from pg_database;

1
2

表 根据 age 查看

需要关注的大表(>1G)的age情况
不能大于20亿

SELECT relname, 
       age(relfrozenxid) as xid_age, 
       pg_size_pretty(pg_table_size(oid)) as table_size
  FROM pg_class 
 WHERE 1=1
   AND relkind = ANY (ARRAY['r'::"char", 'p'::"char"])
   AND pg_table_size(oid) > 1073741824
 ORDER BY age(relfrozenxid) DESC 
 LIMIT 20
;

1
2
3
4
5
6
7
8
9
10
11

select current_database() as datname,
       pn.nspname||'.'||pc.relname as nspname_relname,
       --' vacuum verbose '||pn.nspname||'.'||pc.relname||' ;' as vacuum_rel,
       --' vacuum full verbose '||pn.nspname||'.'||pc.relname||' ;' as vacuum_full_rel,
       pc.relkind,
       pc.relfrozenxid,
       pc.relminmxid,
       age(pc.relfrozenxid) as age_relfrozenxid,
       txid_current() as txid_curr,
       pg_size_pretty(pg_total_relation_size(pc.oid)) as total_relation_size,
       pg_size_pretty(pg_table_size(pc.oid)) as table_size,
       pg_size_pretty(pg_indexes_size(pc.oid)) as indexes_size
  from pg_class pc
       left outer join pg_namespace pn
                    on pc.relnamespace = pn.oid
 where 1=1
   and pc.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])
   and pn.nspname not in ('pg_catalog','information_schema')
   and pn.nspname not like 'pg_toast%'
   and pg_table_size(pc.oid) >= 100*1024*1024
 order by age(pc.relfrozenxid) desc,
          pg_table_size(pc.oid) desc
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

表 根据live row，dead row 查看

这个是为了提高性能

select current_database() as datname,
       pt.schemaname||'.'||pt.relname as schemaname_relname,
       --' vacuum verbose '||pt.schemaname||'.'||pc.relname||' ;' as vacuum_rel,
       --' vacuum full verbose '||pt.schemaname||'.'||pc.relname||' ;' as vacuum_full_rel,
       pc.relfrozenxid,
       age(pc.relfrozenxid) as age_relfrozenxid,
       txid_current() as txid_curr,
       pg_size_pretty(pg_total_relation_size(pt.relid)) as total_relation_size,
       pg_size_pretty(pg_table_size(pt.relid)) as table_size,
       pg_size_pretty(pg_indexes_size(pt.relid)) as indexes_size,
       pt.n_live_tup,
       pt.n_dead_tup,
       round(case when (pt.n_live_tup+pt.n_dead_tup) = 0 then 0.0000
                  else pt.n_dead_tup/((pt.n_live_tup+pt.n_dead_tup) *1.0) 
              end,4) as dead_tup_lv
  from pg_stat_all_tables pt
       left outer join pg_class pc
                    on pt.relid=pc.oid
 where 1=1
   and pt.schemaname not in ('pg_catalog','information_schema')
   and pt.schemaname not like 'pg_toast%'
   and pg_table_size(pt.relid) >= 100*1024*1024
 order by case when (pt.n_live_tup+pt.n_dead_tup) = 0 then 0
               else pt.n_dead_tup/((pt.n_live_tup+pt.n_dead_tup) *1.0) 
           end desc
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

索引

select 
      current_database(),
      pn2.nspname||'.'||pc2.relname as nspname_relname,
      pn.nspname||'.'||pc.relname as nspname_indexname,
      round(100 * pg_relation_size(idx.indexrelid)/pg_relation_size(idx.indrelid))/100 as index_ratio,
      pg_size_pretty(pg_relation_size(idx.indexrelid)) as index_size,
      pg_size_pretty(pg_relation_size(idx.indrelid)) as table_size
 from pg_index idx
      left outer join pg_class pc
                   on idx.indexrelid=pc.oid
      left outer join pg_namespace pn
                   on pc.relnamespace=pn.oid
      left outer join pg_class pc2
                   on idx.indrelid=pc2.oid
      left outer join pg_namespace pn2
                   on pc2.relnamespace=pn2.oid             
where 1=1
  --and pc2.relname='table name'
  and pn.nspname not in ('pg_catalog','information_schema')
  and pn.nspname not like 'pg_toast%'
  and pc.relkind = 'i'
  and pc2.relkind = 'r'
  and pg_relation_size(idx.indrelid) > 0
  and pg_relation_size(idx.indrelid) > 100*1024*1024
order by  --pg_relation_size(idx.indexrelid),
         --pg_relation_size(idx.indrelid),
         round(100 * pg_relation_size(idx.indexrelid)/pg_relation_size(idx.indrelid))/100 desc
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

参考:
http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html
http://www.databasesoup.com/