Databases · PostgreSQL

We run PostgreSQL because it’s ACID‑safe, battle‑tested, and pairs cleanly with Rust ORMs like sqlx and Diesel. Everything here targets Postgres 16 but 95 % works back to 12.

1 · Spin‑up

docker run --rm -d --name postgres -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=mydb -p 5432:5432 postgres

2 · Connect with psql

psql postgres://postgres:postgres@localhost:5432/postgres

Quick meta‑commands:

All meta‑commands are baked in psql.

3 · Transactions 101

BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;
COMMIT;   -- or ROLLBACK;

One bad statement ? Roll back and your data stays consistent-as per the SQL standard Postgres implements.

4 · Performance quick wins

• Indexes-create them on columns used in WHERE or join predicates:

CREATE INDEX idx_user_email ON users (email);

Index syntax and caveats live in the official docs.

• Explain plans-confirm the planner hits that index:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

Green numbers = faster path, red = full table scan.

5 · UUIDs in Postgres

Postgres ships a native uuid type; we prefer UUIDv7 (time‑ordered) for better index locality in event stores. Use the pgcrypto extension until official uuidv7() ships.

6 · Migrations

We version schema in Git with sqlx migrate (see /contributing/cli_tool.md). One migration = one file pair:

20250511103000_create_users.up.sql
20250511103000_create_users.down.sql

Never edit a committed migration; write a new one.

Next stop → SQL Basics.