script to find all bigint that can be converted to int fields in postgresql performance

Question

I want to compact a postgresql 11 db and my idea is to convert lots of bigint to int data types if and only if there is a reasonable lag to the limit of int data type considering the data inside.

So, can you help me in finding a script to find all bigint that can be converted to int fields in postgresql ?

• user1822: If you are doing that for performance reasons, I doubt you will actually be able to measure any difference. It might reduce the occupied space on disc, but that's unlikely either.
• Gabriele D'Onufrio: thank you, I am doing for both, even a small improvement is welcome

Answer 1

Following script should help to do the job:

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 <= [phone] 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;
--
$$;

Example:

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=# 

• Gabriele D'Onufrio: it gives me that error:: Errore SQL [42809]: ERROR: "antivirus_exception_pk" is an index¶ Dove: PL/pgSQL function cbi() line 25 at EXECUTE
• pifor: I have fixed the script with where pg_class.relkind in ('r','v');
• Gabriele D'Onufrio: tks a lot mister