Column-oriented databases

Column-oriented (columnar) databases store each column together on disk, not each full row. They are optimized for analytics (read many rows, few columns). Row-oriented SQL databases (PostgreSQL, MySQL) store each row together and are optimized for OLTP (read/update one row at a time).

Column DB faster than row — for analytics

	Row oriented	Column oriented
Storage	`orderId Amount 1 10 2 20 3 30 How its stored on Disk? struct row {id, amount} {1,10}{2,20}{3,30}`	`orderId: 1 2 3 4 Amount 30 32 56 78 How its stored in Disk? file1(orderId): 1 2 3 4 file2(Amount): 30 32 56 78`
Query (sum all amount)	`SELECT SUM(amount) FROM orders;` How it works underhood( if there are 1 Million rows) - SQL need to traverse every byte - Store all entries in RAM and then sum. - Consume more RAM & Slower wrt columnar	`SELECT SUM(amount) FROM orders;` How it works underhood - It bypasses the ID file and all other coloumn files - It reads only the amount file sequentially from disk. - Less RAM, faster
Examples	Mysql, postgres	ClickHouse, Amazon Redshift, Google BigQuery, Apache Parquet (file format)
Usecase	Better for transactional workloads, where you frequently read or write entire records	- AVG / SUM / COUNT over millions of rows - GROUP BY time bucket, region, product - Filter + aggregate (WHERE timestamp range, GROUP BY sensor)

Columnar wins for(Analytics, Time series) data

Time series data = many rows with a timestamp plus metrics (sensor readings, stock prices, request latency). Analytics usually touch timestamp + one or two metrics, not every column — that is where columnar stores win.

Sample data — IoT sensor readings

timestamp            sensor_id  temperature_c  humidity_pct  firmware_version
2026-08-15 10:00:00  S1         22.1           45            v3.2
2026-08-15 10:01:00  S1         22.3           46            v3.2
2026-08-15 10:00:00  S2         18.0           50            v3.1
2026-08-15 10:01:00  S2         18.2           51            v3.1
... (billions of rows over months)

Analytics query

SELECT
  date_trunc('hour', timestamp) AS hour,
  AVG(temperature_c)          AS avg_temp
FROM sensor_readings
WHERE sensor_id = 'S1'
  AND timestamp BETWEEN '2026-08-01' AND '2026-08-15'
GROUP BY hour
ORDER BY hour;

This query needs only timestamp, sensor_id, and temperature_c. It does not need humidity or firmware_version.

Row-oriented SQL (e.g. PostgreSQL)

On disk — each row stored together:
[10:00 S1 22.1 45 v3.2][10:01 S1 22.3 46 v3.2][10:00 S2 18.0 50 v3.1]...

For the query above:
• Scans millions of matching rows (or index ranges on timestamp + sensor_id)
• Each row page still carries humidity + firmware — extra bytes read from disk
• Works, but I/O grows with row width and table size