Storage and Access Optimization: Row vs Column, Indexes, and Partitioning

OLTP and OLAP use radically different storage layouts and access patterns. OLTP relies on row oriented storage with page level caching and B-tree indexes optimized for point lookups and short range scans. When you query an order by ID, the database uses a B-tree index to jump directly to the page containing that row, reads the entire row (all columns together), and returns in single digit milliseconds. Indexes are narrow and selective, targeting primary access paths like user ID, order ID, or session token. Wide or unselective secondary indexes amplify write latency because every insert or update must maintain multiple index structures, creating write amplification that can degrade throughput from 50,000 to 10,000 writes per second. OLAP uses columnar storage where each column is stored separately with aggressive compression via dictionary encoding and run length encoding. When you query for total revenue by region, the system reads only the revenue and region columns, ignoring the other 50 columns in the fact table. This dramatically reduces I/O: scanning 1 billion rows might read 10 GB of compressed column data instead of 500 GB of full row data. Vectorized execution processes thousands of values per CPU instruction using Single Instruction Multiple Data (SIMD), achieving scan throughput of gigabytes per second per core. Zone maps and min/max statistics enable partition pruning: if you filter for orders in March 2024 and a partition's zone map shows it only contains January data, the query engine skips it entirely without reading a single byte. Partitioning strategies differ fundamentally. OLTP partitions large tables by time or tenant to keep working sets hot and maintenance bounded: archiving old partitions, rebuilding indexes, or running vacuum operations can target specific partitions without locking the entire table. Amazon's order tables might partition by order date with daily or weekly granularity, keeping the last 90 days hot for fast lookups while aging older data to cheaper storage tiers. OLAP partitions by time and frequently filtered dimensions (date, region, product category), then clusters or sorts within partitions to group related data together for better compression and predicate pushdown. Poor partitioning decisions have severe consequences: partitioning OLAP data by high cardinality user ID instead of by time creates millions of tiny files that overwhelm metadata operations and prevent partition pruning, forcing full scans that turn 10 second queries into 10 minute queries.

Amazon order lookup: B-tree index on order_id jumps to page, reads entire row in 3 ms; analytical query scans 50 billion order rows but reads only order_date, region, and revenue columns (columnar), completing in 12 seconds

Google BigQuery: columnar scan of 10 TB fact table with partition pruning on date filters; reads only 500 GB after pruning 95% of partitions, returns aggregates in 8 seconds using slot parallelism

Uber trip analytics: OLTP trip state table has selective index on trip_id and user_id; OLAP fact table partitioned by day and clustered by city_id, enabling city specific revenue rollups to scan 1% of data