Transactional (OLTP) vs Analytical (OLAP) Databases

The two families of database answer different questions and are tuned for different access patterns. Choosing the wrong one shows up as either slow reports or a system that cannot keep up with its own writes.

Transactional databases (OLTP — online transaction processing)

1. Handle day-to-day transactions and real-time operations.
2. Are optimised for fast, small read and write operations.
3. Maintain data consistency through ACID properties — atomicity, consistency, isolation, durability.
4. Typically use normalised data structures to minimise redundancy.
5. Serve use cases such as banking transactions, order processing and inventory management.
6. Hold current, in-the-moment data.
7. Are designed for many concurrent users each making a small change.

Analytical databases (OLAP — online analytical processing)

1. Are built for business analysis and decision support.
2. Are optimised for complex queries and large-scale aggregation.
3. Focus on analysis and reporting rather than transaction processing.
4. Often use denormalised structures so that queries touch fewer joins.
5. Serve use cases such as business intelligence, trend analysis and forecasting.
6. Hold historical data spanning long time periods.
7. Are designed for fewer users running complex queries over large datasets.

Note the naming trap: an operational database is the transactional one — the store the running business writes to. OLAP is the analytical side, fed from it.

Common architectural patterns

For transactional databases

1. Primary–replica
   • The primary node handles writes.
   • Multiple read replicas scale reads.
   • Replication is synchronous or asynchronous, which decides whether a replica can serve a read immediately after a write.
2. Sharding
   • Data is distributed across multiple nodes.
   • Horizontal partitioning by key range or hash value.
   • Each shard owns a subset of the total data.

For analytical databases

1. Star schema
   • Fact tables in the centre.
   • Dimension tables around the edges.
   • Optimised for analytical queries.
2. Data lake
   • Raw data stored as it arrives.
   • Schema applied on read rather than on write.
   • Multiple processing engines over the same storage.
3. Lambda architecture
   • A batch processing layer.
   • A speed layer for real-time processing.
   • A serving layer that answers queries from both.

graph TD subgraph "Transactional DB Patterns" A[Client Application] --> B[Load Balancer] B --> C1[Primary DB] C1 --> D1[Replica 1] C1 --> D2[Replica 2] style C1 fill:#f9f,stroke:#333 style D1 fill:#bbf,stroke:#333 style D2 fill:#bbf,stroke:#333 subgraph "Sharding Pattern" E[Router] --> F1[Shard 1] E --> F2[Shard 2] E --> F3[Shard 3] end end subgraph "Analytical DB Patterns" G[ETL Process] --> H[Data Warehouse] H --> I1[Data Mart 1] H --> I2[Data Mart 2] J[Stream Processing] --> K[Real-time Analytics] style H fill:#f96,stroke:#333 style I1 fill:#9f6,stroke:#333 style I2 fill:#9f6,stroke:#333 end

The star schema

A star schema classifies every table as either a fact table or a dimension table. Fact tables store observations — sales, balances, readings — as numeric measures plus the keys of the dimensions that describe them. Dimension tables describe the business entities those keys point at.

erDiagram FACT_SALES ||--o{ DIM_CUSTOMER : has FACT_SALES ||--o{ DIM_PRODUCT : has FACT_SALES ||--o{ DIM_DATE : has FACT_SALES ||--o{ DIM_STORE : has FACT_SALES { int sale_id PK int customer_id FK int product_id FK int date_id FK int store_id FK decimal amount int quantity decimal discount } DIM_CUSTOMER { int customer_id PK string name string email string segment string address } DIM_PRODUCT { int product_id PK string name string category string sub_category decimal price } DIM_DATE { int date_id PK date full_date int year int quarter int month string month_name } DIM_STORE { int store_id PK string name string city string state string country }

Key characteristics:

1. Central fact table
   • Holds the business metrics.
   • Carries foreign keys to every dimension table.
   • Measures are typically numeric and additive.
2. Dimension tables
   • Hold descriptive attributes.
   • Are joined to the fact table by their primary keys.
   • Are denormalised for query performance.
3. Advantages
   • Simple to understand and navigate.
   • Well suited to OLAP queries.
   • Efficient for aggregations and joins.
4. Typical uses
   • Sales analysis
   • Financial reporting
   • Inventory management
   • Customer behaviour analysis

A snowflake schema is the variation in which dimension tables are themselves normalised into further tables. It reduces redundancy at the cost of longer join chains, and is generally worth it only for very large dimensions.

Resources

Transactional (OLTP):

1. PostgreSQL — High Availability, Load Balancing and Replication
2. MySQL — Replication
3. SQL Server — Always On availability groups

Analytical (OLAP):

1. Apache Hadoop documentation
2. Snowflake — key concepts and architecture
3. Amazon Redshift — system and architecture overview
4. Understand star schema and its importance — Microsoft’s guidance, the most complete free write-up of the model