OLAP vs. OLTP Database Partitioning

Term 16 of 68 in the ERPStack technical glossary

OLAP vs. OLTP Database Partitioning at a glance

OLTP shape

Many small transactions: read 1 row, write 2, commit — measured in milliseconds

OLAP shape

Few large scans aggregating millions of rows across a handful of columns

First separation

1 read replica for reporting, before any second database technology is introduced

Second step

A columnar store such as ClickHouse, fed asynchronously from PostgreSQL 18

Built with

PostgreSQL 18 with Drizzle ORM 0.45 for transactions, 1 read replica next, then ClickHouse or TimescaleDB fed over Apache Kafka, dashboards in Next.js 16 and React 19

Numbers that matter

OLTP touches 1 to 5 rows in milliseconds; OLAP scans millions; 1 read replica solves most of the conflict; 2 stores only when measured

OLTP engines

PostgreSQL 18 through Drizzle ORM 0.45 from Next.js 16, MongoDB Atlas for documents, or Redis for hot key-value paths behind a REST API

OLAP engines

ClickHouse and TimescaleDB fed over Apache Kafka from PostgreSQL 18, with MongoDB Atlas or Redis on the OLTP side and Next.js 16 and React 19 rendering the dashboards

Escalation order

4 steps before a second engine — index, partition, materialise, then add 1 PostgreSQL 18 read replica

Where the split appears

finance close reporting, retail sales analysis and logistics dashboards — the 3 places 1 Postgres instance starts serving 2 incompatible workloads at once.