postgres should be your best friend
postgres should be your best friend everywhere, in every project
postgres is not just storage. it gives you SQL, ACID, MVCC, WAL, constraints, indexes and a query planner in the same system.
a table is a heap of pages, usually 8 kb each. a physical row is a tuple containing fields such as `xmin`, `xmax` and `ctid`.
`insert` creates a tuple. `update` invalidates the old one and creates a new one. `delete` sets `xmax`, but does not immediately remove the bytes.
every query reads from a snapshot. two transactions can therefore see different versions of the same logical row. that is MVCC.
a seq scan reads every page, checks each tuple's visibility, then applies the predicate. its cost is roughly `O(p + n)`.
an index scan usually traverses a b-tree in `O(log_b n)`, retrieves tids, then loads the corresponding tuples from the heap. its real cost is `O(log_b n) + k heap fetches`.
when `k` becomes large, a seq scan can be cheaper. reading 30% of a table sequentially often costs less than performing thousands of scattered heap accesses.
a bitmap heap scan sits between both plans: postgres collects tids, groups them by page, then reads the heap in physical order.
an index only scan skips the heap only when the index contains every required column and the visibility map marks the pages as `all-visible`. otherwise, postgres still needs a heap fetch.
every index makes some reads faster, but makes writes more expensive, consumes cache, generates WAL and can create bloat. HOT can avoid index updates when no indexed column changes.
`analyze` gives the planner cardinalities, histograms, common values and correlations. when `estimated rows` and `actual rows` differ, the planner is working with the wrong representation of your data.
`vacuum` recycles dead tuples, cleans indexes, updates the visibility map and freezes old transaction ids. it usually does not shrink the file: the space becomes reusable by postgres.
returning space to the OS requires free pages at the end of the file, `vacuum full` with an exclusive lock, or `truncate` when the entire table can be removed.
autovacuum triggers around `threshold + scale_factor * reltuples`. with the defaults, a 100 million row table can accumulate around 20 million dead tuples before triggering.
the important settings are `autovacuum_vacuum_scale_factor`, `autovacuum_vacuum_threshold`, `autovacuum_analyze_scale_factor`, `autovacuum_max_workers`, `naptime`, `cost_limit`, `cost_delay` and `freeze_max_age`.
if vacuum cannot reclaim anything, check long-running transactions, `idle in transaction` sessions and replication slots. they can preserve old snapshots and prevent tuple cleanup.
always start with `explain (analyze, buffers, wal)`. postgres tells you where the CPU, page reads and writes are actually going.