用於在 postgresql 性能中查找所有可以轉換為 int 欄位的 bigint 的腳本

我想壓縮一個 postgresql 11 db，我的想法是將大量 bigint 轉換為 int 數據類型，當且僅當考慮到內部數據時 int 數據類型的限制存在合理的滯後。

那麼，你能幫我找到一個腳本來找到所有可以在 postgresql 中轉換為 int 欄位的 bigint 嗎？

以下腳本應該有助於完成這項工作：

create or replace procedure cbi ()
language plpgsql
as $$
--
declare 
s text;
max_value bigint;
c cursor for
select 
 pg_namespace.nspname, 
 pg_class.relname, 
 pg_attribute.attname, 
 pg_type.typname 
from pg_class
join pg_namespace
on pg_class.relnamespace = pg_namespace.oid
and pg_namespace.nspname <> 'pg_catalog'
join pg_attribute
on pg_class.oid = pg_attribute.attrelid
join pg_type
on pg_attribute.atttypid = pg_type.oid
and pg_type.typname='int8'
where pg_class.relkind in ('r','v');
begin
for r in c
loop
 s := 'select  max(' || r.attname || ') from ' || r.nspname || '.' || r.relname; 
 execute s into max_value;
 if max_value <= +2147483647 and max_value >= -2147483648 
 then
  raise notice '%.%.% max=% can be replaced with int.', r.nspname, r.relname, r.attname, max_value;
 else
  raise notice '%.%.% max=% CANNOT be replaced with int.', r.nspname, r.relname, r.attname, max_value;
 end if;
end loop;
end;
--
$$;

例子：

postgres=# call cbi();
NOTICE:  public.pg_stat_statements.queryid max=9148650842799727408 CANNOT be replaced with int.
NOTICE:  public.pg_stat_statements.calls max=118 can be replaced with int.
NOTICE:  public.pg_stat_statements.rows max=4060311 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_hit max=577699 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_read max=637 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_dirtied max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_written max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_hit max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_read max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_dirtied max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_written max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_read max=171 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_written max=171 can be replaced with int.
NOTICE:  public.t.x max=1 can be replaced with int.
CALL
postgres=# 

引用自：https://dba.stackexchange.com/questions/262513