Databases Tutorial

A hands-on tour of relational databases using PostgreSQL, from your first SELECT to running them in production.

Who This Is For

• Developers who know a programming language and want the database to stop being a mystery box
• Engineers comfortable with basic SELECT who want to understand indexing, transactions, and query plans
• Anyone who has been burned by a slow query and wants to read EXPLAIN output

Contents

Fundamentals

1. Introduction: What databases are, the relational model, installing PostgreSQL, first query
2. SQL Basics: SELECT, WHERE, ORDER BY, LIMIT, expressions, and the quirks of NULL
3. Schema Design: CREATE TABLE, data types, primary and foreign keys, constraints, CHECK

Core Concepts

4. Writing Data: INSERT, UPDATE, DELETE, RETURNING, and upserts with ON CONFLICT
5. Joins: INNER, LEFT, RIGHT, FULL, self-joins, subqueries, and CTEs
6. Aggregation: GROUP BY, HAVING, aggregate functions, window functions

Advanced

7. Indexing: B-tree, composite, partial indexes, EXPLAIN, and when an index hurts
8. Transactions: ACID, isolation levels, locks, deadlocks, SAVEPOINT
9. Normalization: 1NF, 2NF, 3NF, functional dependencies, when to denormalize
10. Query Performance: EXPLAIN ANALYZE, query plans, statistics, common slow patterns

Ecosystem

11. NoSQL and Alternatives: Redis, MongoDB, SQLite vs PostgreSQL vs MySQL, picking the right store

Mastery

12. Best Practices: Migrations, backups, connection pooling, SQL injection, monitoring, idioms

How to Use This Tutorial

1. Read sequentially for a complete learning path
2. Type out the examples. Don't just read them
3. Run every query against a real PostgreSQL instance (local install, Docker, or a cloud free tier)

Quick Reference

Essential Commands

# Install on macOS
brew install postgresql@16
brew services start postgresql@16

# Create and drop databases
createdb learning
dropdb learning

# Connect
psql learning

# Run a SQL file
psql learning -f schema.sql

# Dump and restore
pg_dump learning > learning.sql
psql learning < learning.sql

Hello World

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO users (email) VALUES ('ada@example.com');

SELECT * FROM users;

Common Patterns

-- Filter, sort, and limit
SELECT id, email
FROM users
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- Join two tables
SELECT o.id, u.email, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

-- Aggregate by group
SELECT status, COUNT(*), SUM(total)
FROM orders
GROUP BY status;

-- Upsert (insert or do nothing on conflict)
INSERT INTO users (email)
VALUES ('lovelace@example.com')
ON CONFLICT (email) DO NOTHING;

Learning Path Suggestions

Application developer (roughly 8 hours)

1. Chapters 01 to 06 for the SQL you'll write every day
2. Chapter 07 for indexes, 08 for transactions
3. Chapter 12 for production habits

Backend engineer going deep (roughly 15 hours)

1. All chapters in order
2. Build a schema for a small project (blog, store, task tracker) and migrate it twice
3. Load a few hundred thousand rows and tune two slow queries using EXPLAIN ANALYZE

Picking a database (roughly 2 hours)

1. Chapter 01 for the relational model
2. Chapter 11 for alternatives
3. Chapters 03 and 09 for schema tradeoffs

Additional Resources

• PostgreSQL documentation
• Use The Index, Luke!
• Designing Data-Intensive Applications
• PG Exercises
• PostgreSQL source

PostgreSQL Version

This tutorial is written for PostgreSQL 16 and covers features up to 16.x. Most examples work on PostgreSQL 13+ with small tweaks.