Columnar Databases for Web Analytics: ClickHouse vs Postgres Group By
Why running aggregations on 50 million event logs in Postgres grinds your server to a halt, and how columnar storage computes averages in 3 milliseconds.
When an application hits 50,000 active users, product analytics queries become dangerous. If you run a SELECT date, count(*) FROM page_views GROUP BY date query across a traditional row-oriented PostgreSQL table with 40 million rows, Postgres has to read every single row off disk, parsing unused columns into memory.
Columnar databases like ClickHouse store data vertically by column rather than horizontally by row.
When you query only the timestamp column:
- ClickHouse only reads bytes associated with that specific field.
- Because identical data types sit adjacent on disk, columnar compression ratios reach 80% to 90%.
- SIMD vector instructions on modern CPUs process 100 million rows per second per core.
Queries that timed out after 30 seconds in Postgres executed in 4.2 milliseconds in ClickHouse. If you are building event analytics or telemetry dashboards, columnar storage is an architectural cheat code.
Written by Daniel Dano
danieldano.com ↗15-year-old software developer, founder of Ryla Labs (50,000+ monthly users across 12 countries), author of Built Young, and high school student leader.