Sweaty Imposter

Run Analytics on the OLTP Database Directly Versus Move Analytics to a Separate OLAP Store

TradeoffDatabase InternalsMidSeniorUpdated

A reporting team wants to run hour-long aggregate queries against the same Postgres serving your checkout flow.

Short answer: Move analytics to a separate OLAP store (columnar warehouse, replica with column store extension, Snowflake/BigQuery). OLTP engines are tuned for short row-oriented transactions; long scans contend with the buffer cache, pollute the plan cache, and (under MVCC) hold back the xmin horizon, bloating tables.

The Two Options

A

Run analytics on the OLTP database directly

B

Move analytics to a separate OLAP store (columnar warehouse, replica with column store extension, Snowflake/BigQuery)

Drill This: Your Pick First

The Better Pick

Option B

Move analytics to a separate OLAP store (columnar warehouse, replica with column store extension, Snowflake/BigQuery)

OLTP engines are tuned for short row-oriented transactions; long scans contend with the buffer cache, pollute the plan cache, and (under MVCC) hold back the xmin horizon, bloating tables. OLAP stores are columnar, vectorized, and built for this.

When the Other One Wins

Run analytics on the OLTP database directly

Small scale or early-stage products can absolutely run reports on a read replica; introducing a warehouse before you need one is over-engineering.