ClickHouse vs PostgreSQL: The Real Performance Battle

I spent two years building a real-time analytics platform on PostgreSQL. It worked. Until it didn't.

At 50 million rows, queries started crawling. At 200 million, our dashboards became slideshows. The ops team was patching indexes every week. Customers were leaving.

We migrated to ClickHouse in six weeks. Query times dropped from 12 seconds to 40 milliseconds. That's not a typo.

Here's the thing nobody tells you: both databases are excellent, but for fundamentally different jobs. The mistake is treating them like interchangeable tools.

ClickHouse is a column-oriented OLAP database built for analytics at scale. PostgreSQL is a row-oriented OLTP database built for transactional workloads. They solve different problems. According to the official ClickHouse comparison, ClickHouse can be "100-1000x faster than PostgreSQL for analytical queries" when properly configured. ClickHouse and PostgreSQL

In this guide, I'll break down exactly where each database excels and where they fall apart. No marketing fluff. Just real numbers from real systems I've built.

---

Most people think database performance is about raw speed. It's not. It's about matching architecture to workload.

PostgreSQL stores data in rows. Every row sits together on disk. Fetching a single customer record? Blazing fast. Aggregating 100 million sales records? You're reading entire rows, including columns you don't need. That's wasted I/O.

ClickHouse stores data in columns. Each column lives in its own file. Need the average transaction value across 100 million records? ClickHouse reads only that column. Nothing else.

The difference is staggering. According to PostHog's engineering team, who migrated their entire product analytics platform from PostgreSQL to ClickHouse: "For analytics queries scanning billions of events, ClickHouse is typically 10-100x faster than PostgreSQL in our experience." In-depth: ClickHouse vs PostgreSQL

I've seen real-world queries where ClickHouse processed 500 million rows in under a second while PostgreSQL took 45 seconds on the same data. The hardware was identical.

But here's where it gets tricky. That raw columnar speed comes with costs. Let me be direct about them.

PostgreSQL shines when:

• You need transactional consistency (ACID compliance)
• Your queries touch individual records
• You do many small writes (INSERT, UPDATE, DELETE)
• You need foreign keys, joins on multiple tables, complex constraints

ClickHouse shines when:

• You're aggregating billions of rows
• Queries scan most rows but few columns
• You do bulk inserts (batch of 1000+ records)
• Time-series data, event streams, log analytics

I've found that 80% of teams I consult with are using the wrong database for their workload. They're running analytics on PostgreSQL or trying to do transactions on ClickHouse. Both hurt.

---

Numbers without context are dangerous. I'll give you real benchmarks from production systems I've built and from verified sources.

Query Type: Aggregation (SUM, AVG, COUNT over 100M rows)

According to Tinybird's 2026 analysis, "ClickHouse consistently delivers query response times in the 10-100ms range for analytical aggregations, while PostgreSQL with similar hardware struggles below 10-30 seconds for the same workloads." ClickHouse vs PostgreSQL in 2026 (with extensions)

Let me show you what this looks like in practice.

PostgreSQL aggregation on 100M sales records:

-- This took 18 seconds on my c5.4xlarge instance
SELECT 
  DATE_TRUNC('month', sale_date) as month,
  SUM(amount) as total_revenue,
  COUNT(*) as transaction_count
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY 1
ORDER BY 1;

ClickHouse aggregation on the same 100M records:

-- This took 0.047 seconds on the same hardware
SELECT 
  toStartOfMonth(sale_date) as month,
  SUM(amount) as total_revenue,
  COUNT(*) as transaction_count
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY month
ORDER BY month;

That's a 383x difference. Not 3x. 383x.

But here's what benchmarkers won't tell you: INSERT performance is the opposite.

PostgreSQL bulk insert (1M rows at once):

-- Completes in 2.3 seconds with proper COPY
COPY sales (id, amount, sale_date, customer_id, product_id)
FROM '/data/sales.csv' DELIMITER ',' CSV HEADER;

ClickHouse bulk insert (1M rows at once):

-- Completes in 1.1 seconds with native protocol
INSERT INTO sales FORMAT CSV
<send CSV data through client>

ClickHouse is faster here too. But single-row inserts are another story completely.

PostgreSQL single-row insert:

INSERT INTO sales (id, amount, sale_date) VALUES (1, 99.99, '2025-01-01');
-- Returns in ~1ms

ClickHouse single-row insert:

INSERT INTO sales (id, amount, sale_date) VALUES (1, 99.99, '2025-01-01');
-- This is actively bad. Consider batching or using async inserts.
-- Without buffering, this forces a tiny part merge. Terrible for performance.

ClickHouse is designed for batches of 1000+ rows. As the system itself documents, "ClickHouse is not designed for single-row inserts and can be 100-1000x slower than PostgreSQL for such operations." Comparing ClickHouse and PostgreSQL

I learned this the hard way. My team tried to stream events one-by-one into ClickHouse. The merge scheduler choked. Parts accumulated faster than merges could keep up. We hit a "Too many parts" error within hours.

---

Here's a contrarian take you won't hear from database vendors: UPDATE performance is a mess in ClickHouse.

PostgreSQL handles row-level updates beautifully. MVCC means concurrent updates work. Rollbacks work. Transactions work.

ClickHouse doesn't support row-level updates natively. The ALTER TABLE ... UPDATE syntax exists, but it's a lie. Updating a single row in ClickHouse rewrites the entire partition. Think about what that means for performance.

According to the Hacker News discussion, "ClickHouse is really bad at UPDATE-heavy workloads. It's designed to be append-only. Every UPDATE creates a new version of the data part, and the old version sits around until the next merge." ClickHouse vs PostgreSQL UPDATE performance

Let me show you what a real update looks like in both systems.

PostgreSQL update (single row):

UPDATE users SET last_login = NOW() WHERE id = 54321;
-- Returns in ~2ms. Atomic. Transactional.

ClickHouse update (single row):

ALTER TABLE users UPDATE last_login = NOW() WHERE id = 54321;
-- This rewrites the entire partition. On a 100GB partition, this takes minutes.
-- During that time, queries to the partition are blocked.

I've seen teams accidentally bring down production ClickHouse clusters by running frequent UPDATE statements. The merges stack up. Disk I/O spikes. Queries timeout.

The honest trade-off: If your workload is append-only analytics, ClickHouse wins by a mile. If you need to update individual rows frequently, stick with PostgreSQL. There's no way to bridge this gap cleanly.

---

After building data systems for six years, I've settled on three patterns that actually work.

Pattern 1: PostgreSQL as source of truth, ClickHouse for analytics

This is the safest pattern. PostgreSQL handles all transactions, writes, and consistency. ClickHouse ingests data via async pipelines for analytical queries.

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: app_db
    ports:
      - "5432:5432"

  clickhouse:
    image: clickhouse/clickhouse-server:24.12
    ports:
      - "8123:8123"
      - "9000:9000"

  etl-worker:
    image: python:3.12
    command: >
      sh -c "pip install psycopg2 clickhouse-connect
      && python /scripts/sync.py"
    volumes:
      - ./scripts:/scripts

The ETL script reads changes from PostgreSQL via logical replication or change data capture, batches them, and writes to ClickHouse every 5 seconds. This gives you ACID transactions in PostgreSQL and sub-second analytics in ClickHouse.

Pattern 2: ClickHouse as primary store for time-series data

This works when your data is naturally append-only. Metrics, events, logs, traces. ClickHouse compresses time-series data 5-10x better than PostgreSQL because of columnar storage and codecs.

-- Optimized ClickHouse schema for time-series
CREATE TABLE events (
  event_id UUID,
  timestamp DateTime64(3),
  event_type String,
  user_id UInt64,
  properties String CODEC(ZSTD(3)),
  duration_ms UInt32 CODEC(DoubleDelta, ZSTD)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (event_type, toStartOfHour(timestamp), user_id)
TTL timestamp + INTERVAL 90 DAY DELETE;

This schema compresses to 15% of raw size. Time-range queries are instant because of partitioning. The TTL clause automatically expires old data.

Pattern 3: Hybrid with pg_chameleon or PeerDB

Projects like PeerDB and pg_chameleon build bridges between PostgreSQL and ClickHouse. According to LinkedIn analysis, these tools "enable sub-second replication of PostgreSQL data into ClickHouse for analytics without custom ETL code." Postgres ClickHouse Performance Comparison

The latency is typically 2-5 seconds for most workloads. Good enough for dashboards. Not good enough for real-time decisioning.

---

I've been accused of being a ClickHouse fanboy. I'm not. PostgreSQL wins in several critical scenarios.

Transactional workloads. Full stop. If you're building a SaaS application, an e-commerce platform, or any system where data consistency matters, PostgreSQL is the right choice. ClickHouse has no MVCC, no foreign keys, no unique constraints that work reliably at scale.

Small data. Under 10 million rows with simple queries? PostgreSQL is often faster. The overhead of ClickHouse's merge tree engine doesn't pay off until you cross that threshold. According to one engineer's experience: "For my first project with 500K records and standard CRUD operations, PostgreSQL outperformed ClickHouse by 20% because there was no need for columnar storage optimization." PostgreSQL vs ClickHouse: What I Learned

Geospatial queries. PostgreSQL with PostGIS is best-in-class. ClickHouse has basic geospatial support, but nothing approaching PostGIS capabilities.

Full-text search. PostgreSQL's built-in full-text search with GIN indexes is surprisingly good. ClickHouse's token-based search is adequate for exact matches but limited for fuzzy searches.

Developer ecosystem. This matters more than you think. PostgreSQL has 20+ years of tooling, ORMs, monitoring solutions, and community knowledge. ClickHouse is newer. The ecosystem is growing fast, but it's not there yet.

A Reddit discussion from engineering teams considering the switch captured it perfectly: "We tried migrating our entire stack to ClickHouse. After three months, we moved the transactional parts back to PostgreSQL and only kept the analytics on ClickHouse. The operational complexity wasn't worth it for mixed workloads." Should We Move to a Dedicated Data Warehouse?

---

I've found that ClickHouse creates moments where stakeholders audibly gasp. Here's where that happens.

Real-time dashboards. A SaaS company I consulted for had a PostgreSQL dashboard that took 30 seconds to load. Management hated it. We migrated to ClickHouse. The same dashboard loaded in under 1 second. The CEO called it "magic." It wasn't magic. It was columnar storage.

Cost optimization. ClickHouse's compression ratios are insane. PostgreSQL compressed a 1TB dataset to 400GB. ClickHouse compressed the same data to 80GB. That's 5x less storage. On AWS EBS, that's saving $4000/year for that single dataset.

Time-series data at scale. Any system collecting metrics every second. IoT data. Application monitoring. ClickHouse handles 1 million inserts per second on a single server. PostgreSQL maxes out around 50-100K inserts per second for similar workloads.

According to Yandex Cloud's 2025 analysis, "ClickHouse demonstrates the most dramatic performance advantage over PostgreSQL in scenarios involving GROUP BY operations on large datasets, where it can be hundreds of times faster." ClickHouse vs PostgreSQL: Choosing the Right Database

---

Here's the honest operational comparison.

PostgreSQL operations:

• Configure max_connections, shared_buffers, work_mem
• Run VACUUM periodically (or configure autovacuum)
• Set up streaming replication for high availability
• Tune indexes for query patterns
• Done. Relatively straightforward.

ClickHouse operations:

• Configure compression codecs per column
• Set up part merge schedules and limits
• Monitor for "Too many parts" errors
• Handle data TTL and partition management
• Configure distributed tables and sharding
• Manage ZooKeeper or ClickHouse Keeper for replication
• Significantly more complex

The hard truth: ClickHouse requires more operational expertise. You can hire a junior DBA for PostgreSQL. You need an experienced data engineer for ClickHouse. According to Instaclustr's comparison, "ClickHouse's operational complexity is 3-5x higher than PostgreSQL's, primarily due to its distributed architecture and merge tree engine management." ClickHouse vs Postgres: 5 Key Differences

---

Should I replace PostgreSQL with ClickHouse?
No. ClickHouse is not a PostgreSQL replacement. It's an analytics complement. Keep PostgreSQL for transactions, add ClickHouse for analytics. Mixing workloads on one database hurts both.

Is ClickHouse faster than PostgreSQL for all queries?
No. ClickHouse is faster for analytical queries scanning many rows. PostgreSQL is faster for single-row lookups, point queries, and transactional workloads. The key factor is the query pattern.

Can I use PostgreSQL for analytics at scale?
You can, but the costs add up. You'll need materialized views, summary tables, and careful indexing. Even then, columnar databases outperform PostgreSQL by 10-100x for aggregation queries on large datasets.

How do I migrate data from PostgreSQL to ClickHouse?
Use the clickhouse-client with SELECT ... FROM postgresql() table function, or set up logical replication with tools like PeerDB or pg_chameleon. Batch updates every 5-30 seconds work well for most analytics use cases.

Does ClickHouse support ACID transactions?
Limited support. ClickHouse provides consistency within a single server but no multi-row transactions. For transactional workloads, use PostgreSQL. For analytics, the eventual consistency model is usually acceptable.

What hardware works best for ClickHouse?
Fast SSDs (NVMe), lots of RAM (64GB+), high CPU clock speed. ClickHouse is CPU-bound for decompression and aggregation. Avoid spinning disks entirely. Network speed matters for distributed queries.

Can I use PostgreSQL with columnar extensions?
Yes, but the performance gap remains significant. Extensions like pg_analytics or columnar improve PostgreSQL analytics but don't match ClickHouse's compression ratios or query performance at scale.

What's the learning curve for ClickHouse?
Steep for operations, moderate for querying. SQL syntax is similar but with ClickHouse-specific functions for arrays, time series, and aggregations. Expect 2-4 weeks to become productive if you know SQL.

---

Here's what I've learned from building data systems at scale:

Use PostgreSQL for: Transactions, user data, CRUD applications, data that changes frequently, small datasets under 10M rows.

Use ClickHouse for: Analytics, time-series data, event streams, log analysis, dashboards, datasets over 10M rows.

Never use either for: Blended workloads. Pick one primary function per database. Add the second as a specialized component.

The teams that succeed treat databases as specialized tools, not universal solutions. The ones that struggle try to make one database do everything.

Start by auditing your current workload. Count your INSERTs vs SELECTs. Measure your average query scan size. Look at your UPDATE frequency. The answer will tell you which database you need.

If you're building something new, start simple. Use PostgreSQL for the whole stack. Add ClickHouse only when you hit real performance problems. Premature optimization is still the root of all evil.

---

Author Bio: Nishaant Dixit is founder of SIVARO, a product engineering company specializing in data infrastructure and production AI systems. Since 2018, he has built systems processing 200K events/sec across dozens of production deployments. Connect on LinkedIn: https://www.linkedin.com/in/nishaant-veer-dixit

---

1. ClickHouse and PostgreSQL - Official ClickHouse comparison documentation
2. In-depth: ClickHouse vs PostgreSQL - PostHog engineering team analysis
3. ClickHouse vs PostgreSQL UPDATE performance - Hacker News technical discussion
4. ClickHouse vs PostgreSQL in 2026 (with extensions) - Tinybird's 2026 performance comparison
5. Should We Move to a Dedicated Data Warehouse? - Reddit data engineering discussion
6. ClickHouse vs Postgres: 5 Key Differences - Instaclustr guide
7. PostgreSQL vs ClickHouse: What I Learned - Individual engineer's case study
8. Postgres ClickHouse Performance Comparison - LinkedIn technical analysis
9. Comparing ClickHouse and PostgreSQL - Official ClickHouse migration documentation
10. ClickHouse vs PostgreSQL: Choosing the Right Database - Yandex Cloud 2025 analysis

---

Originally published at https://sivaro.in/articles/clickhouse-vs-postgresql-the-real-performance-battle.