Example Table
| rowid | id | first_name | last_name | ssn | salary | dob | title | joined |
|---|
| 1001 | 1 | John | Smith | 111 | 101,000 | 1/1/1991 | eng | 1/1/2011 |
| 1002 | 2 | Alice | Johnson | 666 | 95,000 | 5/3/1990 | mgr | 2/3/2012 |
| 1003 | 3 | Bob | Lee | 333 | 99,000 | 3/4/1992 | eng | 3/5/2013 |
Sample Queries (No Indexes)
SELECT first_name FROM emp WHERE ssn = 666;
SELECT * FROM emp WHERE id = 1;
SELECT SUM(salary) FROM emp;
Row-Oriented Databases
Concept
- Data stored as rows on disk.
- A single disk I/O reads a complete row (all columns).
- Good for operations that need many/all columns of specific rows.
- Example usage: OLTP (Online Transaction Processing).
Disk Layout
Row 1: [1, John, Smith, 111, 101000, 1/1/1991, eng, 1/1/2011]
Row 2: [2, Alice, Johnson, 666, 95000, 5/3/1990, mgr, 2/3/2012]
Row 3: [3, Bob, Lee, 333, 99000, 3/4/1992, eng, 3/5/2013]
Dry Run of Queries
- SELECT first_name FROM emp WHERE ssn = 666;
- Scans row-by-row → finds ssn = 666 in Row 2 → fetches first_name = Alice
- I/O Cost: Higher if the table is large (full scan)
- SELECT * FROM emp WHERE id = 1;
- Row 1 matches id = 1 → entire row fetched in one read
- Efficient for full-row reads
- SELECT SUM(salary) FROM emp;
- Must scan all rows and access salary from each
- Total = 101000 + 95000 + 99000 = 295000
- Less efficient for large tables due to row scanning
Column-Oriented Databases
Concept
- Data stored as columns on disk.
- A single disk I/O reads one column across multiple rows.
- Great for aggregation and queries involving few columns.
- Example usage: OLAP (Online Analytical Processing).
Disk Layout
Column: id → [1, 2, 3]
Column: first_name → [John, Alice, Bob]
Column: last_name → [Smith, Johnson, Lee]
Column: ssn → [111, 666, 333]
Column: salary → [101000, 95000, 99000]
Column: dob → [1/1/1991, 5/3/1990, 3/4/1992]
Column: title → [eng, mgr, eng]
Column: joined → [1/1/2011, 2/3/2012, 3/5/2013]
Dry Run of Queries
- SELECT first_name FROM emp WHERE ssn = 666;
- Read ssn column → find match at index 1 → read first_name[1] = Alice
- Efficient with minimal reads
- SELECT * FROM emp WHERE id = 1;
- Find match in id column at index 0 → fetch all values from other columns at index 0
- I/O Cost: High (reads all columns individually)
- SELECT SUM(salary) FROM emp;
- Only salary column is accessed → [101000 + 95000 + 99000] = 295000
- Highly efficient for aggregation
Pros & Cons Comparison
| Feature | Row-Oriented | Column-Oriented |
|---|
| Read/Write Performance | Optimal for inserts and updates | Slower writes (each column updated separately) |
| Query Type | OLTP (e.g., banking, ecommerce) | OLAP (e.g., reporting, analytics) |
| Compression Efficiency | Poor | Excellent (similar values compressed well) |
| Aggregation Performance | Poor (must scan full rows) | Excellent (only required columns read) |
| Query with Many Columns | Efficient | Slower due to multiple column reads |
| Query with Few Columns | Slower (unnecessary data read) | Efficient (only needed columns read) |
| Storage Layout | Row-by-row | Column-by-column |
| Use Case | Frequent updates, record access | Reporting, analytics, dashboards |
Summary
- Row-Based Databases are suitable for workloads with frequent inserts/updates and where entire rows are often accessed (OLTP).
- Column-Based Databases excel at analytical workloads with many aggregations and selective column access (OLAP).