Database Complete Guide — Internals, Indexes, Query Planner, Partitioning, Vector DB (Season 2 Ep 13, 2025)

Intro — Why You Have to Know Databases Deeply

Counterexamples to "surely knowing SQL is enough?":

• P99 latency spikes → undebuggable without understanding indexes and the planner
• Transaction isolation issues → unreproducible and unfixable without knowing isolation levels
• Exploding storage costs → partitioning and compression strategy required
• Choosing a distributed DB → not knowing the internals leads to the wrong choice
• Vector search → embedding store design required

For a senior engineer in 2025, deep database knowledge is required literacy.

Part 1 — Two Philosophies of Storage Engines

1.1 B-Tree (Balanced Tree)

Read-optimized. The default in traditional RDBMSs (PostgreSQL, MySQL InnoDB).

     [50]
    /    \
  [20]  [80]
 /  \   /  \
[10][30][70][90]

Characteristics:

• Balanced binary tree (in practice the fanout is in the hundreds)
• O(log N) lookup
• In-place update → low write amplification
• Widely used as an index structure

Downside: many random writes cause splits plus I/O.

1.2 LSM-Tree (Log-Structured Merge)

Write-optimized. RocksDB, Cassandra, ScyllaDB, LevelDB, ClickHouse.

Memtable (RAM)
   ↓ flush
L0 SSTables (disk)
   ↓ compact
L1 SSTables
   ↓ compact
L2 SSTables ...

Characteristics:

• Every write is append-only → sequential I/O
• Compaction tidies things up afterwards
• Extremely fast writes
• Spends space and CPU on compaction

Downside: reads have to search several layers → mitigated with Bloom filters and friends.

1.3 How to Choose

1.4 2024-2025 Trend: B-Tree and LSM Converge

• Aurora, Neon: storage layer split out (object storage such as S3 plus a local cache)
• TigerBeetle: finance-specific, LSM plus determinism
• FoundationDB: layered, ACID plus key-value
• Postgres Incremental Materialized View: read-optimal plus real-time

Part 2 — Indexes in Depth

2.1 Eight Kinds of Index

2.2 Five Principles of Index Design

1. Queries come first: design from the queries you actually run
2. Selectivity: highly selective columns first
3. Composite index order: start with the filter you use most often
4. Cover with INCLUDE: avoid touching the table
5. Drop indexes you do not use: they degrade write performance

2.3 Top 10 Reasons an Index Is Not Used

1. A function is applied: WHERE LOWER(email) = ... → needs an expression index
2. Implicit type conversion: WHERE id = '123' (id is an int)
3. Leading wildcard: LIKE '%foo' → full scan
4. OR conditions: the optimizer may give up → use UNION
5. Not equals (!=): mostly does not use an index
6. NULL comparison: Postgres can index IS NULL, but design with care
7. The data is small: the planner prefers a sequential scan
8. Wrong composite index order: WHERE b = ? against an (a, b) index
9. Stale statistics: ANALYZE needed
10. Index bloat: REINDEX needed

Part 3 — The Query Planner and EXPLAIN

3.1 What the Planner Does

It converts SQL (declarative) into an execution plan (procedural). Cost-based optimization (CBO).

3.2 Reading PostgreSQL EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
GROUP BY u.name;

Example output:

HashAggregate  (cost=1234..5678 rows=100)
  Group Key: u.name
  ->  Hash Join  (cost=500..1200 rows=10000)
        Hash Cond: o.user_id = u.id
        ->  Seq Scan on orders o  (cost=0..800 rows=100000)
        ->  Hash  (cost=300..300 rows=500)
              ->  Index Scan on users_country_idx  (cost=0..300 rows=500)

How to read it:

• From the inside outward
• cost=startup..total
• rows: estimated row count (compare against the actual figure from ANALYZE)
• actual time: real elapsed time
• Buffers: memory and disk access

3.3 Important Node Types

3.4 Postgres Has No Plan Hints

MySQL and Oracle have hints. In Postgres you steer the planner by tuning ANALYZE, statistics targets, and random_page_cost.

Part 4 — Transaction Isolation

4.1 ACID Restated

• Atomicity: all or nothing
• Consistency: no constraint violations
• Isolation: concurrent execution behaves like serial execution
• Durability: once committed, it stays

4.2 Four Isolation Levels (SQL-92)

4.3 Default Isolation Level by DB

4.4 Snapshot Isolation vs Serializable

• Snapshot Isolation (SI): each transaction sees a snapshot taken when it started
  • Downside: write skew is possible
• Serializable (MVCC SSI): SI plus conflict detection → abort
  • Postgres offers SSI through SERIALIZABLE

4.5 A Write Skew Example

At least one of two doctors must be on call:

-- T1: reads on_call, sees 2 → OK
-- T2: concurrently reads on_call, sees 2 → OK
-- Both flip their own status to off-call
-- Commit → 0 doctors on call. Constraint violated

Only Serializable detects this.

4.6 Deadlock

Two or more transactions wait on each other's locks. The DB detects it → sacrifices one (abort).

Prevention:

• Consistent lock ordering (ascending ID)
• SELECT FOR UPDATE NOWAIT
• Retry logic (idempotency plus backoff)

Part 5 — MVCC (Multi-Version Concurrency Control)

5.1 MVCC Basics

"Writers do not block readers, and readers do not block writers."

Multiple versions of each row are kept, and each transaction reads the version belonging to its own snapshot.

5.2 MVCC in Postgres

• Tuple header: xmin (creating transaction), xmax (deleting transaction)
• Vacuum: cleans up dead tuples. Mandatory.
• autovacuum: automatic, but it still needs tuning

5.3 The Vacuum Bloat Problem

• A long-running transaction blocks autovacuum
• Table and index bloat → degraded performance
• Fix: pg_repack, VACUUM FULL, regular monitoring

5.4 MySQL InnoDB MVCC

• Previous versions live in the undo log
• The purge thread cleans them up
• Clustered index (physically ordered by primary key)

Part 6 — Partitioning and Sharding

6.1 Vertical vs Horizontal

• Vertical: split by column (almost never used)
• Horizontal: split by row. Partitioning and sharding.

6.2 Partitioning (Inside a Single DB)

Native in Postgres (10+):

CREATE TABLE events (
  id bigserial,
  event_time timestamptz,
  data jsonb
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Upside: partition pruning, easy maintenance (DROP old partition). Limit: still one DB. Bounded by a single server's capacity.

6.3 Sharding (Multiple DBs)

Spread the data across several servers.

Choosing a shard key:

• High cardinality
• Account for the access pattern
• Easy to rebalance
• Avoid hot shards

Sharding methods:

• Hash: even spread, range queries ❌
• Range: range queries OK, hot shard risk
• Directory-based: flexible, metadata to maintain

6.4 Cross-Shard Queries

Expensive by default. When a JOIN or ORDER BY spans several shards, the merge happens at the application level.

Fixes:

• Keep related data on the same shard (by tenant_id)
• Read replica plus materialized view
• Keep OLAP separate (data warehouse)

6.5 Distributed SQL DBs (Sharding Handled for You)

• CockroachDB
• YugabyteDB
• Spanner
• TiDB
• Vitess (on top of MySQL)
• Citus (a Postgres extension)

Part 7 — Replication and HA

7.1 Replication Styles

• Physical (WAL/binlog): byte level, exact
• Logical: SQL or row level, flexible (heterogeneous, partial)

7.2 Postgres Replication in 2025

• Streaming Replication: ships WAL
• Logical Replication (10+): per table
• Synchronous option: certain but slow
• Patroni: HA management (the standard)

7.3 The Failover Pattern

Primary failure detected
  ↓ (consensus: etcd/Patroni)
Pick the most up-to-date standby
  ↓
Promote the new primary + the application switches DSN
  ↓
Rebuild the former primary

Key point: prevent split-brain (fencing).

7.4 Putting Read Replicas to Work

• Spread out read-heavy workloads
• Accept the replication lag
• "Read-your-writes" goes to the primary

Part 8 — PostgreSQL Runs Away With 2025

8.1 Why Postgres Won

Why Postgres became the de facto default when picking a DB in 2024-2025:

1. Extensibility: a huge number of extensions
2. JSONB: NoSQL features built in
3. pgvector: vector DB capability
4. PostGIS: unmatched GIS
5. Timescale: time-series
6. Citus: distribution
7. Logical Replication: CDC and migrations
8. License: the PostgreSQL License (permissive)
9. Community: stable, not captive to one big vendor
10. Managed in the cloud: Aurora, RDS, Cloud SQL, Neon, Supabase

8.2 Top 15 Postgres Extensions (2025)

8.3 The 2024-2025 Postgres Ecosystem

• Neon: Serverless Postgres, branching
• Supabase: Postgres plus Auth plus Realtime
• Xata: DevEx first
• Aurora Postgres: managed by AWS
• ParadeDB: search extensions

Part 9 — NoSQL: When to Reach for It

9.1 Five NoSQL Categories

9.2 The 2025 Reality: "Postgres Is Enough"

Postgres covers most NoSQL use cases:

• Document → JSONB
• Key-Value → an UNLOGGED table, or Redis alongside it
• Time-Series → TimescaleDB
• Graph → the Apache AGE extension

Still pick NoSQL when you need:

• Global low latency (DynamoDB Global Table)
• Extreme write volume (Cassandra)
• Specialized graph algorithms (Neo4j)

9.3 Where Redis Sits

In-memory cache, queue, ranking, rate limiting. The tool "everybody has at least one of."

The 2024 license change → the Valkey fork (driven by AWS and Google). More options to choose from now.

Part 10 — Vector DB

10.1 Why a Vector DB Is Needed

Approximate nearest neighbor (ANN) search, for things like LLM embedding search and recommendations.

10.2 ANN Algorithms

• HNSW (Hierarchical Navigable Small World): fast and accurate, memory ↑
• IVF (Inverted File): clustering based
• PQ (Product Quantization): saves memory
• HNSW + PQ: hybrid

10.3 2025 Vector DB Comparison

10.4 2025 Recommendations

• Simple RAG: pgvector (bolt it onto the Postgres you already have)
• Large scale with advanced filters: Qdrant or Weaviate
• Fully managed: Pinecone
• Local or edge: LanceDB

10.5 A pgvector Example

CREATE EXTENSION vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Search
SELECT content
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Part 11 — A Six-Month DB Roadmap

Month 1: SQL in Depth

• Window functions, CTEs, recursion
• Hands-on transaction isolation and deadlocks
• Index design

Month 2: Postgres Internals

• Fully understand EXPLAIN
• MVCC and Vacuum
• WAL and Replication

Month 3: Storage Engines

• DDIA chapter 3 (Storage)
• Try implementing a B-Tree and an LSM
• Study RocksDB and Cassandra

Month 4: Distributed DBs

• The architecture of CockroachDB and Spanner
• Sharding design
• Operating replication

Month 5: Vector DB

• pgvector in practice
• Understand HNSW
• Build a RAG data layer

Month 6: Optimization and Operations

• Slow query analysis
• Index tuning
• Connection Pooling (PgBouncer)
• Zero-downtime schema migration (Postgres in depth)

Part 12 — A 12-Point DB Checklist

1. You know the criteria for choosing B-Tree vs LSM-Tree
2. You know five Postgres index types and what each is for
3. You can name five reasons an index is not used
4. You can read EXPLAIN ANALYZE output
5. You know the four transaction isolation levels along with the anomalies they prevent
6. You can explain write skew
7. You know the relationship between MVCC and Vacuum
8. You know the difference between partitioning and sharding
9. You know the trade-offs of hash vs range sharding
10. You know the top 5 Postgres extensions
11. You know how to choose between pgvector and Qdrant
12. You know why a connection pool (PgBouncer) is necessary

Part 13 — Ten DB Antipatterns

1. Trusting the ORM and never inspecting the queries: N+1 and loading far too many columns are common
2. Adding indexes one by one without limit: writes slow down. Drop the ones that go unused
3. Long transactions: MVCC bloat. Keep transactions short
4. Blind faith in the default isolation level: not knowing the limits of Read Committed leads to write skew bugs
5. Managing connections one at a time: thousands of connections with no pool → Postgres falls over
6. Everything into JSONB: you give up the strengths of a schema. Structured data belongs in columns
7. The wrong shard key: hot shard → resharding hell
8. Unplanned migration downtime: a zero-downtime migration strategy is mandatory
9. Ignoring vacuum tuning: your table bloats 10x without you noticing
10. Using the DB as a message queue: possible, but not recommended. Use Redis or Kafka

Closing — The DB Is "the Gravity of a Production System"

You can replace the application, but you cannot easily change the DB schema. The DB is the part of a production system with the most inertia.

Which is why you have to design it well the first time:

• An index design that holds up for five years
• Partitioning and sharding ready for 10x growth
• Isolation levels that guarantee data integrity
• Observability and backups that prepare you for disaster

DB engineering in 2025 looks like this:

• Postgres First: most of it is solved here
• Vector DB: mandatory in the LLM era
• Distributed DB: only for the very largest services
• NoSQL: only when you have a clear reason

Next Post — "Networking Complete Guide: HTTP/3, QUIC, TLS 1.3, gRPC, WebSocket, CDN"

Season 2 Ep 14 is the plumbing of the internet: networking. The next post covers:

• TCP vs UDP vs QUIC
• The HTTP/1.1 → 2 → 3 evolution
• The TLS 1.3 handshake
• gRPC vs REST vs GraphQL vs tRPC
• WebSocket vs SSE vs Long Polling
• CDN architecture (Cloudflare, Fastly)

The wires you cannot see, in the next post.