Analytics: OLTP vs OLAP and time-series stores

OLTP (online transaction processing) is the workload of your product: insert an order, update a balance, fetch a user by ID. Queries touch a few rows, need millisecond latency and strict correctness. Row-oriented databases such as PostgreSQL and MySQL store each row's columns together, which is ideal for reading or writing a whole row.

OLAP (online analytical processing) is the workload of dashboards and reports: 'revenue per city per hour for the last 90 days'. Queries scan millions or billions of rows but only a few columns. Column-oriented stores (ClickHouse, Apache Druid, Apache Pinot, BigQuery, Snowflake, Redshift) store each column separately, compress it heavily (often 5 to 20 times), and process data in vectorised batches, so scanning one column of a billion rows takes seconds or less.

Time-series stores (Prometheus, InfluxDB, TimescaleDB, VictoriaMetrics) are a specialised case for data shaped as (metric, labels, timestamp, value): server metrics, sensor readings, stock ticks. They optimise for append-only writes in time order, compression of nearby values, downsampling (keeping 1-minute averages after 7 days) and automatic retention.

The architecture that connects them: the OLTP database stays the source of truth; changes flow out via CDC or events into Kafka; stream processors or batch jobs load them into a warehouse or real-time OLAP store; dashboards and data scientists query that store, never the production primary.

1. 1Separate the workloads: analytical queries that scan large ranges compete with transactions for CPU, memory and I/O. Even a read replica only isolates load partly, and long queries on a replica can conflict with replication.
2. 2Capture changes: Debezium reads the Postgres WAL or MySQL binlog and publishes row changes to Kafka; product events (clicks, views) go straight to Kafka from services.
3. 3Batch path (ELT): land raw data in object storage as Parquet (a data lake), then transform with SQL (dbt) in a warehouse such as BigQuery, Snowflake or Redshift. Freshness: minutes to hours. Cheap and flexible for ad-hoc analysis.
4. 4Real-time path: ingest from Kafka directly into ClickHouse, Druid or Pinot, which make rows queryable within seconds; or pre-aggregate with Flink (for example clicks per ad per minute) and write results.
5. 5Model for analytics: a star schema with a large fact table (orders, events) and small dimension tables (city, restaurant, date), or one wide denormalised table, which column stores handle well.
6. 6Partition and sort by time: partition by day or month so queries skip old data, and sort by the most common filter (for example (city, event_time)) so the engine reads only matching blocks.
7. 7Pre-aggregate what is queried constantly: materialized views or rollup tables (per minute, per hour) turn a billion-row scan into a thousand-row lookup.
8. 8Time-series specifics: keep raw points for a short window (say 15 days), downsample to coarser resolution for long-term storage, and limit label cardinality, since every unique label combination (for example user_id as a label) creates a new series and can exhaust memory.

-- ClickHouse: column store table plus a per-minute rollup
CREATE TABLE order_events (
  event_time DateTime,
  city_id    UInt32,
  order_id   UInt64,
  status     LowCardinality(String),
  amount     UInt32
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (city_id, event_time);

CREATE MATERIALIZED VIEW orders_per_min
ENGINE = SummingMergeTree ORDER BY (city_id, minute) AS
SELECT city_id, toStartOfMinute(event_time) AS minute,
       count() AS orders, sum(amount) AS revenue
FROM order_events WHERE status = 'PLACED'
GROUP BY city_id, minute;