Bootcamp

# ===== 0-30 min: install + connect =====
# Docker:
docker run -d --name pg -p 5432:5432 \
  -e POSTGRES_USER=dev -e POSTGRES_PASSWORD=dev -e POSTGRES_DB=app \
  postgres:16

psql 'postgres://dev:dev@localhost:5432/app'

# ===== 30-60 min: schema =====
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  total NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'new',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

# ===== 60-120 min: queries =====
INSERT INTO users (email, name) VALUES ('a@x.io', 'Alex'), ('b@x.io', 'Sam');
INSERT INTO orders (user_id, total, status)
SELECT id, 49.95, 'paid' FROM users;

SELECT u.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name
ORDER BY total_spent DESC;

# Window functions:
SELECT
  user_id,
  total,
  SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM orders;

# ===== 120-180 min: indexes + EXPLAIN =====
CREATE INDEX orders_user_created ON orders (user_id, created_at DESC);
CREATE INDEX users_email ON users (email);

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = '...' ORDER BY created_at DESC LIMIT 20;

# ===== 180-240 min: JSON + arrays =====
ALTER TABLE users ADD COLUMN tags TEXT[];
UPDATE users SET tags = ARRAY['vip', 'beta'] WHERE id = '...';

SELECT name FROM users WHERE 'vip' = ANY(tags);

ALTER TABLE orders ADD COLUMN data JSONB DEFAULT '{}';
CREATE INDEX orders_data_kind ON orders ((data->>'kind'));

SELECT * FROM orders WHERE data @> '{"kind":"refund"}';

# ===== 240-300 min: transactions + isolation =====
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

# Isolation levels:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# Advisory locks:
SELECT pg_try_advisory_lock(12345);

# ===== 300-360 min: replication =====
# Configure primary postgresql.conf:
wal_level = replica
max_wal_senders = 10

# pg_hba.conf: allow replica connection
host replication replicator 10.0.0.0/8 scram-sha-256

# Bootstrap replica:
pg_basebackup -h primary -D /var/lib/postgresql/data -U replicator -P -X stream

# ===== 360-420 min: backups + PITR =====
# Logical:
pg_dump -Fc app > app.dump
pg_restore -d app_restore app.dump

# Physical + PITR:
pg_basebackup + archive_command for WAL archiving.
Tools: pgBackRest, Barman.

# Test restore monthly!

# ===== 420-480 min: monitoring =====
# Built-in:
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
SELECT * FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

# Exporters: postgres_exporter (Prometheus) + Grafana dashboards.

# ===== Patterns =====
# - TIMESTAMPTZ + NUMERIC + JSONB + UUID PKs
# - Composite indexes match left-anchored queries
# - EXPLAIN every slow query
# - Replicas + tested backups + monitoring before launch
# - Use roles per service responsibility

# ===== Pitfalls =====
# - DOUBLE PRECISION for money
# - TIMESTAMP without tz
# - VARCHAR(n) magic numbers
# - Forgetting indexes on FK columns
# - No tested restore plan

-- 30-day Postgres bootcamp in the lesson body.