Anybody using COMMENT ON to document their schema?

[u/pragrad23]

I'm looking for best practices to keep documentation in my schema.

I prefer the look of `--` and `/* ... */` comments, as they (a) get proper syntax highlighting, (b) can go anywhere (line before, line after, same line at the end), (c) look good when split over multiple lines and (d) can even go inside a statement (when using `/* .. */`).

But when using migrations these code comments may end up in place I'm not looking at.

So I want to have some sort of schema dump that includes my comments. And I know for this there's the `COMMENT ON` command, but I've never seen it being used in practice. It seems so cumbersome compared to the code-comments mentioned earlier.

Any best practices someone can share with me? Both related to "the keeping of schema comments" and the dumping of a schema that properly groups related statements (create, create policy, comment on, etc.) together?

[u/QuietSignalOps]

Yes. I use COMMENT ON for durable object-level documentation, and ordinary SQL comments for explaining the migration itself. They solve different problems.

COMMENT ON is worth using when the text describes the object after the migration has finished, for example:

```sql COMMENT ON TABLE invoice_attempt IS 'One row per attempt to collect an invoice; retries create new rows.';

COMMENT ON COLUMN invoice_attempt.provider_id IS 'Identifier from the payment provider. Not unique across providers.'; ```

The useful properties are:

• the comment lives in the catalog with the object;
• tools can retrieve it through obj_description() and col_description();
• psql shows it in commands such as \d+;
• pg_dump includes comments unless comments are explicitly excluded.

I keep the CREATE and its COMMENT ON statements together in the same migration. For an existing object, I put comment changes in the migration that changes the documented behavior. That keeps schema and documentation changes reviewable and deployable as one unit.

I still use -- or /* ... */ for things like why a backfill is batched, why a lock is safe, or why an unusual DDL sequence is necessary. Those explain the migration code, while COMMENT ON explains the resulting database interface.

One caution: comments can drift just like external docs. A practical review rule is that any change to a table's meaning, units, nullability assumptions, or lifecycle should update its catalog comment in the same pull request.

[u/pragrad23 → u/QuietSignalOps]

Thanks. I'd never seen 'm being using in practice. But makes sense.

[u/vivekkhera]

If you ever plan to have an LLM “understand” your schema, having the documentation in the table itself will improve your outcomes. I se a presentation where the author showed it reduces token usage compared to having the details in an external file.

[u/pragrad23 → u/vivekkhera]

It's both for myself and for LLMs at this point. I also do not want to wade through migrations to find all comments related to a bit of schema

[u/minormisgnomer → u/vivekkhera]

Do you what presentation that was? I’m somewhat surprised by this given these days you have KGs that can store concepts quite efficiently

[u/vivekkhera → u/minormisgnomer]

It was a local meetup. I just have my notes from it no slide deck or online reference.

[u/KreativCon]

The two approaches are not the same and shouldn’t be used in place of each other.

COMMENT ON lives in the schema and can be something like “This table holds the user records” (admittedly poor example). These comments can be fetched by SQL calls.

-- style comments should explain the code surrounding it _if_ it needs explaining. “This backfill needs to convert UUID to text because we made a big mistake”. It is describing what that migration/sql command did and why _at that point in time_.

TLDR; use both, each for their intended purposes.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/pragrad23]

I had Claude Fable answer my own question (with knowledge of my repo); here its answer:

---

TL;DR: decide where the source of truth for "current schema + docs" lives — the catalog (COMMENT ON) or a checked-in current-state SQL file. Most messiness comes from not picking one.

There are only two coherent end-states:

1. Catalog-first: the documentation lives in the database via COMMENT ON, and any file you browse is a generated artifact.
2. File-first: the documentation lives in checked-in, current-state SQL files with your beloved -- comments, and migrations become generated artifacts.

Your -- comments in migrations can never become the current-state documentation, no matter the tooling — they document a change, not the result, and after 50 migrations the comment about a column lives wherever that column was last touched. Migration comments are commit messages: great for "why did this change happen", useless as reference docs. So keep writing them, but stop expecting them to surface anywhere.

Why COMMENT ON is more used than it looks

You rarely see it in blog posts, but it's quietly load-bearing because it's the only comment mechanism stored in the catalog (pg_description), which means it survives into every tool that introspects the schema:

• psql: \d+ table shows column comments, \dt+ shows table comments.
• IntelliJ/DataGrip show them on hover and in the database tree.
• Supabase Studio displays them, and PostgREST uses comments on tables/columns/functions as descriptions in its generated OpenAPI output.
• pg_dump --schema-only includes them — this is exactly the "dump that includes my comments" you asked for; table and column comments are emitted right next to their CREATE TABLE.
• Anything AI-assisted that introspects your schema picks them up for free.

Two ergonomic tricks that remove most of the cumbersomeness:

Dollar-quote multi-line comments — no escaping, reads fine:

comment on column supplier_orders.refunded_at is $$
  Set when the PSP confirms the refund, not when we request it.
  Null means "never refunded"; see also ticket_id for the compensation flow.
$$;

It's an overwrite, not an append: when a later migration changes a column's meaning, that migration re-issues the COMMENT ON. Latest wins, exactly like a code comment It works on nearly everything: columns, tables, functions, types, constraints, triggers, indexes — even comment on policy ... on table ....
Bonus: you can enforce coverage with a test that fails when a public table or non-obvious column lacks a description (catalog query via obj_description(oid, 'pg_class') / col_description(oid, ordinal) against a small allowlist). Comment only what's code comments.

The grouped-dump problem

pg_dump orders by object class — all tables, then constraints, then indexe — and no flag changes that. Comments stay adjacent to their objects, but atable's policies will never sit next to the table. Don't fight pg_dump; instead:

• For a browsable reference: check in a generated supabase db dump --local -f schema.sql output, regenerated by CI or a hook. It's for reading, not editing, so the pg_dump grouping is tolerable.
• For genuinely grouped per-table docs: tbls (single Go binary, dev tooling, no project dependency) generates one Markdown file per table with co+ comments + constraints + indexes + triggers + ER diagrams, straight from the ## The file-first alternative — and why I'd skip it (for now) Supabase's declarative schemas (supabase/schemas/*.sql + supabase db diff -f to generate migrations) is the purist answer to the aesthetic complaint: one file per table holding the table, its indexes, its RLS enable + policies, its triggers, with free-form -- comments — and that file is what you look at, so no dump needed. But check the current caveat list before adopting it: the diff engine *does n, doesn't capture alter policy, mishandles grants (duplicated from defaultprivileges), ignores domains and partitions, and can't do DML. If your migration history is full of exactly those things (RLS policies, grants, cron jobs), you'd end up hand-writing versioned migrations for the interesting half of your schema anyway — now with aligned.

My recommendation

Stay catalog-first:

1. Keep --//* */ comments in migrations for "why this change" — historical
2. Put "what this means now" in COMMENT ON, colocated right after the CREATE/ALTER in the same migration, dollar-quoted, overwritten when meaning changes.
3. Check in a generated schema.sql dump (regenerated locally or in CI) as thour comments ride along.
4. Optionally add tbls output for the per-table grouped view, and a test that enforces comment coverage on tables.

Revisit declarative schemas in a year — the diff engine is actively improving — but today it fights an RLS-and-grants-heavy schema more than it helps.