Postgres Columnstore index vs. ClickHouse for analytics

Benchmark Summary

"Postgres on the Tablespace platform is 1.6 times faster and 50% less expensive than ClickHouse Cloud. This benchmark was independently run and verified by ClickBench committers."

Tablespace is a cloud-native database platform that adds columnstore indexes to Postgres for faster analytical (OLAP) queries. ClickHouse is a column-oriented SQL database management system for executing OLAP queries.

The performance of Tablespace was evaluated using the ClickBench Benchmark, comparing it with various OLAP databases. The ClickBench Benchmark uses the HITS dataset which contains 100 million rows from a real-world production environment to measure database performance, assessing factors like data import time and 43 additional queries. The benchmark assesses 60+ databases and ranks them based on their overall performance across all testing scenarios. Tablespace emerged as the world's fastest Postgres database platform for running analytical queries.

Tablespace vs. ClickHouse

Our comparison between Postgres on the Tablespace platform and ClickHouse revealed that Tablespace, leveraging fewer compute cores, achieved 1.6 times faster speeds than ClickHouse Cloud while also offering a 50% reduction in cost.

Benchmark Hardware & Cost Savings

For Tablespace the LARGE-1 compute shape which has 16CPUs and 32GB of RAM was used. For ClickHouse Cloud the 24CPUs and 96GiB of RAM compute shape was used in both AWS and GCP. Although the Tablespace shape is significantly smaller than the ClickHouse Cloud shapes, queries on Tablespace run 1.6 times faster than ClickHouse Cloud. ‍

"Tablespace emerged as the world's fastest Postgres database platform for running analytical queries."

Tablespace is also 50% less expensive than ClickHouse Cloud. These cost savings reported are actually very conservative because Postgres on the Tablespace platform can handle both transactional and analytical workloads (HTAP). Since ClickHouse does not support transactional workloads ClickHouse Cloud users would still need to run a separate transactional database further increasing infrastructure and operational costs. See Tablespace Pricing.

How does Tablespace work?

The default table type in Postgres is a heap table which stores data in a row-based format. While this row-based storage excels at transactional processing and maintaining data integrity, it is not inherently optimized for analytical workloads. Analytical queries often involve scanning large portions of data across multiple columns, which can lead to a significant I/O overhead when scanning row-based storage using Postgres' Transactional Engine.

Postgres on the Tablespace platform has been enhanced to execute analytical queries much faster than Vanilla Postgres. Analytical queries are accelerated by creating columnstore indexes on Postgres heap tables. The columnstore indexes which are transactionally consistent with the heap tables, store data in a columnar format to reduce I/O overhead. Tablespace also adds a Vectorized Columnar Engine to Postgres which queries the columnstore indexes very fast.

"Since the columnstore is implemented as an index rather than a proprietary table type, all Postgres features like primary keys, referential integrity constraints and additional indexes are supported. Its just Postgres!"

Tablespace is fully optimized for Hybrid Transactional and Analytical (HTAP) workloads. Analytical queries are routed transparently to the Vectorized Columnar Engine and transactional queries are routed to the Transactional Engine. This results in a 10x - 2000x increase in performance over Vanilla Postgres. Since the columnstore is implemented as an index rather than a proprietary table type, all Postgres features like primary keys, referential integrity constraints and additional indexes are supported. Its just Postgres!

Creating a columnstore index in Postgres

On the Tablespace platform, columnstore indexes can be defined for any Postgres table. First sign-up for a Tablespace account at https://cloud.tablespace.io and create a database and table. Let’s suppose we have the following table definition:

A columnstore index can be created on the table to speed-up analytical queries. The index should cover the most commonly accessed columns. The specific order of the columns in the CREATE INDEX ... USING columnstore statement does not matter.

Once created, the columnstore index is automatically used by the Postgres planner when executing analytical queries. Similar to other indexes in Postgres, the columnstore index guarantees full transaction and crash safety. The Tablespace quickstart guide can be found here.

‍

Tablespace vs. ClickHouse - Full benchmark results

These results were independently verified by ClickBench committers.

Resources

• Sign up for Tablespace here
• Tablespace Homepage is here
• Tablespace quickstart guide can be found here
• We encourage you to run the benchmarks yourselves. Instructions for running the benchmarks can be found here
• Tablespace vs. All Postgres Compatible databases - benchmark results
• Tablespace vs. ClickHouse - benchmark results

‍