Passionate developer from the land down under 🐨. Crafting clean and efficient code to solve real-world problems. With a keen eye for detail and a knack for problem-solving, I thrive in turning ideas into robust software solutions. Let's build something incredible together! #DeveloperLife #CodeCrafting"
Chapter Overview
This chapter dives into the fundamental operations of databases - how they store and retrieve data. It categorizes storage engines into two main groups:
I. Online Transaction Processing (OLTP) Databases
Optimized for user-facing applications with high volume of requests
Typical access patterns involve reading/writing small numbers of records using indexes
Disk seek time is often the performance bottleneck
II. Online Analytical Processing (OLAP) Databases
Handle lower query volumes but each query is very demanding
Access patterns involve sequentially scanning millions of records
Disk bandwidth, not seek time, is the bottleneck.
The chapter aims to provide application developers with a solid understanding of storage engine internals to guide database selection, tuning, and comprehension of database documentation.
Visual
OLAP vs OLTP
| Aspect | OLAP | OLTP |
| Purpose | Complex analytics on historical data | Transaction processing |
| Workload | Fewer complex queries | High volume of simple queries |
| Access Pattern | Sequential scans | Random access via indexes |
| Data Volume | Very large (TB/PB) | Smaller (GB/TB) |
| Data Model | Denormalized (star/snowflake) | Normalized (relational) |
| Storage | Column-oriented | Row-oriented |
| Indexing | Less relevant | Crucial for performance |
| Bottleneck | Disk bandwidth | Disk seek time |
| Updates | Periodic batch loads | Real-time updates |
Summary
In essence, Chapter 3 equips developers with critical insights into the core data storage and retrieval mechanisms of databases, enabling better alignment of database choices with application requirements.
