Compare Row-Oriented (OLTP InnoDB) vs Columnar-Oriented (OLAP ClickHouse / DuckDB) database storage.
❓ Interview Prompt: Compare Row-Oriented databases (PostgreSQL/MySQL - fast single-row CRUD) vs Columnar Databases (ClickHouse/DuckDB - high-speed analytical aggregations, vectorization, SIMD).
Comparing database storage layout models:
| Feature | Row-Oriented Storage (MySQL InnoDB / Postgres) | Columnar Storage (ClickHouse / DuckDB / Redshift) |
|---|---|---|
| Data Layout | Consecutive rows stored together on disk pages | Consecutive values of a single column stored together |
| Best For | High-concurrency OLTP transactional reads/writes ('WHERE id = 101') | High-speed OLAP analytical queries ('SUM(price) WHERE year = 2026') |
| Compression | Poor compression ratio | Extreme Compression ($5\times-10\times$ ratio; identical data types) |
| Vectorization | Process 1 row at a time | SIMD Hardware Vectorization (Processes millions of numbers per CPU cycle) |
Code Implementation Example
-- Columnar query reading ONLY 2 columns from disk instead of scanning full rows
SELECT country, SUM(total_amount)
FROM analytics_events -- Columnar engine (ClickHouse)
GROUP BY country;Use Row-Oriented databases (MySQL/PostgreSQL) for transactional OLTP apps, and Columnar databases (ClickHouse/DuckDB) for analytical OLAP dashboards.