Chapter Title: Part 14 — Database Engineering

Learning Objectives

• Understand query optimization and indexing strategy.
• Master transactions, isolation levels, and deal with deadlocks.
• Manage connection pools for high concurrency.
• Design replication concepts, read replicas, and database scaling.
• Execute backups, recovery, migrations, and schema evolution.
• Implement partitioning concepts.

Prerequisites

Basic SQL knowledge (SELECT, INSERT, UPDATE, DELETE), foundational understanding of how servers handle requests, and basic knowledge of data structures (like B-Trees).

Why Does This Exist?

The Problem Before the Solution

Before advanced database engineering, data was stored in simple flat files or rudimentary databases without concurrency controls. When two users tried to update a bank balance simultaneously, one update would overwrite the other silently (lost update problem). When data grew, reading a file sequentially became too slow.

Why the Old Approach Breaks

As traffic scales:

• Flat files lock the entire file on every write, crippling concurrency.
• Unindexed tables require O(N) full-table scans. With 10 million rows, a simple lookup takes seconds.
• Single instances become a single point of failure and bottleneck for read/write limits.

History

The transition from navigational databases in the 1960s to relational models (Codd's paper in 1970). The introduction of ACID transactions in system R and later commercialized by Oracle and IBM. In the 2000s, NoSQL emerged to solve scaling challenges, but the 2010s saw NewSQL bridging the gap, and traditional databases like PostgreSQL adopting advanced clustering and JSON support.

Mental Model (Analogy -> Reality)

Internal Working (Memory, stack, process, network, etc.)

When you query the database:

1. Network: Your application gets a connection from the Connection Pool (to avoid TCP handshake overhead).
2. Process: The DB engine spawns or assigns a thread/process.
3. Memory: It checks the shared buffers (RAM) for the required pages. If not there, it reads from disk.
4. Transactions: It uses a Write-Ahead Log (WAL). Changes are written sequentially to the WAL on disk (fast) before modifying the actual data files (slow, random I/O), guaranteeing recovery on crash.

Visual Explanation (ASCII diagrams)

  [Application]
       | (Connection Pool)
  [Database Thread] --> [Query Parser] --> [Query Optimizer]
                                                |
  [Disk / WAL] <----- [Storage Engine] <--------+
       | (Replication)
  [Read Replica]
  

Syntax

-- Indexing
CREATE INDEX idx_users_email ON users(email);

-- Transactions
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
  

Tiny Example

Without an index, finding a user by email takes a full scan. With a B-Tree index, it becomes a fast tree traversal.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
  

Walkthrough

Let's optimize a slow query. You have a `orders` table with 50 million rows.

1. You run `SELECT * FROM orders WHERE status = 'PENDING' AND created_at < NOW() - INTERVAL '1 day';`
2. It takes 15 seconds.
3. You run `EXPLAIN ANALYZE` and see `Seq Scan on orders`.
4. You add a composite index: `CREATE INDEX idx_orders_status_date ON orders(status, created_at);`
5. The query now takes 5 milliseconds using an `Index Scan`.

Break It

Let's cause a deadlock.

-- Connection 1
BEGIN;
UPDATE users SET status = 'active' WHERE id = 1;

-- Connection 2
BEGIN;
UPDATE users SET status = 'active' WHERE id = 2;

-- Connection 1
UPDATE users SET status = 'active' WHERE id = 2; -- Blocks

-- Connection 2
UPDATE users SET status = 'active' WHERE id = 1; -- DEADLOCK!
  

Debug It

The database will automatically detect the cycle and kill one of the transactions, rolling it back and throwing an error. In your application code, you catch this specific error and retry the transaction with exponential backoff.

Mini Project

Implement a connection pool in Node.js using `pg`.

const { Pool } = require('pg');
const pool = new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  connectionString: process.env.DATABASE_URL,
});

async function getUser(id) {
  const client = await pool.connect();
  try {
    const res = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return res.rows[0];
  } finally {
    client.release();
  }
}
  

Real Application Feature

Database Migrations and Schema Evolution using tools like Knex, Prisma, or Flyway. You never manually run `ALTER TABLE` in production. You write migration scripts that are applied automatically during deployment, ensuring the schema tracks with your application code version.

Production Implementation

For high availability, you set up Replication. A Primary node handles all Writes. It streams the WAL to one or more Read Replicas. If the Primary fails, an automated failover mechanism promotes a replica to Primary.

Production Usage

Scaling strategies:

• Vertical Scaling: Increase RAM/CPU on the database server.
• Read Replicas: Offload SELECT queries to replicas.
• Partitioning: Split large tables into smaller physical pieces (e.g., partition by year).
• Sharding: Distribute data across completely separate database servers (e.g., users 1-1M on DB1, 1M-2M on DB2).

Performance

Indexes speed up reads but slow down writes because the index tree must be updated on every INSERT/UPDATE/DELETE. Too many indexes kill write performance. Missing indexes kill read performance. Finding the balance is the core of query optimization.

Best Practices

• Always use a Connection Pool.
• Use connection-level or transaction-level statement timeouts to prevent rogue queries from locking resources forever.
• Use migrations for schema changes.
• Understand Isolation Levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable). Default is often Read Committed, which allows non-repeatable reads.

Interview Questions

Engineering Challenge

Design a schema and querying strategy for a real-time collaborative document editor, considering isolation levels and conflict resolution.

Revision Sheet

- Indexing: B-Tree for equality/range, Hash for equality, GIN for JSON/arrays.
- Locks: Row-level vs Table-level.
- Deadlocks: Occur when transactions wait on each other in a cycle.
- Connection Pool: Reuses expensive TCP connections.
- WAL: Write-ahead log ensures durability without random disk I/O immediately.

Connections

Connects to Part 13 (Backend API Design) for where queries originate, and Part 15 (System Design) where caching and sharding are discussed at a macro architectural level.

Mini Project (20-30 min)

import { Pool } from 'pg';

const pool = new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  connectionString: process.env.DATABASE_URL,
});

async function getUser(id: number) {
  const client = await pool.connect();
  try {
    const res = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return res.rows[0];
  } finally {
    client.release();
  }
}

Bigger Project (1-2 hours)

Build a query analyzer that runs EXPLAIN ANALYZE and reports slow queries.

import { Client } from 'pg';

async function analyzeQuery(query: string) {
  const client = new Client();
  await client.connect();
  const res = await client.query('EXPLAIN ANALYZE ' + query);
  
  const executionTimeLine = res.rows.find(row => row['QUERY PLAN'].includes('Execution Time'));
  console.log('Query Analysis:');
  console.log(res.rows.map(row => row['QUERY PLAN']).join('\n'));
  
  await client.end();
}

analyzeQuery('SELECT * FROM users WHERE status = \'PENDING\'');

Interview Questions