Database Performance Fundamentals
Core database performance concepts: WAL, CPU vs RAM trade-offs, IOPS, Throughput, OLTP vs OLAP. Essential background for AWS database service selection on SAA/SAP exams.
Overview
Database performance fundamentals are the core metrics and workload models — CPU, RAM, IOPS, Throughput, OLTP, and OLAP — that determine which database service and configuration is right for a given use case.
Understanding these concepts is essential for selecting the right AWS service and instance configuration. The key dimensions are: compute resources (CPU, RAM), storage performance (IOPS, Throughput), and workload type (OLTP vs OLAP).
Write-Ahead Log (WAL)
A common database logging mechanism where all changes are first written to a log before being applied to the main data files.
- Ensures durability — data survives crashes because the log is persisted first
- Ensures consistency — incomplete transactions are rolled back on recovery
- Can be streamed to replicas or another region for continuous synchronization
- Foundation of replication in PostgreSQL; used by Amazon RDS and Amazon Aurora
Compute: CPU vs. RAM
| Resource | Affects | Prioritize When |
|---|---|---|
| CPU | Query execution speed, transaction throughput, parallelism | Workload is compute-bound (many concurrent transactions, complex logic) |
| RAM | Caching, buffer pools, in-memory query working sets | Workload is memory-bound (large datasets, heavy caching, holding in-flight state) |
Rule of thumb by workload type:
- Online Transaction Processing (OLTP): CPU > RAM
- Online Analytical Processing (OLAP): RAM > CPU
Input/Output Operations Per Second (IOPS)
A storage performance metric measuring how many read/write operations a storage system can handle per second.
- Critical for workloads with many small, random I/O operations
- OLTP workloads are IOPS-bound: high concurrency of short, random reads/writes
AWS context: Amazon EBS volume type determines maximum IOPS (e.g., io2 Block Express supports up to 256,000 IOPS).
Throughput
A storage performance metric measuring how much data volume is transferred per second (e.g., 500 MB/s).
- Critical for workloads that move large, sequential data
- OLAP workloads are throughput-bound: full table scans, bulk exports, large aggregations
AWS context: EBS st1 (Throughput Optimized HDD) is designed for high sequential throughput at lower cost than SSD options.
IOPS vs. Throughput Decision
| Workload | Primary Metric | Why |
|---|---|---|
| OLTP | IOPS | Many small, random operations |
| OLAP | Throughput | Large sequential data scans |
Online Transaction Processing (OLTP)
A data processing model handling a large number of short, atomic transactions (Create, Read, Update, Delete — CRUD) in real time.
Typical systems: Relational Database Management Systems (RDBMS) — MySQL, PostgreSQL, Oracle
| Characteristic | Detail |
|---|---|
| Concurrency | High (many users simultaneously) |
| Latency | Low (fast response per transaction) |
| IOPS demand | High |
| Write pattern | Write-heavy (INSERT / UPDATE / DELETE) |
| Consistency model | Strong — Atomicity, Consistency, Isolation, Durability (ACID) |
Use Cases: E-commerce orders, banking transactions, user account management
AWS Services: Amazon RDS, Amazon Aurora
Online Analytical Processing (OLAP)
A data processing model optimized for complex queries over large volumes of historical, aggregated data.
Typical systems: Data warehouses — Amazon Redshift, Snowflake, BigQuery
| Characteristic | Detail |
|---|---|
| Throughput demand | High (large sequential scans) |
| Read pattern | Read-heavy (SELECT with JOINs, aggregations, window functions) |
| Query complexity | High |
| Storage format | Often columnar (compress and skip irrelevant columns) |
| Write pattern | Batch loads — not optimized for real-time writes |
Use Cases: Business Intelligence (BI), reporting dashboards, data forecasting, trend analysis
AWS Services: Amazon Redshift
OLTP vs. OLAP Quick Reference
| Dimension | OLTP | OLAP |
|---|---|---|
| Primary metric | IOPS | Throughput |
| Query type | Many small, fast | Few large, slow |
| Data scope | Current / recent | Historical / large |
| Concurrent users | Many (operational users) | Fewer (analysts) |
| Consistency | ACID required | Eventual OK for reads |
| AWS service | RDS / Aurora | Redshift |
Related Services / See Also
- Amazon RDS — managed OLTP relational database
- Amazon Aurora — cloud-native OLTP RDBMS (MySQL / PostgreSQL compatible)
- Amazon EBS — block storage with configurable IOPS and throughput tiers
Cost Optimization
AWS cost management — strategies for right-sizing, pricing models, reserved capacity, and tooling to reduce cloud spend without sacrificing performance.
Disaster Recovery Strategies
DR strategy comparison (Backup & Restore, Pilot Light, Warm Standby, Multi-Site Active/Active) with RTO and RPO definitions. Core SAA/SAP topic.