Overview
This article compares the UPDATE performance of ClickHouse and PostgreSQL, highlighting ClickHouse's significant speed advantages in bulk updates while also acknowledging PostgreSQL's strengths in single-row updates. The findings emphasize the importance of execution models and the impact of caching on performance metrics.
What You'll Learn
1
How to benchmark UPDATE performance between ClickHouse and PostgreSQL
2
Why ClickHouse can be up to 4,000× faster on bulk updates compared to PostgreSQL
3
When to choose ClickHouse over PostgreSQL for OLTP workloads
Prerequisites & Requirements
- Understanding of OLTP and OLAP database concepts
- Familiarity with ClickHouse and PostgreSQL(optional)
Key Questions Answered
How does ClickHouse's UPDATE performance compare to PostgreSQL?
ClickHouse can perform bulk UPDATEs up to 4,000× faster than PostgreSQL under cold conditions, while achieving parity on single-row updates. This performance advantage stems from ClickHouse's columnar design and parallel processing capabilities, which allow it to efficiently locate and update large datasets.
What are the key differences in transaction handling between ClickHouse and PostgreSQL?
PostgreSQL wraps every statement in a fully transactional context, ensuring durability and rollback capabilities, while ClickHouse does not enforce transactions by default. This leads to PostgreSQL having per-update overhead, whereas ClickHouse can operate with lower latency in scenarios where transactions are not required.
What are the implications of MVCC in PostgreSQL and ClickHouse?
Both PostgreSQL and ClickHouse use Multi-Version Concurrency Control (MVCC) to allow concurrent reads and writes without blocking. This ensures that readers see a consistent snapshot of the data, but the implementation differs, affecting performance during updates.
Key Statistics & Figures
Bulk UPDATE performance
up to 4,000× faster
ClickHouse compared to PostgreSQL under cold conditions
Data size for ClickHouse
~30 GiB
Size of the TPC-H lineitem table with scale factor 100
Data size for PostgreSQL
~85 GiB
Size of the TPC-H lineitem table with scale factor 100
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Clickhouse
Used for OLAP workloads and performance benchmarking
Database
Postgresql
Used as a baseline for OLTP performance comparison
Key Actionable Insights
1Consider using ClickHouse for workloads that involve frequent bulk updates, as it significantly outperforms PostgreSQL in this area.With ClickHouse's ability to handle bulk updates up to 4,000× faster, it is ideal for scenarios where large datasets are modified frequently, such as in OLTP applications.
2Leverage PostgreSQL's transactional capabilities when data integrity and rollback are critical.PostgreSQL's full transactional support ensures that updates are durable and can be rolled back, making it suitable for applications where data consistency is paramount.
3Evaluate the caching strategies of both databases to optimize performance based on your workload.Understanding how each database utilizes caching can help you configure your environment for optimal performance, especially in scenarios where data is frequently accessed.
Common Pitfalls
1
Overlooking the differences in transactional handling between ClickHouse and PostgreSQL can lead to misconfigured applications.
Developers should be aware that PostgreSQL's transactional model adds overhead, which may not be suitable for all workloads, especially those that can benefit from ClickHouse's faster bulk updates.
Related Concepts
Oltp Vs Olap
Multi-version Concurrency Control (mvcc)
Database Performance Optimization