Database Patterns

Database Patterns

Core Principles

• PostgreSQL Primary — Relational data, transactions, complex queries
• Redis Secondary — Caching, sessions, real-time data
• Index-First Design — Design queries before indexes
• JSONB Sparingly — Structured data prefers columns
• Cache-Aside Default — Read-through, write-around
• Tiered Storage — Hot/Warm/Cold data separation
• No backwards compatibility — Migrate data, don't keep legacy schemas

---

PostgreSQL

Data Type Selection

Schema Design

-- Use UUID for distributed-friendly IDs
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'suspended')),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Updated timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Indexing Strategy

-- B-Tree: Equality, range, sorting (default)
CREATE INDEX idx_users_email ON users(email);

-- Composite: Leftmost prefix rule
-- Supports: (user_id), (user_id, created_at)
-- Does NOT support: (created_at) alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial: Reduce index size
CREATE INDEX idx_active_users ON users(email)
  WHERE status = 'active';

-- GIN for JSONB: Containment queries
CREATE INDEX idx_metadata ON users USING GIN (metadata jsonb_path_ops);

-- Expression: Specific JSONB field
CREATE INDEX idx_user_role ON users ((metadata->>'role'));

-- Full-text search
CREATE INDEX idx_search ON products USING GIN (to_tsvector('english', name || ' ' || description));

JSONB Usage

-- Good: Dynamic attributes, rarely queried fields
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(19,4) NOT NULL,
  category TEXT NOT NULL,           -- Extracted: frequently queried
  attributes JSONB DEFAULT '{}'     -- Dynamic: color, size, specs
);

-- Query with containment
SELECT * FROM products
WHERE category = 'electronics'              -- B-Tree index
  AND attributes @> '{"brand": "Apple"}';   -- GIN index

-- Query specific field
SELECT * FROM products
WHERE attributes->>'color' = 'black';       -- Expression index

-- Update JSONB field
UPDATE products
SET attributes = attributes || '{"featured": true}'
WHERE id = '...';

Query Optimization

-- Always use EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id
ORDER BY u.created_at DESC
LIMIT 20;

-- Watch for:
-- ❌ Seq Scan on large tables → Add index
-- ❌ Sort → Use index for ordering
-- ❌ Nested Loop with many rows → Consider JOIN order
-- ❌ Hash Join on huge tables → Add indexes

Connection Pooling

// PgBouncer or built-in pool
import { Pool } from 'pg';

const pool = new Pool({
  max: 20,                      // Max connections
  idleTimeoutMillis: 30000,     // Close idle connections
  connectionTimeoutMillis: 2000, // Fail fast
});

// Connection count formula:
// connections = (cores * 2) + effective_spindle_count
// Usually 10-30 is enough

---

Redis

Data Structure Selection

Key Naming

# Format: <entity>:<id>:<attribute>
user:123:profile
user:123:settings
order:456:items
session:abc123

# Use colons for hierarchy
# Enables pattern matching with SCAN
SCAN 0 MATCH "user:*:profile" COUNT 100

TTL Strategy

const TTL = {
  SESSION: 24 * 60 * 60,      // 24 hours
  CACHE: 15 * 60,             // 15 minutes
  RATE_LIMIT: 60,             // 1 minute
  LOCK: 30,                   // 30 seconds
};

// Set with TTL
await redis.set(`cache:user:${id}`, JSON.stringify(user), 'EX', TTL.CACHE);

// Check TTL
const remaining = await redis.ttl(`cache:user:${id}`);

---

Caching Patterns

Cache-Aside (Lazy Loading)

async function getUser(id: string): Promise<User> {
  const cacheKey = `user:${id}`;

  // 1. Check cache
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // 2. Cache miss → Query database
  const user = await db.user.findUnique({ where: { id } });
  if (!user) {
    throw new NotFoundError('User not found');
  }

  // 3. Populate cache
  await redis.set(cacheKey, JSON.stringify(user), 'EX', 900);

  return user;
}

Write-Through

async function updateUser(id: string, data: UpdateInput): Promise<User> {
  // 1. Update database
  const user = await db.user.update({
    where: { id },
    data,
  });

  // 2. Update cache immediately
  await redis.set(`user:${id}`, JSON.stringify(user), 'EX', 900);

  return user;
}

Cache Invalidation

async function deleteUser(id: string): Promise<void> {
  // 1. Delete from database
  await db.user.delete({ where: { id } });

  // 2. Invalidate cache
  await redis.del(`user:${id}`);

  // 3. Invalidate related caches
  const keys = await redis.keys(`user:${id}:*`);
  if (keys.length > 0) {
    await redis.del(...keys);
  }
}

Cache Stampede Prevention

async function getUserWithLock(id: string): Promise<User> {
  const cacheKey = `user:${id}`;
  const lockKey = `lock:user:${id}`;

  // Check cache
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // Try to acquire lock
  const acquired = await redis.set(lockKey, '1', 'EX', 10, 'NX');

  if (!acquired) {
    // Another process is loading, wait and retry
    await sleep(100);
    return getUserWithLock(id);
  }

  try {
    // Double-check cache (another process might have populated it)
    const rechecked = await redis.get(cacheKey);
    if (rechecked) {
      return JSON.parse(rechecked);
    }

    // Load from database
    const user = await db.user.findUnique({ where: { id } });
    await redis.set(cacheKey, JSON.stringify(user), 'EX', 900);
    return user;
  } finally {
    await redis.del(lockKey);
  }
}

Cache Penetration Prevention

async function getUserSafe(id: string): Promise<User | null> {
  const cacheKey = `user:${id}`;

  const cached = await redis.get(cacheKey);

  // Check for cached null
  if (cached === 'NULL') {
    return null;
  }

  if (cached) {
    return JSON.parse(cached);
  }

  const user = await db.user.findUnique({ where: { id } });

  if (!user) {
    // Cache null with short TTL
    await redis.set(cacheKey, 'NULL', 'EX', 60);
    return null;
  }

  await redis.set(cacheKey, JSON.stringify(user), 'EX', 900);
  return user;
}

---

Tiered Storage

┌─────────────────────────────────────────────────┐
│                   Application                    │
└─────────────────────────────────────────────────┘
                        │
        ┌───────────────┼───────────────┐
        ▼               ▼               ▼
   ┌─────────┐    ┌─────────┐    ┌─────────┐
   │  Redis  │    │ Postgres │    │ Archive │
   │  (Hot)  │    │  (Warm)  │    │  (Cold) │
   └─────────┘    └─────────┘    └─────────┘

   < 1ms          ~10ms           ~100ms+
   Active data    Recent data     Historical
   Memory         SSD             Object storage

Partitioning for Cold Data

-- Partition by date range
CREATE TABLE orders (
  id UUID NOT NULL,
  user_id UUID NOT NULL,
  total NUMERIC(19,4) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2025_q1 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Archive old data
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

-- Move old data to archive
WITH moved AS (
  DELETE FROM orders
  WHERE created_at < NOW() - INTERVAL '1 year'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

---

Transactions

ACID Compliance

// Use transactions for multi-table operations
async function transferFunds(fromId: string, toId: string, amount: number) {
  await db.$transaction(async (tx) => {
    // Deduct from source
    const from = await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    });

    if (from.balance < 0) {
      throw new Error('Insufficient funds');
    }

    // Add to destination
    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    });
  });
}

Optimistic Locking

-- Add version column
ALTER TABLE products ADD COLUMN version INT DEFAULT 1;

-- Update with version check
UPDATE products
SET
  stock = stock - 1,
  version = version + 1
WHERE id = $1 AND version = $2
RETURNING *;

-- If no rows returned, concurrent modification occurred

---

Checklist

## Schema
- [ ] UUID or BIGSERIAL for primary keys
- [ ] TIMESTAMPTZ for all timestamps
- [ ] NUMERIC for money, not FLOAT
- [ ] CHECK constraints for enums
- [ ] Foreign keys with ON DELETE

## Indexing
- [ ] Index for each WHERE clause pattern
- [ ] Composite indexes match query order
- [ ] GIN index for JSONB containment
- [ ] EXPLAIN ANALYZE for slow queries

## Caching
- [ ] Cache-aside as default pattern
- [ ] TTL on all cached data
- [ ] Cache invalidation on writes
- [ ] Stampede/penetration protection

## Operations
- [ ] Connection pooling configured
- [ ] Slow query logging enabled
- [ ] Backup and recovery tested
- [ ] Partition strategy for growth

---

See Also

• reference/postgresql.md — PostgreSQL deep dive
• reference/redis.md — Redis patterns
• reference/caching.md — Caching strategies

Caching Strategies

Pattern Comparison

---

Cache-Aside (Lazy Loading)

Most common pattern. Application manages both cache and database.

class CacheAsideRepository<T> {
  constructor(
    private cache: Redis,
    private db: Database,
    private ttl: number = 900
  ) {}

  async get(id: string): Promise<T | null> {
    const cacheKey = this.getCacheKey(id);

    // 1. Try cache
    const cached = await this.cache.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    // 2. Miss: load from DB
    const data = await this.db.findById(id);
    if (!data) {
      return null;
    }

    // 3. Populate cache
    await this.cache.set(cacheKey, JSON.stringify(data), 'EX', this.ttl);

    return data;
  }

  async update(id: string, data: Partial<T>): Promise<T> {
    // 1. Update database
    const updated = await this.db.update(id, data);

    // 2. Invalidate cache (don't update - avoids inconsistency)
    await this.cache.del(this.getCacheKey(id));

    return updated;
  }

  async delete(id: string): Promise<void> {
    await Promise.all([
      this.db.delete(id),
      this.cache.del(this.getCacheKey(id)),
    ]);
  }

  private getCacheKey(id: string): string {
    return `entity:${id}`;
  }
}

Pros:

• Simple to implement
• Resilient to cache failures
• Only caches accessed data

Cons:

• First request always slow
• Potential inconsistency window

---

Write-Through

Every write goes to both cache and database synchronously.

class WriteThroughRepository<T> {
  async save(id: string, data: T): Promise<T> {
    // Write to both synchronously
    const saved = await this.db.save(id, data);
    await this.cache.set(
      this.getCacheKey(id),
      JSON.stringify(saved),
      'EX',
      this.ttl
    );
    return saved;
  }

  async get(id: string): Promise<T | null> {
    // Try cache first
    const cached = await this.cache.get(this.getCacheKey(id));
    if (cached) {
      return JSON.parse(cached);
    }

    // Load and cache
    const data = await this.db.findById(id);
    if (data) {
      await this.cache.set(
        this.getCacheKey(id),
        JSON.stringify(data),
        'EX',
        this.ttl
      );
    }
    return data;
  }
}

Pros:

• Cache always consistent with DB
• Reads always fast after first write

Cons:

• Higher write latency
• Cache may contain unused data

---

Write-Behind (Write-Back)

Writes go to cache immediately, database updated asynchronously.

class WriteBehindRepository<T> {
  private writeQueue: Map<string, T> = new Map();
  private flushInterval: NodeJS.Timer;

  constructor() {
    // Periodic flush to database
    this.flushInterval = setInterval(() => this.flush(), 1000);
  }

  async save(id: string, data: T): Promise<void> {
    // Write to cache immediately
    await this.cache.set(
      this.getCacheKey(id),
      JSON.stringify(data),
      'EX',
      this.ttl
    );

    // Queue for DB write
    this.writeQueue.set(id, data);
  }

  private async flush(): Promise<void> {
    if (this.writeQueue.size === 0) return;

    const batch = new Map(this.writeQueue);
    this.writeQueue.clear();

    // Batch write to database
    await this.db.batchUpsert(Array.from(batch.entries()));
  }

  async shutdown(): Promise<void> {
    clearInterval(this.flushInterval);
    await this.flush(); // Final flush
  }
}

Pros:

• Extremely low write latency
• Batches DB writes efficiently

Cons:

• Risk of data loss (cache failure before flush)
• Complex error handling
• Eventual consistency

---

Cache Invalidation Strategies

Time-Based (TTL)

// Simple but may serve stale data
await redis.set('key', 'value', 'EX', 300); // 5 minutes

Event-Based

// Invalidate on mutations
async function updateUser(id: string, data: UpdateInput) {
  await db.user.update({ where: { id }, data });

  // Invalidate all related caches
  await redis.del(`user:${id}`);
  await redis.del(`user:${id}:profile`);
  await redis.del(`users:list`); // List cache
}

Version-Based

// Include version in cache key
async function getUser(id: string) {
  const version = await redis.get(`user:${id}:version`);
  const cacheKey = `user:${id}:v${version}`;

  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // ... load and cache
}

async function updateUser(id: string, data: UpdateInput) {
  await db.user.update({ where: { id }, data });
  await redis.incr(`user:${id}:version`); // Increment version
}

---

Cache Problems & Solutions

Cache Stampede

Many requests hit cache miss simultaneously.

// Solution: Distributed lock
async function getWithLock(id: string): Promise<Data> {
  const cacheKey = `data:${id}`;
  const lockKey = `lock:data:${id}`;

  // Check cache
  let data = await redis.get(cacheKey);
  if (data) return JSON.parse(data);

  // Try to acquire lock
  const locked = await redis.set(lockKey, '1', 'EX', 10, 'NX');

  if (locked) {
    try {
      // Double check
      data = await redis.get(cacheKey);
      if (data) return JSON.parse(data);

      // Load and cache
      const fresh = await loadFromDB(id);
      await redis.set(cacheKey, JSON.stringify(fresh), 'EX', 300);
      return fresh;
    } finally {
      await redis.del(lockKey);
    }
  } else {
    // Wait for other process to populate
    await sleep(100);
    return getWithLock(id);
  }
}

Cache Penetration

Queries for non-existent data always hit database.

// Solution: Cache negative results
async function getUserSafe(id: string): Promise<User | null> {
  const cached = await redis.get(`user:${id}`);

  if (cached === 'NULL') return null; // Cached non-existence
  if (cached) return JSON.parse(cached);

  const user = await db.user.findUnique({ where: { id } });

  if (user) {
    await redis.set(`user:${id}`, JSON.stringify(user), 'EX', 300);
  } else {
    await redis.set(`user:${id}`, 'NULL', 'EX', 60); // Short TTL for negative
  }

  return user;
}

// Solution: Bloom filter
// Pre-check if ID can possibly exist

Cache Avalanche

Many cache entries expire at once.

// Solution: Jittered TTL
function getJitteredTTL(baseTTL: number): number {
  const jitter = Math.random() * 60; // 0-60 seconds
  return baseTTL + jitter;
}

await redis.set('key', 'value', 'EX', getJitteredTTL(300));

Hot Key

Single key receives excessive traffic.

// Solution: Local cache + Redis
const localCache = new Map<string, { data: string; expires: number }>();

async function getHotKey(key: string): Promise<string> {
  // Check local cache first
  const local = localCache.get(key);
  if (local && local.expires > Date.now()) {
    return local.data;
  }

  // Check Redis
  const data = await redis.get(key);
  if (data) {
    // Cache locally with short TTL
    localCache.set(key, { data, expires: Date.now() + 1000 });
    return data;
  }

  // Load from source...
}

---

Multi-Level Caching

┌─────────────┐
│  L1: Local  │  In-process, < 1ms
│   (Memory)  │  Size: 100MB
└──────┬──────┘
       │ miss
       ▼
┌─────────────┐
│ L2: Redis   │  Network, ~1ms
│  (Remote)   │  Size: 10GB
└──────┬──────┘
       │ miss
       ▼
┌─────────────┐
│ L3: Database│  Disk, ~10ms
└─────────────┘

class MultiLevelCache<T> {
  private l1 = new Map<string, { data: T; expires: number }>();
  private l2: Redis;

  async get(key: string): Promise<T | null> {
    // L1: Local memory
    const l1Data = this.l1.get(key);
    if (l1Data && l1Data.expires > Date.now()) {
      return l1Data.data;
    }

    // L2: Redis
    const l2Data = await this.l2.get(key);
    if (l2Data) {
      const parsed = JSON.parse(l2Data);
      // Promote to L1
      this.l1.set(key, { data: parsed, expires: Date.now() + 1000 });
      return parsed;
    }

    return null;
  }

  async set(key: string, value: T, ttl: number): Promise<void> {
    // Write to both levels
    this.l1.set(key, { data: value, expires: Date.now() + 1000 });
    await this.l2.set(key, JSON.stringify(value), 'EX', ttl);
  }
}

PostgreSQL Deep Dive

Advanced Data Types

Arrays

-- Array column
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[] DEFAULT '{}'
);

-- Insert
INSERT INTO posts (id, title, tags)
VALUES (uuid_generate_v4(), 'Hello', ARRAY['tech', 'tutorial']);

-- Query: Contains element
SELECT * FROM posts WHERE 'tech' = ANY(tags);

-- Query: Contains all elements
SELECT * FROM posts WHERE tags @> ARRAY['tech', 'tutorial'];

-- Index for array operations
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

JSONB Operations

-- Access operators
SELECT
  metadata->>'name' as name,        -- Text extraction
  metadata->'address' as address,   -- JSON extraction
  metadata#>>'{address,city}' as city,  -- Nested text
  metadata @> '{"active": true}' as is_active  -- Containment
FROM users;

-- Update operations
UPDATE users SET metadata = metadata || '{"verified": true}';  -- Merge
UPDATE users SET metadata = metadata - 'oldField';              -- Remove key
UPDATE users SET metadata = jsonb_set(metadata, '{nested,key}', '"value"');

-- Array in JSONB
SELECT * FROM products
WHERE metadata->'features' ? 'waterproof';  -- Has element

-- JSONB aggregation
SELECT
  metadata->>'category' as category,
  COUNT(*) as count
FROM products
GROUP BY metadata->>'category';

Full-Text Search

-- Create tsvector column
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

-- Populate
UPDATE articles SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

-- Index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Search
SELECT *, ts_rank(search_vector, query) as rank
FROM articles, plainto_tsquery('english', 'database optimization') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Auto-update trigger
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

---

Advanced Indexing

Index Types Comparison

Covering Indexes (Index-Only Scans)

-- Include non-key columns
CREATE INDEX idx_orders_covering ON orders (user_id, created_at)
INCLUDE (status, total);

-- Query uses index only (no table access)
SELECT status, total
FROM orders
WHERE user_id = $1 AND created_at > $2;

Partial Indexes

-- Index only relevant rows
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';

-- Much smaller than full index
-- Query must match WHERE clause
SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day';

Expression Indexes

-- Index on expression
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Query must use same expression
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- JSONB field
CREATE INDEX idx_users_role ON users ((metadata->>'role'));
SELECT * FROM users WHERE metadata->>'role' = 'admin';

---

Query Optimization

EXPLAIN Output

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = $1;

-- Key metrics:
-- Planning Time: Query planning duration
-- Execution Time: Actual execution duration
-- Buffers: shared hit (cache) vs read (disk)
-- Rows: Estimated vs Actual

Common Issues

-- ❌ Seq Scan on large table
Seq Scan on orders (cost=0.00..1234.00 rows=10000 width=100)
-- Fix: Add index on filter columns

-- ❌ Index not used
-- Possible causes:
-- 1. Statistics outdated → ANALYZE table
-- 2. Low selectivity → Consider partial index
-- 3. Type mismatch → Cast appropriately
-- 4. Function on column → Use expression index

-- ❌ Sort operation
Sort (cost=1000.00..1050.00 rows=10000 width=100)
  Sort Key: created_at
-- Fix: Add index matching ORDER BY

-- ❌ Nested Loop with many rows
Nested Loop (cost=0.00..100000.00 rows=1000000 width=200)
-- Fix: Ensure join columns are indexed

Statistics

-- Update statistics
ANALYZE users;
ANALYZE orders;

-- View statistics
SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_analyze
FROM pg_stat_user_tables;

-- Adjust statistics target for important columns
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE orders;

---

Concurrency

Isolation Levels

-- Read Committed (default)
-- Sees committed data at statement start
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Repeatable Read
-- Sees snapshot from transaction start
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Serializable
-- Full isolation, may fail with serialization error
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Locking

-- Row-level lock
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;

-- Skip locked rows (useful for queue processing)
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Advisory locks (application-level)
SELECT pg_advisory_lock(hashtext('process-orders'));
-- ... do work ...
SELECT pg_advisory_unlock(hashtext('process-orders'));

Deadlock Prevention

-- Always lock in consistent order
-- ❌ Bad: Different order in different transactions
-- Transaction 1: UPDATE accounts SET ... WHERE id = 1; UPDATE accounts SET ... WHERE id = 2;
-- Transaction 2: UPDATE accounts SET ... WHERE id = 2; UPDATE accounts SET ... WHERE id = 1;

-- ✅ Good: Same order everywhere
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

---

Partitioning

Range Partitioning

CREATE TABLE events (
  id UUID NOT NULL,
  type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

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

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

-- Auto-create future partitions (pg_partman extension)

List Partitioning

CREATE TABLE orders (
  id UUID NOT NULL,
  region TEXT NOT NULL,
  total NUMERIC(19,4)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU', 'UK');
CREATE TABLE orders_asia PARTITION OF orders FOR VALUES IN ('JP', 'CN', 'KR');

Hash Partitioning

CREATE TABLE sessions (
  id UUID NOT NULL,
  user_id UUID NOT NULL,
  data JSONB
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

---

Performance Tuning

Key Parameters

-- Memory
shared_buffers = '256MB'        -- 25% of RAM for dedicated server
effective_cache_size = '768MB'  -- 75% of RAM
work_mem = '64MB'               -- Per-operation memory
maintenance_work_mem = '128MB'  -- For VACUUM, CREATE INDEX

-- Connections
max_connections = 100           -- Keep low, use pooler

-- WAL
wal_buffers = '16MB'
checkpoint_completion_target = 0.9

-- Query planning
random_page_cost = 1.1          -- For SSD (default 4.0 for HDD)
effective_io_concurrency = 200  -- For SSD

Connection Pooling with PgBouncer

[databases]
myapp = host=localhost dbname=myapp

[pgbouncer]
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Redis Deep Dive

Data Structures

Strings

# Basic operations
SET user:123:name "John"
GET user:123:name

# With expiration
SET session:abc "data" EX 3600

# Atomic increment
INCR views:page:456
INCRBY views:page:456 10

# Set if not exists
SETNX lock:resource "holder"

# Multiple operations
MSET user:1:name "Alice" user:1:email "alice@test.com"
MGET user:1:name user:1:email

Hashes

# Store object
HSET user:123 name "John" email "john@test.com" age 30

# Get field
HGET user:123 name

# Get all
HGETALL user:123

# Increment field
HINCRBY user:123 login_count 1

# Check existence
HEXISTS user:123 email

Lists

# Queue (FIFO)
LPUSH queue:tasks "task1"
RPOP queue:tasks

# Stack (LIFO)
LPUSH stack:items "item1"
LPOP stack:items

# Blocking pop (for workers)
BRPOP queue:tasks 30

# Get range
LRANGE notifications:user:123 0 9

Sets

# Add members
SADD online_users "user:123" "user:456"

# Check membership
SISMEMBER online_users "user:123"

# Get all
SMEMBERS online_users

# Set operations
SINTER tags:post:1 tags:post:2      # Intersection
SUNION tags:post:1 tags:post:2      # Union
SDIFF online_users premium_users    # Difference

Sorted Sets

# Leaderboard
ZADD leaderboard 100 "player:1" 95 "player:2" 110 "player:3"

# Get top 10
ZREVRANGE leaderboard 0 9 WITHSCORES

# Get rank
ZREVRANK leaderboard "player:1"

# Score range
ZRANGEBYSCORE leaderboard 90 100

# Increment score
ZINCRBY leaderboard 5 "player:1"

Streams

# Add entry
XADD events:orders * order_id "123" total "99.99"

# Read entries
XREAD COUNT 10 STREAMS events:orders 0

# Consumer groups
XGROUP CREATE events:orders processors $ MKSTREAM

# Read as consumer
XREADGROUP GROUP processors worker1 COUNT 1 STREAMS events:orders >

# Acknowledge processing
XACK events:orders processors <message-id>

---

Patterns

Distributed Lock

async function acquireLock(
  redis: Redis,
  key: string,
  ttl: number
): Promise<string | null> {
  const token = crypto.randomUUID();
  const acquired = await redis.set(key, token, 'EX', ttl, 'NX');
  return acquired ? token : null;
}

async function releaseLock(
  redis: Redis,
  key: string,
  token: string
): Promise<boolean> {
  // Lua script for atomic check-and-delete
  const script = `
    if redis.call("get", KEYS[1]) == ARGV[1] then
      return redis.call("del", KEYS[1])
    else
      return 0
    end
  `;
  const result = await redis.eval(script, 1, key, token);
  return result === 1;
}

// Usage
const lock = await acquireLock(redis, 'lock:order:123', 30);
if (lock) {
  try {
    await processOrder();
  } finally {
    await releaseLock(redis, 'lock:order:123', lock);
  }
}

Rate Limiting

async function checkRateLimit(
  redis: Redis,
  key: string,
  limit: number,
  windowSeconds: number
): Promise<{ allowed: boolean; remaining: number }> {
  const current = await redis.incr(key);

  if (current === 1) {
    await redis.expire(key, windowSeconds);
  }

  return {
    allowed: current <= limit,
    remaining: Math.max(0, limit - current),
  };
}

// Sliding window rate limit
async function slidingWindowRateLimit(
  redis: Redis,
  key: string,
  limit: number,
  windowMs: number
): Promise<boolean> {
  const now = Date.now();
  const windowStart = now - windowMs;

  // Remove old entries
  await redis.zremrangebyscore(key, 0, windowStart);

  // Count current window
  const count = await redis.zcard(key);

  if (count < limit) {
    // Add new entry
    await redis.zadd(key, now, `${now}:${crypto.randomUUID()}`);
    await redis.pexpire(key, windowMs);
    return true;
  }

  return false;
}

Session Storage

interface Session {
  userId: string;
  createdAt: number;
  data: Record<string, unknown>;
}

async function createSession(
  redis: Redis,
  userId: string,
  data: Record<string, unknown>
): Promise<string> {
  const sessionId = crypto.randomUUID();
  const session: Session = {
    userId,
    createdAt: Date.now(),
    data,
  };

  await redis.set(
    `session:${sessionId}`,
    JSON.stringify(session),
    'EX',
    86400 // 24 hours
  );

  // Track user sessions
  await redis.sadd(`user:${userId}:sessions`, sessionId);

  return sessionId;
}

async function getSession(redis: Redis, sessionId: string): Promise<Session | null> {
  const data = await redis.get(`session:${sessionId}`);
  return data ? JSON.parse(data) : null;
}

async function destroyAllUserSessions(redis: Redis, userId: string): Promise<void> {
  const sessions = await redis.smembers(`user:${userId}:sessions`);
  if (sessions.length > 0) {
    await redis.del(...sessions.map(s => `session:${s}`));
    await redis.del(`user:${userId}:sessions`);
  }
}

Pub/Sub

// Publisher
async function publishEvent(redis: Redis, channel: string, event: unknown) {
  await redis.publish(channel, JSON.stringify(event));
}

// Subscriber
function subscribeToEvents(redis: Redis, channel: string, handler: (event: unknown) => void) {
  const subscriber = redis.duplicate();

  subscriber.subscribe(channel, (err) => {
    if (err) throw err;
  });

  subscriber.on('message', (ch, message) => {
    if (ch === channel) {
      handler(JSON.parse(message));
    }
  });

  return () => subscriber.unsubscribe(channel);
}

---

Lua Scripting

Atomic Operations

-- Conditional update
-- KEYS[1] = key, ARGV[1] = expected, ARGV[2] = new value
local current = redis.call('GET', KEYS[1])
if current == ARGV[1] then
  redis.call('SET', KEYS[1], ARGV[2])
  return 1
else
  return 0
end

// Usage
const script = `...`;
const result = await redis.eval(script, 1, 'key', 'expected', 'newValue');

Increment with Max

-- Increment but don't exceed max
-- KEYS[1] = key, ARGV[1] = increment, ARGV[2] = max
local current = tonumber(redis.call('GET', KEYS[1]) or '0')
local increment = tonumber(ARGV[1])
local max = tonumber(ARGV[2])

local new_value = math.min(current + increment, max)
redis.call('SET', KEYS[1], new_value)
return new_value

---

Cluster & High Availability

Sentinel (HA)

import Redis from 'ioredis';

const redis = new Redis({
  sentinels: [
    { host: 'sentinel-1', port: 26379 },
    { host: 'sentinel-2', port: 26379 },
    { host: 'sentinel-3', port: 26379 },
  ],
  name: 'mymaster',
});

Cluster

import Redis from 'ioredis';

const cluster = new Redis.Cluster([
  { host: 'node-1', port: 6379 },
  { host: 'node-2', port: 6379 },
  { host: 'node-3', port: 6379 },
]);

// Use hash tags for co-location
// Keys with same {tag} go to same slot
await cluster.set('user:{123}:profile', '...');
await cluster.set('user:{123}:settings', '...');

---

Monitoring

Key Metrics

# Memory usage
INFO memory

# Connected clients
INFO clients

# Operations per second
INFO stats

# Slow queries
SLOWLOG GET 10

# Key statistics
DBSIZE
INFO keyspace

Memory Analysis

# Memory usage by key
MEMORY USAGE key_name

# Find big keys
redis-cli --bigkeys

# Memory doctor
MEMORY DOCTOR