PostgreSQL

psql is the official interactive terminal for Postgres. Every concept in this track — transactions, EXPLAIN, locks — is something you can drive and observe directly in psql before any application code exists. Learning the database from its own shell means you understand what your driver is doing later, instead of treating it as a black box. The default superuser and database are both named postgres.

A constraint is a rule the database enforces on every write, no matter which client or service performs it. PRIMARY KEY makes a row uniquely addressable; NOT NULL forbids missing values; UNIQUE blocks duplicates; CHECK enforces a predicate like price >= 0. Putting these in the schema means the rule cannot be bypassed by a buggy service or a hand-typed query — the database is the last line of defence, and it never gets tired. Store money as numeric or integer minor units (cents), never float: binary floating point cannot represent 0.10 exactly and will silently drift.

When you concatenate user input into a SQL string, a value like '; DROP TABLE products; -- becomes executable SQL — that’s injection. Parameterised queries send the SQL and the values over separate channels, so a value is always treated as data, never as code. In psql you can prototype with \set-style variables; in application code your driver exposes placeholders ($1, $2). As a bonus the planner can reuse a prepared plan across calls. Make “always parameterise” a reflex, not a judgement call.

A transaction is a unit of work that is atomic (all statements commit or none do), consistent (it moves the database from one valid state to another, constraints intact), isolated (concurrent transactions don’t see each other’s half-done work), and durable (once committed, it survives a crash). The classic example is moving stock and recording an order: if the order insert succeeds but the stock decrement fails, you’ve sold something you don’t have. BEGIN starts the transaction, COMMIT makes it permanent, and ROLLBACK (or any error) throws the whole thing away. This is the single most important reason to choose a relational database for a system of record.

An index is a sorted side structure that lets Postgres find rows without scanning the whole table. The default B-tree index serves equality and range queries on ordinary columns. But adding an index is a hypothesis, not a fact — the query planner is cost-based and may still choose a sequential scan if the table is tiny or the filter is unselective. EXPLAIN shows the planned strategy; EXPLAIN ANALYZE actually runs the query and reports real row counts and timings. Read it bottom-up: look for Index Scan (good) versus Seq Scan over a large table (a clue you may be missing an index). A partial index (WHERE …) covers only the rows you query, staying small and cheap to maintain.

jsonb stores JSON in a decomposed binary form that is fast to query and index — unlike json, which keeps the raw text. You get document-style flexibility for genuinely variable data (product attributes, event payloads) while keeping your relational columns, constraints, and transactions for the structured core. Query with the -> (returns json) and ->> (returns text) accessors, test containment with the @> operator, and accelerate containment queries with a GIN index — the index type built for “does this document contain that key/value”. The lesson: you rarely have to choose between relational and document — Postgres does both, in one ACID engine.

A FOREIGN KEY constraint ties a column in one table to a primary key in another and makes the database enforce the relationship on every insert, update, and delete. Without it, an application bug can leave orphan rows — an order pointing at a deleted product — that quietly corrupt reports and break joins. With it, the database rejects the bad write, and ON DELETE actions (RESTRICT, CASCADE, SET NULL) let you declare exactly what should happen to children when a parent goes away. This is integrity you get for free, forever, instead of integrity you hope every service remembers to check.

Postgres uses MVCC (Multi-Version Concurrency Control): readers see a consistent snapshot and don’t block writers. That’s great for throughput, but it means two transactions can both read stock = 1, both decide it’s fine, and both decrement — overselling. SELECT ... FOR UPDATE takes a row-level lock: the first transaction to grab the row makes the second wait until the first commits, so the second sees the already-decremented value and correctly refuses. This pessimistic lock is the simplest correct fix for “reserve the last unit” style contention. Keep the locked section short — you are serialising everyone who wants that row.

Isolation level controls how much concurrent transactions can affect each other. The Postgres default is READ COMMITTED: each statement sees rows committed before it started, which still allows some write-skew anomalies. SERIALIZABLE is the strongest level — it guarantees the result is as if transactions ran one at a time, catching anomalies that row locks alone miss. The trade-off: instead of blocking, Postgres may abort a transaction at commit time with a serialization failure, SQLSTATE 40001. That is not a bug — it’s the database telling you to retry the whole transaction. So SERIALIZABLE always comes with a bounded retry loop in application code. Use it when correctness under concurrency matters more than squeezing out the last bit of throughput.

An embedding is a fixed-length array of floats that captures the meaning of text or an image; similar items have nearby vectors. pgvector is an official Postgres extension that adds a vector type and distance operators — <-> (L2), <=> (cosine), <#> (inner product) — so “find the most similar products” becomes an ORDER BY embedding <=> $1 LIMIT k query. Keeping vectors in Postgres means your semantic search shares the same transactions, constraints, and backups as the rest of your data — no second database to keep in sync. For large tables, an approximate index (HNSW or IVFFlat) trades a little recall for a large speed-up. You generate the embeddings with a model; Postgres stores and searches them.

First, backups: pg_dump produces a consistent logical snapshot you can restore with pg_restore — schedule it and test the restore, because an untested backup is a rumour. Second, VACUUM: because MVCC keeps old row versions until no transaction can see them, deleted/updated rows leave “dead tuples”. Autovacuum reclaims them and updates the planner’s statistics; if you ever see tables bloating or plans going stale, autovacuum tuning is where you look. Third, connections: each Postgres connection is a real backend process, so thousands of direct app connections will exhaust memory. Put a pooler (PgBouncer, or your managed provider’s built-in pooler) in front, and size the pool to the database, not the app fleet. These three habits separate a database that survives production from one that surprises you at 3am.