PostgreSQL — Basics
Architecture
Section titled “Architecture”- Process-per-connection (forked). Heavy → put PgBouncer in front.
- WAL for durability. fsync on commit.
- Shared buffers ~25% of RAM. Background writers, autovacuum, checkpointer.
- Numeric:
int2/4/8,numeric(p,s),real,double precision. - Text:
text(preferred),varchar(n). No perf diff. - Time:
timestamptz(always — UTC stored),date,interval. - JSON:
jsonb(binary, indexable). Neverjson. - Arrays, UUID, citext, range, geometric, network, enum.
Constraints
Section titled “Constraints”PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY. Deferrable for cycles. EXCLUDE USING gist for no-overlap (e.g., time ranges).
Indexes
Section titled “Indexes”| Type | Use |
|---|---|
| B-tree (default) | equality, range, ORDER BY, LIKE 'foo%' |
| Hash | equality only (rarely beats B-tree) |
| GIN | jsonb, arrays, full-text |
| GiST | geo, full-text, exclusion, ranges |
| SP-GiST | partitioned space (IPs, phone) |
| BRIN | huge ordered tables (timestamps); tiny |
- Partial:
WHERE deleted_at IS NULL— index subset. - Covering:
INCLUDE (col)— index-only scan. - Expression:
ON users (lower(email)). - Multi-col: leftmost-prefix only.
Inner, Left, Right, Full Outer, Cross, Lateral, Self. Planner picks: nested loop, hash, merge.
Transactions
Section titled “Transactions”BEGIN/COMMIT/ROLLBACK. Savepoints. Default = Read Committed.
| Level | Prevents |
|---|---|
| Read Committed | dirty reads |
| Repeatable Read | + non-repeatable + phantom (PG snapshot) |
| Serializable | + serialization anomalies (SSI). Retry on 40001. |
SQL features
Section titled “SQL features”- CTE:
WITH foo AS (...). PG 12+ inlines simple ones. - Window:
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...),LAG,LEAD. RETURNING *,INSERT ... ON CONFLICT (col) DO UPDATE(upsert).DISTINCT ON (col),LATERAL.
NULL = NULL is NULL. Use IS NULL / IS DISTINCT FROM. count(col) skips, count(*) doesn’t.
Permissions
Section titled “Permissions”Roles, schemas, GRANT/REVOKE. Row-level security via CREATE POLICY.