PostgreSQL for Architecture Graphs

A graph in a relational database

Architecture models are graphs, which prompts the reasonable question of whether they belong in a graph database. For repository purposes the answer is usually no, and the reason is not performance.

It is that the workload is not really graph-shaped. The dominant operations are: load a whole model, save a whole model, list what is in a project, search by name, and traverse one or two hops from an element. Only the last is graph-flavoured, and PostgreSQL handles two-hop traversals on a properly indexed relationship table without breaking stride.

Meanwhile you get transactions, referential integrity, mature backup tooling, and a database your operations team already runs — which matters more in an enterprise than query elegance.

Index for the questions asked

Three indexes carry most of the load:

-- traversal in both directions
CREATE INDEX rel_source_idx ON relationship (model_id, source_element_id);
CREATE INDEX rel_target_idx ON relationship (model_id, target_element_id);

-- name lookup and search within a model
CREATE INDEX element_name_idx ON element (model_id, lower(name));

-- diagram contents are always fetched per diagram
CREATE INDEX dobj_diagram_idx ON diagram_object (diagram_id);

Note that every index is scoped by model_id. Almost no query in this workload spans all models without a permission filter, and leading with the model keeps the index selective as the estate grows.

The N+1 that hurts

The performance problem that shows up in practice is not traversal. It is loading a model for the client: fetch elements, then for each element fetch its properties, then for each diagram fetch its objects.

On a model with 2,000 elements that is thousands of round trips, and it turns a 200-millisecond operation into fifteen seconds. The fix is unglamorous — fetch each table once for the whole model and assemble in memory — and it is worth writing the load path that way from the beginning, because retrofitting it means touching everything.

Revisions and storage

Storing complete model content per revision rather than diffs is the right call, and the arithmetic supports it. A large architecture model serialises to a few megabytes. A thousand revisions is a few gigabytes. That is nothing against the operational cost of reconstructing states by replaying diffs, and it means a corrupted revision cannot invalidate everything after it.

Compress the stored content and store it out of the main row — PostgreSQL's TOAST handles this automatically for large values — so that queries against revision metadata do not drag content off disk.

Where it would stop scaling

Being honest about the limits. This design assumes models are bounded — thousands of elements, not millions — and that the unit of work is a model rather than the whole estate.

If you genuinely need estate-wide graph analytics — shortest paths across every model, impact analysis spanning hundreds of models — a relational core plus a derived graph or search index for those queries is a better answer than making the primary store a graph database. Keep the system of record boring and project from it.

Connection handling

A small operational note that catches people. Model loads and publishes are bursty and hold connections for the duration of a transaction. Without pooling, a handful of concurrent publishes can exhaust the connection limit on a managed instance sized for a normal web workload.

Pool, and size the pool against publish concurrency rather than request concurrency — most requests are cheap, and the expensive ones are the ones that matter here.

Soft delete and why it complicates queries

A repository that keeps history needs elements to survive their own deletion, because a revision from last year references them.

The usual answer is a soft delete — a flag rather than a removal — and it has a cost worth knowing about in advance: every query now needs the predicate, and the one place someone forgets it is where deleted elements appear in a search result.

Two things reduce the risk. Put current-state queries behind a view that applies the filter, so forgetting is harder. And separate the concept of "deleted from the current model" from "referenced by history" — the second is a property of revision content, which is immutable anyway and needs no flag.

Measuring before optimising

Performance work on this kind of system goes wrong in a predictable way: people optimise traversal because it feels like the graph-shaped part, and traversal was never the bottleneck.

Instrument three things and the answer usually becomes obvious: query count per model load, wall time for publish broken into validate, write and index, and the size distribution of models. In most deployments the expensive operation is loading a large model for the client, the cause is round trips rather than any individual query, and the fix is batching rather than indexing.

Why not a graph database

The obvious objection to a relational store for a graph is that graph databases exist. It is worth answering with the numbers rather than with preference.

Architecture estates are small. A large enterprise landscape is tens of thousands of elements and perhaps twice as many relationships. Graph databases earn their keep at scales where relational joins genuinely fall over, which is orders of magnitude beyond this. At architecture scale a well-indexed relational store answers every query fast enough that the difference is unmeasurable by a user.

Against that, a second database technology has real costs: another thing to back up, patch, monitor, tune, and hire for. In most organisations PostgreSQL is already operated by someone. That asymmetry — no measurable benefit against a permanent operational cost — is the whole argument, and it changes only if the estate reaches a size no architecture estate reaches.

Recursive queries that terminate

Dependency traversal is the query that justifies the design and the one most likely to hang, because architecture graphs contain cycles and a naive recursive query will follow one forever.

Two guards, both cheap. Track the visited path within the recursion and refuse to revisit a node, which handles cycles correctly rather than merely bounding them. And impose a depth limit regardless, because a query that legitimately reaches depth thirty is almost certainly a modelling error rather than a real dependency chain, and returning that as a result is more useful than returning ten thousand rows.

Filtering by relationship type inside the recursion rather than afterwards matters more than it looks. Following every relationship type produces a closure covering most of the estate, because association relationships connect everything to everything. Following only dependency and serving relationships produces an answer.

What to do about JSONB properties in queries

Putting organisation-specific properties in a JSONB column is the right call for schema stability and it moves the difficulty to query time, where filtering on a property is no longer a plain column comparison.

PostgreSQL handles this well provided the indexes exist. A GIN index over the whole property document supports containment queries, which covers most filtering. Where a specific property is filtered constantly — lifecycle status, almost always — an expression index on that single key is dramatically faster and worth adding once the pattern is clear.

The trap is sorting. Sorting by a JSONB value works and is slow, because the values are text and the index does not help an ordering operation the way it helps a filter. A catalogue sorted by lifecycle across the whole estate is the query that will surface this, and the fix is either an expression index or promoting that one property to a real column.

Keeping revision queries fast

Storing every revision in full is right for retrieval and it produces a table that grows without bound, which affects the queries that search across revisions rather than fetching one.

Fetching a specific revision stays fast forever — it is a primary key lookup. What degrades is anything scanning the revision table: listing history for a model, finding when an element was last changed, or comparing two arbitrary revisions.

The measures that keep those fast are ordinary. Index the revision table on model and timestamp together, because every history query filters on both. Keep the revision metadata in a narrow table with the content in a separate one, so scans over metadata do not drag megabytes of serialised model through memory. And consider partitioning by model once the table is large, which makes single-model history queries touch one partition.

Concurrency at the database level

The application enforces one lease per model, which means write contention on model content is essentially zero. That is a comfortable position and it makes the remaining contention easy to overlook.

Where it appears is the audit table, which every operation writes to, and any sequence or counter that is updated per publish. Both are single points of serialisation, and both are fine at architecture volumes — a few hundred writes an hour is nothing — but they are the first things to look at if throughput ever surprises you.

The pattern worth avoiding is a transaction that spans the extraction or the publication rendering. Holding a database transaction open for minutes while doing work outside the database blocks vacuuming and bloats the table, and it is an easy mistake to make when the code reads naturally that way.

Backups, restores and the size question

A repository storing full revisions grows steadily, and the operational team will ask what the trajectory is. Having the number avoids an argument based on intuition.

The dominant term is revision content, and it is predictable: model size multiplied by publishes per year. Everything else — elements, relationships, properties, audit — is small by comparison and grows with the estate rather than with activity.

PostgreSQL's custom-format dump compresses this well, because serialised architecture models are highly repetitive. A repository whose raw revision storage is tens of gigabytes will typically dump to a small fraction of that, which keeps the backup window and the restore time in ordinary territory.

Extensions worth using and worth avoiding

PostgreSQL's extension ecosystem is a real advantage and also a way to acquire an operational dependency that makes the database harder to host.

Worth using: pg_stat_statements, because diagnosing a slow query without it is guesswork, and it is available on every managed platform. Full-text search is built in and is usually sufficient for repository-side search, which avoids adding a search engine to the deployment.

Worth thinking twice about: anything not available on managed PostgreSQL. An extension that requires a self-hosted instance quietly removes the option of moving to a managed service later, and that is a decision most teams would rather keep open. If a feature depends on one, it is worth checking whether the standard capability is genuinely insufficient at architecture scale, because it usually is not.

When to measure rather than argue

Most of the performance discussion around a repository is speculative, conducted before there is any data, and it consistently optimises the wrong thing.

The queries that turn out to be slow in practice are rarely the ones predicted. Fetching a model is fast. Traversal is fast once indexed. What is slow is usually a catalogue query joining elements to properties to relationships across the whole estate, or a search facet computed without a filter — and both are application-level mistakes rather than database limits.

Loading the estate with realistic volumes and running the real queries takes an afternoon and settles every question that would otherwise be argued for a fortnight. Do it early, keep the fixture, and rerun it before each release; the regression it catches will pay for the afternoon several times over.

Running it in production, quietly

The schema and the queries are the design; the design earns its keep only if the database runs unremarkably for years, and PostgreSQL rewards a short list of operational habits that fit an architecture repository's unusual profile — small by database standards, append-heavy, read by bursts.

Autovacuum needs no heroics at this scale, but the append-only revision tables deserve a glance quarterly: they never update rows, so bloat stays low, while their indexes grow monotonically and benefit from the occasional reindex during a maintenance window nobody notices. Monitoring wants three numbers, not thirty: connection count against the pool's ceiling, the p95 of the handful of named queries the portal and the client actually run, and disk growth per month — which, as the storage arithmetic earlier showed, should be boringly linear, making any inflection a signal worth reading. Wire the slow-query log to a threshold just above the worst named query's normal time, and the first regression announces itself with a query text attached instead of with a user complaint.

Upgrades deserve the same calm. Minor versions apply on the standby-and-switch pattern during the publication's quiet window; major versions get rehearsed once against a restored copy — the same copy the restore drill already produces, which is the kind of coincidence good operations are made of. And resist the accumulation of cleverness: no triggers encoding business rules the application also encodes, no scheduled jobs mutating model data outside the API's audit trail, no direct-to-database "quick fixes" however senior the requester. The database's job in this architecture is to be the boring, durable, inspectable bottom layer — every operational choice that keeps it boring is a choice in favour of every property the layers above promise.

The quiet conclusion of the whole exercise is that architecture data is a solved storage problem wearing an exotic costume. A disciplined relational schema, a handful of well-chosen indexes and PostgreSQL's perfectly ordinary operational toolkit handle an enterprise's architecture graph with capacity to spare — leaving the interesting problems where they belong, in the model rather than under it.

The advice, compressed: model the graph honestly, index for the four questions readers actually ask, keep history append-only, and let PostgreSQL be ordinary. Databases reward practices that decline to be interesting.

One closing habit ties the operational and design halves together: keep the four named queries — the element lookup, the neighbourhood walk, the catalogue aggregation, the point-in-time reconstruction — in a file in the repository, with their current explain plans pasted beneath them, refreshed whenever the schema changes. It is ten minutes of curation per change, and it means every future performance conversation starts from evidence instead of recollection. Databases drift the way everything else drifts; a practice that keeps its questions and their costs written down notices the drift while it is still an observation rather than an outage.