database-patternslisted

# 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 ```sql -- 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; $$ LANGUAG