Nine spreads. Each has a picture, the idea, and queries you can run today: in SQLite with zero setup, then in PostgreSQL for the real thing.
"Normalized schema with constraints, indexed for the queries we actually run, changed through migrations, and backed up with a restore we've tested."
Use the tabs or the ← → keys to turn pages.
| SQLite | PostgreSQL | |
|---|---|---|
| Setup | none; one file | install a server |
| Concurrency | many readers, one writer | many readers and writers |
| Users / auth | none (file perms) | roles, grants |
| Types | flexible ("affinity") | strict, rich (JSONB, arrays) |
| Best for | embedded, local | multi-user production |
$ sqlite3 demo.db sqlite> .tables .schema users .headers on .mode column .quit
$ docker run -d --name pg -e POSTGRES_PASSWORD=devpass -p 5432:5432 postgres:16 $ psql -h localhost -U postgres postgres=# \l \c mydb \dt \d users \du \q
"SQLite for embedded and tests, Postgres once we need concurrent writers and real access control."
CREATE TABLE teams (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
age INT CHECK (age >= 0),
plan TEXT NOT NULL DEFAULT 'free',
team_id BIGINT REFERENCES teams(id) ON DELETE SET NULL,
settings JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);CREATE TABLE users ( id INTEGER PRIMARY KEY, -- auto-increments email TEXT NOT NULL UNIQUE, age INTEGER CHECK (age >= 0), plan TEXT NOT NULL DEFAULT 'free', team_id INTEGER REFERENCES teams(id), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ) STRICT; PRAGMA foreign_keys = ON; -- off by default in SQLite!
NUMERIC, never floating pointTIMESTAMPTZ; SQLite ISO-8601 text"Primary keys, NOT NULL by default, unique and check constraints, and foreign keys on."
INSERT INTO users (email, age) VALUES ('ama@x.com', 31), ('kofi@x.com', 27);
SELECT id, email FROM users WHERE plan = 'pro' ORDER BY created_at DESC LIMIT 10;
UPDATE users SET plan = 'pro' WHERE email = 'ama@x.com'; -- always a WHERE!
DELETE FROM users WHERE id = 2;
SELECT plan, count(*) AS n, avg(age) AS avg_age
FROM users
WHERE age IS NOT NULL
GROUP BY plan
HAVING count(*) > 5
ORDER BY n DESC;
-- Postgres: insert-or-update. SQLite supports the same syntax.
INSERT INTO users (email, plan) VALUES ('ama@x.com', 'team')
ON CONFLICT (email) DO UPDATE SET plan = EXCLUDED.plan
RETURNING id, plan;
IS NULL, never = NULLname LIKE 'A%'; Postgres also has ILIKE (case-insensitive)SELECT with the same WHERE first, or use a transaction"I filter with WHERE, aggregate with GROUP BY, and I never run UPDATE or DELETE without checking the rows first."
-- who ordered what SELECT u.email, o.id AS order_id, o.total_cents FROM users u JOIN orders o ON o.user_id = u.id WHERE o.total_cents > 5000; -- every user, even those with no orders SELECT u.email, count(o.id) AS orders, coalesce(sum(o.total_cents),0) AS spent FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.email ORDER BY spent DESC; -- users who never ordered SELECT email FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id); -- readable steps with a CTE WITH big AS ( SELECT user_id, sum(total_cents) AS spent FROM orders GROUP BY user_id ) SELECT u.email, big.spent FROM big JOIN users u ON u.id = big.user_id WHERE big.spent > 100000;
row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) for "latest per user""LEFT JOIN to keep the unmatched side, CTEs for readability, and aggregate before joining many-to-many."
orders.user_id).order_items(order_id, product_id, qty)).ON DELETE behavior.CREATE TABLE order_items ( order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL REFERENCES products(id), qty INT NOT NULL CHECK (qty > 0), price_cents INT NOT NULL, -- price at time of sale PRIMARY KEY (order_id, product_id) );
deleted_at column instead of removing rows"Third normal form by default; denormalize only for a measured performance reason."
CREATE INDEX idx_orders_user ON orders (user_id); CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC); CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email)); CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending'; -- partial
-- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20; -- look for: Index Scan (good) vs Seq Scan on a big table (suspect) -- SQLite EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42; -- SEARCH orders USING INDEX idx_orders_user (good) / SCAN orders (bad)
(user_id, created_at) serves filter-by-user then sortWHERE lower(email) = … ignores a plain index; index the expression"EXPLAIN ANALYZE first, index the filter and join columns, then measure again."
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- or ROLLBACK; if anything looked wrong -- Postgres: lock a row while you decide, so nobody changes it under you BEGIN; SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- ... check, then UPDATE ... COMMIT;
| Postgres isolation | Stops |
|---|---|
| READ COMMITTED (default) | dirty reads |
| REPEATABLE READ | + non-repeatable reads |
| SERIALIZABLE | + anomalies; retry on conflict |
PRAGMA journal_mode=WAL; for concurrent reads"Short transactions, consistent lock order, retry on serialization failure."
CREATE ROLE app LOGIN PASSWORD 'use-a-secret-manager'; CREATE DATABASE shop OWNER app; GRANT CONNECT ON DATABASE shop TO app; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app; CREATE ROLE reporting LOGIN; GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
$ pg_dump -Fc -h localhost -U postgres shop > shop.dump # backup $ createdb -U postgres shop_restore $ pg_restore -U postgres -d shop_restore shop.dump # restore test $ psql -c "VACUUM (ANALYZE);" shop
-- what's running, and what's slow?
SELECT pid, state, now() - query_start AS running, left(query, 80)
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY running DESC;
SELECT pg_size_pretty(pg_database_size('shop'));
SELECT pg_cancel_backend(12345); -- stop a runaway query
"Least-privilege roles, a connection pooler, nightly dumps plus WAL for point-in-time recovery, and a restore drill."
# Python + SQLite (built in)
import sqlite3
con = sqlite3.connect("app.db")
con.execute("PRAGMA foreign_keys = ON")
con.execute("INSERT INTO users (email) VALUES (?)", (email,)) # ? placeholders
row = con.execute("SELECT id, plan FROM users WHERE email = ?", (email,)).fetchone()
con.commit()
# Python + PostgreSQL (psycopg 3)
import psycopg
with psycopg.connect("postgresql://app:secret@localhost/shop") as conn:
cur = conn.execute("SELECT id, plan FROM users WHERE email = %s", (email,))
row = cur.fetchone()
# NEVER: f"SELECT * FROM users WHERE email = '{email}'" ← SQL injection
-- 0007_add_last_login.sql ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ; CREATE INDEX idx_users_last_login ON users (last_login);
WHERE user_id = ANY($1)."Parameterized queries, migrations in git, a pool, and I check for N+1 before it ships."