Automatic Data Dictionary Generation in PostgreSQL

Automatic Data Dictionary Generation in PostgreSQL

A practical guide to producing a self-updating catalog of your PostgreSQL schema with pure SQL and lightweight automation.

What Is a Data Dictionary?

A data dictionary is a centralized catalog that documents every table, column, data type, constraint, default value, and business description in a database. Instead of living in a wiki or spreadsheet, the information is sourced directly from your database metadata so it never drifts from reality.

Why You Should Automate It

• Eliminate tribal knowledge: New engineers instantly understand schemas without asking senior teammates.
• Reduce bugs: Accurate column definitions prevent mis-typed JOINs and incorrect aggregations.
• Support governance: Regulatory standards (SOX, HIPAA, GDPR) require clear data lineage and definitions.
• Zero maintenance: When the schema changes, regenerated output reflects reality—no stale docs.

How PostgreSQL Stores Metadata

PostgreSQL keeps rich schema metadata in two primary locations:

1. information_schema — ANSI-standard views (portable across databases).
2. pg_catalog — PostgreSQL-specific system tables that expose extras like column comments, indexes, and statistics.

Combining these sources lets you build a comprehensive dictionary.

Core Query for Tables and Columns

SELECT
ns.nspname AS schema_name,
cls.relname AS table_name,
col.ordinal_position,
col.column_name,
col.data_type,
col.is_nullable,
col.column_default,
pgd.description AS column_comment
FROM information_schema.columns col
JOIN pg_catalog.pg_class cls ON cls.relname = col.table_name
JOIN pg_catalog.pg_namespace ns ON ns.oid = cls.relnamespace
LEFT JOIN pg_catalog.pg_description pgd ON (
pgd.objoid = cls.oid AND pgd.objsubid = col.ordinal_position)
WHERE ns.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name, table_name, ordinal_position;


This statement returns one row per column and pulls in human-written comments stored via COMMENT ON COLUMN. By piping its results into Markdown, HTML, or a spreadsheet you already have a basic dictionary.

Capturing Constraints and Indexes

Primary & Foreign Keys

SELECT
tc.table_schema,
tc.table_name,
tc.constraint_type,
kcu.column_name,
ccu.table_schema AS referenced_schema,
ccu.table_name AS referenced_table,
ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type IN ('PRIMARY KEY','FOREIGN KEY');


Indexes

SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog','information_schema');


Adding Business Context with Comments

PostgreSQL exposes the COMMENT command that attaches free-text descriptions to any object. Encourage your engineers to annotate tables and columns at creation time:

COMMENT ON TABLE users IS 'Application end-users. Source: Auth0 webhook';
COMMENT ON COLUMN users.created_at IS 'UTC timestamp when the account was provisioned';


Because our dictionary query pulls pg_description, these remarks appear automatically.

Automating Delivery

1. Wrap the SQL in a script (.sql file, dbt model, or bash psql -c).
2. Export to CSV, JSON, or Markdown using psql --csv or \\copy.
3. Render via static-site generator (MkDocs, Docusaurus) or BI tool.
4. Schedule in CI (GitHub Actions, Airflow, dbt Cloud) so docs refresh after every migration.

Best Practices

• Version comments in migrations: treat COMMENT statements like schema code so reviews ensure quality.
• Scope queries by namespace: exclude system schemas to avoid noise.
• Snapshot as of release: for regulated environments, archive the dictionary with each deploy tag.
• Surface in your SQL editor: tools such as Galaxy surface comments in autocomplete, so authors see definitions inline.

Common Mistakes and How to Fix Them

1. Relying Only on information_schema

Why it’s wrong: You lose PostgreSQL-specific gems like comments and index definitions.
Fix: Join with pg_catalog as shown above.

2. Forgetting to Rebuild After Migrations

Why it’s wrong: The dictionary drifts, misleading users.
Fix: Hook regeneration into CI or migration runners.

3. Querying Production During Peak Hours

Why it’s wrong: Although metadata queries are cheap, they still touch system catalogs and can create locks under heavy DDL churn.
Fix: Run on a read-replica or off-hours schedule.

Putting It All Together

With fewer than 100 lines of SQL you can programmatically extract every structural and descriptive detail of your PostgreSQL database, export it in human-friendly form, and refresh it automatically. The result is a living data dictionary that scales with your organization and guards against knowledge loss.

How Galaxy Helps

Galaxy’s desktop SQL editor displays table and column comments directly in its sidebar and autocomplete pop-ups, so engineers see the data dictionary as they write queries. The AI Copilot can even suggest missing COMMENT statements or generate full documentation queries similar to those above. By saving the dictionary extraction SQL in a shared Collection, teams can endorse it and rerun with one click—eliminating outdated wikis forever.

Next Steps

• Add COMMENT coverage to all critical schemas.
• Commit the dictionary extraction query to version control.
• Automate nightly exports to your documentation site.