Why We Dropped the ORM: Building a Bulletproof, High-Concurrency Financial Ledger with Raw AsyncPG and FastAPI

When designing financial engines or reservation systems, standard web development conventions often break down under heavy parallel load. Most modern backend applications rely heavily on Object-Relational Mappers (ORMs) like SQLAlchemy, Django ORM, or Prisma. While ORMs accelerate early-stage CRUD development, they introduce significant hidden overhead and unpredictable behavior in high-concurrency environments:

• Opaque Query Generation: ORMs abstract away underlying SQL execution plans, making it difficult to predict or optimize how database transactions acquire locks under parallel contention.
• Connection Overhead: Inefficient session lifecycle management inside ORM wrappers can rapidly exhaust database connection pools during concurrency spikes.
• Abstraction Leaks: Strict financial invariants—such as ensuring account balances never drop below zero and guaranteeing zero double-spending—require explicit, granular control over transaction boundaries, row-level locks, and atomicity.

To solve this, we built Chronos: an independent financial ledger and transaction engine engineered from the ground up to bypass ORMs entirely. Leveraging raw AsyncPG, explicit row-level locking (SELECT FOR UPDATE), PgBouncer connection pooling, dual-tier fail-open idempotency, and asynchronous event streaming via Kafka (KRaft) and RabbitMQ, Chronos sustains heavy concurrent stress with absolute data integrity.

System Topology & Data Flow

 [Client / k6 Load Tester]
            │
            ▼
     [ Nginx Load Balancer ]
            │
            ├─────────────────────────┬─────────────────────────┐
            ▼                         ▼                         ▼
     [ FastAPI App 1 ]         [ FastAPI App 2 ]         [ FastAPI App 3 ]
            │                         │                         │
            ├─────────────────────────┴─────────────────────────┤
            ▼                                                   ▼
     [ Redis (Idempotency Cache) ]                       [ PgBouncer Pooler ]
     *(Fail-Open Tier 1 Shield)*                                │
                                                                ▼
                                                        [ PostgreSQL 15 ]
                                                        *(Tier 2 Ground Truth)*
                                                                │
                                         ┌──────────────────────┴────────────────────┐
                                         ▼                                           ▼
                                  [ Apache Kafka ]                            [ RabbitMQ ]
                              (Event Streaming / Log)                    (Async Background Tasks)

---

1. Architecture Deep-Dive & Raw AsyncPG Row Locking

The Core Problem: Race Conditions in High-Concurrency Ledgers

In a typical ORM-driven application, updating an account balance usually follows a naive read-modify-write pattern:

1. Fetch the account record from the database into application memory.
2. Check if balance >= requested_amount in application code.
3. Compute new_balance = balance - requested_amount.
4. Issue an UPDATE query back to the database.

Under heavy concurrent traffic (e.g., hundreds of virtual users hitting an endpoint within the same millisecond), multiple application workers read the same initial balance simultaneously. Both pass the in-memory balance check, and both commit their updates—resulting in race conditions, double-spending, and negative account balances.

The Solution: Zero-ORM Architecture with Raw AsyncPG

To eliminate race conditions without introducing fragile application-side distributed locks, Chronos shifts atomicity guarantees directly to PostgreSQL using raw SQL executed via AsyncPG.

Furthermore, real-world commerce requires supporting Two-Phase Reservation Flows (Authorize & Settle):

1. RESERVATION_HOLD: Verifies funds, exclusively locks the row, and deducts the balance upfront to prevent double-spending while downstream services (fraud detection, inventory checks, payment gateways) execute.
2. RESERVATION_COMMIT: Confirms the final settlement. Because funds were already deducted during the hold phase, the balance is verified without deducting twice, and an audit trail entry links directly to the hold via reference_id.

async def process_transaction(
    pool: asyncpg.Pool,
    user_id: str,
    amount: Decimal,
    entry_type: str,
    idempotency_key: uuid.UUID | str = None,
    reference_id: uuid.UUID | str = None,
) -> dict:
    entry_str = entry_type.value if hasattr(entry_type, "value") else str(entry_type)
    idem_uuid = uuid.UUID(str(idempotency_key)) if idempotency_key else None
    ref_uuid = uuid.UUID(str(reference_id)) if reference_id else None

    async with pool.acquire() as conn:
        async with conn.transaction():
            # 1. Acquire explicit row-level lock (Serializes concurrent requests)
            account = await conn.fetchrow(
                """
                SELECT account_id, balance 
                FROM accounts 
                WHERE user_id = $1 
                FOR UPDATE
                """,
                user_id,
            )
            if not account:
                raise ValueError(f"Account for user '{user_id}' not found.")

            current_balance = account["balance"]
            account_id = account["account_id"]

            # 2. Enforce strict balance rules and state transitions
            if entry_str in ("WITHDRAWAL", "RESERVATION_HOLD"):
                if current_balance < amount:
                    raise ValueError(f"Insufficient funds: {current_balance} < {amount}")
                new_balance = current_balance - amount
                ledger_amount = -amount

            elif entry_str == "DEPOSIT":
                new_balance = current_balance + amount
                ledger_amount = amount

            elif entry_str == "RESERVATION_COMMIT":
                # Funds were already locked/deducted during RESERVATION_HOLD
                new_balance = current_balance
                ledger_amount = -amount

            # 3. Update snapshot balance only if value shifted
            if new_balance != current_balance:
                await conn.execute(
                    "UPDATE accounts SET balance = $1 WHERE account_id = $2",
                    new_balance,
                    account_id,
                )

            # 4. Insert immutable audit trail entry
            entry_id = await conn.fetchval(
                """
                INSERT INTO ledger_entries (account_id, amount, entry_type, reference_id, idempotency_key)
                VALUES ($1, $2, $3, $4, $5)
                RETURNING entry_id
                """,
                account_id,
                ledger_amount,
                entry_str,
                ref_uuid,
                idem_uuid,
            )

            return {
                "account_id": str(account_id),
                "entry_id": str(entry_id),
                "previous_balance": float(current_balance),
                "new_balance": float(new_balance),
                "amount_processed": float(amount),
            }

Why Raw SQL over ORM Abstractions?

1. Explicit Transaction Isolation: By invoking SELECT ... FOR UPDATE inside an explicit connection.transaction() block, PostgreSQL locks the specific target row for the exact lifespan of that transaction. Concurrent requests targeting the same account_id queue cleanly at the database engine level until the active transaction commits or aborts.
2. Zero-Overhead Serialization: AsyncPG communicates directly with PostgreSQL over its native binary protocol, bypassing the object-instantiation, state-tracking, and session-management overhead inherent to ORMs.
3. Deterministic Execution Plans: Raw SQL ensures execution plans and index usage remain completely transparent during extreme traffic spikes.

---

2. Connection Pooling & Multi-Tiered Idempotency

When scaling backend services to sustain hundreds of concurrent requests, connection management and cache resilience are just as vital as query performance. Chronos pairs PgBouncer transaction pooling with a multi-tiered, fail-open idempotency architecture.

1. Managing Connection Storms with PgBouncer

PostgreSQL operates on a process-per-connection architecture where every active client connection consumes dedicated memory and CPU resources. When hundreds of virtual users hit an application simultaneously, direct database connections quickly saturate, causing pool exhaustion and connection timeouts.

To eliminate connection storms, Chronos routes all database traffic through PgBouncer configured in transaction pooling mode:

• Connection Multiplexing: Thousands of incoming HTTP requests across horizontally scaled FastAPI workers are multiplexed across a compact, persistent pool of database connections.
• Minimized Memory Footprint: Releasing connections at transaction boundaries rather than holding them across idle web requests keeps database resource utilization flat under peak load.

---

2. Multi-Tiered Idempotency: Redis Shield + PostgreSQL Ground Truth

Financial APIs require absolute idempotency to prevent duplicate charges or double settlements caused by network timeouts or aggressive client retries.

A common architectural trap is relying solely on Redis: What happens if Redis restarts, hits its memory ceiling and evicts keys, or drops off the network? A naive implementation either crashes or returns HTTP 500 Internal Server Errors.

Chronos implements a multi-tiered defense-in-depth model:

Incoming Request (Idempotency-Key: UUID)
         │
         ▼
[ Tier 1: Redis Fast-Path ] ── (Cached?) ──► Return Cached Result (<1ms)
         │
    (Lock & Pass)
         │
   [ Redis Outage? ] ── (Fail-Open) ──┐
         │                            │
         ▼                            ▼
[ Tier 2: PostgreSQL Ground Truth Fallback ]
  - Checks: SELECT FROM ledger_entries WHERE idempotency_key = $1
  - Enforces: UNIQUE INDEX (idempotency_key) WHERE idempotency_key IS NOT NULL
  - If duplicate found: Short-circuit and return original receipt
  - If new: Execute SELECT FOR UPDATE and atomic balance mutation

Tier 1: The Redis Fast-Path Shield

async def handle_idempotency(
    key: str,
    ttl: int = 86400,  
    lock_ttl: int = 10,  
) -> Tuple[Optional[dict], bool]:
    try:
        r = get_redis()
        cache_key = f"idempotency:result:{key}"
        lock_key = f"idempotency:lock:{key}"

        # 1. Fast Path: Return cached response immediately
        cached_val = await r.get(cache_key)
        if cached_val:
            return json.loads(cached_val), False

        # 2. Atomic Distributed Lock (NX = Only set if Not Exists)
        acquired = await r.set(lock_key, "PROCESSING", nx=True, ex=lock_ttl)
        if acquired:
            return None, True # Primary worker won the race

        # 3. Concurrent Duplicate Requests Poll Until Primary Completes
        # ...
    except Exception as e:
        # Graceful Fail-Open: If Redis drops, log warning and let PostgreSQL defend itself
        print(f"Warning: Redis error during idempotency check, failing open: {e}")
        return None, True

Tier 2: PostgreSQL Database-Level Fallback

If Redis crashes or evicts a key, the fail-open wrapper returns (None, True). Chronos routes the request directly to PostgreSQL.

Before acquiring row locks or updating balances, the database executes an atomic check backed by a partial unique index:

-- Schema Guard: Global uniqueness enforced on request keys
CREATE UNIQUE INDEX idx_ledger_entries_idempotency_key_unique 
ON ledger_entries(idempotency_key) 
WHERE idempotency_key IS NOT NULL;

# PostgreSQL Fallback Check inside process_transaction()
if idem_uuid is not None:
    existing_entry = await conn.fetchrow(
        """
        SELECT e.entry_id, e.account_id, e.amount, e.entry_type, a.balance
        FROM ledger_entries e
        JOIN accounts a ON e.account_id = a.account_id
        WHERE e.idempotency_key = $1
        """,
        idem_uuid,
    )
    if existing_entry:
        return {
            "account_id": str(existing_entry["account_id"]),
            "entry_id": str(existing_entry["entry_id"]),
            "previous_balance": float(existing_entry["balance"]),
            "new_balance": float(existing_entry["balance"]),
            "amount_processed": float(abs(existing_entry["amount"])),
            "idempotency_replayed": True,
        }

This delivers the best of both worlds: sub-millisecond deduplication in RAM under normal operations, and absolute data consistency during infrastructure failures.

---

3. Dual-Profile Load Testing & Observability (k6 + Prometheus + Grafana)

Architectural designs must be validated against real stress. We evaluated Chronos using k6 across two distinct performance profiles, backed by real-time metric collection via Prometheus and visualization in Grafana.

// Test Configuration: Ramping arrival rate reaching 300 RPS peak
export const options = {
  scenarios: {
    ledger_stress: {
      executor: 'ramping-arrival-rate',
      startRate: 20,
      timeUnit: '1s',
      preAllocatedVUs: 50,
      maxVUs: 300,
      stages: [
        { duration: '10s', target: 100 }, // Warm-up
        { duration: '30s', target: 300 }, // Ramp-up
        { duration: '20s', target: 300 }, // Peak sustained load
        { duration: '10s', target: 0 },   // Cool-down
      ],
    },
  },
  thresholds: {
    http_req_failed: ['rate<0.01'],
    http_req_duration: ['p(95)<500', 'p(99)<1500'],
  },
};

Profile 1: Distributed Scale Profile (load_test_distributed.js)

Simulates a high-volume retail banking environment by distributing transactions across a pool of 50 independent account rows to benchmark raw throughput and connection pooling.

• Throughput: Sustained ~201.1 RPS across 14,132 total requests over 70 seconds.
• Latency Distribution:
• Median: 7.31ms
• P(90): 13.96ms
• P(95): 16.13ms

• P(99): 25.54ms

• Reliability: 0.00% HTTP failure rate (14,132/14,132 passed) with 100% check verification.
  

 █ TOTAL RESULTS 
    checks_succeeded...: 100.00% 28164 out of 28164
    http_req_duration..: avg=9.03ms min=839µs med=7.31ms max=426ms p(95)=16.13ms p(99)=25.54ms
    http_req_failed....: 0.00% (0 out of 14132)

---

Profile 2: Hot-Row Contention Profile (load_test_contention.js)

Tests extreme concurrency limits against a single entity row (usr_k6_stress_v2) using SELECT FOR UPDATE to measure database queueing and lock contention.

• Throughput: Sustained ~199.1 RPS across 13,942 requests targeting the exact same account.
• Controlled Queueing & Tail Latency:
• Median: 6.44ms (Fast for unblocked queries)
• P(90): 172.43ms
• P(95): 881.65ms

• P(99): 1.57s

• Integrity Verification: 0.00% HTTP failure rate (13,942/13,942 passed) and 100% check success (27,883/27,883).
  Despite intense queueing behind a single row lock, PostgreSQL's row serialization completely prevented race conditions, deadlocks, and double-spending with zero data corruption.
  

 █ TOTAL RESULTS 
    checks_succeeded...: 100.00% 27883 out of 27883
    http_req_duration..: avg=100.93ms min=877µs med=6.44ms max=3.14s p(95)=881.65ms p(99)=1.57s
    http_req_failed....: 0.00% (0 out of 13942)

---

Real-Time Observability (Grafana Dashboard)

Real-time metric collection via Prometheus visualized through Grafana during peak load testing:

---

Production Observability Stack

Telemetry is scraped in real-time by Prometheus and visualized in Grafana dashboards tracking:

1. Request Throughput: sum(rate(http_requests_total[1m])) measuring live RPS curves across all replicas.
2. Tail Latency: histogram_quantile(0.99, sum(rate(http_request_duration_seconds_bucket[1m])) by (le)) tracking P99 response degradation under load.
3. Error Tracking: Custom PromQL fail-safe queries (sum(rate(http_requests_total{status=~"5.."}[1m])) or vector(0)) verifying clean zero-error baselines throughout testing.

---

4. Engineering Takeaways

1. Drop the ORM for Financial Cores: While ORMs accelerate standard CRUD development, high-concurrency ledgers demand surgical database control. Raw AsyncPG provides complete visibility over transaction isolation boundaries and explicit row locks.
2. Never Rely on a Single Layer for Idempotency: Treating Redis as the sole gatekeeper creates a dangerous single point of failure. Multi-tiered defense (Redis fast-path + PostgreSQL unique constraints) ensures system availability without sacrificing ledger consistency.
3. Model Workloads Realistically: Separating distributed multi-account benchmarks from hot-row contention tests highlights critical system characteristics: blazing-fast retail throughput under distributed access, and safe, predictable serialization under extreme contention.