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_activity on 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.

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+.

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_activity on 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.

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+.

OLTP/OLAP Best Practices

Search Documentation

OLTP/OLAP Best Practices