Reference: Analytics Replica Topology

Composite reference for e-commerce OLTP on PostgreSQL 18.4 (GCP Cloud SQL) with BI workloads isolated to a delayed physical replica (15-minute recovery_min_apply_delay). Metabase and dbt read replica only; primary serves checkout.

Topology

Primary (OLTP, r8g.4xlarge)
   │
   ├── Hot standby replica (read-only API, <5s lag)
   │
   └── Delayed replica (BI + dbt, 15 min lag, larger disk)

Configuration

# postgresql.conf on delayed replica only
recovery_min_apply_delay = '15min'
hot_standby_feedback = on
max_standby_streaming_delay = '30s'

-- On primary: monitor replay on both replicas
SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       replay_lag
FROM pg_stat_replication;

BI Guardrails

-- Role limits on delayed replica
ALTER ROLE bi_reader SET statement_timeout = '10min';
ALTER ROLE bi_reader SET work_mem = '64MB';
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM bi_reader;

# dbt profiles.yml points to delayed replica host
# Metabase connection string includes ApplicationName for pg_stat_activity

• No CREATE TEMP TABLE marathon queries on hot replica.
• dbt runs ANALYZE only on mart schemas owned by BI role.

Incident Story (Abbreviated)

Black Friday: analyst ran missing-join query on hot replica; replay lag threatened OLTP failover RPO display. Fix: Moved ad hoc SQL to delayed replica; added statement_timeout and routing docs.

Cost Note

Delayed replica one size larger than hot replica (extra disk for sort spills). Cheaper than warehouse for < 5 TB curated marts.

Related

• Reference: Multi-Tenant SaaS - OLTP baseline
• OLAP Offload ADR - when to leave Postgres
• Replication Lag Emergency - lag triage
• SLO Dashboards - freshness metrics

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.