Database field guides · 31 volumes
Minibooks for
the systems
behind SQL.
From physical storage to distributed transactions, each guide builds a compact mental model from tested database behavior.Read independently.
Connect the models.
Connect the models.
AI-generated from the blog archive.
These minibooks were generated with AI from Franck Pachot's archived blog posts. Links to the original articles are retained for source context and verification.isolation · MVCC · serializability
SQL Isolation Levels
From anomalies to serializable executionA practical mental model for snapshots, conflicts, explicit locking, and retries across Oracle, PostgreSQL, and distributed SQL.B-trees · access paths · execution plans
Indexes and Access Paths
From data structure to execution planHow B-trees, covering indexes, scan methods, and optimizer costs turn a predicate into physical work.PostgreSQL · cardinality · join planning
PostgreSQL Query Planning
Estimates, costs, joins, and plan stabilityA practical model for understanding why PostgreSQL chooses a plan and how to investigate when that choice is wrong.sharding · consensus · distributed PostgreSQL
Distributed SQL
For PostgreSQL developersHow familiar SQL, indexes, joins, and transactions change when storage and consensus span nodes and regions.foreign keys · locking · migrations
Foreign Keys and Concurrency
Integrity, indexes, locks, and online changeWhy referential integrity is also a concurrency protocol, and how Oracle, PostgreSQL, and distributed SQL enforce it.parsing · optimization · execution
The Life of a SQL Statement
Parse, plan, execute, observe, repeatFollow SQL from text and binds through parsing, optimization, execution, caching, invalidation, and runtime evidence.time · ordering · consistency
Database Time and Ordering
Clocks, commits, snapshots, and sort orderA precise vocabulary for the different kinds of time and order that applications ask databases to provide.keyset pagination · indexes · distributed queries
Scalable Pagination
Stable pages without counting from zeroDesign deterministic, index-backed pagination that remains fast and understandable across joins, partitions, and distributed SQL.PostgreSQL · MVCC · vacuum
PostgreSQL MVCC Backstage
Tuple versions, visibility, vacuum, and recoveryLook behind PostgreSQL snapshots to understand heap tuples, index behavior, vacuum, uniqueness, and crash recovery.schema design · invariants · contention
Schema Design for Concurrency
Make invariants executableMove correctness from timing assumptions into keys, constraints, indexes, queues, and retryable transactions.Oracle · PostgreSQL · migration
Oracle to PostgreSQL
Translate behavior, not syntaxA migration field guide to the architectural differences behind plans, MVCC, indexes, datatypes, transactions, and operations.WAL · redo · checkpoints · recovery
WAL, Redo, and Durability
From commit records to crash recoveryHow PostgreSQL WAL and Oracle redo turn memory changes into durable, recoverable database state.Oracle · plan baselines · regression control
SQL Plan Management
Baselines, evolution, and regression controlUse Oracle SQL Plan Management as a controlled acceptance process for execution plans, not a substitute for optimizer evidence.Oracle · CDB · PDB · dictionary internals
Oracle Multitenant Internals
CDB roots, PDB dictionaries, and object linksA physical and dictionary-level model of Oracle containers, common metadata, object links, DDL replay, and plug-in compatibility.locks · blockers · deadlocks · diagnostics
Locks, Blocking, and Deadlocks
Read wait graphs across database enginesDiagnose lock modes, blocker chains, deadlock cycles, and intentional coordination in Oracle, PostgreSQL, and distributed SQL.partitioning · pruning · sharding · placement
Partitioning and Sharding
Pruning, placement, and global constraintsDesign range, list, and hash boundaries that improve lifecycle and locality without sacrificing SQL semantics.statistics · cardinality · histograms · feedback
Optimizer Statistics
Cardinality, correlation, and adaptive evidenceUnderstand what histograms, column groups, sampling, partition statistics, and feedback can tell an optimizer.replication · Data Guard · failover · RPO/RTO
Replication and High Availability
Transport, apply, failover, and recovery objectivesTurn log transport and replicas into explicit durability, availability, consistency, RPO, and RTO guarantees.wait events · active sessions · runtime evidence
Database Observability
Sessions, waits, plans, and workload timeConnect requests to active sessions, wait events, runtime plans, statement aggregates, and historical workload evidence.MongoDB · WiredTiger · MVCC · checkpoints
WiredTiger Storage Engine
Pages, checkpoints, MVCC, and MongoDB persistenceA physical model of how MongoDB uses WiredTiger for B-tree storage, cache management, compression, checkpoints, and durable history.LSM tree · SST · compaction · amplification
LSM-Tree Storage
Memtables, SST files, compaction, and amplificationUnderstand how log-structured merge trees turn random writes into sequential files and pay for that efficiency through reads and compaction.Raft · consensus · quorum · replication
Raft Consensus
Terms, quorums, replicated logs, and failure recoveryBuild an operational model of Raft leader election, log replication, quorum commit, membership, and consistent reads.MongoDB · JSONB · BSON · document model
Document Data Modeling
JSONB, BSON, nesting, indexes, and consistencyModel document data deliberately across MongoDB and PostgreSQL, from aggregate boundaries and embedded arrays to indexing and update amplification.connections · pooling · sessions · admission
Database Connections
Pools, sessions, admission, and failure diagnosisTreat database connections as bounded stateful resources, from listener and authentication handshakes to pooling, serverless bursts, and session cleanup.embeddings · ANN · HNSW · hybrid search
Vector Search
Embeddings, ANN indexes, filters, and hybrid retrievalBuild and evaluate semantic retrieval with vector representations, approximate indexes, metadata filtering, and full-text hybrid ranking.BM25 · inverted indexes · relevance · top-k
BM25 and Full-Text Search
Terms, relevance, indexes, and top-k retrievalUnderstand BM25 scoring, inverted indexes, analyzers, and the lexical side of hybrid search across MongoDB and PostgreSQL.Db2 · cursor stability · savepoints · access paths
Db2 Essentials
Isolation, savepoints, access paths, and platform contextA compact Db2 mental model built from cross-engine experiments in concurrency, physical row identity, plan control, and cloud deployment.Oracle · licensing · options · compliance evidence
Oracle Licensing
Editions, options, metrics, and evidenceBuild an evidence-based Oracle licensing inventory from contracts, deployed editions, enabled options, infrastructure metrics, and actual feature use.Oracle · Standard Edition · SE2 · resource limits
Oracle Standard Edition
SE2 limits, architecture, and operational choicesDesign Oracle Standard Edition deployments around SE2 resource limits, included capabilities, platform rules, and measured workload needs.PostgreSQL · vacuum · autovacuum · freezing
PostgreSQL Vacuum
Cleanup, freezing, visibility, and autovacuum capacityOperate PostgreSQL vacuum as a correctness and throughput service driven by tuple churn, snapshot horizons, and transaction age.PostgreSQL · bloat · dead tuples · write amplification