Database Design

-- 1NF: Atomic values, no repeating groups-- 2NF: No partial dependencies on composite keys-- 3NF: No transitive dependencies
-- Users table (normalized)CREATE TABLE users (  id SERIAL PRIMARY KEY,  email VARCHAR(255) UNIQUE NOT NULL,  created_at TIMESTAMPTZ DEFAULT NOW());
-- Addresses table (separate entity)CREATE TABLE addresses (  id SERIAL PRIMARY KEY,  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,  street VARCHAR(255),  city VARCHAR(100),  country VARCHAR(100),  is_primary BOOLEAN DEFAULT false);

-- When read performance matters more than write consistencyCREATE TABLE order_summaries (  id SERIAL PRIMARY KEY,  order_id INTEGER REFERENCES orders(id),  customer_name VARCHAR(255),  -- Denormalized from customers  total_amount DECIMAL(10,2),  item_count INTEGER,  last_updated TIMESTAMPTZ DEFAULT NOW());

-- B-tree (default) for equality and range queriesCREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Partial index for specific conditionsCREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;
-- GIN index for array/JSONB columnsCREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- Covering index (includes additional columns)CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);

-- Check index usageSELECT  schemaname, tablename, indexname,  idx_scan, idx_tup_read, idx_tup_fetchFROM pg_stat_user_indexesORDER BY idx_scan DESC;
-- Find missing indexesSELECT  relname, seq_scan, seq_tup_read,  idx_scan, idx_tup_fetchFROM pg_stat_user_tablesWHERE seq_scan > idx_scanORDER BY seq_tup_read DESC;

-- Always use transactionsBEGIN;
-- Add column with default (non-blocking in PG 11+)ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- Create index concurrently (doesn't lock table)CREATE INDEX CONCURRENTLY idx_users_status ON users(status);
-- Backfill data in batchesUPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
COMMIT;

-- Always use EXPLAIN ANALYZEEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- Key metrics to watch:-- - Seq Scan vs Index Scan-- - Actual rows vs Estimated rows-- - Buffers: shared hit vs read

-- Use EXISTS instead of IN for large setsSELECT * FROM users uWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- Pagination with keyset (cursor) instead of OFFSETSELECT * FROM postsWHERE created_at < '2024-01-01'ORDER BY created_at DESCLIMIT 20;
-- Use CTEs for complex queriesWITH active_users AS (  SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days')SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);

-- Primary keyALTER TABLE users ADD PRIMARY KEY (id);
-- Foreign key with cascadeALTER TABLE orders ADD CONSTRAINT fk_orders_user  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- Check constraintALTER TABLE products ADD CONSTRAINT chk_price_positive  CHECK (price >= 0);
-- Unique constraintALTER TABLE users ADD CONSTRAINT uniq_users_email UNIQUE (email);
-- Exclusion constraint (no overlapping ranges)ALTER TABLE reservations ADD CONSTRAINT excl_no_overlap  EXCLUDE USING gist (room_id WITH =, tsrange(start_time, end_time) WITH &&);