OLTP vs. OLAP: Why Both Matter

Your application database (OLTP) and your analytics database (OLAP) are optimized for fundamentally different workloads. Using the right engine for the right job gives you the best of both worlds.

Aspect	OLTP (PostgreSQL)	OLAP (ClickHouse)
Schema	Normalized (3NF)	Denormalized (Star/Snowflake)
Storage	Row-oriented	Column-oriented
Optimized for	Transactions (INSERT/UPDATE/DELETE)	Aggregations (SUM, COUNT, GROUP BY)
Consistency	Strict ACID	Eventual consistency OK
Compression	Moderate (2-4x)	Aggressive (10-40x)
Query pattern	Single-row lookups	Full-table scans, analytics

The 2-Step Approach

Step 1: Mirror 1:1

Simple, reliable, immediate value

Use the Altinity Sink Connector to mirror your OLTP tables directly into ClickHouse with the same schema. No transformation needed. This is the fastest path to analytics.

ClickHouse is still 10-100x faster for analytics even with the same normalized schema, because column-oriented storage and vectorized execution make aggregation queries dramatically faster.

Step 2: Transform

Reshape into a proper OLAP star schema

Use dbt Core or ClickHouse materialized views to reshape the mirrored data into a denormalized star schema. This unlocks maximum analytical performance — pre-joined fact tables, dimension tables, and pre-aggregated metrics.

Architecture: Mirror Then Transform

OLTP DB CDC Mirror ClickHouse dbt Transform ClickHouse ┌─────────┐ ┌──────────┐ ┌────────────┐ ┌──────────┐ │PostgreSQL│ ────▶ │ (raw) │ ────▶ │ dbt models │ ────▶ │ (star) │ └─────────┘ └──────────┘ └────────────┘ └──────────┘ source 1:1 mirror reshape star schema

Even Without Step 2, You Get:

Column-oriented storage — only reads the columns your query needs, not entire rows

Vectorized execution — processes data in batches using SIMD instructions, not row-by-row

No ACID overhead — no locks, no MVCC, no transaction bookkeeping for read queries

10-40x compression — your 100 GB PostgreSQL database might be 5-10 GB in ClickHouse

Start with Step 1. Mirror your data, run your analytics queries, and measure the improvement. If you need even more speed on complex joins and aggregations, then add Step 2 with dbt.