PostgreSQL Basics
The best-in-class OSS relational database. Type-safe, ACID-compliant, rich feature set (JSONB, full-text, extensions). Default choice for most projects.
1 credit
Why Postgres over (fill in the blank)
- **vs MySQL** — better standards compliance, stronger features (true transactional DDL, window funcs, CTEs), less surprising edge cases.
- **vs MongoDB** — JSONB gives you document flexibility AND relational safety. Most "we chose Mongo" projects end up needing joins.
- **vs SQLite** — Postgres is a real server; SQLite is a library. SQLite for single-node apps, mobile, tests.
- **vs Cloud-only (Dynamo, Firestore)** — Postgres is portable across providers; no vendor lock-in.
First queries
sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
meta JSONB DEFAULT '{}'::jsonb
);
CREATE INDEX idx_users_created_at ON users(created_at);
INSERT INTO users (email, meta)
VALUES ('a@x.com', '{"role":"admin"}')
ON CONFLICT (email) DO UPDATE SET meta = EXCLUDED.meta
RETURNING id, created_at;Features that set it apart
- **JSONB** — schemaless column + indexed queries. Best of both worlds for semi-structured data.
- **CTEs + recursive** — complex queries as readable steps. Tree traversals, dependency graphs.
- **Window functions** — ranking, running totals, lead/lag without collapsing rows.
- **Full-text search** — built-in, indexable, good enough until you hit Elastic-scale.
- **Extensions** — PostGIS (geo), pg_trgm (fuzzy search), TimescaleDB (time-series), pgvector (embeddings).
Beginner mistakes
- `varchar(n)` vs `text` — use `text`. Postgres doesn't store it differently; `varchar(n)` just adds a length check.
- `id SERIAL` — works but use `BIGSERIAL` or `BIGINT GENERATED AS IDENTITY` to avoid overflow.
- Missing indexes on FKs — every FK column usually wants an index. Postgres doesn't auto-create them.
- `TIMESTAMP WITHOUT TIME ZONE` — use `TIMESTAMPTZ`. Always store UTC, render in the client's timezone.
- Storing enums as text — use actual enum types or a lookup table. Prevents typos.