The DBA catalog toolbox

conceptThe concept

Every query here joins the catalogs from the previous lesson. They are worth saving as views in an admin schema or as checks in CI against a staging database:

• Missing primary keys: logical replication cannot replicate UPDATE and DELETE on them without REPLICA IDENTITY FULL, and duplicates creep in.
• Unindexed foreign keys: deletes on the parent and lock contention, as in the production scenario.
• Duplicate indexes: two indexes with the same columns in the same order double the write cost for no benefit.
• Invalid indexes: a failed CREATE INDEX CONCURRENTLY leaves an index that is maintained on every write but never used.
• Dependencies: pg_depend records that a view uses a table, a sequence is owned by a column, an index belongs to a table.
• Sequence headroom: an integer sequence stops at 2,147,483,647.

whyWhy it matters

An integer primary key running out of values is a full outage with a painful fix (rewriting the column to bigint on a huge table). Catching it at 70% gives you months to migrate calmly.

Use caseReal-world scenario

A weekly job runs these checks on every production database and opens a ticket when any of them returns rows.

Try itExamples

Run and edit these in the interactive lesson.

SELECT c.oid::regclass AS table_name
FROM pg_class c
WHERE c.relkind IN ('r', 'p') AND c.relnamespace = 'public'::regnamespace
  AND NOT EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p');

SELECT c.conrelid::regclass AS table_name, c.conname, pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] = c.conkey
  )
ORDER BY 1, 2;

BEGIN;
CREATE INDEX orders_status_idx ON orders (status);
CREATE INDEX orders_status_again ON orders (status);
SELECT indrelid::regclass AS table_name, array_agg(indexrelid::regclass) AS duplicates
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, coalesce(pg_get_expr(indexprs, indrelid), ''), coalesce(pg_get_expr(indpred, indrelid), '')
HAVING count(*) > 1;
ROLLBACK;

BEGIN;
CREATE VIEW paid_orders AS SELECT id, total FROM orders WHERE status = 'paid';
SELECT DISTINCT v.oid::regclass AS dependent_view
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class v ON v.oid = r.ev_class
WHERE d.refobjid = 'orders'::regclass AND v.oid <> 'orders'::regclass;
ROLLBACK;

SELECT s.schemaname, s.sequencename, s.data_type, s.last_value, s.max_value,
       round(100.0 * coalesce(s.last_value, 0) / s.max_value, 4) AS pct_used
FROM pg_sequences s
ORDER BY pct_used DESC;

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid OR NOT indisready;

internalsUnder the hood

pg_depend.deptype tells you the strength of a dependency: n normal (DROP needs CASCADE), a automatic (dropped with the referenced object, such as an index with its table), i internal and e extension membership. Views depend on tables through their rewrite rule, which is why the view dependency query goes through pg_rewrite.

Avoid / preferDo this, not that

DROP TABLE orders CASCADE;

-- list dependents first, then decide
SELECT pg_describe_object(classid, objid, objsubid)
FROM pg_depend
WHERE refobjid = 'orders'::regclass AND deptype = 'n';

ALTER TABLE big ALTER COLUMN id TYPE bigint;
-- at 99% sequence usage, during an outage

-- at 60 to 70% usage, plan an online migration:
-- add a bigint column, backfill in batches,
-- swap in a short maintenance window

PitfallsCommon mistakes

• Dropping an index that looks unused on the primary while replicas use it for reporting: idx_scan is per server.
• Comparing only the first column when looking for duplicate indexes: (a, b) and (a) are not duplicates, though (a) is often redundant.
• Ignoring invalid indexes: they still cost writes and can block a retry with the same name.

LimitsWhen not to use it

Catalog checks tell you about structure, not usage. Pair them with pg_stat_user_indexes and pg_stat_statements before dropping anything, and never drop unique or primary key indexes because they look unused: they enforce constraints.

SecuritySecurity notes

Running these checks needs only read access to the catalogs, so give the monitoring role pg_monitor rather than superuser. Dropping or rebuilding objects still requires ownership.

ChallengeTry it yourself

SELECT i.indexrelid::regclass AS index_name, i.indrelid::regclass AS table_name,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
WHERE t.relnamespace = 'public'::regnamespace
  AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = i.indexrelid)
ORDER BY pg_relation_size(i.indexrelid) DESC;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• System catalogs
• System information functions
• Object identifier types