OLTP/OLAP Best Practices
Mixed workloads need topology and GUC discipline, not hope. Use this checklist before pointing Metabase at the production primary on PostgreSQL 18.4.
How to Use This List
- Map every BI connection to replica or warehouse.
- Set role defaults before granting reporting login.
- Review
pg_stat_activityon primary weekly for scan queries.
A - Topology
- Route BI and ad hoc SQL to read replicas by default. Primary for writes and low-latency reads only.
- Display replication lag in BI tools when KPIs are time-sensitive. Trust but verify replay timestamp.
- Cap reporting pool_size independently from OLTP. Limit concurrent cache killers.
- Schedule heavy batch ETL off peak even on replicas. Protect replay catch-up.
- Plan warehouse export before primary melts. Logical replication or ETL when replicas saturate.
B - GUC and Role Split
- Separate roles: oltp_app, reporting, admin. Different timeouts and work_mem.
- Keep OLTP work_mem modest (4-32MB). Avoid global 256MB defaults.
- Set reporting work_mem only with pool_size and temp_file_limit. Prevent RAM and disk blowups.
- Disable or limit parallel gathers on OLTP role. Enable on replica for analytics.
- Use statement_timeout seconds on OLTP, minutes on reporting. Enforced via ALTER ROLE SET.
C - Query and Schema Guardrails
- Require EXPLAIN review for new reporting SQL. Before production schedule.
- Prefer materialized views for repeated dashboards. Refresh on replica when possible.
- Avoid wide SELECT * on TOAST-heavy tables in BI. Project columns.
- Index for OLTP paths on primary; denormalize on replica/MV for OLAP. Different access patterns.
- Block cross-tenant reporting without RLS tests. Connect as reporting role in staging.
D - Monitoring
- Alert primary CPU spike correlated with reporting usernames. Misrouting detector.
- Track replica lag SLO during BI windows. Scale replica before lag SLA breaks.
- Monitor temp_bytes on reporting role sessions. work_mem spill signal.
- Use pg_stat_statements by userid. Quantify OLAP cost share.
- Post-incident review: was analytics on primary? Common root cause.
FAQs
Is one big instance OK for MVP?
Yes briefly with strict timeouts; plan replica before revenue scale.Metabase on primary?
Only with read-only user, aggressive timeout, and tiny pool; replica strongly preferred.sync replica for BI?
Reduces lag; adds primary commit latency cost.CDC vs BI load?
Both compete on primary; monitor slots and WAL volume.partition for OLAP?
Prune historical scans; helps both primary and replica.cloud autoscaling?
Scale replica vCPU/RAM before primary if BI grows.MV on primary?
Refresh locks writes; prefer replica or concurrent refresh patterns.HTAP extensions?
Evaluate separately; core Postgres still needs guardrails.finance month-end?
Pre-scale replica pool; freeze ad hoc SQL on primary.performance group complete?
Revisit EXPLAIN section when plans regress after topology split.Related
- Workload Basics - Conflict overview
- Read Replicas for Reporting - Routing
- Parallel Query - Worker tuning
- Pooling Best Practices - Pool separation
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+.