A schema you did not write

The symptom

The explorer lists audit, billing, catalog, commerce, crm and identity. Fourteen tables, a few views, a few functions. Opening them one at a time tells you what columns exist, not which three tables matter, which way the arrows point, or what breaks if you drop something. An hour in the right order answers all three.

The diagram first

Press Mod-Shift-e, or pick Open ER diagram… from the command palette, and choose commerce. The diagram is per schema. It draws the schema's own tables and pulls in the tables from other schemas that a foreign key reaches, counted as linked tables in the header.

The figure opens in diagram mode. The design mode it can step into is another page, Design a schema change. For this hour, stay in the diagram.

Set the columns mode to Keys only. Every table shrinks to its primary key, unique keys and foreign keys, which is all you need to see the shape. Find table… jumps to a table when the canvas outgrows the screen.

Click a table. The inspector on the right shows Columns and Est. rows, then Keys & Invariants, PK and UQ rows with the constraint name, then Relationships. Each relationship is a card tagged Outbound FK, Inbound FK or Self-ref, with the constraint name and both tables with their columns. Its rules line reads Del: and Upd: with the referential action, then Validated or Not validated, and Deferrable when it is. Open table goes to the rows.

Find the hubs

Count inbound edges. The hub is the table everything points at, here crm.customers: commerce.orders.customer_id, billing.invoices.customer_id and identity.accounts.customer_id all land on it. Select it and the Inbound tab under Relationships lists them. commerce.orders is the second hub: commerce.order_items and billing.invoices point at it.

Then the tables nothing points at. commerce.order_items, billing.invoice_lines, billing.payments and audit.change_log have no inbound edges. They are leaves: detail rows, logs, history. Nothing downstream breaks when you change them.

Then the odd one. Open the diagram for catalog. catalog.categories has an edge that leaves the table and comes back: parent_id references categories.id, and the inspector tags it Self-ref. That is a tree in one table, 310 rows of it, and any query for a category's ancestors will be recursive.

The three reports

Under each schema in the explorer, Schema reports: Columns, Indexes, Constraints. They read the whole schema at once.

Columns has four views, All, Mixed types, FK without index and Mostly empty, from pg_stats and the catalog. Mixed types finds a column name declared integer in one table and bigint in another, which will bite in a join. FK without index finds the foreign keys whose deletes will scan. Mostly empty finds columns nobody fills in. All three are things the person who left knew and did not write down.

Indexes has Map and Table views, a Needs a look list of indexes that are unused, covered by another or invalid, and Other checks. On billing, Other checks counts one foreign key without an index and names it: payments.invoice_id. Write it down. It is the whole of Why does this DELETE take three minutes?.

Constraints lays every table out as Points at it, Table and its rules and Points to, with its own Needs a look count for rules added NOT VALID.

Open three tables

Press Mod-p for Open table… and open crm.customers, commerce.orders and billing.invoices. The two hubs and the thing that gets paid: every ticket will touch one of them.

The status bar on each shows an estimate, of ~12M on orders. Click Count for the exact number when it matters. Then read ten rows with the value inspector: select a cell and press Space. It decodes a timestamp into UTC, ISO 8601 and Unix seconds, and tells you which UUID version stores.id is. It opens attributes on customers as a JSON tree, and says under In this table how many loaded rows share the value. Ten rows read this way tell you what status holds and whether total_cents is really cents.

The first query

Write it against a hub, not a leaf:

SELECT status, count(*)
FROM   commerce.orders
WHERE  placed_at >= now() - interval '30 days'
GROUP  BY status
ORDER  BY 2 DESC;

If it is slow, the next page is Why is this query slow?. If it is fast, you have your first fact about the business: how many orders a month, in which states.

What to write down after the hour

Four lines, in a file next to the ticket:

• The hubs: crm.customers, commerce.orders, billing.invoices, each keyed on id.
• The audit trigger: orders and invoices both write to audit.change_log on every change. Every UPDATE you run is recorded there, 4,000,000 rows and counting.
• The one foreign key with no index: billing.payments.invoice_id.
• The view nobody understands: billing.monthly_revenue, over invoices, payments and identity.accounts. A view, billing.revenue_dashboard, and a function, billing.export_revenue(), depend on it, and a materialized copy, billing.monthly_revenue_mv, is refreshed by billing.refresh_revenue() as reporting_owner. The view page, which is Pro, shows this under Used by. It is why you do not rename a column in invoices on day one. See What a view page tells you.

Caveats

• The diagram draws declared foreign keys only. A relationship the schema never declared is invisible: a column called customer_id in a table with no edge to customers looks like any other column. Spot one by its name, then confirm on that table's Constraints page, which will say Points to no other table.
• Est. rows in the inspector and of ~12M in the status bar are catalog estimates from the last ANALYZE. Count is exact.
• The layout is saved per diagram in the app's browser store. Tables you move stay moved on this computer, and nowhere else.
• MySQL, MariaDB and SQLite draw the diagram and have the Columns, Indexes and Constraints reports. The inspector's column facts come from MySQL 8 histograms, and MariaDB has none. See MySQL, MariaDB and SQLite.