-- ❌ Broken pattern: two operations outside a transaction — partial failure leaves inconsistent state
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;
-- If the second UPDATE fails (network drop, constraint violation), user 1 lost $500 permanently

-- ✅ Correct: wrap in an explicit transaction — all-or-nothing atomicity
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;
COMMIT;
-- If any statement fails, ROLLBACK is automatic — both accounts remain consistent

-- MVCC allows this to run simultaneously without blocking the UPDATE below
SELECT SUM(balance) FROM accounts; -- reader sees snapshot of committed data at query start

-- This writer proceeds without waiting for the reader above
UPDATE accounts SET balance = balance + 100 WHERE user_id = 42;
-- MVCC creates a new row version; the reader's snapshot is unaffected

-- ❌ Broken pattern: storing all variable attributes in a text column
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT,
  attributes TEXT  -- JSON blob as plain text — no validation, no indexing, slow parsing
);

-- ✅ Correct: JSONB — binary-stored, indexable, queryable semi-structured data
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,       -- structured: indexed, constraint-able
  price_cents INTEGER NOT NULL,  -- structured: typed, arithmetic-able
  attributes JSONB              -- variable: product-specific fields (color, size, specs)
);

-- GIN index makes JSONB queries fast at scale
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Query a nested JSONB field — uses the GIN index
SELECT name FROM products
WHERE attributes @> '{"color": "navy", "size": "M"}';

-- Enable the PostGIS extension (once per database)
CREATE EXTENSION IF NOT EXISTS postgis;

-- Add a geometry column to an existing table
ALTER TABLE locations ADD COLUMN geom GEOMETRY(Point, 4326);

-- Find all restaurants within 2km of a coordinate — uses spatial index
SELECT name, ST_Distance(geom, ST_MakePoint(-73.985, 40.748)::geography) AS dist_meters
FROM locations
WHERE ST_DWithin(geom::geography, ST_MakePoint(-73.985, 40.748)::geography, 2000)
ORDER BY dist_meters;

-- Enable pgvector (once per database)
CREATE EXTENSION IF NOT EXISTS vector;

-- Store embeddings alongside structured data — no separate vector DB needed at < 10M rows
CREATE TABLE articles (
  id UUID PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding VECTOR(1536)  -- OpenAI text-embedding-3-small dimension
);

-- HNSW index — fast approximate nearest neighbor for query-time retrieval
CREATE INDEX idx_articles_embedding ON articles
USING HNSW (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

-- Semantic similarity search — retrieves the 5 most relevant articles
SELECT title, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM articles
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

-- ❌ Broken pattern: over-normalized — every read requires expensive joins
CREATE TABLE order_items (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders(id),
  product_id UUID REFERENCES products(id),
  quantity INTEGER
  -- To show an order receipt: JOIN orders + order_items + products + users
  -- 4-table join on every page load
);

-- ✅ Correct: strategic denormalization — embed stable, read-time data at write time
CREATE TABLE order_items (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders(id),
  product_id UUID REFERENCES products(id),
  quantity INTEGER,
  -- Snapshot the product name and price AT ORDER TIME — they may change later
  product_name TEXT NOT NULL,      -- denormalized snapshot
  unit_price_cents INTEGER NOT NULL -- denormalized snapshot
  -- Receipt query: JOIN orders + order_items only — 2-table join
);

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents > 0),
  active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- Variable attributes: color options, dimensions, technical specs, certifications
  attributes JSONB NOT NULL DEFAULT '{}'
);

-- Indexed columns for filtering UI
CREATE INDEX idx_products_category ON products (category) WHERE active = true;
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Partial index: only active products in the feed query
CREATE INDEX idx_products_active_created ON products (created_at DESC) WHERE active = true;

-- ❌ Broken: dropping a column immediately locks the table and breaks old app versions
ALTER TABLE users DROP COLUMN legacy_field;

-- ✅ Phase 1: mark as deprecated — old code still works, new code ignores it
COMMENT ON COLUMN users.legacy_field IS 'DEPRECATED: removed in v2.4 — do not read';

-- ✅ Phase 2 (next deploy): remove from all queries in application code

-- ✅ Phase 3 (after full rollout): safe to drop — no readers remain
ALTER TABLE users DROP COLUMN legacy_field;

-- BRIN for append-only time-series data (events, logs) — 200x smaller than B-tree
CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at);

-- Partial index — only index the subset you query — dramatically smaller and faster
CREATE INDEX idx_orders_pending ON orders (created_at DESC)
WHERE status = 'pending';  -- only ~5% of rows — index is 95% smaller

-- ❌ Broken pattern: filtering by tenant ID in the application — every query must remember it
-- SELECT * FROM invoices WHERE tenant_id = current_user_tenant -- easy to forget
-- A single missing WHERE clause exposes all tenants' data

-- ✅ Correct: enforce tenant isolation at the database layer with RLS
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Policy: each row is only visible to the session's active tenant
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Application sets the tenant context before any query — RLS enforces the rest
SET app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';
SELECT * FROM invoices;  -- automatically scoped to this tenant — no WHERE needed

-- Install the pg_audit extension for compliance-grade audit logging
-- Add to postgresql.conf:
-- shared_preload_libraries = 'pgaudit'
-- pgaudit.log = 'write, ddl'  -- log all writes and schema changes

-- Query the PostgreSQL log for audit events (format depends on pgaudit config)
-- Each logged event includes: timestamp, user, database, object, command, statement

Step 1: Connection pool exhaustion (most common first signal)
  → Deploy PgBouncer in TRANSACTION mode
  → Set pool_size = (CPU cores × 2) + disk spindles

Step 2: Read replica lag / read-heavy workload
  → Add streaming replicas; route SELECT to replicas via load balancer

Step 3: Write throughput ceiling (primary WAL saturated)
  → Vertical scale (larger instance, faster NVMe)
  → Then evaluate: CockroachDB for horizontal SQL, or DynamoDB for simple key workloads

Step 4: Dataset size > available RAM (index doesn't fit in shared_buffers)
  → Partitioning (RANGE on created_at for time-series tables)
  → Then evaluate: paradigm migration