PostgreSQL & pgvector

🔴 First, because every tuning decision, every recovery procedure and half the failure diagnoses in this path are downstream of this picture. A supervisor process, one backend per connection, a shared buffer pool, and a write-ahead log that is written before the data pages are. That last fact alone explains durability, crash recovery, replication, point-in-time recovery and checkpoint behaviour — four later units that otherwise have to be memorised separately.

The operational vocabulary: a cluster is a data directory and a running server, not a database, and the ambiguity in that word confuses people for months. Covers initialising a cluster, the configuration files and their precedence, and the context levels that determine whether a setting takes effect immediately, on reload, or only on restart — which is the practical detail that turns a tuning change into a maintenance window.

How things are named and who may touch them. Databases as isolation boundaries you cannot query across, schemas as namespaces, the search path that silently resolves an unqualified name and is a genuine security consideration, and roles that are simultaneously users and groups. Ownership and default privileges belong here too — the source of the perennial 'I granted access and it still says permission denied'.

PostgreSQL's type system is one of the strongest reasons to choose it, and most applications use about six types. Covers the ones left on the table: binary JSON with its own indexing and operators, arrays, ranges, network and UUID types, generated columns, and exact numeric versus floating point — where using the wrong one for money is a bug that survives every test suite. Type choice is a schema decision that is expensive to reverse.

Tables, keys, foreign keys, check and exclusion constraints, and normalisation used as a tool rather than a doctrine. Constraints are framed as what they are — correctness guarantees enforced by the engine, which is strictly stronger than correctness enforced by application code — along with the honest cost of each at write time and the locking implications of adding one to a live table.

Joins and their semantics, common table expressions, window functions, lateral joins, grouping sets, and set operations. Window functions in particular replace whole categories of application-side looping, and the difference in performance is not marginal. A Skill element: it is closed by writing queries against real data, not by recognising syntax.

🔴 The single concept that explains the most production incidents. Postgres does not update rows in place; it writes a new version and leaves the old one until nothing can see it. That one mechanism explains why readers never block writers, why a long-running transaction is dangerous far away from itself, why tables grow when nothing was inserted, and why the next unit exists at all. Teach this wrong and everything about maintenance becomes ritual.

The direct consequence of A7 and the most common source of a self-inflicted outage. Dead tuples accumulate, autovacuum reclaims them, and when autovacuum cannot keep up the table bloats and queries slow. The Risk element is transaction ID wraparound: it is a genuine, documented way to take a database offline, it is entirely preventable, and it announces itself in the logs for a long time before it happens. Hazard AND mitigation.

The access methods and what each is actually for: the default balanced tree, inverted indexes for multi-valued and full-text data, the generalised search tree behind geometric and — importantly for Track B — nearest-neighbour searching, block range indexes for naturally ordered large tables, and partial and expression indexes. Also the cost side, which gets skipped: every index is write amplification and disk, and an unused index is pure overhead.

The skill that separates someone who can use Postgres from someone who can fix it. Statistics, cost estimation, join order and method selection, and then reading actual execution output with timing and buffer counts. The diagnostic move being trained is specific: compare the estimated row count against the actual, because a large divergence is the root cause of most bad plans. A Skill — closed by diagnosing real slow queries.

What a transaction guarantees at each isolation level, the anomalies each level permits, and the serialisation failures the strictest level makes your application responsible for retrying. Lock modes and what blocks what, deadlocks and how they are resolved, advisory locks, and — the practical item — which schema changes take a lock that stops all traffic and which do not.

How Postgres is extended, which is the reason the second track is possible at all: new types, operators, index access methods and functions loaded into a running database. Covers what an extension can and cannot do, versioning and upgrades, the trust and availability question on managed platforms, and the handful of extensions worth knowing about. This is the bridge unit into Track B.

The unit whose absence ends companies. Logical dumps versus physical base backups, continuous archiving and point-in-time recovery, streaming replication and replica lag, synchronous versus asynchronous commit and the data-loss window each implies, and failover. The Risk element is stated plainly: an untested backup is not a backup, and the assessable skill is a performed restore, not a configured backup job.

Client authentication and the host-based rules file that most people edit once and never understand, role privileges and the default grants that surprise people, row-level security, encryption in transit and at rest, and auditing. Risk elements throughout, because the mitigations are specific configurations rather than principles.

Closing Track A with the operational loop: the statistics views, finding the queries that consume the most total time rather than the slowest single execution, watching for long transactions and lock waits, log configuration, and connection pooling — which matters because the process-per-connection model in A1 makes a few thousand idle connections a real problem rather than a theoretical one.

Track B opens with the concept rather than the extension, because the most common mistake in vector search is not a database mistake. An embedding is a model's representation of an input, similarity in that space means whatever the model was trained to make it mean, and vectors from different models are not comparable at all. Distance metric choice belongs here too, and it is not free — it must match how the model was trained.

Getting vectors into a table honestly. The type and its dimension limits, the storage cost per row and how quickly it dominates a table, the effect of oversized rows on out-of-line storage, and the distance operators and their corresponding operator classes. This unit is where the practical scale question gets answered: how many vectors of what dimension fit before the design has to change.

A sequential scan computing exact distances is correct by construction, needs no index, and on a modest dataset is fast enough. Starting here gives the ground truth every approximate method must be measured against, and it prevents the standard error of adding an index before there is any baseline to compare recall against. The unit ends with the honest threshold at which exact search stops being viable.

🔴 The decision that determines whether a retrieval system works. The two available index families differ in build time, memory, insert behaviour and how they degrade — one partitions the space and needs representative data before it is built, the other builds a navigable graph incrementally at higher cost. Both trade recall for speed through parameters, and the assessable skill is measuring that trade against the Track B unit 3 ground truth rather than accepting a default.

🔴 The failure that surprises everyone building retrieval with permissions or categories. A query that filters by metadata and then ranks by similarity can be executed two ways, and both are bad in different ways: filter first and the vector index is unusable, search first and the filter throws away most results, leaving fewer than requested. Understanding how the planner chooses, and the design patterns that avoid the choice, is what makes filtered retrieval work in production.

Vector similarity and lexical search fail in different ways — semantic search misses exact identifiers, product codes and rare terms; keyword search misses paraphrase — and combining them measurably beats either. Covers Postgres full-text search, running both retrievals, and the fusion methods for combining rankings that are on different and incomparable scales.

The upstream decision that determines retrieval quality more than any index parameter, and the one most systems never revisit. Chunk size and overlap, respecting document structure, what metadata travels with the chunk, and keeping the original text retrievable. It sits in this path rather than an application one because the schema choice is where it is fixed, and reversing it means re-embedding everything.

Without measurement, every change to the previous seven units is a guess. A labelled evaluation set, recall and precision at a cutoff, ranking measures, latency at the tail rather than the mean, and the discipline of changing one parameter at a time. A Skill element and the most transferable thing in Track B — it is what allows an honest claim that a retrieval system got better.

Track A's operational content applied to this specific load, which stresses the database in unusual ways. Index build time and memory on large tables, the maintenance cost when vectors are updated frequently, backup size, and the interaction with vacuum from unit A8 — heavy updates on wide vector rows generate dead tuples fast. The Risk element is capacity: a vector table's growth curve is steeper than teams plan for.