Loomscope

ADR-0002: PostgreSQL as the only datastore

Edit this page

Status: Accepted Date: 2026-01-15

Context

Loomscope needs to do several things that commonly pull in separate infrastructure:

  • Relational inventory — hosts, services, ports, certificates, vulnerabilities, and the relationships between them.
  • Background jobs — scheduled scans, CVE matching, snapshot capture, cloud synchronisation, alert delivery.
  • Realtime fan-out — pushing discovery progress to open browsers.
  • Caching — mostly of CVE feed data.
  • Multi-tenant isolation — several organisations in one deployment.

The reflex architecture is PostgreSQL for data, Redis for cache and pub/sub, and a broker such as RabbitMQ or NATS for jobs. Each is defensible in isolation.

The constraint that overrides this is who installs Loomscope. It is deployed on-premises, frequently by a small team, sometimes into an air-gapped environment where every component must be mirrored, and occasionally by someone whose actual job is not operations. Every stateful component is something they have to run, monitor, back up, upgrade and restore — and something that can fail in a way they have to diagnose.

Decision

PostgreSQL 17 is the only datastore. There is no Redis, no message broker and no separate search index.

  • Jobs are rows, claimed with SELECT … FOR UPDATE SKIP LOCKED. River provides the Go-side bindings.
  • Realtime is LISTEN/NOTIFY translated to SSE at the HTTP edge.
  • Caching is tables, plus the CVE mirror on disk.
  • Search is pg_trgm GIN indexes.
  • Tenant isolation is row-level security with FORCE enabled, scoped by a SET LOCAL app.org_id inside each transaction.

That last point deserves emphasis. Isolation is enforced by the database, not by remembering to write WHERE organization_id = …. A query that forgets its tenant filter returns nothing rather than another tenant's rows. This is only possible because there is one store — a cache or search index alongside would be a second copy of the data with no equivalent guarantee.

Alternatives considered

PostgreSQL + Redis. Rejected. Redis would serve caching and pub/sub, both of which PostgreSQL already handles adequately at this scale. The cost is a second stateful service to operate, secure and mirror for air-gap, and a second place tenant data can leak from.

PostgreSQL + a broker. Rejected. Brokers earn their keep with high job throughput, complex routing, or work spanning services that must not share a database. Loomscope has one writer, modest volume, and jobs whose state belongs next to the data they mutate — putting a scan job in a broker while its results go to PostgreSQL means distributed-transaction problems for no gain.

A dedicated search engine. Rejected. pg_trgm handles hostname and service search at the target scale. Revisit if full-text search across snapshot payloads becomes a requirement.

Consequences

Accepted costs.

  • PostgreSQL becomes the scaling bottleneck. At very large deployments the answer is a bigger database and read replicas, not a different topology.
  • LISTEN/NOTIFY payloads are capped at 8000 bytes, so notifications carry identifiers and clients re-read. This is more round trips than a broker would need.
  • Job processing throughput is bounded by PostgreSQL. Fine for scans measured in seconds; it would not suit millisecond-latency work.
  • Long-running listeners hold connections, so pools are separated by workload (UI, daemon, session) to prevent starvation.

Gains.

  • docker compose up with two services is the whole runtime.
  • One thing to back up. One thing to restore. One thing to mirror for air-gap. One thing to upgrade.
  • Row-level security as a real tenant boundary rather than a convention.
  • Jobs, data and notifications participate in the same transaction: a scan result and its job state commit together or not at all.

Boundary. This decision is about the default deployment. If a single deployment ever needs to exceed what one PostgreSQL instance can serve, the first move is read replicas and partitioning, and only then a reconsideration — documented in a superseding record.