Database Reliability Engineering (DBRE)

Introduction: The Magic Box vs. Reality

ACID Properties: The Contract

How Data Is Physically Stored: Pages and Heap Files

The I/O Bottleneck: Buffer Pools

Ensuring Durability: The Write-Ahead Log (WAL)

Indexing for Speed: B-Trees and B+ Trees

Concurrency Without Locking: Introduction to MVCC

MVCC in Action: Snapshots and Visibility Rules

Isolation Levels: Tuning the Strictness

The MVCC Hangover: Bloat and Vacuuming

Introduction: The Limits of a Single Box

Scaling Reads: The Primary-Replica Model

Managing Replicas: Replication Slots

Scaling Writes: Sharding

The Pain of Resharding

The Connection Bottleneck: Introducing PgBouncer

PgBouncer Pooling Modes

Introduction: Why "Not Only SQL"?

Key-Value Stores: The Speed Demons

Document Databases: Flexibility First

Wide-Column Stores: The Big Data Heavyweights

Wide-Column Internals: LSM Trees & SSTables

Graph Databases: It's All About Relationships

Introduction: The Problem with More Than One

The CAP Theorem: The Impossible Triangle

Beyond CAP: The PACELC Theorem

Tunable Consistency and Quorums

Hinted Handoff: Dealing with Temporary Outages

Read Repair and Anti-Entropy: Keeping Data Consistent Over Time

Introduction: Bridging the SQL and NoSQL Divide

The Google Spanner Revolution

TrueTime: Spanner's Secret Weapon

CockroachDB: Spanner for Everyone Else

TiDB: Separation of Powers

Choosing Between NewSQL Systems

Introduction: The Need for Speed

Caching Pattern: Cache-Aside (Lazy Loading)

Caching Patterns: Write-Through and Write-Back

Eviction Policies: Taking Out the Trash

Cache Penetration: The Attack of the Nulls

Cache Stampede: The Thundering Herd

Introduction: The Physical Reality of Data

The Compression Trade-off Triangle

The Speed Demons: LZ4 and Snappy

The Balanced Master: Zstandard (Zstd)

The Specialist: Gorilla (Time-Series Compression)

Row vs. Columnar Storage

The Power of Columnar: Parquet and ORC

Introduction: The Illusion of Solitude

Anomaly 1: Dirty Read

Anomaly 2: Non-Repeatable Read (Fuzzy Read)

Anomaly 3: Phantom Read

The Hierarchy of Isolation Levels

The Silent Killer: Write Skew

Pessimistic Locking: "Trust No One

Optimistic Locking: "Hope for the Best

Introduction: The Physics of Data

The B-Tree: The Organized Librarian

The LSM Tree: The Fast Journalist

The Three Amplifications (The RUM Conjecture)

Deep Dive: Write Amplification

Deep Dive: Read Amplification

Deep Dive: Space Amplification

Introduction: The `ALTER TABLE` Nightmare

The Shadow Table Strategy (The Ghost)

Approach 1 — Trigger-Based Sync (pt-online-schema-change)

Approach 2 — Binlog-Based Sync (gh-ost)

The Cutover — The Moment of Truth

Schema Migration Compatibility — What Your Application Must Handle

Online Schema Migrations in PostgreSQL

Introduction: Defensive Driving for Databases

The Anti-Pattern: Queue in Database

The Pattern: Circuit Breaking Bad Queries

The Pattern: Deadline Propagation

The Pattern: Connection Pool Sizing and Admission Control

Introduction: When B-Trees Fail

GIN Indexes: The Inverted Index

GiST Indexes: The Shape of Data

Covering Indexes: The Shortcut

Partial Indexes: The Specialist

The Cost of Over-Indexing

Introduction: The Relativity Problem

The Gold Standard: External Consistency

Google Spanner: TrueTime (The Hardware Solution)

CockroachDB: Hybrid Logical Clocks (The Software Solution)

Comparison: TrueTime vs. HLC

Introduction: The Declarative Standard

The Anatomy of a SELECT Statement

Joins: Connecting the Dots

Aggregation: GROUP BY and HAVING

Common Table Expressions (CTEs)

Window Functions: The Power User Tool

Normalization: Organizing Data for Integrity

Introduction: Schrödinger's Backup

Logical Backups: The Portable Approach

Physical Backups: The Speed Demons

Point-in-Time Recovery (PITR) Mechanics

RTO vs. RPO: Defining Success Before Disaster Strikes

Designing a Restore Drill: What "Tested" Actually Means

Introduction: OLTP vs. OLAP

Columnar Storage: The Secret Sauce

Schemas: Star vs. Snowflake

The Pipeline: ETL vs. ELT

Partitioning and Clustering: Query Performance at Scale

Introduction: The Failure of the Monolith

Principle 1: Domain-Oriented Ownership

Principle 2: Data as a Product

Principle 3: Self-Serve Data Platform

Principle 4: Federated Computational Governance

Data Mesh vs. Data Lake vs. Data Warehouse: When to Use What

Introduction: The Connection Storm

What Is a Database Proxy?

Connection Multiplexing: The Core Feature

Query Caching: Offloading Reads Transparently

Read/Write Splitting: Routing by Query Intent

Query Firewall and Observability

Introduction: The Batch ETL Problem

Log-Based CDC: How It Works

The Engine: Debezium & Kafka Connect

The Pipeline: DB to Warehouse

Handling Schema Evolution

The Transactional Outbox Pattern

Introduction: The MVCC Hangover

Postgres Autovacuum: How It Works

Tuning Postgres: The Scale Factor

Tuning Postgres: The Cost Limit

Cassandra Compaction: The Strategy Game

Monitoring Bloat: How to Know You Have a Problem