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