How to Document a Postgres Database

Document a Postgres database in two layers. Write the descriptions as SQL COMMENT statements in a .sql file, and apply that file with psql -f, so the documentation is version-controlled with the schema. Then connect DbSchema and export those comments plus the diagram as one interactive HTML5 file anyone can open in a browser.

A Postgres database often outlives the people who built it. Tables get added. Columns get renamed. Reasons get forgotten. Documentation is how that knowledge survives.

Document a Postgres database in four steps

1. Write the descriptions. COMMENT ON TABLE and COMMENT ON COLUMN statements go into a .sql file that sits next to your migrations.
2. Apply the file. Run it with psql -f against the database, in a single transaction, so the comments land as a unit.
3. Connect DbSchema to the database. It reverse-engineers the schema and picks up the comments that are already in it.
4. Export the documentation. DbSchema Pro writes an interactive HTML5 file that holds the diagram, the tables and every description, and it opens in any browser with no license needed to read it.

Steps 1 and 2 need nothing but psql, and the next section covers them in full. Steps 3 and 4 add the diagram, the search and a single file you can hand to a business analyst. Still choosing a tool? Compare the database documentation tools first. Documenting a model that has no database behind it yet? Start from logical design documentation instead.

1. Why database documentation matters

A database is shared by many people. Developers write queries against it. Business analysts build reports from it. QA tests against it. New hires need to understand it fast.

Without documentation, all of that knowledge lives in people's heads. When someone leaves the company, their knowledge leaves with them.

Shared documentation turns tribal knowledge into a company asset. It also speeds up onboarding. A new developer can read table and column descriptions instead of asking around.

Good documentation reduces mistakes too. If a column's purpose is documented, it's less likely somebody drops it by accident.

In this article we'll use a small sports database as an example. It has three tables: teams, players, and matches.

2. Generating documentation with psql

PostgreSQL attaches descriptions to database objects with the COMMENT command[1]. Keep those statements in a .sql file and apply the file with psql -f, and the documentation is version-controlled with the schema instead of typed once and lost. The commands below were checked against PostgreSQL 18[2], and they are unchanged since PostgreSQL 13[3].

-- documentation.sql -- keep this file in version control, next to your migrations.
-- Apply it with:
--   psql -d sports_db -v ON_ERROR_STOP=1 --single-transaction -f documentation.sql

COMMENT ON TABLE teams IS
  'Sports teams participating in the league';

COMMENT ON COLUMN players.position IS
  'Player role on the field, e.g. goalkeeper, defender';

-- One line per object, same command. Uncomment and extend for the rest of the schema:
-- COMMENT ON SCHEMA public             IS 'Sports league: teams, players, matches';
-- COMMENT ON TABLE  players            IS 'Players registered with a team';
-- COMMENT ON TABLE  matches            IS 'Matches scheduled or played between two teams';
-- COMMENT ON COLUMN players.team_id    IS 'Team the player is registered with';
-- COMMENT ON COLUMN players.jersey_number IS 'Shirt number';
-- COMMENT ON COLUMN teams.founded_year IS 'Year the club was founded';
-- COMMENT ON COLUMN matches.match_date IS 'Kick-off date';
-- Views, indexes, constraints, functions and sequences take the same command.

-- Re-running the file is safe: only one comment is stored per object, so a second
-- COMMENT on the same object replaces the first.
-- Remove a description again by setting it to NULL:
-- COMMENT ON COLUMN players.position IS NULL;

Apply the file from a terminal. The same psql call reads the descriptions back afterwards, which is the fastest way to confirm every statement landed.

# Apply the file. ON_ERROR_STOP=1 with --single-transaction means either every
# comment lands or none of them do.
psql -d sports_db -v ON_ERROR_STOP=1 --single-transaction -f documentation.sql

# Read the descriptions back. Each -c takes a single backslash command.
psql -d sports_db -c '\dt+'          # every table, with its description
psql -d sports_db -c '\d+ players'   # one table, with a description per column

\dt+ lists each table with its description. \d+ players prints the comment beside each column of one table. The output below has been edited by removing some columns so it fits more comfortably in a browser; both commands print more columns than shown here.

Assuming we followed this process thoroughly for the rest of the tables and their columns, at this point, the database is documented. But the documentation lives inside the database itself.

To read it, someone has to connect with psql and run commands for every table and column, one at a time. There's no diagram. No overview. No way to browse visually.

Sharing this with a non-technical teammate, like a business analyst, is hard. They would need database access and psql knowledge just to read a description.

4. Interactive HTML5 documentation with DbSchema

DbSchema can generate the documentation for our database as an interactive HTML5 page, all contained in one file.

Here's what it can do:

• Open in any browser. No server, no plugins, no DbSchema license needed to view it.
• Share it on a web server. Put it on an internal server and the whole team can browse it from a link. View Sample
• Hover for tooltips. Hovering over a table or column shows its description as a tooltip.
• Click to navigate. Clicking a table or column jumps to its full description in the page.
• Vector diagrams. The schema diagram is an SVG image, so it stays sharp at any zoom level and the file stays small, much smaller than a PNG screenshot.

It includes a diagram of the schema, plus (by default) every table and column description. The rest of this article shows how to customize it and generate it.

Before we move on to that, it's worth noting that the column description we previously entered via a SQL COMMENT command on players.position was automatically picked up when DbSchema connected to the database, and so included in the HTML documentation. DbSchema thus integrates previous documentation efforts well.

5. Connecting DbSchema to Postgres

Open DbSchema and create a new connection. Choose PostgreSQL as the database type.

DbSchema includes the PostgreSQL JDBC driver, so there's nothing extra to install.

Enter the host, port, database name, and credentials, then connect.

DbSchema reverse-engineers the schema and shows the teams, players, and matches tables, with their columns and foreign keys.

6. Adding comments directly in the diagram

Double-click a table or column in the diagram to open its properties, and type a description.

This is the same kind of comment as COMMENT ON in psql, but added visually, without writing SQL.

The small callout icon next to players.position or teams.city (after our addition) gives a quick visual indication that a comment is present on that column.

7. Export the HTML5 documentation

Once the comments are ready, open the documentation export dialog from the Diagram menu.

Choose HTML5 as the format, pick a file name, and export.

In the documentation export dialog, you can select what kinds of database objects to include in the generated documentation. And in the same dialog, you can also choose one or more layouts to include in your documentation export.

8. Using multiple layouts (diagrams)

A layout in DbSchema is a diagram tab. You can drag tables onto a layout to create a diagram.

A project can have many layouts. Each one can show a different part of the schema. For example, one layout could show all three tables for a general overview. Another layout could focus only on teams and players.

The same table can appear in multiple layouts. This lets you organize documentation by topic, not just by schema structure.

Each layout can be exported to its own separate HTML file. This way you can show more or fewer details of your schema to different stakeholders, depending on their need to know.

You can also include multiple layouts in the same HTML file. This is particularly useful in larger projects with many tables.

9. PDF and Markdown documentation

The same export dialog can also produce a PDF or a Markdown file. All three formats are covered side by side in the guide to generating database documentation.

The PDF is a printable report, useful for audits or formal reviews.

The Markdown file lists each table as a section, with columns, types, and descriptions in a text table. It's a good fit for a GitHub repository or a wiki page.

Conclusion

Documenting a Postgres database starts with COMMENT ON TABLE and COMMENT ON COLUMN, applied from a file with psql -f. That's a good first step, but it stays locked inside the database.

DbSchema builds on the same idea: descriptions on tables and columns. The difference is that it turns them into a diagram-based, interactive document that anyone on the team can open and understand. For a second worked example on Postgres, read how to document a PostgreSQL schema.

Download DbSchema and point it at your own Postgres database. The HTML5, PDF and Markdown documentation described here is in the Pro edition, and the Pro trial runs 15 days.

Sources

1. PostgreSQL 18 documentation: COMMENT
2. PostgreSQL 18 documentation: psql
3. PostgreSQL 13 documentation: psql