Database best practices and optimization checklist

-- SCHEMA DESIGN CHECKLIST

-- 1. Use appropriate data types
CREATE TABLE users_optimized (
  id SERIAL PRIMARY KEY,               -- Auto-increment
  uuid UUID DEFAULT gen_random_uuid(), -- UUID for external IDs
  username VARCHAR(50) NOT NULL,       -- Fixed max length
  email VARCHAR(100) NOT NULL,
  bio TEXT,                             -- Unlimited text
  age SMALLINT,                        -- Small numbers
  balance DECIMAL(12,2),               -- Money (exact)
  is_active BOOLEAN DEFAULT true,
  settings JSONB,                      -- Structured data
  created_at TIMESTAMPTZ DEFAULT NOW() -- Timezone-aware
);

-- DON'T use:
-- VARCHAR without limit
-- CHAR for variable-length
-- FLOAT for money
-- TIMESTAMP without timezone

-- 2. Add constraints for data integrity
ALTER TABLE users_optimized
ADD CONSTRAINT username_length CHECK (char_length(username) >= 3),
ADD CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
ADD CONSTRAINT age_range CHECK (age BETWEEN 0 AND 150),
ADD CONSTRAINT balance_positive CHECK (balance >= 0);

-- Unique constraints
ALTER TABLE users_optimized
ADD CONSTRAINT users_username_unique UNIQUE (username),
ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 3. Create indexes strategically
-- Index foreign keys
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Index frequently queried columns
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite index for combined queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index for specific conditions
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';

-- Covering index (includes additional columns)
CREATE INDEX idx_orders_covering ON orders(user_id)
INCLUDE (total, created_at);

-- DON'T:
-- Index every column
-- Create redundant indexes
-- Forget to index foreign keys

-- 4. Use foreign keys for referential integrity
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending'
);

-- Cascade options:
-- ON DELETE CASCADE: Delete related rows
-- ON DELETE SET NULL: Nullify foreign key
-- ON DELETE RESTRICT: Prevent deletion
-- ON DELETE NO ACTION: Same as RESTRICT

-- 5. Implement proper defaults
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  status VARCHAR(20) DEFAULT 'draft',
  views INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Auto-update trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- PERFORMANCE CHECKLIST

-- 1. Regular maintenance
-- Vacuum to reclaim space
VACUUM ANALYZE users;

-- Full vacuum (locks table)
VACUUM FULL users;

-- Autovacuum settings (postgresql.conf)
/*
autovacuum = on
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_naptime = 1min
*/

-- 2. Monitor slow queries
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Slowest queries
SELECT
  calls,
  total_exec_time / 1000.0 AS total_time_seconds,
  mean_exec_time / 1000.0 AS mean_time_seconds,
  query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

-- 3. Optimize common queries
-- Use EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 0
ORDER BY order_count DESC
LIMIT 10;

-- 4. Use connection pooling
/*
// pgBouncer configuration
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

// Application
const pool = new Pool({
  host: 'pgbouncer-host',
  port: 6432,
  max: 20, // Max connections per app instance
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});
*/

-- 5. Use transactions appropriately
BEGIN;

INSERT INTO users (username, email) VALUES ('newuser', 'new@example.com');

INSERT INTO profiles (user_id, bio)
VALUES (currval('users_id_seq'), 'Bio text');

COMMIT;
-- Both succeed or both fail

-- SECURITY CHECKLIST

-- 1. Least privilege principle
CREATE ROLE app_readonly;
GRANT CONNECT ON DATABASE myapp TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

CREATE ROLE app_readwrite;
GRANT CONNECT ON DATABASE myapp TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

-- 2. Row Level Security (RLS)
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_documents ON documents
FOR ALL
TO app_user
USING (user_id = current_setting('app.current_user_id')::INT);

-- 3. Prepared statements (prevent SQL injection)
-- Always use parameterized queries

-- 4. Encrypt sensitive data
CREATE EXTENSION IF NOT EXISTS pgcrypto;

ALTER TABLE users ADD COLUMN password_hash VARCHAR(255);

UPDATE users
SET password_hash = crypt('password123', gen_salt('bf'))
WHERE id = 1;

-- Verify password
SELECT (password_hash = crypt('password123', password_hash)) AS valid
FROM users WHERE id = 1;

-- 5. Regular backups
/*
# Full backup
pg_dump -U postgres -d myapp -F c -f myapp_backup.dump

# Restore
pg_restore -U postgres -d myapp myapp_backup.dump

# Continuous archiving (WAL)
# postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'
*/

-- MONITORING CHECKLIST

-- 1. Connection monitoring
SELECT
  count(*) AS total_connections,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity;

-- 2. Table bloat
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- 3. Cache hit ratio
SELECT
  'index hit rate' AS name,
  (sum(idx_blks_hit)) / NULLIF(sum(idx_blks_hit + idx_blks_read), 0) AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT
  'table hit rate' AS name,
  sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;
-- Target: > 0.99 (99%)