Find your widest tables (by column count)

SELECT
  n.nspname AS schema,
  c.relname AS table,
  count(*) FILTER (WHERE a.attnum > 0 AND NOT a.attisdropped) AS live_columns,
  count(*) FILTER (WHERE a.attisdropped) AS dropped_columns,
  count(*) FILTER (WHERE a.attnum > 0) AS total_attnums
FROM pg_attribute a
JOIN pg_class c      ON c.oid = a.attrelid
JOIN pg_namespace n  ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p')   -- r=table, p=partitioned table
  AND n.nspname NOT IN ('pg_catalog','information_schema')
GROUP BY 1,2
ORDER BY total_attnums DESC
LIMIT 50;

Identify “mostly NULL” columns (classic wide-table driver)

SELECT
  attname,
  null_frac,
  avg_width
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 'your_table'
ORDER BY null_frac DESC, avg_width DESC
LIMIT 30;

Get a rough “row width” estimate (planner stats)

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  pg_size_pretty(pg_relation_size(relid)) AS heap_size
FROM pg_stat_user_tables
ORDER BY pg_relation_size(relid) DESC
LIMIT 20;

Query 1: Get tables with non-empty TOAST relations and maximum OID usage

This query identifies tables with active TOAST relations and reports the maximum OID used in each table and is helpful in pinpointing TOASTed relations that are nearing the OID limit.

DO $$
DECLARE
  r record;
  o oid;
  t text;
BEGIN
  FOR r IN SELECT oid, oid::regclass as t FROM pg_class WHERE relkind = 't' and relowner != 10 LOOP
    EXECUTE 'SELECT max(chunk_id) FROM ' || r.t INTO o;
      IF o IS NOT NULL THEN
        SELECT relname INTO t FROM pg_class WHERE reltoastrelid = r.oid;
        RAISE NOTICE '%: %', t, o;
      END IF;
  END LOOP;
END
$$;

Sample output:

NOTICE:  pg_proc: 13979
NOTICE:  pg_statistic: 14336
NOTICE:  pg_rewrite: 14319
NOTICE:  toastt1: 11683945
NOTICE:  toasttab1: 11663893

Query 2: Count the number of OIDs used per TOAST relation

To track the OID usage within specific TOAST relations, run the following query to count the number of distinct chunk IDs. This helps to understand how many OIDs are being used by each TOAST relation, providing insight into potential OID exhaustion.

DO $$
DECLARE
  r record;
  o oid;
  t text;
BEGIN
  FOR r IN SELECT oid, oid::regclass as t FROM pg_class WHERE relkind = 't' and relowner != 10 LOOP
    EXECUTE 'SELECT COUNT(DISTINCT chunk_id) FROM ' || r.t INTO o;
      IF o <> 0 THEN
        SELECT relname INTO t FROM pg_class WHERE reltoastrelid = r.oid;
        RAISE NOTICE '%: %', t, o;
      END IF;
  END LOOP;
END
$$;

Sample output:

NOTICE:  pg_proc: 2
NOTICE:  pg_statistic: 8
NOTICE:  pg_rewrite: 85
NOTICE:  toastt1: 10
NOTICE:  toasttab1: 1000
NOTICE:  toasttab: 1003