BigQuery→Postgres serving migration (per-page, fallback-guarded, parity-gated)

The Problem

Client-facing dashboards served their reads from BigQuery, making pages slow (an Overview Metrics page sat well above its floor) and expensive. The move to Postgres-backed serving had to accelerate pages without ever showing a client a wrong number or leaking one tenant's data to another.

Approach & Architecture

Advised and reviewed a page-by-page migration behind an additive SERVING_PAGES flag, with a single read chokepoint (readVia) that falls back to BigQuery on error and logs loudly, a module-load guard (PAGES_WITH_FALLBACK) that refuses to enable a page whose read sites aren't converted, and a two-witness parity model. Each page flip had to pass five deliverables: exact parity (owner + scoped witnesses), convergence to the measured page floor from a co-located k6 runner, zero BigQuery jobs in a healthy render, real in-function round-trip timings, and an end-to-end fallback exercise.

readVia(page, read, fromServing, fromBigQuery) is the only place that decides the engine; isServingActive(page) is a non-throwing predicate used by readVia, while servingActiveFor(page) throws for any unconverted 'bare gate' site. A per-page additive flag (SERVING_PAGES) plus a PAGES_WITH_FALLBACK set enforce that an enabled page has all its sites converted. Postgres serving.* uses SECURITY INVOKER RPCs so per-table RLS applies; a consolidated RPC LEFT-JOINs annotations onto an inventory spine. verify-serving-parity runs as owner (faithful copy), verify-scoped-parity runs as a scoped editor (correct subset), verify-tenant-isolation covers widening via a fixture. A k6 scenario in us-east1 mints a throwaway internal session per run and reaps it in a finally/trap to measure the floor.

Key Decisions & Trade-offs

Hardest Part

Discovering that the parity harness itself was blind: verify-serving-parity ran as owner (RLS bypassed) and verify-overview-metrics as superadmin, so a policy that NULLed an annotation for a scoped user would pass green. Fixed by adding verify-scoped-parity measured under a scoped editor session and requiring the consolidated LEFT-JOIN RPC to return annotations identical to the separate RPCs for that same session.

Technical Detail

Code

export async function readVia<T>(page, read, fromServing, fromBigQuery): Promise<T> { if (!(await useServing(page))) return fromBigQuery(); try { return await fromServing(); } catch (e) { console.error([serving] FALLBACK TO BIGQUERY page=${page} read=${read}: ${e.message}, + serving is degraded for this page and BigQuery is answering instead. This is not normal.); return fromBigQuery(); }
}

export type ServingPage = | "overview-metrics" | "linked-accounts" | "data-integrity" | "conversions" | "classifications" | "clients-console" | "geo" | "portal" | "wizard";
const ENABLED_PAGES = new Set((process.env.SERVING_PAGES ?? "").split(",").map(p=>p.trim()).filter(Boolean));
export function servingEnabledFor(page: ServingPage): boolean { return ENABLED_PAGES.has(page); }

const PAGES_WITH_FALLBACK = new Set<ServingPage>(["overview-metrics"]);
// module load: if SERVING_PAGES lists a page not here -> throw
// useServing(page): if enabled but a bare gate is reached -> throw

Measured Results

Migrated five production serving surfaces (overview-metrics, linked-accounts, data-integrity, conversions, crm) from BigQuery to Postgres behind a per-page flag with a loud fallback, each passing exact parity (owner + scoped), zero-BigQuery-jobs renders, real round-trip timings and an end-to-end fallback exercise. Conversions and CRM converged well under target (420/684 and 389/659 ms); Overview Metrics dropped from a BigQuery-dominated path to 793 ms with residual work handed to Phase 2B. Multiple silent-failure modes were caught before they reached a client.

Every figure above was recorded during the work itself. Where no number was measured, none is claimed.

Full Stack