OLTP vs OLAP: Transactional vs Analytical Workloads

OLTP (Online Transaction Processing) describes the workload shape of typical application databases: many small, fast read/write transactions, each touching a small number of rows, needing strong consistency and low latency — RDS, Aurora, and DynamoDB are all optimized for this shape. OLAP (Online Analytical Processing) describes a fundamentally different shape: relatively few queries, but each scanning and aggregating across enormous numbers of rows (e.g. 'total revenue by region by month for the last 3 years'), where individual-row latency doesn't matter but aggregate scan throughput does.

Amazon Redshift is a columnar data warehouse purpose-built for OLAP: it stores data column-by-column rather than row-by-row (the opposite of RDS/Aurora's row-oriented storage), which is dramatically more efficient when a query only needs a few columns across millions of rows (a common analytical pattern) rather than all columns of a few specific rows (a common transactional pattern). Redshift also uses massively parallel processing across multiple compute nodes to execute a single large query, something OLTP-oriented databases aren't architected for.

Running OLAP-shaped queries directly against an OLTP database is a common and painful anti-pattern: a large aggregate scan competes for the same resources (I/O, memory, CPU) that live transactional queries need, degrading application performance for real users. The standard fix is extracting data into a separate data warehouse (via a scheduled export, change-data-capture pipeline, or streaming ETL) specifically so analytical workloads have their own dedicated, appropriately-architected resource.