Why InfraOrcinus Runs on PostgreSQL: Relational Rigor, ACID Governance, and Sovereign Architecture

By Chirag Panchal · Founder & Lead Architect · 2026-10-02 · 12 min read

Why we deliberately chose PostgreSQL over cloud-native document stores like Azure Cosmos DB. An architectural deep-dive into state locking, relational ACID guarantees, application-layer encryption, and zero-vendor-lock-in sovereignty.

Development Status & Architectural Evolution: InfraOrcinus is in active development. Architecture specifications, internal interfaces, and operational protocols described herein reflect our current engineering design and are continuously refined to make InfraOrcinus robust, resilient, and enterprise-grade.

"In web applications, an eventual consistency delay results in a delayed profile picture update. In infrastructure delivery, an eventual consistency delay causes duplicate terraform apply runs, split-brain state locks, and simultaneous mutations of production cloud backbones. Infrastructure demands relational rigor."

When designing the foundational data layer for InfraOrcinus, one of the most critical architectural decisions was choosing the persistence engine.

In modern cloud engineering, there is persistent hype around serverless multi-model databases like Azure Cosmos DB, Amazon DynamoDB, or Google Cloud Spanner. Marketing materials promise infinite global scale, multi-master replication, and zero-touch management.

Yet, for an infrastructure delivery plane and AI governance engine, we deliberately rejected Azure Cosmos DB and proprietary NoSQL document stores in favor of PostgreSQL 16+.

Here is an architectural breakdown of why PostgreSQL is the only solid, secure, and sovereign choice for governing production infrastructure.

1. The Fallacy of Eventual Consistency in Cloud Delivery

Infrastructure as Code (IaC) engines like Terraform and OpenTofu manage state. A state file is a cryptographic map between code definitions and physical cloud resources (VPCs, IAM roles, subnets, RDS instances, and peering connections).

A. Strict Mutual Exclusion vs Cross-Partition Limitations

When an automated GitOps pipeline, an AI agent, or a platform engineer triggers a run:

1. An exclusive state lock must be acquired instantaneously.
2. The current state version must be read with immediate, serializable consistency.
3. If another worker attempts to acquire the lock concurrently, it must be atomically rejected or queued.

In document stores like Azure Cosmos DB, multi-item transactions and ACID guarantees are strictly scoped to a single logical partition key. Cross-partition transactions do not exist natively. Furthermore, transactional stored procedures in Cosmos DB have strict 5-second execution limits and size constraints.

If your database cannot enforce serializable ACID transactions across organizations, workspaces, runs, and approval gates, you invite race conditions. Two concurrent workers can read the same unlocked state, generate conflicting plans, and execute destructive out-of-order applies.

With PostgreSQL, we leverage:

• ACID Transactions (SERIALIZABLE or READ COMMITTED with row-level locking): SELECT ... FOR UPDATE provides rock-solid, deterministic mutual exclusion.
• Immediate Consistency: Writes and lock acquisition always go to the primary, so the write path has no replication lag. (Read replicas, if you add them for reporting, are asynchronous and must never serve lock or state reads.)
• Relational Integrity: Foreign keys (ON DELETE RESTRICT) ensure that a run record can never be orphaned from its parent stack or organization.

B. Choosing the Right Isolation Level

SERIALIZABLE and READ COMMITTED plus SELECT ... FOR UPDATE solve the same problem differently, and the choice matters under contention:

• READ COMMITTED with explicit row locks is the right tool for locks and job claims. Contenders queue on the row (or skip it with SKIP LOCKED for job queues) and exactly one wins, with no retries needed.
• SERIALIZABLE protects multi-row invariants, such as checking an approval count across several rows, but PostgreSQL may abort a transaction with a serialization failure (40001). Code using it must retry.
• The rule of thumb: use row locks for mutual exclusion, and reserve SERIALIZABLE for cross-row invariants where you accept the retry loop.

2. True Data Sovereignty vs Cloud Hyperscaler Lock-in

InfraOrcinus is built for organizations under strict regulatory regimes, including the EU AI Act, GDPR, NIS2 and BSI C5.

A. The Cosmos DB Lock-in Trap

Azure Cosmos DB is a proprietary, closed-source Microsoft cloud service. Adopting Cosmos DB means:

• The control plane cannot be deployed on AWS, Google Cloud, or European sovereign providers (such as Hetzner, OVHcloud, Scaleway, or Exoscale).
• It cannot run inside air-gapped defense networks, on-premises data centers, or local edge appliances.
• You surrender jurisdiction over query engines, wire protocols, and storage runtimes.

B. The PostgreSQL Sovereignty Guarantee

PostgreSQL is open source, battle-tested for over 35 years, and completely vendor-neutral.

By standardizing on PostgreSQL:

• Universal Portability: InfraOrcinus runs identically on a developer's workstation via Docker, on bare-metal Linux servers, on managed cloud services (AWS RDS/Aurora, GCP Cloud SQL, Azure Database for PostgreSQL), or inside sovereign Kubernetes clusters.
• Jurisdictional Control: Platform operators choose exactly where their data resides. Under GDPR Article 44-50, data residency is guaranteed because the operator specifies the database instance in their own sovereign jurisdiction.
• Local AI Integration: Local AI agents (such as models powered by Hanuvan Local AI) interact with the database over low-latency, private, on-premise networks without leaking prompts, schemas, or state across international boundaries.

3. Predictable Performance vs Request Unit (RU/s) Chaos

In document databases like Cosmos DB, pricing and performance are metered through Request Units (RU/s). Every query, read, write, and index scan consumes RUs.

A. Large State Files and Throttling

Terraform state files in medium-to-large environments easily exceed several megabytes of deeply nested JSON.

• In Cosmos DB, writing a 5 MB document burns hundreds of Request Units in a single operation. If multiple stacks deploy simultaneously, pipelines exhaust their provisioned RU threshold and receive HTTP 429 Too Many Requests rate-limiting errors, stalling critical rollouts.
• Cosmos DB imposes a hard 2 MB limit per item in the NoSQL API. Stacks that generate large state files simply break or require complex chunking workarounds.

B. The PostgreSQL TOAST and JSONB Advantage

PostgreSQL handles variable-length attributes effortlessly via TOAST (The Oversized-Attribute Storage Technique) and native binary JSON (JSONB):

• Large state documents are automatically compressed and stored out-of-line without blocking table scans.
• Query performance remains deterministic and fast with GIN indexing on structured attributes.
• Predictable infrastructure costs: you pay for compute and storage capacity, not per-query micro-billing that spikes during emergencies.

3b. Operating PostgreSQL at Scale

A single well-sized PostgreSQL primary carries a delivery plane much further than most teams expect, because the workload is write-light and state-heavy: a few large documents, many small coordination rows, and an append-only audit stream. Planning for growth means addressing four things:

1. Connection pooling. Many runners and API replicas can exhaust max_connections. Place PgBouncer (transaction pooling) between the application and the database. Keep lock and claim transactions short so they pool well, and avoid session-level advisory locks that transaction pooling cannot preserve.
2. State table growth. Every apply writes an immutable state version. Apply a retention policy (for example, keep all versions for N days and thinned snapshots after that) and rely on TOAST compression for the large payloads. Monitor table and TOAST bloat and tune autovacuum for the hot tables.
3. Audit log growth. The append-only audit_logs table only grows. Use time-based declarative partitioning so old partitions can be archived to cold storage or detached without ever running UPDATE or DELETE against history, and index on organization and timestamp for investigations.
4. Predictable indexing. Index the foreign keys and lookup paths you actually query (stack, organization, run status), and use GIN indexes only on JSONB attributes you filter on.

High Availability in One Paragraph

Production deployments run a primary with synchronous or asynchronous standbys managed by Patroni, a cloud-managed Multi-AZ service, or an operator such as Crunchy PGO. The API server keeps nothing important on local disk, so failover is a database event, not an application one. Readiness probes fail while the database connection is broken, which pulls an instance from rotation until it recovers. The full failover and recovery model is covered in the high-availability and disaster-recovery article.

3c. Honest Trade-offs

No choice is free. PostgreSQL asks more of the operator than a serverless document store:

We accept these costs deliberately. For a system whose job is to say "yes, exactly one run holds this lock", operational effort is a better trade than weaker guarantees.

4. Security and Governance on PostgreSQL

PostgreSQL is one layer of the security model, not the whole of it. This section separates what the product does from what an operator must configure.

A. Deployment Topologies

InfraOrcinus connects to PostgreSQL with a standard connection string, so it runs against any PostgreSQL-compatible service that accepts one.

1. Edge and staging. A developer or staging setup can run the database and API on a private Docker network, with an outbound-only Cloudflare tunnel for remote administration. No inbound ports are opened. This is how our own staging environment is run.
2. Enterprise and managed services. Operators commonly use a private endpoint or VPC-hosted service, such as Amazon Aurora or RDS, Azure Database for PostgreSQL Flexible Server, or Cloud SQL, with private networking. We have not validated every managed flavour. Check the connection, migrations and backups on your service before relying on it.
3. On-premises and sovereign. A self-managed high-availability cluster (for example Patroni or Crunchy Data PGO) keeps data inside your own data centre.

B. Database Hardening: Operator Configuration

The repository does not contain a pg_hba.conf or a superuser lockdown. These are the controls we recommend, and an operator sets them:

• Restrict the superuser to local sockets. Reject administrative superuser logins over TCP, and allow them only through a local Unix socket on the host.
• Use a least-privilege application role that can reach only the application database.
• Require scram-sha-256 for network authentication. It is the default for current PostgreSQL versions, but verify it in your own pg_hba.conf.
• Keep the database off public networks. Bind it to private endpoints or internal interfaces only.

C. Encryption Layering

• Application layer. Secrets and state payloads are encrypted with AES-256-GCM before they reach SQL. Vault Transit can be configured as the encryption provider instead of a local key. See the state backend article for the details.
• Database layer (optional). The platform ships a pgcrypto migration that encrypts sensitive sub-fields in the database. It is a second layer for secrets, not a replacement for the application layer.
• Passwords. User passwords are stored as salted bcrypt hashes.

D. Tenant Isolation

Tenant isolation is enforced in the application. Every query is scoped by the caller's organization and project, and a request for a record outside that scope returns the same "not found" answer as a record that does not exist.

Row-level security is not used. We considered it. The product does not currently define database policies, so a query written without a tenant filter would not be stopped by the database. Treat the application's scoping as the control, and cover it with tests (the permission matrix in the repository is one example).

E. The Audit Trail

Operational events, such as plans, policy evaluations, approvals, rejections and applies, are recorded in an audit_logs table, and state-changing HTTP requests are logged with the actor, the path, the status code and the source IP. The application only appends entries.

What the audit trail does not yet guarantee:

• Not hash-chained. Entries are not linked to their predecessors, so an edit or a deletion inside the database would not be detectable by the product.
• Not database-enforced. The application database role is not yet restricted from updating or deleting audit rows. Restrict this in your own database role configuration.

Export the audit log to an append-only store you control if you need that guarantee before we ship it.

5. Schema Rigor and Recovery

A. Schema Migrations

The schema is managed with forward-only SQL migrations, one file per change, in the repository's db/migrations directory. Each file is idempotent (CREATE ... IF NOT EXISTS, ADD COLUMN IF NOT EXISTS), so re-running one is safe. The migrations are applied by the server-side migration process, not by the application's startup, so an operator applies them before a release. Confirm each migration's transaction behaviour in your own process, because the repository does not wrap them automatically.

B. Backups and Recovery

The repository includes backup and restore scripts:

• scripts/backup.sh runs pg_dump in the custom format (-Fc), which restores selectively with pg_restore.
• scripts/restore.sh restores a dump with pg_restore.

Point-in-time recovery needs WAL archiving, and that is configured on the database service, not in this repository. Set it up for production, and rehearse a restore before you need one.

Summary: Engineering for Permanence

Infrastructure delivery needs strong relational guarantees, tenant isolation and recovery you have tested. Choosing PostgreSQL gives us:

• Atomic locking. State locks and job claims are single conditional writes, so exactly one contender wins. Job claims use row locks with SKIP LOCKED.
• Relational integrity. Transactions and constraints in one database, with no cross-partition limitations.
• A runs-anywhere database. A standard connection string and standard tools, so you can move to a managed service or your own cluster.
• Operator-owned hardening. Network rules, roles, encryption keys and backups are configured by you, and the product documents what it needs.

Row-level security and a hash-chained audit trail are on the roadmap. Neither is shipped today.