监控序列以及表大小

序列超过 10 w 的表

select schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
from pg_tables where pg_total_relation_size(schemaname||'.'||tablename) > 10000000  -- 大于 10M 的表
and schemaname<>'pg_catalog';


test2=# --序列超过 10 w 的表
test2=# with t as (select schemaname, sequencename ,last_value, schemaname||'.' || split_part(sequencename,'_',1)  as owned_by_table from pg_sequences where last_value>100000) 
test2-# select * , pg_size_pretty(pg_total_relation_size(owned_by_table)) as table_size   from t order by last_value desc, pg_total_relation_size(owned_by_table) desc;
 schemaname | sequencename | last_value | owned_by_table | table_size 
------------+--------------+------------+----------------+------------
 public     | test2_id_seq |    1000000 | public.test2   | 42 MB
(1 row)

-- 假设 业务对象都在 public 模式下, 那么我们在查询与拼接的时候, 可以忽略 schema 的处理, 
-- 对于大数据量, 可以提供性能
CREATE OR REPLACE FUNCTION func_monitor_large_sequences2(seq_size_limit  bigint default 100000)
RETURNS setof large_sequece
AS 
$BODY$ 
DECLARE
BEGIN
	return query 
	with t1 as (
		select schemaname, sequencename ,last_value
        --, schemaname||'.' || split_part(sequencename,'_',1)  as owned_by_table, split_part(sequencename,'_',2) column_name 
		from pg_sequences where last_value>seq_size_limit
    )             
    select 
        t1.schemaname::text, 
        t1.sequencename::text ,
        t1.last_value::bigint, 
        t2.table_name::text as owned_by_table, 
        t2.column_name::text ,
        t2.data_type::text,
        pg_size_pretty(pg_total_relation_size(t2.table_name::text))::TEXT
    from t1 join 
    (
        SELECT table_schema,table_name, column_name, column_default, split_part(column_default::text,$$'$$,2) as sequence_name, data_type
        FROM information_schema.columns
    ) t2 on (t2.sequence_name = t1.sequencename);
END;
$BODY$ LANGUAGE plpgsql;
 
-- 使用实例:
select * from func_monitor_large_sequences2(1000000);
test2=# select * from func_monitor_large_sequences2();
 schemaname |  tablename   | current_value | owned_by_table | column_name | data_type | table_size 
------------+--------------+---------------+----------------+-------------+-----------+------------
 public     | test2_id_seq |       1000000 | test2          | id          | integer   | 42 MB
 public     | test4_id_seq |       1000000 | test4          | id          | integer   | 42 MB
 public     | seq_xx_id    |       1000000 | test5          | id          | integer   | 64 MB
(3 rows)

表大小大于 10M 的表

test2=# 
test2=# select schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
test2-# from pg_tables where pg_total_relation_size(schemaname||'.'||tablename) > 10000000  -- 大于 10M 的表
test2-# and schemaname<>'pg_catalog';
 schemaname | tablename | size  
------------+-----------+-------
 public     | test2     | 42 MB
 public     | test3     | 64 MB
(2 rows)

test2=# analyse test2, test3;
ANALYZE

test2=# select schemaname, relname, n_live_tup  from pg_stat_user_tables where relname in('test2','test3') ;                                                                                            
 schemaname | relname | n_live_tup 
------------+---------+------------
 public     | test2   |    1000000
 public     | test3   |    1000000
(2 rows)