PostgreSQL,
zero to advanced

-- CTE plus window function: top 3 grades per course
WITH ranked AS (
    SELECT course_id, student_id, grade,
           rank() OVER (PARTITION BY course_id ORDER BY grade DESC) AS pos
    FROM enrollments
)
SELECT course_id, student_id, grade
FROM ranked
WHERE pos <= 3;

ALTER TABLE students ADD COLUMN profile JSONB NOT NULL DEFAULT '{}';
CREATE INDEX idx_students_profile ON students USING GIN (profile);

SELECT full_name
FROM students
WHERE profile @> '{"club": "robotics"}';

CREATE MATERIALIZED VIEW course_stats AS
SELECT course_id, count(*) AS n, round(avg(grade),2) AS avg_grade
FROM enrollments
GROUP BY course_id;

CREATE UNIQUE INDEX ON course_stats (course_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY course_stats;

CREATE TABLE staff (
    id      INT PRIMARY KEY,
    name    TEXT NOT NULL,
    boss_id INT REFERENCES staff(id)
);
INSERT INTO staff VALUES (1,'Dean',NULL), (2,'Head of CS',1), (3,'Lecturer A',2), (4,'Lecturer B',2), (5,'Head of Math',1);

WITH RECURSIVE chain AS (
    SELECT id, name, boss_id, 0 AS depth
    FROM staff
    WHERE boss_id IS NULL
    UNION ALL
    SELECT s.id, s.name, s.boss_id, c.depth + 1
    FROM staff s
    JOIN chain c ON s.boss_id = c.id
    WHERE c.depth < 20
)
SELECT repeat('    ', depth) || name AS org_chart, depth
FROM chain
ORDER BY depth, id;

CREATE OR REPLACE FUNCTION grade_letter(score NUMERIC) RETURNS TEXT
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    IF score IS NULL THEN
        RETURN NULL;
    ELSIF score >= 3.7 THEN
        RETURN 'A';
    ELSIF score >= 3.0 THEN
        RETURN 'B';
    ELSE
        RETURN 'C';
    END IF;
END;
$$;

SELECT full_name, grade_letter(gpa) AS letter FROM students;

CREATE PROCEDURE enroll_student(p_student BIGINT, p_course BIGINT)
LANGUAGE plpgsql AS $$
DECLARE
    seats INT;
BEGIN
    SELECT count(*) INTO seats FROM enrollments WHERE course_id = p_course;
    IF seats >= 30 THEN
        RAISE EXCEPTION 'Course % is full', p_course;
    END IF;
    INSERT INTO enrollments (student_id, course_id) VALUES (p_student, p_course);
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'Student % is already enrolled', p_student;
END;
$$;

CALL enroll_student(6, 1);
CALL enroll_student(6, 1);

ALTER TABLE students ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT now();

CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_students_touch
BEFORE UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

CREATE TABLE audit_log (
    id        BIGINT GENERATED ALWAYS AS IDENTITY,
    logged_at TIMESTAMPTZ NOT NULL,
    payload   JSONB
) PARTITION BY RANGE (logged_at);

CREATE TABLE audit_log_2026_10 PARTITION OF audit_log
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- Keyset pagination: stays fast on huge tables, unlike large OFFSET values
SELECT id, full_name
FROM students
WHERE id > 1000
ORDER BY id
LIMIT 50;

-- Primary (postgresql.conf): wal_level = logical
-- Publish selected tables
CREATE PUBLICATION pub_students FOR TABLE students, courses;

-- Subscriber: pull those changes continuously
CREATE SUBSCRIPTION sub_students
CONNECTION 'host=primary dbname=university user=repl'
PUBLICATION pub_students;

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_students_name_trgm ON students USING GIN (full_name gin_trgm_ops);
SELECT full_name FROM students WHERE full_name ILIKE '%rahm%';

SHOW shared_buffers;
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

CREATE ROLE app_user LOGIN PASSWORD 'change_me';
GRANT CONNECT ON DATABASE university TO app_user;
GRANT SELECT, INSERT, UPDATE ON students TO app_user;
REVOKE ALL ON enrollments FROM PUBLIC;

# Compressed custom format dump
pg_dump -Fc -d university -f university.dump

# Restore into a separate copy first
createdb university_copy
pg_restore -d university_copy university.dump

-- Safe migration on a big table: nullable column, batched backfill, then constraint
ALTER TABLE students ADD COLUMN phone TEXT;
UPDATE students SET phone = '' WHERE id BETWEEN 1 AND 1000 AND phone IS NULL;
ALTER TABLE students ADD CONSTRAINT phone_required CHECK (phone IS NOT NULL) NOT VALID;
ALTER TABLE students VALIDATE CONSTRAINT phone_required;

-- Find the slowest statements (needs the pg_stat_statements extension)
SELECT query, calls, round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 5;

CREATE TABLE notes (
    id    SERIAL PRIMARY KEY,
    owner TEXT NOT NULL DEFAULT current_user,
    body  TEXT NOT NULL
);

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE notes FORCE ROW LEVEL SECURITY;

CREATE POLICY own_notes ON notes
    USING (owner = current_user)
    WITH CHECK (owner = current_user);

# docker-compose.yml
services:
  db:
    image: pgvector/pgvector:pg16
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: university
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (
    id        BIGSERIAL PRIMARY KEY,
    content   TEXT NOT NULL,
    embedding vector(3)
);
INSERT INTO docs (content, embedding)
VALUES ('cat', '[1,0,0]'), ('kitten', '[0.9,0.1,0]'), ('car', '[0,0,1]');
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

-- Nearest two documents by cosine distance
SELECT content FROM docs ORDER BY embedding <=> '[1,0,0]' LIMIT 2;