How to Document PostgreSQL Databases Effectively

If you are looking for the official PostgreSQL manual, you can find it on their website. However, if your job is producing readable, up-to-date documentation of an existing PostgreSQL database schema, you are in the right place. Database documentation is a vital yet often overlooked part of every IT project. Keeping it updated has to be a consistent effort from the team, as it serves as the blueprint for your data.

What is database documentation?

Good database documentation can be compared with the blueprint of a building. It contains every aspect of the database, the indexes, tables, columns, data-types, etc. Each of them with comments and callouts, making it easier to understand the role of every database object. Most of the time, the documentation is the common ground between the development or testing teams and the business teams.

The documentation can reduce risks during maintenance because the team can follow the implications of every action. Because it facilitates the transfer of information, good documentation will also reduce the time (and cost) of newcomers training.

The documentation has a bigger impact and a longer lifespan than one might expect. Of course, the case varies from project to project, but most often a database will be accessed by more than just the development or project management team. It can be an outside BI Consultant or an Auditor that has nothing to do with your project and having good documentation can save time and effort.

What good schema documentation contains

Before choosing a method, it helps to know what the finished thing has to contain. A complete pass over database documentation for a PostgreSQL schema covers all five of these:

• Table and column descriptions: Clear definitions of what data is stored.
• Entity-Relationship (ER) diagrams: Visual representations of how tables connect.
• Primary and foreign keys: Constraints that enforce data integrity.
• Indexes and triggers: Underlying logic that affects performance and behavior.
• Data types and default values: Technical specifications for developers.

Adding comments with COMMENT ON

The way of documenting the database plays an important part in making it a constant task. To document a PostgreSQL database from the shell, you can use the COMMENT command. This will add descriptions to your columns, tables, indexes, etc. COMMENT is a custom Postgres command and can’t be used as a standard SQL query.

To view data and add comments to it, you first have to access the PostgreSQL shell. Once there, execute \d+ to view all your tables with descriptions. Executing a regular \d command will only show a few details about your tables leaving out the description.

As we can see above, the actor table doesn’t have any description. To insert a comment, we can execute the next command:

COMMENT ON TABLE actor IS ‘Table containing actors’;

The result will be:

As we can see, the description of the table was updated. Let’s do another example, but this time for a column. To view the columns of a table, we will execute:

\d+ actor

It’s similar to the command from above, the only difference is that we specify the name of the table at the end. This should give us:

Now, I will add a comment to the actor_id column by executing:

COMMENT ON COLUMN actor.actor_id IS ‘id of the actor’;

And the result:

Similarly, you can add a comment on indexes. Instead of \d+ you will use \di+ and COMMENT ON INDEX index_name IS comment;

Building a data dictionary from the catalog

Another method is querying the catalog directly to build a data dictionary. PostgreSQL stores metadata in the pg_catalog and information_schema views. You can write SQL queries against these views to extract a list of tables, columns, and data types. While effective, writing these queries manually is tedious and doesn't provide visual context like Entity-Relationship (ER) diagrams.

Document visually with an ER diagram

You can do both things (documenting & exporting) from a single place and without too much trouble. With DbSchema, you don’t need to write queries in the PostgreSQL shell; you can design a PostgreSQL schema visually and document the database from a friendly GUI.

DbSchema is a database design & management tool that allows you to interact with the diagram and edit the database objects right there. First, you have to connect it to your Postgres database. Don’t worry about JDBC drivers, DbSchema already integrates it.

After connecting, DbSchema will reverse engineer the diagram. Being interactive, the diagram makes it very easy to document your database. If you want to add comments to tables or columns, all you have to do is double-click on it and insert the comment:

Yes, it’s that simple. In DbSchema you can also insert callouts. These are general comments that will show in the diagram.

Exporting interactive HTML documentation

In DbSchema the diagram is viewed in a layout. A layout is a tab in which you can add tables, edit them, add foreign keys, etc. Basically, it is a drawing board for your database design. A single project can contain many diagrams, each focused on a specific part of the schema, and the same table can appear in more than one diagram.

This gives you the freedom to create a layout including what tables you want from the database and export an HTML5 or PDF documentation based on it. The export process is just a few clicks away.

Here you can select what to include in your documentation. After everything is set, select a place to export it and you’re done.

Connecting, reverse-engineering and editing the diagram are available in the free Community Edition. Exporting HTML5, PDF or Markdown documentation is a Pro Edition feature. The output is interactive database documentation: a vector diagram plus a per-table data dictionary.

The HTML5 documentation is interactive. Meaning that it will show details when you hover database objects like in the image above. If you click on an object, it will automatically jump to the table containing info about it. The HTML5 documentation can be opened with any internet browser.

Keeping documentation in sync after schema changes

One of the biggest challenges with PostgreSQL database documentation is keeping it accurate as the schema evolves. If you rely solely on manual exports or static data dictionaries, your docs will quickly become outdated. With DbSchema you reverse-engineer the live database again, refresh the model, and regenerate the export, so new tables, modified columns and updated foreign keys are reflected. Reverse-engineering is in the free Community Edition; the documentation export and schema synchronization are Pro Edition features.

Frequently asked questions

Is there a tool to generate database documentation automatically?

Yes. DbSchema connects to your database, reverse-engineers the schema, and generates interactive HTML5, PDF or Markdown documentation from your live tables and comments. Connecting and reverse-engineering are in the free Community Edition; the documentation export is a Pro Edition feature.

How do I list all tables and their descriptions in psql?

You can use the \d+ command in the PostgreSQL shell to list all tables along with the descriptions added via the COMMENT command.

Conclusion

Keeping the documentation updated is very important for the project. It brings benefits to the technical aspect, making it easier to understand the database, and also to the team, enhancing the communication between departments.

DbSchema makes this easier: it reverse-engineers the schema into an ER diagram, keeps table and column comments in the model, and exports the whole thing as interactive HTML5, PDF or Markdown documentation.

Download DbSchema and connect it to your PostgreSQL database. Reverse-engineering, the interactive diagram and the comment editors are in the free Community Edition; the HTML5, PDF and Markdown documentation export is in the Pro Edition, which comes with a 15-day trial.