Datastores & Connection Pooling

How the backend connects to its datastores, how each survives a node failure, and how each scales reads by adding replicas.

The mental model for all of them: single-primary for writes, read-offload to replicas, with HA delegated to the connection endpoints rather than run in process. All three are required to be configured — a missing URL exits at startup — but they differ in what a missing server costs: PostgreSQL is the source of truth and load-bearing, while Redis and the explore database (resource index + dependency graph) are best-effort, so the backend keeps serving — and booting — while those two are down. Operator-facing setup (which URL points where, the upgrade ordering) lives in Configuration → High availability; this page is the implementation reference.

PostgreSQL — source of truth (load-bearing)

Connection model

The pool lives in internal/backend/db/. Connect opens two separate database/sql pools over the pgx/v5 driver, each wrapped in otelsql with a db.pool.role attribute (internal/backend/db/otel.go):

• writeDB ← DATABASE_URL (role primary)
• readDB ← DATABASE_URL_READ, falling back to DATABASE_URL when unset (role replica)

CQRS is enforced at compile time (internal/backend/db/pool.go):

• BeginWriteTx returns a WriteTx that wraps *sql.Tx; only write repository methods accept it.
• BeginReadTx returns a plain *sql.Tx opened ReadOnly: true on the read pool; read methods accept it.
• A read issued inside a write transaction calls WriteTx.Unwrap() to reuse the same primary connection (read-after-write consistency within the unit of work).

LISTEN/NOTIFY (internal/backend/db/listener.go) runs on a dedicated pgx connection outside the pool, pinned to the primary — Postgres NOTIFY events are local to the server that runs them and are not replicated, so a listener on a replica would never fire. It reconnects with capped backoff; a missed notification is harmless because the scheduler jobs reprocess pending rows.

Pool sizing (ConfigFromEnv) defaults to 25 open / 5 idle connections, 30-minute lifetime, 5-minute idle timeout, per pool, tunable via DB_* (write) and DB_READ_* (read). Note that with DATABASE_URL_READ unset both pools open against the primary.

HA resilience

Failover is delegated to the endpoint: point DATABASE_URL at a service that follows the primary across promotion (e.g. CloudNativePG’s -rw service). On failover, in-flight writes return an error to the caller — there is no blind retry, which is correct for non-idempotent writes — and database/sql discards the broken connections and reopens against the repointed service. Brief error window, self-healing.

Postgres is the one store the backend cannot serve without: the readiness probe (Pool.Ping) pings both pools, so a fully-unreachable database drains the pod from rotation. That is intentional — it is the system of record.

Read scalability

Point DATABASE_URL_READ at a replica service (CNPG -ro for replicas only, or -r to include the primary). Adding hot standbys scales read capacity horizontally; the Kubernetes Service L4-balances connections across them. Balancing is per-connection, not per-query — a pooled connection sticks to one replica for its lifetime, and the 30-minute ConnMaxLifetime rebalances over time and after topology changes.

The cost is replication lag: a write committed on the primary may not yet be visible on a replica. The deployment state machine is immune (every invariant runs inside a primary write transaction), but a UI list fetched immediately after a write can briefly trail by the replication delay. With DATABASE_URL_READ unset there is no staleness — and no offload.

Redis — caches and rate limiting (best-effort, fail-open)

Connection model

A single shared redis.UniversalClient (go-redis v9), built in cmd/backend/redis.go, serves every Redis consumer: the rate limiter’s Lua token-bucket EVAL (internal/backend/ratelimit/redis.go), the logs cache and live-tail snapshot and dependency-graph view cache (internal/backend/services/*_cache.go), and the KMS decrypt cache (cmd/backend/kms_decrypt_cache.go).

Topology is chosen by a URL marker, not a connectivity probe:

• plain REDIS_URL → single-node NewClient
• ?sentinel_master=<name> → NewFailoverClusterClient (Sentinel HA)

Because detection is by marker, the client constructs even when Redis is unreachable (go-redis connects lazily), so a temporarily-down Redis never blocks startup. Every consumer wraps its operations in a tight context timeout (50–250 ms for caches, 100 ms for the rate limiter), so a slow Redis can never slow a request.

HA resilience

The failover cluster client follows master promotion automatically and reloads its node view on Sentinel updates. One SentinelAddrs entry suffices: it is normally a Service fronting every Sentinel pod, and go-redis discovers the other sentinels from it.

Redis is fully fail-open and not load-bearing:

• Caches return a miss on any error and fall through to S3 (internal/backend/services/logs_cache.go, dependency_graph_cache.go).
• The rate limiter fails open — it serves the request and records a degraded metric (internal/backend/middleware/rate_limit.go). The trade is that limits go unenforced during a Redis outage.
• The KMS decrypt cache misses on any store error and falls through to a real KMS Decrypt (internal/crypto/cipher_aws_kms_aes256.go); Encrypt never touches it, so the boot-time cipher self-test depends on KMS, not Redis.

So a Redis outage degrades features but keeps the backend serving and booting.

Read scalability

The failover cluster client is configured with RouteRandomly, which NewClusterClient.init() promotes to ReadOnly=true — the gate go-redis checks before diverting a command. Read-only commands (GET, …) then spread across the data nodes (master and replicas, so the master’s read share falls to 1/(N+1) as replicas are added), while writes — and the rate limiter’s EVAL, which Redis does not flag read-only — stay on the master. Adding replicas scales read capacity; reads degrade to the master when replicas are failing.

The URL’s connection tuning (?pool_size=, ?read_timeout=, ?max_retries=, …) is carried onto the Sentinel client by applyRedisURLTuning, so the single-node and Sentinel topologies tune identically.

Resource explorer index & dependency graph — gantrycd_explore (required, best-effort availability)

For the resource views, filters, and provider-alias capture built on this store, see resource_explorer.md.

Connection model

The explore database holds two derived, recreatable indexes off the critical primary: the resource explorer index (resource_index_repo.go — a projection of the run that last touched each stack’s state: a success, or an apply that failed part-way) and the cross-stack dependency graph (dependency_edge_repo.go — one row per declared dependency edge). Neither is optional: EXPLORE_DATABASE_URL is required, and buildExploreDatabase aborts boot when it is unset or unparseable. Availability is gated separately and fails open — the pool connects lazily and never pings, so a valid-but-unreachable explore database does not block startup; Explore queries error at request time and the async task queue’s backoff/dead-letter absorbs the background work, since the queue itself lives on the core database.

cmd/backend/database.go opens a second *db.Pool (db.ConnectExplore) from EXPLORE_DATABASE_URL (+ optional EXPLORE_DATABASE_URL_READ), with explore-specific otelsql roles (explore-primary/explore-replica) so its query telemetry stays distinct from the primary’s. The explore repositories own this pool internally rather than taking a caller transaction — the explore database is a distinct connection that core write paths cannot thread their transaction through, so authz is resolved against the primary (StackService.ScopePatterns) and pushed into the explore query as a denormalized stack_name filter.

HA resilience

Everything in the explore database is rebuildable from runs, so it is fully best-effort: it is populated incrementally on configured-branch run completion and rebuilt by a 12-hour sweep, with an hourly sweep recomputing the cached diagnostics findings. It deliberately does not gate the readiness probe — a degraded explore database must not drain the pod, since search and insights are auxiliary — and ConnectExplore skips the startup ping (database/sql connects lazily and self-heals), so an explore DB unreachable at boot does not block startup either: search/insights error and reindex tasks retry until it recovers. Org/stack hard-deletes purge their explore rows + edges + findings inline (ResourceIndexService.RemoveOrg/RemoveStack, DependencyGraphService.RemoveOrg/RemoveStack), with the sweeps as backstop.

For observability, the explore pool’s connection stats are emitted under pool=explore-write/explore-read labels (distinct from the primary), and the reindex/rebuild/diagnostics work runs through the async-task queue — so a stalled pipeline shows up as a growing backlog in the standard async-task gauge (by task_type) plus the per-task last_error, which is what alerting should watch.

Cycle and orphan diagnostics read the dependency edges from this same database (via an in-process Tarjan SCC pass — see Cycle detection at scale); there is no separate graph store to configure.

Deployment

Provision the gantrycd_explore database (same cluster as the primary by default, or a dedicated instance to fully offload) the same way as the core database, using the atlas-runtime image — explore_* commands mirror the core schema_apply/migrate_* commands one-for-one:

docker run --rm -e EXPLORE_DATABASE_URL=... -e EXPLORE_DATABASE_DEV_URL=... \
  gantrycd/atlas explore_schema_apply --auto-approve   # declarative schema
docker run --rm -e EXPLORE_DATABASE_URL=... \
  gantrycd/atlas explore_migrate_set_baseline          # bootstrap, then…
docker run --rm -e EXPLORE_DATABASE_URL=... \
  gantrycd/atlas explore_migrate_apply                 # data migrations

Locally the equivalent make atlas_explore_schema_apply, atlas_explore_migrate_set_baseline and atlas_explore_migrate_apply targets mirror their core counterparts. The schema lives in schema-explore/ and the versioned migrations in migrations-explore/ — a separate Atlas env so its destructive diff is scoped to the explore database and never touches the core tables — both bundled in the atlas-runtime image and the db-migration-<version>.tar.gz artifact. The explore index is recreatable from runs, so it ships no data migrations today; the chain is anchored by a baseline marker so explore_migrate_apply is a clean no-op until one is ever added.

Side by side

The scaling ceiling

All three scale reads by adding replicas, but writes are single-primary in every case — Postgres primary, Redis master, explore primary — and none is write-sharded. For a CD control plane (modest write rate, read-heavy UI, ephemeral cache/rate-limit keys) that is the right shape and not a near-term constraint. The NewFailoverClusterClient in particular is “one logical shard with N read replicas,” not a hash-slot-sharded cluster. If write volume ever outgrows a single primary, scaling it is a per-store change (Postgres partitioning/Citus, Redis Cluster sharding) — not warranted today.

See also

• Configuration → High availability — operator setup and upgrade ordering
• Architecture — components and communication boundaries
• Rate Limiting — the Redis token-bucket policies
• Cross-Stack Dependency Graph — the Postgres-backed graph + triggers
• Cycle Detection at Scale — SCC vs closed-walk, with benchmarks
• Data Encryption — the KMS decrypt cache