ClickHouse matches PG for single-row UPDATEs and 4000 x faster for bulk UPDATEs
7 days ago
- #database-performance
- #PostgreSQL
- #ClickHouse
- ClickHouse matches PostgreSQL for single-row UPDATEs and is significantly faster for bulk UPDATEs, up to 4,000× in tests.
- PostgreSQL is fully transactional by default, adding overhead per update, while ClickHouse avoids this unless explicitly starting a transaction.
- Both databases use Multi-Version Concurrency Control (MVCC) for isolation, ensuring readers see consistent snapshots during updates.
- ClickHouse's columnar design and parallelism enable faster bulk updates by efficiently scanning and updating only relevant columns.
- Benchmark tests show ClickHouse's superior performance in analytical queries post-update, maintaining high speed whether updates are freshly applied or fully merged.
- Bulk updates in ClickHouse benefit from parallel processing, especially when WHERE clauses don't use primary keys, reducing scan times dramatically.
- PostgreSQL excels in transactional contexts, while ClickHouse shines in scenarios requiring high-speed updates and analytical query performance.
- Future developments may bring transaction support to ClickHouse, potentially closing the gap with PostgreSQL in transactional performance.