Row-Oriented (Postgres) Versus Columnar (BigQuery/ClickHouse)
TradeoffSeniorStaffUpdated
Analytics dashboard scanning aggregations over billions of rows but only 3-4 columns at a time.
Short answer: Columnar (BigQuery/ClickHouse). Columnar stores read only the queried columns, compress them extremely well, and vectorize aggregations, orders of magnitude faster for analytics.
The Two Options
A
Row-oriented (Postgres)
B
Columnar (BigQuery/ClickHouse)
Drill This: Your Pick First
The Better Pick
Option B
Columnar (BigQuery/ClickHouse)
Columnar stores read only the queried columns, compress them extremely well, and vectorize aggregations, orders of magnitude faster for analytics.
When the Other One Wins
Row-oriented (Postgres)
For online transaction processing (OLTP) workloads with frequent row inserts/updates and lookups by primary key, row stores win on write latency and point reads.