How to Document a Postgres Database

A Postgres database often outlives the people who built it. Tables get added. Columns get renamed. Reasons get forgotten sometimes.

Documentation is how that knowledge survives.

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 has a built-in way to attach descriptions to database objects: the COMMENT command. This command is alas not included in the SQL standard, but has fairly broad support in other database systems. To add a comment to a table and respectively to a column, you could run something like:

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';

3. The result so far

To see these comments from psql you'd run \dt+ to see comments on the tables. And similarly, run \d+ players to see the comments on the columns of the players table. The output below has been edited by removing some columns so it more comfortably fits in a browser. The \dt+ and \d+ psql commands will 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.
• 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.

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. 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.

Download DbSchema for free and try generating documentation for your own Postgres database.