Cutting a data API from 21s to ~250ms

#TL;DR

Interactive UI operations — list views, keyed lookups, validation saves — were backed by a warehouse used as an OLTP store. Below: how I measured it, the two migrations, and what I refused to optimize.

#Symptoms: tail latency, not averages

Tickets piled up on paths that should feel instant. Means on cached routes looked fine; p95/p99 sat at 21–30s on write-heavy and full-list reads.

Root causes after instrumentation:

Structured benchmarks on INSERT/MERGE/UPDATE/DELETE over REST confirmed: Capacitor-backed storage + job semantics ≠ row store semantics. Not "bad SQL" — wrong system for synchronous UI.

#Before

#Stage 1: Storage Write API (gRPC)

Replace hot-path DML with AppendRows on a committed write stream:

var client = await BigQueryWriteClient.CreateAsync();
var stream = await client.CreateWriteStreamAsync(new CreateWriteStreamRequest
{
    Parent = tableReference,
    WriteStream = new WriteStream { Type = WriteStream.Types.Type.Committed },
});
 
await using var append = client.AppendRows();
await append.WriteAsync(new AppendRowsRequest
{
    WriteStream = stream.Name,
    ProtoRows = new ProtoRows
    {
        WriterSchema = protoSchema,
        SerializedRows = { rowBatch.Select(r => r.ToByteString()) },
    },
});

Why this beats DML REST:

Measured result: hot write paths under 2s (~60% vs baseline). Still too slow for UI — reads remained on BigQuery.

Stream lifecycle matters: create stream → append batches → finalize. Reusing streams avoids per-request stream creation cost (we pooled streams per table partition in practice).

#Stage 2: PostgreSQL OLTP + hybrid reads

Writes improved; reads still hit the warehouse. UI needed:

• Keyed lookups (WHERE id = @id, WHERE parent_id IN (...))
• Small bounded result sets (nav counts, picker lists)
• Transactional updates with row-level consistency

#Schema + index strategy

Model only what the UI queried — not a warehouse-wide mirror:

CREATE TABLE records (
  id          UUID PRIMARY KEY,
  tenant_id   UUID NOT NULL,
  status      SMALLINT NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL
);
CREATE INDEX ix_records_tenant_status ON records (tenant_id, status);

EF Core migrations versioned alongside API routes. Partial indexes on hot filters where cardinality justified them.

#Cutover mechanics

Route-by-route — no big bang:

1. Backfill — batch copy from warehouse with row-count + checksum parity
2. Shadow read — API handler queries Postgres + warehouse, compares hashes in logs (not user-visible)
3. Flip read — feature flag per route; monitor p95/error rate
4. Flip write — dual-write window, then warehouse write off
5. Enrichment only — hierarchy/analytics stay async off hot path

Shadow read handler shape:

var pgRow = await _pg.Records.FindAsync(id);
var bqRow = await _bqEnrichment.FetchAsync(id); // async, optional
if (_shadowMode) _comparer.LogDelta(pgRow, bqRow);
return Map(pgRow, bqRow); // UI reads Postgres only

#After

Measured result: hot reads 200–300ms p95, variance collapsed.

#What we refused to do

• Index-tune warehouse DML into sub-second (wrong tool)
• Big-bang schema migration (rollback must be per-route)
• Drop warehouse entirely (enrichment still valuable)

#Adjacent production constraints

Latency isn't valid in isolation:

• Multi-pod cache — centralized Redis Next.js cache handler; without it, pods serve divergent cached pages
• Gateway + API test coverage — regressions on migrated routes caught in CI
• Multi-step workflows — approval state machines require correctness gates; fast wrong writes are worse than slow right ones

#Lessons

1. Profile p99, not mean — UI trust follows worst-case.
2. Write API ≠ read database — stage 1 and 2 solve different bottlenecks; order mattered.
3. Hybrid architecture is valid — OLTP for interaction, warehouse for analytical enrichment.
4. Ask "should this request touch this system?" before "how do I tune this query?"

#Stage-by-stage latency (measured)

The win was not one index — it was stopping analytical reads from sharing the OLTP connection pool during business hours.

#Multi-pod cache gotcha

After horizontal scaling the Next.js BFF, pods served different cached pages until a centralized Redis cache handler was wired. Latency looked fixed on one pod and flaky globally — always validate cache coherence when the API tier scales out.

#Closing thought

Slow reads are usually a topology problem: the wrong database tier on the wrong path, not a missing index on the first hop. Fix where the request walks before you argue about ORM choice.

#Reader field guide

When this pattern applies: A product UI waits on warehouse DML or full-table scans; p99 latency dominates support noise while averages look fine; you need keyed reads, small writes, or transactional updates on the request path. Skip it if the workload is batch-only analytics with no synchronous caller.

Operational checklist

• Measure p95/p99 per route before tuning SQL — means hide warehouse job latency.
• Classify each handler: OLTP (keyed read/write) vs enrichment (can be async).
• For hot writes still on BigQuery, pilot Storage Write API append streams before debating indexes.
• Backfill Postgres (or your row store) with row-count + checksum parity; run shadow reads before flipping traffic.
• Cut over one route at a time with a feature flag; watch p95 and error rate, not deploy count.
• Keep warehouse on the path only for hierarchy joins and analytics that do not block the UI thread.

Where to put the data (request path)

#Related reading

#On this site

#References (curated)

I still sanity-check warehouse semantics against Google’s docs before arguing for Postgres—DML job latency is easy to misread when averages look fine.