GitLab

Goal

Analyze all int2 and int4 primary keys in the observed database and show risks to hit int2/int4 limits.

See https://www.postgresql.org/docs/current/datatype-numeric.html

TODO / Implementation details

It seems that we need to use PLpgSQL code to be able to process all tables/PKs in a single request.

• Take this as a basis to get data:

do $$
declare
  rec record;
  out text;
  val int8;
  ratio numeric;
begin
  out := '';
  for rec in 
    select 
      c.oid,
      (select spcname from pg_tablespace where oid = reltablespace) as tblspace,
      nspname as schema_name,
      relname as table_name,
      t.typname,
      attname
    from pg_index i
    join pg_class c on c.oid = i.indrelid
    left join pg_namespace n on n.oid = c.relnamespace
    join pg_attribute a on
      a.attrelid = i.indrelid
      and a.attnum = any(i.indkey)
    join pg_type t on t.oid = atttypid 
    where
      i.indisprimary
      and t.typname in ('int2', 'int4')
      and nspname <> 'pg_toast'
  loop
    execute format('select max(%I) from %I.%I;', rec.attname, rec.schema_name, rec.table_name) into val;
    if rec.typname = 'int4' then
      ratio := (val::numeric / 2^31)::numeric;
    elsif rec.typname = 'int2' then
      ratio := (val::numeric / 2^15)::numeric;
    else
      assert false, 'unreachable point';
    end if;
    if ratio > 0.01 then -- report only if > 1% of capacity is reached
      out := out || format(
        e'\nTable: %I.%I, column: %I, type: %s, reached value: %s (%s%%)',
        rec.schema_name,
        rec.table_name,
        rec.attname,
        rec.typname,
        val,
        round(100 * ratio, 2)
      );
    end if;
  end loop;
  raise info '%', out;
end;
$$ language plpgsql;

• Check its behavior on tables with a multi-column PK (optional)
• Get values from the query avove, store in the JSON report
• Create .md report in the following form (order by "Capacity used"):

Table | PK (type) | Current max value | ▼ Capacity used, %
---------------------------------
table_in_public_schema  | colname (int4) |  ... | 12
custom_schema.table_in_custom_schema |  colname (int2) | 23
....

Acceptance criteria

As a DBA, I can quickly check what are current risks to reach int2/int4 limits in PKs. I see what are the values reached in every PK listed. I also see % of max capacity (if it's more that 20%, I should plan conversion to int8 ASAP).