How to Visualize a PostgreSQL Database Schema as an ER Diagram

Visualizing a PostgreSQL database schema as an entity-relationship diagram remains one of the fastest ways to understand a functional domain. When you join a project, audit an existing database, or prepare a migration, the ER diagram gives you an overview that reading raw CREATE TABLE statements does not provide.

Several desktop and web tools can produce this kind of diagram. They are not all equal, and the right choice comes down to a simple question: do you want to see the actual schema as it exists in the database, or a model you maintain in parallel?

Declared schema or inferred schema

Two approaches coexist. The first is to write the model in a dedicated format such as DBML, then generate the diagram from that file. This is useful for designing a database before it exists, but the diagram quickly drifts from reality if the database evolves without a parallel update to the file.

The second approach is to introspect the database directly. The tool reads information_schema and pg_catalog, collects the tables, columns, primary keys, and foreign keys, then draws. The diagram reflects the exact state of the database at that moment. This is the approach used by pgAdmin, DBeaver, DataGrip, and QoreDB.

Free and open source tools

Since version 6, pgAdmin ships with an ERD Tool. It works and remains the natural option when you already work in pgAdmin. The automatic layout is rudimentary and the rendering isn't designed to explore a database with several hundred tables, but for a medium-sized schema it gets the job done.

SchemaSpy is a Java command-line tool that generates a static HTML site documenting the schema. It produces Graphviz diagrams and runs against any JDBC database. The report is rich, with detailed relationships, types, and constraints. The workflow is heavier: you need a JDK, a PostgreSQL JDBC driver, and then you open the HTML in a browser.

DBeaver Community offers an ERD viewer built into the client. It introspects the database, supports foreign keys, and lets you hide tables. The exploration experience stays dense, especially on large schemas where the rendering loads the entire diagram before any filtering.

Proprietary tools

JetBrains DataGrip offers a contextual diagram for a given table or an entire schema. The introspection quality is good, the rendering is legible, and the integration with the SQL editor is consistent. It's a paid option, integrated into the IntelliJ ecosystem.

dbdiagram.io runs in the browser on top of a homegrown DSL, DBML. It's effective for producing a design diagram to share in documentation. It doesn't connect to a PostgreSQL database to read its actual state: you have to import a SQL dump or write the DBML by hand.

QoreDB's approach

QoreDB includes an interactive ER diagram built around native introspection through the PostgreSQL driver. When the view opens, the client loads a schema's tables in parallel, reads their columns, primary keys, and foreign keys via information_schema, then assembles the diagram. Everything happens locally; the application pushes no metadata to any third-party service.

The rendering is deliberately compact. Each table is a node, with its key columns shown first: primary key, outgoing foreign keys, incoming foreign keys. The other columns become visible by expanding the table. This rule avoids saturating the screen on wide schemas, where displaying every column of every table makes reading impossible.

The edges between tables are computed from the FKs declared in the catalog. QoreDB also adds support for Virtual Relations: relationships defined on the client side for cases where the database doesn't declare its FKs. This is useful on legacy databases where constraints are enforced at the application level and never materialized in the schema.

The view can be manipulated with keyboard and mouse: zoom, pan, search by table name, isolating a table and its neighbors to focus on a subset. The layout logic arranges tables into balanced columns, without asking the user to place each node manually.

A concrete workflow with QoreDB

The typical use begins by opening an existing PostgreSQL connection. From the object tree, you select the schema to visualize and open the ER view. Loading is progressive: an indicator shows the number of introspected tables in real time, with a default concurrency of six tables in parallel to limit the load on the server.

Once the diagram is assembled, you filter by name to isolate a coherent subset, for example all the tables tied to a business domain. You can click a table to open its contents in a query tab or in the Data Grid. It's this loop, visual exploration then targeted action, that makes the diagram useful day to day rather than just an image to archive.

For databases where some FKs are missing, you declare Virtual Relations from the dedicated panel. They are stored in the local configuration and appear in the diagram as edges distinct from the real FKs. The database isn't modified, and the relationships stay on your workstation.

Choosing based on context

The right tool for generating a PostgreSQL ER diagram depends on the use case. For an initial design to share in an RFC, dbdiagram.io remains handy. For a complete report to archive, SchemaSpy does the job. For day-to-day exploration of a real database, an integrated desktop client like DataGrip, DBeaver, or QoreDB is more efficient: the diagram is always up to date with the living schema, and it serves as an entry point to queries rather than a frozen artifact.