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

Use Case	Type	Avoid
Primary Key	UUID / BIGSERIAL	INT (range limits)
Timestamps	TIMESTAMPTZ	TIMESTAMP (no timezone)
Money	NUMERIC(19,4)	FLOAT (precision loss)
Status	TEXT + CHECK	INT (unreadable)
Semi-structured	JSONB	JSON (no indexing)
Full-text	TSVECTOR	LIKE '%..%'

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));