Documenting a PostgreSQL Database

What Database Schema Documentation Should Contain

Effective PostgreSQL schema documentation captures the complete structural and semantic blueprint of your database. Rather than functioning as a manual for PostgreSQL server administration, schema documentation details the specific tables, columns, constraints, and relationships that power an application. Database architects rely on this metadata to maintain structural consistency, audit security policies, and onboard engineers without ambiguity.

Before establishing structural models or applying database normalization rules up to the 3rd normal form (3NF), teams must establish standard definitions for every relational asset. Normalization is the process of designing a relational database so that it avoids the anomalies that arise from the common database actions INSERT, UPDATE and DELETE[1]. Getting there means the entities, relationships and data types have to be written down explicitly first, which is what schema documentation is for.

• Entity definitions: Table names, purpose statements, and functional ownership within the business domain.
• Attribute metadata: Column names, explicit PostgreSQL data types, default values, and nullability constraints.
• Key constraints: Primary keys, composite keys, and unique indexes that enforce record uniqueness.
• Referential integrity: Foreign key constraints, target reference tables, and cascading delete or update actions.
• Indexing strategies: Standard B-tree, GIN, GiST, or BRIN indexes configured for query optimization.
• Operational metadata: Triggers, stored procedures, table partitioning schemes, and object-level permissions.

Standardizing these components ensures that downstream architectural audits, schema migrations, and API integrations proceed against verified structural contracts rather than unverified assumptions.

In-Database Comments: The COMMENT ON Command

PostgreSQL provides native data dictionary support via the SQL standard extension command COMMENT ON. This command attaches arbitrary documentation strings directly to database objects inside the system catalogs, so the descriptions travel with the live schema instead of drifting in a wiki. Write the statements once, keep them in a versioned .sql file and apply them with psql -f, and every later reader of the catalog picks the same text up: psql, your own queries, and DbSchema when it reverse-engineers the database into a model.

Only one comment string is stored for each database object: issuing a new COMMENT command for the same object replaces the existing comment, and specifying NULL or an empty string removes it[2]. Comments are also dropped automatically when their object is dropped.

1. Comment on a table: COMMENT ON TABLE orders IS 'Stores customer order headers and settlement states.';
2. Comment on a column: COMMENT ON COLUMN orders.total_amount IS 'Final order cost in cents, including tax and discounts.';
3. Comment on an index: COMMENT ON INDEX idx_orders_customer_id IS 'B-tree index supporting customer order lookups.';
4. Remove a comment: COMMENT ON TABLE orders IS NULL;

There is presently no security mechanism for viewing comments: any user connected to a database can see all the comments for objects in that database, and comments on shared objects such as databases, roles and tablespaces are readable from any database in the cluster. Never put credentials, tokens or personal data in a comment literal.

Building a Data Dictionary: Querying pg_catalog

PostgreSQL stores all user comments and structural definitions within internal system tables. Architects can programmatically extract these descriptions to build automated data dictionaries, export metadata pipelines, or verify documentation coverage across deployment environments.

The primary catalog for object annotations is pg_description[3]. This table stores comments using four core columns: objoid (the OID of the object), classoid (the OID of the system catalog table containing the object), objsubid (the column index number for column comments, or 0 for tables), and description (the arbitrary documentation text).

To extract a complete data dictionary of public tables, columns, and descriptions, join pg_description against pg_class, pg_attribute, and pg_namespace:

• SELECT c.relname AS table_name, a.attname AS column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, d.description FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped LEFT JOIN pg_catalog.pg_description d ON d.objoid = c.oid AND d.objsubid = a.attnum WHERE n.nspname = 'public' AND c.relkind IN ('r', 'v', 'm') ORDER BY c.relname, a.attnum;

File-Based psql Documentation

The psql interactive command-line interface provides built-in utilities to inspect database structures and export documentation directly from terminal workflows. Using metadata commands, developers can extract complete schema descriptions into portable text and HTML files without external dependencies.

Connect to your database instance using standard client parameters on default port 5432[4]. The expanded inspection command \d+ displays column data types, modifiers, storage strategies, and all associated descriptions attached via COMMENT ON.

1. Connect to the target database: psql -h localhost -p 5432 -U postgres -d enterprise_db
2. Inspect a table with descriptions: \d+ public.orders
3. Output table metadata directly to a text file: \o /tmp/orders_schema.txt followed by \d+ public.orders
4. Run a batched SQL documentation script: psql -h localhost -p 5432 -U postgres -d enterprise_db -f generate_dictionary.sql -o schema_report.txt

Two more meta-commands answer what usually sends you to a second tab. \l lists every database on the server, including any you have just built with CREATE DATABASE, and \c switches the session to one of them, because PostgreSQL has no USE statement. Inside a database, \dn lists the schemas and \dt lists the tables. Running the same script from a continuous integration job keeps the raw data dictionary dump current for developers who work strictly in the terminal.

Visualizing the Schema: Creating ER Diagrams

Text-based data dictionaries capture precise column details, but large enterprise schemas require visual representation to convey multi-table cardinality and inheritance hierarchies. Visual Entity-Relationship ( ER diagrams) allow architects to evaluate relational patterns rapidly.

DbSchema draws the diagram from the same metadata: it connects over JDBC and reads the PostgreSQL system catalogs, which are the place where the server stores schema metadata such as information about tables and columns, and which are themselves regular tables[5]. Primary keys, foreign key references and table namespaces all come out of that read, which is why the connectors on the diagram match the constraints the server actually enforces rather than a hand-drawn guess.

• Reverse-engineer live PostgreSQL schemas: DbSchema connects over JDBC, reads the catalog definitions and lays the tables out automatically. Connecting and reverse-engineering are free in the Community Edition.
• Organize sub-diagrams: Divide large production databases with hundreds of tables into modular layout groups by business context (such as billing, inventory, or authentication).
• Persist offline design models: DbSchema saves the schema to a local, human-readable XML model file that you can version-control in Git and edit with no server reachable. Saving the model to a file is a Pro Edition feature.

Visual modeling clarifies complex join paths, helping teams identify redundant foreign keys, missing indexes and unindexed relations before deploying structural changes to production. On a large PostgreSQL schema the diagram doubles as an index of what exists, which a data dictionary alone does not give you.

Exporting Interactive HTML Documentation

Sharing database documentation across engineering, product and analytics teams requires a format that needs no proprietary viewers or database client installations. DbSchema exports the model as HTML5, PDF or Markdown; the HTML5 output is a self-contained page that opens in any browser, with no DbSchema installed and no licence on the reader's machine. Generating the export is a Pro Edition feature and is covered by the 15-day Pro trial, while connecting to PostgreSQL, reverse-engineering it and reading the diagram stay free in the Community Edition.

The generated documentation opens on a vector ER diagram where the table and column comments surface instantly as mouse-over tooltips. Under it, a per-table data dictionary writes out every column with its full definition, then the indexes, then the foreign keys with their on-delete actions spelled out, and each referenced table name is a working in-page link. Readers move through all of it without installing software or holding database credentials. You can inspect an active layout in the sample HTML5 documentation.

1. Open the PostgreSQL model in DbSchema, either freshly reverse-engineered or reopened from a saved .dbs file.
2. Choose Diagram > Export HTML5/MD/PDF Documentation, or press Cmd+D, to open the Schema Documentation dialog.
3. Choose HTML5 as the export format and specify the output destination directory.
4. Tick the content the export should carry: Text Documentation, Table of Contents, Diagram Vector Image, Image Mouse-Over Tooltips, All Columns, Indexes, Foreign Keys, Triggers, Sequences and the rest.
5. Publish the exported HTML assets to an internal web server, documentation portal, or static hosting bucket.

The export is a folder of ordinary files, so it goes wherever your team already reads things: an internal web server, a documentation portal, a static hosting bucket, or committed next to the schema in Git. Choosing a database documentation tool that writes plain files, rather than locking the output inside a viewer, is what makes that possible.

Keeping PostgreSQL Documentation in Sync

Database documentation quickly loses value when DDL modifications occur without updating data dictionaries and diagram files. Schema drift occurs when migrations alter columns, add constraints, or drop tables while central documentation remains unchanged.

Maintaining accurate documentation requires integrating schema comparison workflows into database change management. DbSchema's two-way schema synchronization resolves drift by comparing the offline XML design model directly against the live PostgreSQL database catalog and generating the migration script in whichever direction you need. Schema synchronization is a Pro Edition feature.

Download DbSchema and open the model against your own database. Connecting to PostgreSQL, reverse-engineering the schema and reading the ER diagram are free in the Community Edition; saving the model offline, synchronizing it against the live database and generating the HTML5, PDF or Markdown documentation are Pro Edition features, and the 15-day Pro trial covers them while you evaluate.

Frequently asked questions

How do I view table comments in PostgreSQL?

You can view table and column comments directly in the terminal using the PostgreSQL psql client. Connecting via the default port 5432 and executing the \d+ command displays all columns alongside any descriptive comments you previously added with the COMMENT ON command.

Where does PostgreSQL store database comments?

PostgreSQL stores all object comments internally in the pg_description system catalog. This catalog contains 4 columns (objoid, classoid, objsubid, and description) and associates your descriptive text with the physical tables and columns tracked in pg_class.

Can I generate HTML documentation from a PostgreSQL database?

Yes. DbSchema reverse-engineers the existing schema and exports it as interactive HTML5, PDF or Markdown documentation. The HTML5 file carries the ER diagram as vector SVG plus a per-table data dictionary, and it opens in any browser with nothing installed. Generating the export needs the Pro Edition or its 15-day trial; connecting and diagramming are free in the Community Edition.

What is the PostgreSQL COMMENT ON command?

The COMMENT ON command allows developers to attach explanatory text directly to database objects. PostgreSQL stores only 1 comment string per object; issuing a new command overwrites the old text, and passing a NULL value removes the comment entirely.

Does database documentation stay in sync with schema changes?

Not automatically. As developers apply migrations and update tables, manual documentation drifts. DbSchema's schema synchronization, a Pro Edition feature, compares your offline design model file against the live PostgreSQL database and generates the migration script, so you regenerate the documentation from a model that matches production.

Sources

1. cvw.cac.cornell.edu
2. postgresql.org
3. postgresql.org
4. postgresql.org
5. postgresql.org