How to Document a PostgreSQL Schema

A PostgreSQL database gives you table names and column types, and nothing about what any of it is for. To document the schema, write that purpose where PostgreSQL keeps it, then let DbSchema draw the diagram and export a document you can hand to someone:

1. Write a description for each table and column, with COMMENT ON or in DbSchema.
2. Connect DbSchema and reverse-engineer the schema into an ER diagram.
3. Export the diagram and the descriptions as HTML5, PDF or Markdown.
4. Keep the DbSchema design file in Git, and compare it with the database whenever the database changes.

Start with descriptions in the database

The examples on this page run on PostgreSQL 17.9. Two tables, a foreign key, a view and a trigger are enough to show every part of the documentation:

CREATE TABLE customer (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    tax_code    text
);

CREATE TABLE orders (
    order_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customer,
    status      text NOT NULL DEFAULT 'new',
    updated_at  timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE customer IS 'One row per account that can place orders.';
COMMENT ON COLUMN customer.tax_code IS 'Personal data. Do not copy outside the EU.';
COMMENT ON COLUMN orders.status IS 'new, paid, shipped or cancelled.';

CREATE VIEW open_orders AS
SELECT o.order_id, c.email, o.status
FROM orders o JOIN customer c USING (customer_id)
WHERE o.status IN ('new', 'paid');

CREATE FUNCTION touch_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END $$;

CREATE TRIGGER orders_touch BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

COMMENT ON stores each sentence in the database's catalog, so every program that reads the catalog finds it, DbSchema included. Read the column descriptions back with col_description:

SELECT table_name, column_name,
       col_description(format('%I.%I', table_schema, table_name)::regclass,
                       ordinal_position) AS description
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name IN ('customer', 'orders')
ORDER BY table_name, ordinal_position;

Five of the seven columns have no description yet:

A view and a trigger take a comment the same way, and it is the place for the reason behind their logic. A trigger's comment names its table too, because a trigger name is unique only within its table:

COMMENT ON VIEW open_orders IS 'Orders that still need work: new or paid.';
COMMENT ON TRIGGER orders_touch ON orders IS 'Sets updated_at on every update of an order.';

The psql side of this, with \d+ and the rules a comment follows, is covered in documenting a PostgreSQL database from psql.

Reverse-engineer the schema into a diagram

Connect DbSchema to the PostgreSQL database and it reverse-engineers the schema into an ER diagram: customer and orders as boxes with their columns, and a line for the foreign key between them. Reverse-engineering reads the catalog, descriptions included, and writes nothing to the database. What it builds is DbSchema's own design model, which you save as a .dbs file.

Then arrange the diagram for the people who will read it. Drag the tables in DbSchema where they belong, and choose Diagram → Create Group of Tables to put a module in a named box. When a large schema comes back with its tables overlapping, Diagram → Auto Arrange Tables lays them out by their foreign keys. A DbSchema project holds as many diagrams as you want, and a table can sit in several of them, so an orders diagram and a billing diagram each show what their reader needs.

The diagram is the first part of the documentation that PostgreSQL cannot store. It is not the last:

Write descriptions and tags in DbSchema

Double-click a table or a column on the diagram and DbSchema opens its dialog, with a field for the description. Where the text goes depends on the connection. Connected, DbSchema runs the statement as soon as you press OK, so PostgreSQL holds the same text as the design file:

COMMENT ON COLUMN customer.email IS 'Login name. Unique across accounts.';

Working offline, the text changes only the design file, and reaches PostgreSQL when you synchronize, as described further down. A foreign key takes a description the same way, which DbSchema writes with COMMENT ON CONSTRAINT. Every documentation format carries the descriptions, and the HTML5 output shows each one as a tooltip on the table or column name.

Beside the free text, DbSchema attaches tags: key-value pairs for what you would otherwise keep in a spreadsheet, such as the owner of a table, a sensitivity level or a deprecation date. Define a tag once, then fill it in next to the description. PostgreSQL has no place for a tag, so tags stay in the design file. They appear in the HTML5 and PDF documentation, and automation scripts can read them. Diagrams take tags too: the export can include every diagram tagged documentation, in the order that the tag's value sets.

Export HTML5, PDF or Markdown documentation

Choose File → Export As... → HTML5/PDF Documentation, or press Ctrl+D, and DbSchema asks for three things: the format, the diagrams to include, and the content, which is the columns, indexes, foreign keys, views and code that go in.

The three formats suit different readers:

HTML5 is the one to start with. The file carries the diagram next to a searchable table list. Click a table to jump to its columns, indexes and foreign keys, and from a foreign key the related table is one click away, which a screenshot pasted into a wiki cannot do.

For a PDF with Russian, Chinese or Japanese text in it, tick Embed Unicode Font in the DbSchema dialog first. Documentation in all three formats is a Pro Edition feature.

Document the views, functions and triggers

The logic in open_orders and orders_touch is the part of the schema that a newcomer cannot work out from the tables. DbSchema reverse-engineers views, functions, procedures and triggers along with the tables and lists them in the tree of the model. A view takes a description like a table, which DbSchema writes with COMMENT ON VIEW. In DbSchema's export dialog, tick View Query, Triggers, Functions and Procedures, and Procs/Triggers Text to put their definitions into the document.

A view has no foreign keys, so it sits on the diagram with no lines. Drag the email column of open_orders onto customer.email and DbSchema draws a virtual foreign key between them. It is stored in the design file and never in PostgreSQL, and it shows which table the view reads.

Keep the design file in Git

DbSchema keeps the whole design (tables, columns, foreign keys, diagrams, descriptions and tags) in one .dbs file in XML. Put that file in the repository next to the code, and the schema gets the history that PostgreSQL does not keep: who changed what, when, and on which branch.

DbSchema runs Git itself. File → Git Collaboration opens the Git dialog for clone, commit, push and pull. After a pull, Model History lists the commits that changed the design file and what each one changed, such as a comment, so you see what changed in the design before you touch the database. One repository can hold several .dbs files, one per database or per component. The Git dialog is in the Pro Edition.

When someone changes the database directly

Changes do not always start in the design. A migration, or a colleague at a psql prompt, adds a column:

ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
COMMENT ON COLUMN orders.shipped_at IS 'Set when the parcel leaves the warehouse.';

From then on, the exported documentation describes a database that no longer exists. Press Refresh in the toolbar, or choose Database → Refresh Model from Database, and DbSchema compares the design file with the database and says how many differences it found.

DbSchema's Review Differences lists them one per row: added, removed and changed tables, columns, indexes and foreign keys, and changed comments. For each row you choose the direction. Merge shipped_at into the design file when the database is right. Commit a change to the database when the design is right, and DbSchema shows the SQL before anything runs. Schema synchronization is in the Pro Edition.

Because the design file is local, the design work needs no connection. Keep working offline in DbSchema, and let the comparison catch up when you reconnect.

Download DbSchema at https://dbschema.com/download.html, connect it to your PostgreSQL database, and reverse-engineer the diagram in the free Community Edition. A new installation starts with a 15-day Architect trial, no credit card needed. The Pro Edition adds what you hand to other people: the saved design file and Git, the HTML5, PDF and Markdown documentation, and schema synchronization.