ClickHouse ® vs pg_mooncake: Does a Postgres columnstore work at scale?

PostgreSQL, enhanced with the pg_mooncake extension, has entered the top tier of analytical databases in the ClickBench benchmark, traditionally dominated by specialized systems like ClickHouse®. This extension integrates DuckDB’s engine to provide columnar storage and vectorized execution, allowing PostgreSQL to manage both transactional and analytical workloads. ClickHouse®, a native columnar database, is optimized for high-volume analytical queries with a distributed architecture and advanced compression techniques, often outperforming pg_mooncake in benchmark tests. While pg_mooncake offers flexibility for mixed workloads by maintaining transactional integrity and supporting updates via PostgreSQL’s native storage systems, it introduces complexity and lacks support for some PostgreSQL features. ClickHouse® excels in handling large datasets, complex queries, and high concurrency, making it preferable for workloads dominated by analytics. The choice between these systems depends on specific workload needs, with PostgreSQL being suitable for environments where transactional and analytical processes are integrated, and ClickHouse® favored for pure analytical tasks requiring maximum performance.