OLTP vs OLAP

Online transaction processing (OLTP) and online analytical processing (OLAP) describe two different jobs a database can be asked to do. The distinction matters on AWS because it is the first fork in the road: transactional workloads belong on RDS or Aurora, analytical workloads on Redshift, and a database tuned for one performs badly at the other.

Relational databases (OLTP)

A relational database in this sense is the system of record for day-to-day operations. It handles real-time transactions and updates against current data.

• Optimised for a high volume of small, fast transactions.
• Normalised schema, which reduces redundancy and keeps writes cheap.
• Frequent writes and updates.
• Typically smaller data volumes.
• Row-oriented storage — a single row is read or written as a unit.

Typical uses: customer orders, inventory management, banking transactions, user accounts, hotel and airline reservations.

Data warehouses (OLAP)

A data warehouse exists to answer questions about history rather than to run the business minute to minute.

• Optimised for complex queries over large scans.
• Denormalised schema — often a star or snowflake schema — chosen for query speed rather than write efficiency.
• Predominantly read operations, loaded in periodic batches.
• Much larger data volumes, retained over years.
• Column-oriented storage, so a query that touches three columns of a hundred reads only those three.

Typical uses: business reporting, sales trend analysis, financial forecasting, customer behaviour analysis, performance metrics.

Side by side

Aspect	Relational database (OLTP)	Data warehouse (OLAP)
Data updates	Continuous, real time	Periodic batch loads
Query type	Simple, standardised, few joins	Complex, ad hoc, many joins and aggregations
Data scope	Current	Historical
Size	Gigabytes	Terabytes to petabytes
Users	Operations staff, customers — many of them	Analysts and executives — few, each expensive
Performance measure	Transactions per second	Query response time
Data model	Highly normalised	Often denormalised
Typical operations	INSERT, UPDATE, DELETE	SELECT for analysis and reporting
Backup and recovery	Regular backups with point-in-time recovery	Periodic full backups, less frequent
Concurrency	Many concurrent short transactions	Fewer queries, each consuming more resources
Data retention	Current data, often a rolling window of weeks or months	Years of history

A worked example

Consider a retailer. Its relational database holds current inventory, processes sales and manages customer accounts — every checkout is a transaction against it. Its data warehouse holds several years of those same sales, denormalised, and answers questions such as which product lines are seasonal, how buying behaviour differs by region, and which stores underperform comparable ones.

The two systems hold overlapping data and serve entirely different questions. That is why both exist, and why running the analytical queries against the transactional database is the mistake this distinction is meant to prevent: a long-running aggregate scan holds resources that the checkout path needs.