Skip to content

ClickHouse vs PostgreSQL

1. Decision

Choose PostgreSQL for OLTP transactional applications requiring strict ACID guarantees, point updates/deletes, complex entity relationships, and operational simplicity; choose ClickHouse for high-throughput OLAP analytics, real-time telemetry, clickstream logging, and aggregation queries over billions of column records.

2. 30-Second Answer

PostgreSQL is a row-oriented relational database (OLTP) optimized for transactional CRUD operations, single-row updates, foreign key enforcement, and low-latency point reads using B-Tree index lookups on disk pages. ClickHouse is an open-source columnar database management system (OLAP) engineered for high-performance analytical queries. ClickHouse stores data on disk column-by-column, utilizes sparse primary indexes, vectorised query execution engines, and heavy SIMD CPU compression, enabling sub-second aggregation queries (COUNT, SUM, AVG) across billions of rows. However, ClickHouse does not support traditional real-time single-row ACID transactions or frequent update mutations.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
PostgreSQL Primary application database, financial transactions, user accounts, point CRUD queries Multi-billion row analytical aggregations, massive append-only logging (>50k QPS) Low (<5ms point lookups) Scale-up; Read Replicas; Manual sharding Strict ACID Medium
ClickHouse Real-time analytics dashboards, ad-tech metrics, clickstream, log storage, IoT sensor streams High-frequency single-row updates/deletes, transaction isolation, primary system of record Sub-second analytical queries (<100ms over 1B rows); Slow point reads Extreme Horizontal scale-out (Distributed tables & ZooKeeper/Keeper replication) Eventually consistent replication; Non-transactional batch writes High

4. Selection Rules

Requirement Best Choice Rationale / Example
Transactional Point Updates/Deletes PostgreSQL Row-level MVCC locking and immediate index update guarantees
Sub-Second Aggregations Over Billions of Rows ClickHouse Columnar disk layouts read only requested query columns, avoiding full row I/O
High Compression Ratio for Log Storage (5x-10x) ClickHouse Compression algorithms (LZ4, ZSTD) operate homogeneously on single column data types
Foreign Key Constraints & Complex Relationships PostgreSQL Enforce referential integrity and multi-table declarative SQL joins
High Velocity Append-Only Event Streaming ClickHouse MergeTree storage engine handles massive batch insert throughput efficiently
Geospatial & Vector Native Queries PostgreSQL PostGIS and pgvector integrate spatial and vector search with SQL

5. Tradeoffs

  1. Columnar Disk Layout vs Row-Oriented Storage
  2. Gain (ClickHouse): Reading 2 columns out of 100 avoids reading 98% of disk payload.
  3. Lose (ClickHouse): Fetching an entire single row (SELECT * WHERE id = X) requires assembly across 100 separate column files.
  4. Vectorized Execution & SIMD vs Scalar Tuple Iteration
  5. Gain (ClickHouse): Operations process CPU vectors (batches of data) directly in L1/L2 cache.
  6. Lose (ClickHouse): High memory consumption during large un-bounded GROUP BY queries.
  7. MergeTree Asynchronous Compaction vs MVCC B-Tree Updates
  8. Gain (ClickHouse): Writes append rapidly to disk parts; background engine asynchronously merges parts.
  9. Lose (ClickHouse): Mutations (ALTER TABLE ... UPDATE) are heavy asynchronous rewrite jobs, not instant transactional updates.
  10. Sparse Primary Index vs Dense B-Tree Index
  11. Gain (ClickHouse): Sparse index fits entirely in RAM (1 index mark per 8192 rows), consuming tiny memory footprint.
  12. Lose (ClickHouse): Cannot pinpoint single rows instantly; must scan 8192-row granularity blocks.
  13. Batch Ingestion vs Transactional Single-Row Inserts
  14. Gain (ClickHouse): Extreme ingestion rates (>100k rows/sec) when written in large batches (>=10,000 rows).
  15. Lose (ClickHouse): Single-row HTTP insert streaming creates millions of tiny disk parts, causing "Too many parts" cluster crashes.

6. Interview Counter Questions

  1. Why is SELECT COUNT(*) instantaneous in ClickHouse over 10 billion rows, but slow in PostgreSQL?
  2. How does the ClickHouse MergeTree storage engine process writes and background part merging?
  3. What is a Sparse Index in ClickHouse and why is it fundamentally different from a PostgreSQL B-Tree?
  4. Why is single-row insertion (INSERT INTO ... VALUES (...) 1 row at a time) an anti-pattern in ClickHouse?
  5. How do updates and deletes (ALTER TABLE ... UPDATE/DELETE) work internally in ClickHouse?
  6. How do you stream real-time transactional data from PostgreSQL to ClickHouse?
  7. What is Vectorized Query Execution and how does SIMD hardware acceleration speed up ClickHouse?
  8. How does ClickHouse handle replication without a master node (ClickHouse Keeper / ZooKeeper)?
  9. Why does data compression perform significantly better on Columnar stores than Row stores?
  10. When should you use ClickHouse Materialized Views vs traditional SQL views?

7. Gold Standard Answers

  1. PostgreSQL must scan heap page tuples to evaluate snapshot visibility per row under MVCC. ClickHouse reads only the metadata integer count or compressed column files directly, utilizing SIMD hardware vectorization to process millions of rows per core per millisecond.
  2. Writes are written as immutable disk "parts". A background process continuously merges smaller parts into larger merged parts (MergeTree algorithm), sorting data by the primary key during compaction.
  3. A PostgreSQL B-Tree index is dense (points to every single row). A ClickHouse index is sparse (creates 1 index mark entry every 8,192 rows). The sparse index identifies the physical data block, and ClickHouse scans that 8,192-row block in RAM.
  4. Each insert block creates a physical directory part on disk. Sending single-row inserts creates thousands of parts faster than background compaction can merge them, throwing "Too many parts in partition" exceptions. Writes must be batched (1,000 to 100,000 rows).
  5. Mutations are executed asynchronously as heavy background data rewrites. ClickHouse rewrites entire data parts containing targeted rows, marking old parts for garbage collection. Mutations should not be used for transactional real-time updates.
  6. Use Change Data Capture (CDC via Debezium reading Postgres WAL) to publish events to Kafka, then ingest into ClickHouse using the native Kafka Engine or a batch consumer worker.
  7. Vectorized execution passes chunks of column arrays (e.g., 4096 values) into CPU registers at once. Modern CPUs execute Single Instruction Multiple Data (SIMD) instructions over these arrays in parallel, maximizing CPU cache locality.
  8. ReplicatedMergeTree nodes use ClickHouse Keeper (or ZooKeeper) to coordinate replication logs and part consensus. Replicas pull missing data parts asynchronously from peer nodes to converge to eventual consistency.
  9. Columnar stores keep identical data types physically contiguous on disk (e.g., 1 billion integers together). LZ4/ZSTD algorithms achieve 5x-10x compression ratios on homogeneous contiguous data vs heterogeneous mixed row data.
  10. ClickHouse Materialized Views calculate aggregations incrementally at insert time as new data parts arrive, storing pre-aggregated results in a separate target MergeTree table for instant sub-millisecond querying.

8. Common Mistakes

  • Using ClickHouse as a primary transactional database for user accounts or e-commerce orders.
  • Streaming single-row INSERT statements into ClickHouse from web app HTTP handlers instead of batching.
  • Attempting to perform real-time row-by-row updates in ClickHouse like a relational SQL database.
  • Designing ClickHouse primary keys with high-cardinality floating attributes first, breaking sparse index block filtering.
  • Running heavy analytical aggregation queries (GROUP BY) directly on live PostgreSQL production OLTP instances.

9. Keywords

ClickHouse, PostgreSQL, OLTP, OLAP, MergeTree, Columnar Store, Sparse Index, Vectorized Execution, SIMD, ReplicatedMergeTree, ClickHouse Keeper, Part Merging, Batch Ingestion, Compression (LZ4/ZSTD), Debezium CDC.

10. Cheat Sheet

  • Core Difference: PostgreSQL = Row-Oriented OLTP (ACID, CRUD, B-Tree); ClickHouse = Column-Oriented OLAP (Analytics, SIMD, Sparse Index).
  • Disk Format: Postgres stores full rows together on disk pages; ClickHouse stores each column in separate compressed files.
  • Index Type: Postgres = Dense B-Tree (points to every row); ClickHouse = Sparse Index (1 mark per 8192 rows).
  • Ingestion Rule: ClickHouse requires batching writes (1k–100k rows per batch). Never issue single-row inserts.
  • Updates: Postgres = Instant MVCC update; ClickHouse = Heavy async background part rewrite (ALTER ... UPDATE).
  • Analytics Strategy: Offload analytical queries from Postgres to ClickHouse via CDC (Debezium + Kafka).

11. Related Decisions