Startups and mid-sized enterprises usually begin their infrastructure journey with a single, monolithic relational database—most commonly PostgreSQL. Initially, this is a brilliant architectural choice. PostgreSQL handles robust ACID compliance, foreign keys, constraints, and joins with unmatched reliability.
It handles users, passwords, sessions, financial logs, and configuration state perfectly. This is the realm of OLTP (Online Transaction Processing).
However, as the platform begins to offer heavy dashboards, historical trend tracking, and multi-dimensional filtering, response times begin to crack. The usual “solutions” are applied: adding read replicas, throwing RAM at the RDS instance, building materialized views, and applying aggressive indexations.
But at billions of rows, a fundamental truth becomes apparent: You cannot optimize an OLTP engine for exhaustive OLAP (Online Analytical Processing) workloads without eventually crashing the system or burning cash.
The Problem: Row-Based Engines vs Column-Based Queries
In PostgreSQL, data is stored in rows. If you want to compute the SUM(total_revenue) across 50 million sales records, the database engine must load entirely unnecessary fields (like customer descriptions, metadata blobs, and boolean flags) from the disk block just to access the total_revenue field.
This causes massive I/O saturation. Memory caches get flushed to make room for huge sequential scans, subsequently slowing down the core transactional duties of the app (like a user simply logging in).
The Numbers Don’t Lie
We benchmarked identical queries across both engines using a real-world retail dataset:
| Query | PostgreSQL (50M rows) | ClickHouse (50M rows) | Speedup |
|---|---|---|---|
SUM(revenue) | 4.2s | 0.08s | 52x |
GROUP BY category, month | 12.7s | 0.31s | 41x |
COUNT DISTINCT customers WHERE region = X | 8.9s | 0.19s | 47x |
Percentile calculation (P95) | 23.4s | 0.52s | 45x |
These aren’t synthetic benchmarks. This is a real dataset with realistic cardinality, running on comparable hardware. The difference is architectural, not configurational.
Why ClickHouse Is Fast (And What It Sacrifices)
ClickHouse achieves its speed through four fundamental design decisions:
1. Columnar Storage
Instead of storing (customer_id, name, email, revenue, date) as contiguous row blocks, ClickHouse stores each column independently. When you query SUM(revenue), it reads only the revenue column from disk. Everything else is ignored.
On a table with 30 columns, this means reading roughly 1/30th of the data for single-column aggregations. The I/O savings are enormous.
2. Aggressive Compression
Because columnar data tends to have repeated patterns (dates cluster by day, categories repeat across millions of rows), ClickHouse applies specialized compression codecs per-column:
- LZ4 for general-purpose compression (fast decompression)
- Delta encoding for timestamps and sequential integers
- Dictionary encoding for low-cardinality strings
Compression ratios of 5-10x are common. A 500GB dataset in PostgreSQL might occupy 60-80GB in ClickHouse, meaning more data fits in memory and disk reads are proportionally faster.
3. Vectorized Query Execution
ClickHouse processes data in batches of 8,192 rows (configurable) rather than row-by-row. This leverages CPU SIMD instructions to perform arithmetic operations across thousands of values in a single CPU cycle.
PostgreSQL processes rows individually through its executor. For analytical workloads touching millions of rows, the overhead per-row is the dominant cost.
4. What ClickHouse Gives Up
Nothing is free. ClickHouse achieves its analytical performance by deliberately not supporting:
- Full ACID transactions (eventual consistency model)
- Row-level UPDATE/DELETE (mutations are async background operations)
- Complex JOINs (local joins work, distributed joins are expensive)
- Foreign key constraints
This is precisely why you don’t use ClickHouse as your application database. It’s an analytical engine, not a transactional one.
The Montinegro Analytics Approach: The Structural Split
To guarantee sub-second reporting for our Business Intelligence clients regardless of incoming data scale, we strictly enforce a bifurcated data architecture in our SaaS solutions:
PostgreSQL: The Source of Truth
We utilize PostgreSQL exclusively for the transactional heartbeat. User sessions, core configurations, permission matrices, and application logic. Its memory is tightly bound to maintaining sub-10ms response times for the backend REST APIs.
The Django ORM talks to PostgreSQL. User authentication, tenant management, subscription billing, feature flags—all transactional operations stay here.
ClickHouse: The Analytical Anchor
For all payload events, heavy logs, metric aggregations, and dimensional telemetry, the data stream is funneled into ClickHouse—a lightning-fast columnar database management system.
Because ClickHouse stores data by columns, the same SUM(total_revenue) query across 50 million rows takes milliseconds. It only grabs the physical bytes of that specific column on disk, compressing it natively via advanced encodings, completely ignoring the noise.
Table Design Principles
We follow strict patterns for ClickHouse table design:
CREATE TABLE analytics.sales_events
(
event_date Date,
tenant_id UInt32,
product_id UInt64,
category LowCardinality(String),
region LowCardinality(String),
revenue Decimal(18, 2),
quantity UInt32,
created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, product_id)
TTL event_date + INTERVAL 3 YEAR
SETTINGS index_granularity = 8192;Key decisions:
- Partition by month keeps file management efficient and enables fast pruning
- Order by tenant first ensures multi-tenant queries only scan relevant data
- LowCardinality for string columns with <10,000 distinct values saves 5-10x memory
- TTL automatically drops data older than 3 years without manual intervention
Celery & Redis: The Ingestion Buffer
To prevent the Django backend from blocking while writing raw data to ClickHouse, we offload the processing heavy lifting. Asynchronous tasks are routed via Redis to Celery workers. These workers parse, clean, and batch-insert incoming data payloads (such as processed .pbip metadata from Power BI integrations) into ClickHouse with supreme efficiency.
The batch insertion pattern is critical. ClickHouse performs poorly with frequent single-row inserts (each insert creates a new data part). We buffer incoming records and flush in batches of 10,000-50,000 rows, achieving insert speeds of 500,000+ rows/second.
# Celery task for batch ClickHouse insertion
@shared_task(bind=True, max_retries=3)
def ingest_analytics_batch(self, records: list[dict]):
if len(records) < BATCH_THRESHOLD:
# Re-queue with exponential delay to accumulate more records
raise self.retry(countdown=30)
client = clickhouse_connect.get_client(...)
client.insert(
'analytics.sales_events',
data=[tuple(r.values()) for r in records],
column_names=list(records[0].keys())
)The Dashboard Consumption Layer
Finally, our visual layers—currently driven by Apache Superset (slated for headless transition to Cube.dev)—are directly hooked into ClickHouse, bypassing the Django ORM and the PostgreSQL transactional core entirely.
The result? The SaaS interface (rendered over Dart/Flutter) remains incredibly snappy. Client dashboards can drill down through billions of rows aggregated in real-time, while application users continue logging in and navigating settings on the PostgreSQL side with zero latency impact.
Materialized Views: Pre-Computed Intelligence
For dashboards that always show the same aggregations (daily revenue by region, weekly active users by tenant), we use ClickHouse’s materialized views to pre-compute results at insert time:
CREATE MATERIALIZED VIEW analytics.daily_revenue_mv
ENGINE = SummingMergeTree()
ORDER BY (tenant_id, event_date, region)
AS SELECT
tenant_id,
event_date,
region,
sum(revenue) AS total_revenue,
count() AS transaction_count
FROM analytics.sales_events
GROUP BY tenant_id, event_date, region;This view updates automatically as new data arrives. Querying daily_revenue_mv for a year of daily data across all regions returns in under 10ms, regardless of how many underlying raw events exist.
When to Make the Split
Not every application needs a dual-database architecture. The inflection point typically occurs when:
- Row count exceeds 10-50 million in your primary analytical tables
- Dashboard queries take >2 seconds and users start complaining
- Read replicas aren’t enough because the query pattern is fundamentally wrong for the storage engine
- You’re considering denormalization to speed up PostgreSQL queries (a sign you’re fighting the architecture)
If you’re pre-seed with 100,000 rows, PostgreSQL handles everything fine. Don’t over-engineer. But when growth hits the inflection point, having a migration plan ready is the difference between a weekend migration and a three-month fire drill.
The Architectural Reality
Data architecture shouldn’t be a monolith constraint. True scale requires an honest admission of limitations: Let PostgreSQL do what it was built to do—manage transactions reliably. Let ClickHouse do what it was built to do—destroy massive analytical queries in milliseconds. And let a proper ingestion layer (Celery + Redis) mediate between the two so neither system compromises the other.