PostgreSQL — Audio Guide

PostgreSQL is a relational database: it stores data in structured tables of rows and columns, much like a spreadsheet, but with rules the engine actually enforces.

Every table has a schema that declares each column's data type plus constraints such as not null, unique, or a check expression. Each write is validated against that schema before it lands, so malformed data is rejected at the door rather than discovered later.

It started life as a Berkeley research project in the 1980s and today runs as a long-running server process that multiplexes many concurrent connections. Across those connections it upholds the ACID guarantees — atomicity, consistency, isolation, durability — which is what lets many clients read and write at once without corrupting each other's work.

A bare key-value store hands back whatever bytes you stored, with no structure and no checks; PostgreSQL trades a little raw speed for correctness and data integrity. It is also deeply extensible — custom data types, index methods, operators, and functions can be added without forking the engine, which is how an extension like PostGIS teaches it geospatial coordinates.

Ask yourself: Why is it true that postgresql is a relational database: it stores data in structured tables of rows and columns, much like a spreadsheet, but with rules the engine actually enforces?

Recall check (try before reading the answer):

1. ON — __________________________________________________________________________________________________________________________________ Answer: The ON clause takes a Boolean value expression, and a pair of rows from the two tables match if that expression evaluates to true.

Data lives in tables, each a named collection of rows, where every row shares the same set of typed columns. A row models one entity — a person, a product, an order.

When you create a table you declare each column's type, and from then on the engine enforces it, refusing any value that does not fit. That single rule eliminates a whole class of bugs, like text sneaking into a numeric column.

The type system is broad: integers, text, booleans, timestamps, arrays, ranges, the binary JSONB document type, and user-defined composite types. The type you pick is not cosmetic — it determines on-disk size, which operators apply, and whether an index can accelerate a query.

Physically, rows are packed into fixed 8 kB pages, and a table is just a sequence of those pages on disk (its heap). A value too large for a page is moved aside and compressed through the TOAST mechanism. You could enforce these rules in application code, but that scatters validation across many code paths that can drift apart, so the database centralizes the checks where they cannot be skipped.

Ask yourself: Why is it true that data lives in tables, each a named collection of rows, where every row shares the same set of typed columns?

Imagine a librarian who lets many people read and write the same notebook with nobody waiting in line. A change is written on a fresh sticky note laid over the old page; the old page stays underneath, so every reader sees the notebook as it was when they started. That is the intuition behind MVCC — multiversion concurrency control.

A transaction is a group of reads and writes that commit or roll back as a unit. Instead of locking a row, an update writes a new row version and marks the old one as expired by the updating transaction.

Every tuple carries two hidden system columns — xmin (the transaction that created it) and xmax (the transaction that deleted it). A reader's snapshot sees only versions committed before it began and not yet deleted, which is how readers never block writers and writers never block readers.

The trade-off is bloat: obsolete versions (dead tuples) accumulate and waste space. A background process, VACUUM, reclaims that space and advances the frozen-XID horizon to prevent transaction-ID wraparound in the finite 32-bit counter. Tuning autovacuum to match write volume is a core operational task at scale.

Ask yourself: Why is it true that imagine a librarian who lets many people read and write the same notebook with nobody waiting in line?

Looking back: What was ON (from "What PostgreSQL Is")? Answer: PostgreSQL is a relational database: it stores data in structured tables of rows and columns, much like a spreadsheet, but with rules the engine actually enforces.

An index is the database's card catalog: rather than scanning every shelf, it reads a card, learns the exact location, and goes straight there. Concretely, an index is a separate on-disk structure pointing at where each value lives, letting the planner avoid a full sequential scan.

The default is a B-tree: a balanced tree of sorted keys. Because it stays balanced, any leaf is reachable in a handful of page reads — O(log n) — even across millions of rows. The sorted layout pays off twice: it accelerates range predicates and can return rows already ordered, satisfying an ORDER BY without a separate sort.

Nothing is free. Every insert, update, and delete must also maintain every index on the table, adding write amplification.

Other access methods target other shapes: hash for pure equality, GIN for the contents of composite values like JSONB or full-text documents, GiST for geometric and nearest-neighbour search, and BRIN for very large, naturally ordered tables where a tiny summary per block beats a full index. Each index costs disk and slows writes, so keep only those your real queries use.

Ask yourself: Why is it true that an index is the database's card catalog: rather than scanning every shelf, it reads a card, learns the exact location, and goes straight there?

When you submit a query, the planner (the cost-based optimizer) does not execute it literally — it weighs alternative routes to the same answer and picks the cheapest, like deciding how to find a book before walking into the stacks.

It rewrites your request into a tree of physical operators and enumerates strategies: different join algorithms — nested loop, hash join, merge join — and different access paths — sequential scan versus index scan.

To estimate each step's cost it leans on statistics gathered by ANALYZE and autovacuum: per-column distinct counts, most-common values, and histograms of data distribution. From these it estimates the row count (cardinality) flowing through each operator, assigns an abstract cost from estimated page reads and CPU work, and selects the plan with the lowest total.

The weakness is estimation error: stale statistics or correlated columns can produce a badly wrong row estimate and therefore a slow plan. That is why EXPLAIN ANALYZE is the primary diagnostic — it prints estimated vs actual rows per node, and the largest gap points straight at the misestimate that hurt you.

Ask yourself: Why is it true that when you submit a query, the planner (the cost-based optimizer) does not execute it literally — it weighs alternative routes to the same answer and picks the cheapest, like deciding how to find a book before walking into the stacks?

Recall check (try before reading the answer):

1. JOIN — ___________________________________________________________________________________________________________________________________________________________________________________________________ Answer: In PostgreSQL, when a join type is not explicitly specified, INNER is the default; joins are formed in the FROM clause using the JOIN keyword, which combines two tables based on a join condition.

Looking back: What was WITH (from "MVCC and Transactions")? Answer: Imagine a librarian who lets many people read and write the same notebook with nobody waiting in line.

Normalized schemas split data across tables to avoid repetition; a join stitches the related rows back together by matching a shared key, such as a customer id.

The common forms each answer a different question:

• Inner join — only rows matching on both sides (customers who placed an order).
• Outer join (left/right/full) — keeps unmatched rows from one or both sides, filling gaps with NULL (every customer, even those with no orders).
• Cross join — the Cartesian product of every pair, which grows multiplicatively.
• Self join — a table joined to itself, useful for hierarchies like employee-and-manager.
• Lateral join — the right-hand subquery may reference each left-hand row, enabling per-row work like "the 3 latest orders per customer".

Re-implementing joins in application code is slower and error-prone. Joins do cost work, so the planner chooses among nested loop, hash, and merge based on input sizes and whether the inputs are already sorted — the classic memory-versus-speed trade-off.

Ask yourself: Why is it true that normalized schemas split data across tables to avoid repetition; a join stitches the related rows back together by matching a shared key, such as a customer id?

Before changing any book on the shelf, our librarian first records exactly what she is about to do in an append-only notebook. That notebook is the write-ahead log (WAL), and writing strictly in order is fast because the disk head never seeks.

PostgreSQL follows the WAL rule: before a modified page may be written to the heap or an index, a record describing that change must be flushed to the sequential log. That single discipline is what makes crash recovery possible.

After a failure, the engine replays the WAL forward from the last checkpoint, reapplying every committed change that had not yet reached the data files. The key tuning knob is checkpoint frequency: frequent checkpoints flush dirty pages often, shortening recovery but adding steady I/O; infrequent checkpoints reduce ongoing work but lengthen replay after a crash.

The same log does double duty — it is streamed to standby servers for physical replication, and it can be decoded into row-level changes for logical replication.

Ask yourself: Why is it true that before changing any book on the shelf, our librarian first records exactly what she is about to do in an append-only notebook?

Looking back: What was WHEN (from "The Query Planner")? Answer: When you submit a query, the planner (the cost-based optimizer) does not execute it literally — it weighs alternative routes to the same answer and picks the cheapest, like deciding how to find a book before walking into the stacks.

A single server is a single point of failure, so production PostgreSQL almost always runs with replication: a primary accepts writes while one or more standbys copy each change as it happens.

The standard mechanism is physical streaming replication — the primary ships its WAL stream to standbys, which replay it to remain byte-for-byte identical. A standby can be promoted to primary on failure and can also serve read-only queries to spread load.

The central trade-off is durability versus latency. Asynchronous replication commits without waiting for any standby — fast, but a crash can lose the most recent transactions. Synchronous replication waits for a standby to acknowledge — safer, but each commit pays the round trip.

Failover (promoting a standby) is deliberately delegated to an external tool rather than decided by the database, which refuses to guess whether a silent primary is truly dead and risk a split-brain. Logical replication is the alternative — it replicates selected tables as row changes, ideal for major-version upgrades or partial data distribution, at the cost of extra overhead.

Ask yourself: Why is it true that a single server is a single point of failure, so production postgresql almost always runs with replication: a primary accepts writes while one or more standbys copy each change as it happens?

The everyday rule behind durability is simple: write down your intent before you act. PostgreSQL formalizes this as the four ACID properties.

• Atomicity — a transaction is all-or-nothing; either every change lands or none does.
• Consistency — a committed transaction always leaves the database obeying its constraints and rules.
• Durability — built on the WAL: the intent to change is flushed to the sequential log before it touches the main data files, so a crash is repaired by replaying the log and no committed work is lost.
• Isolation — provided by MVCC snapshots, so each in-flight transaction sees a stable view and never observes another's half-finished work.

None of this is free: the guarantees cost extra work and a little latency versus a loose store that promises nothing. In return, the engine takes full ownership of never losing or corrupting committed data — erasing an entire category of application bugs before they can occur.

Ask yourself: Why is it true that postgresql formalizes this as the four acid properties?

Looking back: What was ON (from "The Write-Ahead Log")? Answer: Before changing any book on the shelf, our librarian first records exactly what she is about to do in an append-only notebook.

MVCC makes PostgreSQL optimistic: it does expensive coordination only when it must. Rather than queue transactions behind row locks, it gives each one a stable snapshot of the data.

An update writes a new row version and leaves the old one visible, so any transaction whose snapshot predates the change still reads the old version cleanly. The result is the MVCC hallmark: readers never block writers and writers never block readers, even under heavy mixed traffic.

The price is bookkeeping. Old versions — dead tuples — accumulate and must be reclaimed by VACUUM running in the background. If vacuum falls behind, tables bloat and both reads and writes slow down.

The whole design is a bet that conflicts between transactions are rare; when that holds, making cheap optimistic copies beats pessimistically locking every row. The bet pays off for most workloads — provided autovacuum is tuned to keep pace with the write rate. (For genuine write-write conflicts, SELECT ... FOR UPDATE or serializable isolation add the pessimism back where it is needed.)

Ask yourself: Why is it true that mvcc makes postgresql optimistic: it does expensive coordination only when it must?

PostgreSQL is declarative: you describe the result you want, and a cost-based expert chooses how to compute it. You write what, the planner decides how.

It enumerates candidate plans — different join orders, join algorithms, and scan methods — estimates each one's cost from table statistics, and runs the cheapest. This is why the same query may use an index today and a sequential scan tomorrow as the data changes, and why refreshing statistics with ANALYZE matters so much.

The weakness is that planning rests on estimates. When statistics are stale, or when two columns are correlated in a way the planner cannot see, it can pick a painfully slow plan. The right fix is to give it better information — fresher stats, extended statistics for correlated columns, a raised statistics target — rather than hand-coding the execution.

Keeping the model declarative is what lets the database keep adapting on its own as your data volume and indexes evolve.

Ask yourself: Why is it true that postgresql is declarative: you describe the result you want, and a cost-based expert chooses how to compute it?

Looking back: What was AS (from "ACID and Durability")? Answer: PostgreSQL formalizes this as the four ACID properties.

Opening a fresh database connection per request is wasteful, so applications keep a small set of connections open and let many clients take turns.

The reason cuts deep: in PostgreSQL each connection is a full OS process with its own memory, and forking one is not cheap. Under a flood of short-lived connections the server can spend more effort creating and tearing down processes than doing useful query work.

The fix is a connection pooler such as PgBouncer sitting in front of the database. It holds a fixed set of real backend connections open and lends one to a client for the duration of a query (or a transaction), then returns it — drastically cutting process churn and memory use.

The trade-off is the pooling mode. Transaction and statement pooling reuse one backend across different clients, which breaks anything relying on session state — prepared statements, temporary tables, session variables, advisory locks. Session pooling preserves that state but pools less aggressively. You choose the mode that matches what your application actually relies on.

Ask yourself: Why is it true that opening a fresh database connection per request is wasteful, so applications keep a small set of connections open and let many clients take turns?

Recall check (try before reading the answer):

1. WITH — _____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ Answer: According to the PostgreSQL documentation, WITH provides a way to write auxiliary statements, known as Common Table Expressions (CTEs), which define temporary tables that exist only for one query; each auxiliary statement can be a SELECT, INSERT, UPDATE, DELETE, or MERGE, and the WITH clause is attached to a primary statement of the same types. The optional RECURSIVE modifier allows a WITH query to refer to its own output, enabling recursive queries that are evaluated iteratively.

Node.js runs your JavaScript on a single main thread driven by an event loop. That one thread is precious — block it and every in-flight request stalls.

How the loop works

• Each turn drains the microtask queue first (resolved Promise callbacks, process.nextTick), then a batch of macrotasks (completed I/O callbacks, timers).
• Asynchronous I/O (file reads, network, a database round-trip) is handed to libuv, which uses the OS's native async facilities where possible and a small thread pool otherwise. The main thread stays free.

The rule interviewers probe

Single-threaded does not mean no concurrency. The loop juggles thousands of concurrent I/O-bound operations; what it can't do is two CPU-bound computations at once.

// ❌ blocks the loop — every other request waits on this hash
const hash = crypto.pbkdf2Sync(pw, salt, 1_000_000, 64, "sha512");

// ✅ stays async — libuv runs it on the thread pool
const hash = await promisify(crypto.pbkdf2)(pw, salt, 1_000_000, 64, "sha512");

For genuinely CPU-heavy work (large data transforms, local model inference) move it off the loop with worker threads. Node shines for many small, I/O-heavy concurrent operations; it is the wrong tool for long synchronous computation on the request path.

An ORM (object-relational mapper) speeds up the boring 80% of data access — but it hides what the database actually does, and on a non-blocking Node service a single slow query can back up the whole event loop.

Where ORMs bite

• Quietly emitting an accidental cross join (a missing join condition → every row paired with every other row).
• Placing a filter after a join instead of before it, scanning far more rows than necessary.
• The N+1 problem: one query for the list, then one more per row.

-- Hand-written: filter early, one round-trip, index-friendly
SELECT u.id, u.email, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= now() - interval '30 days'
  AND o.status = 'paid';

The interview answer

Most ORMs offer an escape hatch (raw query / query builder) — knowing when to reach for it, and being able to read the resulting plan, is the senior signal.

ACID is the contract a relational database makes so concurrent users never corrupt each other's data.

• Atomicity — all statements in a transaction commit, or none do. The classic example is a funds transfer: debit one account, credit another; if either fails the whole thing rolls back.
• Consistency — every committed transaction respects the database's rules (FOREIGN KEY, CHECK, UNIQUE, NOT NULL), so invalid state can't be written.
• Isolation — concurrent transactions don't see each other's half-finished work; the isolation level tunes how strict this is.
• Durability — once committed, data survives a crash. PostgreSQL flushes the write-ahead log (WAL) to disk before acknowledging the commit, so recovery can replay it.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- atomic + durable; ROLLBACK undoes both

The trade-off to name out loud: strict correctness costs concurrency. Reach for the strongest guarantees on financial work; relax them for analytics. In every case keep transactions short to minimize locking and contention.

Isolation level controls which read anomalies a transaction can observe. There are four, and three anomalies.

* In PostgreSQL, Read Uncommitted behaves like Read Committed — it never actually shows dirty reads. ** PostgreSQL's Repeatable Read (a snapshot) also blocks most phantoms in practice.

The anomalies

• Dirty read — seeing another transaction's uncommitted change.
• Non-repeatable read — the same row read twice returns different values.
• Phantom read — the same WHERE re-run returns new rows.

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... your reads/writes ...
COMMIT;   -- may raise serialization_failure → your app must RETRY

Most OLTP traffic runs at Read Committed or Repeatable Read. Use Serializable for transfers and invariant-critical work — and be ready to retry, because higher levels cost throughput and long transactions can cascade aborts.

PostgreSQL keeps concurrent transactions from clobbering each other with MVCC — multi-version concurrency control.

• Each write creates a new row version rather than overwriting in place.
• A reader sees a consistent snapshot as of its start, so readers never block writers and writers never block readers. That non-blocking design is why Postgres scales reads well.
• Dead (no-longer-visible) versions are reclaimed later by VACUUM.

Optimistic vs pessimistic

• Pessimistic — take locks up front (SELECT … FOR UPDATE). Safe under heavy contention, but serializes work.
• Optimistic — assume no conflict, detect it at commit, retry if needed. PostgreSQL's Serializable Snapshot Isolation (SSI) is optimistic: it watches for dangerous read/write patterns and aborts one transaction with a serialization failure.

-- Pessimistic: lock the row so a concurrent txn waits
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

Trade-offs to state: Read Committed rarely aborts and is fast; Repeatable Read gives a stable snapshot; Serializable prevents all anomalies but adds CPU overhead and may abort under contention. The universal mitigation: keep transactions short to cut lock time and retry loops.

When you write a JOIN you specify what to combine; the query planner decides how. It costs three physical strategies and picks the cheapest from table statistics.

EXPLAIN ANALYZE
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id   -- index on orders.customer_id ⇒ nested loop + index, or hash
WHERE c.region = 'EU';

Why a backend engineer cares: a LATERAL join without a supporting index forces a full scan for every driving row — catastrophic at scale. Reading the chosen plan tells you which index to add and whether to raise work_mem, so your low-level queries stay predictable in production.

Tuning a slow query is a repeatable drill: profile first, then fix the plan.

1. Read the plan

EXPLAIN (ANALYZE, BUFFERS) SELECT …;

Look for:

• Unexpected Seq Scan on a large table → a missing or unusable index.
• High row estimates / a runaway row count → an accidental cross join (missing join condition).
• Batches: N (> 1) or "external merge Disk" → work_mem starvation; the hash/sort spilled to disk.

2. Apply the right fix

-- Index the columns you filter and join on
CREATE INDEX ON orders (customer_id, status);

-- Give a heavy hash/sort room to stay in RAM (per-operation, scope it tight)
SET LOCAL work_mem = '128MB';

3. Cache when you can trade freshness for speed. For analytics or read-heavy hot paths, a cache rebuilt from the source-of-truth table serves results far faster — you accept that the data may be slightly stale. Don't cache where you need strict isolation.

The interview signal is the method — measure with EXPLAIN ANALYZE, identify the bottleneck, change one thing, re-measure — not memorized fixes.

Tie the threads together. A backend interview rewards engineers who reason about trade-offs, not trivia.

Data & correctness

• Pick an isolation level deliberately: Read Committed (default, fast) vs Repeatable Read (stable snapshot) vs Serializable (safest, may abort → retry).
• Know your data structures & algorithms cold — they underpin query and code efficiency.
• Design normalized, scalable schemas; denormalize only with a measured reason.

Systems

• Node's single-threaded loop gives cheap concurrency; offload CPU-bound work to worker threads.
• CAP theorem: under a network partition you choose consistency or availability. Most relational stores choose consistency.
• For cross-service transactions, use the Saga pattern — compensating actions instead of one big rollback, with eventual consistency.

Security — never hard-code secrets; inject via environment variables / a secrets manager (e.g. AWS Secrets Manager). Cover authentication, authorization, encryption, and input validation.

Testing — unit + integration + performance tests, wired into the workflow so regressions surface early.

Collaboration — communicate trade-offs clearly, handle disagreement constructively, adapt as requirements change. That's the difference between a coder and an engineer.

This is a spoken drill round for a backend interview, focusing on storage and concurrency.

The interviewer asks: How do readers avoid blocking writers?

You answer: PostgreSQL uses Multi-Version Concurrency Control. Each write creates a new row version instead of overwriting the old one. A reader sees a consistent snapshot as of when it started. This means readers never block writers, and writers never block readers. That non blocking design boosts throughput for most applications.

The interviewer asks: What does a snapshot see in the middle of a transaction?

You answer: At the Repeatable Read isolation level, the transaction sees a consistent snapshot for its entire duration. The snapshot is taken at the start. Even if other transactions commit new changes, this transaction will not see them. This prevents non repeatable reads. Phantom reads are still possible at that level in theory, but PostgreSQL blocks most phantoms in practice.

The interviewer asks: Where do dead row versions come from and what cleans them?

You answer: When a transaction updates a row, PostgreSQL keeps the old version. That version is no longer visible to any active transaction. These dead versions accumulate over time. The VACUUM process reclaims them later. It cleans up the storage used by those old row versions. This prevents the database from growing without bound.

The interviewer asks: Which isolation level should you pick for a payments style workload and why?

You answer: For financial work, you need the strongest correctness guarantees. Choose Serializable isolation. It prevents all three read anomalies: dirty reads, non repeatable reads, and phantom reads. Serializable is the safest level. However, it is the slowest option and adds CPU overhead. Under heavy contention, it may abort transactions. Your code must catch that serialization failure and retry. Keep transactions short to minimize locking and contention. The trade off is strict correctness costs concurrency. In practice, use the highest level only when invariants matter most, like for transfers.

The interviewer asks, when does an index help and when is it pure write overhead? You answer that search indexes are eventually consistent by design. Full ACID on the index would bottleneck writes. So for a search index, strong guarantees add overhead with no benefit. But for read-heavy queries, an index can speed up lookups. You must measure with explain analyze to know.

The interviewer asks, how do you read the plan for a slow query and compare estimates to actuals? You answer that the method is key. Use explain analyze to measure. Identify the bottleneck. Change one thing. Then re measure. Compare the estimated rows to the actual rows. Big differences mean the planner has bad information.

The interviewer asks, why does ordering with a limit need deterministic tie breaks? You answer that without a tie break, results can change between runs. This can cause non repeatable reads even at read committed. To get stable output, add a unique column to the order by clause. That makes the sort deterministic.

The interviewer asks, what does connection pooling change at scale? You answer that connection pooling reduces the overhead of opening new connections. But it does not change the isolation level or the locking behavior. Keep transactions short. Long transactions under serializable isolation can cause cascading aborts. Connection pooling helps with throughput, not correctness.

Imagine you run a small shop and you keep all your customer orders in one spreadsheet file. One quiet morning, two of your employees open that same file at exactly the same moment, and each one saves their work right on top of the other. Orders vanish, and nobody can tell what really happened. A database is built to stop that kind of mess from ever happening. Think of it as a well organized filing cabinet watched over by a careful librarian. The librarian makes sure that only one person writes to a record at a time, and that every reader always gets the correct, up to date information. The particular librarian we will follow throughout these chapters is called PostgreSQL. It is free to use, and it has quietly run serious applications for decades, so you can trust it. The rest of this guide simply walks through how this careful librarian does its job.

The librarian's main trick is a method called MVCC, short for multi-version concurrency control. The idea is gentle. Instead of letting writers and readers fight over one copy, the librarian keeps several versions of each record on hand. A query that only reads data is handed a steady snapshot, frozen at the moment it began. Because of that, readers never have to wait on writers, and writers never have to wait on readers. The whole shop can stay busy at once without anyone tripping over anyone else.

Different jobs, though, need different amounts of care, so the librarian offers four levels of strictness called isolation levels. The most relaxed level is named Read Committed, and it is the default that most people use. At this level you will never see a change that someone else has not finished saving yet. You might, however, read the same row twice and get two different answers, because a neighbor committed an edit in between. If that bothers you, step up to Repeatable Read. It freezes one snapshot for your entire piece of work, so the rows you already saw stay exactly the same all the way through. Even so, there is a catch. Brand new rows can still slip into a later query, and that surprise has a name, a phantom. For the very strictest guarantee, choose the level named Serializable. It behaves as if every transaction ran one at a time, all alone, with no surprises at all.

That strongest level is not free. Serializable does its checking in a hopeful, optimistic way. It lets your work run, watches quietly for a dangerous clash, and only then cancels one of the transactions if a genuine conflict actually appears. When that happens, your program must catch the error and simply try the whole thing over again from the start. So you pick the level that fits the job at hand. Money transfers want the strict end, everyday reports are happy near the relaxed end, and most ordinary work lives comfortably in the middle. Keep each transaction short, and the librarian will keep your data safe and consistent for everyone.

A database keeps its information in tables, and a table is really just a tidy list of rows. Each row holds one complete record, like everything you know about a single customer or a single order. When the database needs to read or change something, it works its way through these rows, and that act of looking through them is called scanning. There is more than one way to scan. The plain way is to walk through every single row in order, from the very first one all the way down to the very last. A faster way, used when you are hunting for one specific record, is to follow an index that jumps you straight to the matching row. Indexes shine here. An index is wonderful for finding one needle in a giant haystack. But reading the whole haystack is exactly the moment when the plain top to bottom scan quietly wins instead. The database has a planner that quietly weighs these options and picks whichever one should be faster for your request. One more lovely detail is worth knowing early. Whenever a row changes, the database does not erase the old copy on the spot. It keeps it. Instead, it quietly writes a fresh version alongside the old one, so anyone who is already reading still sees a calm and consistent picture. That small habit is the secret that lets many people read and write at the very same time without ever colliding. Tables are the foundation of everything that follows, the simple building block that lets you store, find, and update your information with confidence.

When you want answers from a database, the wonderful part is that you describe what you want, not how to go and get it. You name the table to look in, and you spell out which rows should match. You can say what order to show them in, and how many you would like back. After that, the database itself figures out the fastest path to your data.

For every single request, PostgreSQL quietly builds a query plan. Choosing a plan that fits both the shape of the query and the nature of your data is what makes the difference between fast and slow. A clever planner does this work for you. If you are ever curious, you can run the explain command and watch the plan it chose. Reading those plans is a small art, and it grows easier with practice.

The plan is a tree of steps called nodes. At the very bottom sit the scan nodes, and their job is to hand back raw rows from a table. There are a few kinds. A sequential scan reads the whole table from top to bottom. An index scan follows a shortcut to find matching rows quickly. A bitmap scan blends index results together when that is more efficient. Above those scan nodes, the planner stacks more nodes whenever your query asks for extra work. Joining two tables together is one such step. Adding things up into a total is another. Sorting the results into order is a third. Each of these higher nodes takes the raw rows from below and shapes them into the answer you asked for.

The trade-off is between speed and accuracy. A well chosen plan makes the query run fast. A poor plan can make it slow. The explain command helps you see the choices the planner made. You can then identify bottlenecks and change one thing at a time. After each change you measure again. This process of describing what you want and letting the database find the best path is how you get answers out. You never have to write step by step instructions. You just say what you need. The database does the heavy lifting behind the scenes. That is the core idea: you describe the result, not the method.

The database serves many people at the same time without them tripping over each other by using a technique called multi-version concurrency control, or MVCC.

Here is how it works. Imagine a shared document. When someone wants to read it, they instantly get a photocopy of the page exactly as it was the moment they started. That copy stays the same even if someone else later edits the original. That is precisely what happens inside the database. Every write creates a brand new version of the row. It does not overwrite the old one. So a reader always sees a consistent snapshot from the start of their work. They never see half-finished changes from another writer.

Because each reader has their own stable photocopy, readers never block writers. And writers never block readers. They can all work at the same time without waiting for each other. This non-blocking design greatly boosts how many requests the system can handle.

But there is a natural trade-off. All those old photocopies pile up. They are no longer visible to anyone, but they still take up space. A background cleanup process called vacuum comes along and tidies them away. It reclaims the disk space so the system does not run out of room.

The default level of care is called Read Committed, and it simply gives each statement its own fresh snapshot, which is fast and almost never causes trouble. If you want a firmer guarantee, you can choose Repeatable Read, which holds one steady snapshot for your whole transaction. For the very strictest behavior, Serializable prevents every read anomaly. It pays for that safety with speed, and it may cancel a transaction when it spots a real conflict. Your code then simply tries again.

For most everyday work the default level is plenty. The one habit that helps in every case is to keep each transaction short. A long transaction can cause trouble at any level, and especially under Serializable, where it raises the chance of a retry. Short transactions hold fewer things and finish cleanly.

So the core trick is pleasingly simple. Give every reader a personal snapshot of the data. Let each writer add a brand new version instead of overwriting the old one. And let a quiet background cleanup sweep away the stale copies once nobody needs them. That is how a whole crowd of people can use one database at the very same time without ever stepping on each other's toes.

Every transaction makes you four quiet promises, and together they keep your data safe even when the whole shop is busy. The first promise is atomicity, which means that all the steps inside a transaction either succeed together or none of them happen at all. Picture moving a coin between two jars. You take it out of one jar and drop it into the other. If either half of that move fails, the whole thing rolls back, exactly as if you never touched it. You will never end up with the coin sitting in both jars, or vanished from both.

The second promise is consistency. It simply means every change has to obey the rules you set, so bad or broken data can never sneak into the system. The third promise is isolation, and it stops two people from tripping over each other's half finished work. Without it, one person might glimpse changes that another has not actually saved yet. The strictest setting behaves as though everyone took their turn one at a time. It catches any clash and politely asks your code to try again. Looser settings run faster, but they allow a few small surprises.

The fourth promise is durability, which means that once your data is saved, it survives even a sudden crash. To pull that off, the database keeps a running diary of every change and writes each entry safely to disk before it ever reports success. If the power dies, it simply reads the diary back and replays it. The honest trade is between strict safety and raw speed. Money work wants the strictest end, while plain reports are happy with something looser. In every single case, the best habit is to keep each transaction short. That one habit keeps everyone's waiting down to a minimum.

The database creates a plan for every query. This plan is a tree of steps called nodes. At the bottom are scan nodes. Scan nodes return raw rows from a table. There are several types of scan nodes. A sequential scan reads every row one after another. An index scan uses an index to find rows faster. The index is a structure that helps the database locate rows quickly. The planner chooses the best plan. It considers the query structure and data properties. Estimated costs guide the choice. These costs come from statistics gathered by a maintenance operation. The statistics are random samples, so they are not exact. The planner aims for good performance. You can see the plan with a special command. The output shows the tree of nodes and cost estimates. For example, an index scan often has a lower cost than a sequential scan. This is because the index provides a direct path to the rows. The database includes a complex planner to make these choices. Understanding these basics helps you reason about database performance. Good statistics require that maintenance operation called vacuum analyze. The examples in documentation use a database after that step. The plan output can be in text format or a format for programs. The tree structure has different node types for scans, joins, and sorts. Each node has a cost. The planner tries to minimize the total cost. That is how the database makes lookups fast. Without an index, a sequential scan would read every row. That is slow for large tables. An index scan is much faster for specific lookups. The index itself needs to be kept updated. But the source does not explain the trade-off of slower writes. So we stick to what is known. The planner considers these access methods. It chooses the one with the lowest estimated cost. This choice is critical for performance. The database uses this process for every query. That is the core idea behind indexes. They enable faster lookups by using a scan node that points directly to the needed rows.

When you run a query, the database decides how to execute it. This decision is a plan. You can ask the database to show you its chosen plan. The way to do this is with the explain analyze command.

Explain analyze actually runs your query and then shows you every step the database took to find your data, along with the time each step spent. The step that ate the most time is your bottleneck, and that is exactly where you should aim your attention. So the recipe is gentle and repeatable. Find a slow query, run explain analyze on it, and look for that one heaviest step. Then change a single thing, perhaps adding an index or rewriting the query a little. Run explain analyze once more to see whether the time actually dropped. If the time fell, you fixed it. If it did not, you simply try a different change next. This patient loop of measure, change, and measure again is the real key to tuning performance.

Now, there are tools that write queries for you. Object relational mappers, or ORMs, can speed up simple reads and writes. But they hide the real query. They might create expensive cross joins without you knowing. That can slow down the database. When performance matters, writing the query yourself gives you control. You can choose the right join type. You can push filters before the join. You can add indexes exactly where needed. This level of control helps you avoid hidden costs. Use an object relational mapper for simple reads and writes. But for critical queries, write the query yourself by hand.

Even when you write the query by hand, the plan the database picks may not be the best one, because it does not truly know everything about your data. A plan can look fine on paper and still run slowly in practice. That is the whole reason you measure instead of guessing. Explain analyze quietly reveals what really happened, so let it, rather than your hunch, have the final word.

While you are tuning, one more old habit pays off. Keep your transactions short, because that reduces contention, and at the strictest Serializable level it also lowers the chance of a retry. Long transactions tend to cause trouble at the higher levels of care. For tuning itself, though, the method never changes. You measure, you change one thing, and you measure again.

PostgreSQL supports the explain analyze command, and you can lean on it to tune any slow query and get a direct look into the database's thinking. Think of the database as a navigation app. It chooses a route before you drive. You can ask it to show you the route it picked. If the route is slow, you can alter your request or add better roads, which are indexes. Then check the route again. This process ensures your queries run efficiently.

When you ask a database to show the top few results, it first sorts all the data completely. Then it takes only the first few. Think of a race: every runner is ranked before the top three get medals. That way the top of the list is the true top.

Now consider ties. Two items may have the same value. Without a tie-breaking rule, the same request could return different pages. The database needs a consistent rule to keep order stable. For example, it can sort by the underlying byte values. That is efficient and stable across versions.

The database can sort words and numbers using more than one set of rules, and these rule sets are called collations. A collation is just the agreement that decides the order of letters and characters. The simplest one, named C, sorts purely by the raw byte values underneath, ignoring any human language. That approach is wonderfully fast, and the very best part is that it behaves the same way forever and never quietly shifts on you.

Many applications, though, want words to sort the way a person naturally expects. For that, the database leans on a language setting chosen when it was first created, which can order letters according to a particular tongue. Your operating system may even offer extra collations on top of that. They feel more human, but they come with a small catch we will see in a moment.

Here is the concrete trade-off. Byte-value sorting is simple and fast. It works the same way every time. But it does not match how people expect words to sort. For example, lowercase and uppercase letters may mix in surprising ways. Natural language sorting feels correct, but it can change when the operating system updates. That may break your pagination.

So when you build a paged list, pick a collation and stick with it. Keep the sorting rule consistent. That way the same query always gives the same top results. Ties are resolved by that fixed rule, not by chance. And remember the ordering happens before you cut the list. The top items are truly the best according to your chosen collation.

Imagine that you are writing a story. Before you copy the final version into a beautiful notebook, you first jot down every change you intend to make in a rough diary. That diary is your safety net. If you spill coffee on the notebook, you can always go back to the diary and rewrite what you lost. A database does exactly the same thing. It keeps a diary called the WAL, short for write-ahead log. Every time you want to save some data, the database writes that change into the diary first. Only after the change is safely in the diary does the database update its main storage files. This simple trick gives the database a superpower: durability. Durability means that once you commit a transaction, that data will survive even if the power goes out.

Let me explain what commit means. When you are finished making a group of changes, you tell the database you are done. That is the commit. The database then confirms success to you. But before it says yes, it flushes the entire diary entry to the disk. Not just to memory, but all the way to the actual spinning disk or solid-state drive. Only after that flush does the database send you the okay. So even if the lights go out a millisecond later, the diary is still there. When the power comes back, the database wakes up, reads the diary, and replays every change that was recorded. It redoes any work that was interrupted. Your data is restored exactly as it was when you committed.

Now, there is a trade-off here. Writing to disk is slower than writing to memory. The diary takes extra time. To speed things up, you might be tempted to turn off full flushing. The database has a setting called synchronous commit. When you set synchronous commit to off, the database says yes to you before the diary is fully on disk. That makes your application feel faster because it does not wait for the disk. But there is a risk. If the crash happens between the yes and the actual disk write, you lose that commit. So you must decide. Do you want maximum safety, even if it means a tiny delay? Or do you want speed and accept the small chance of losing the very last piece of data? Choose synchronous commit on for financial work. Choose off for logs or analytics where a little loss is acceptable.

That is how a single database stays alive after a crash. But what about a dead server that never comes back? The source material does not describe keeping a second copy on another machine, so I cannot go into that detail here. However, the principle of a diary remains central. The write-ahead log is the foundation that makes any recovery possible. Keep your transactions short. A long transaction that holds onto its diary entries can cause problems. Short transactions mean the diary is always small, and recovery is fast. So remember: write the diary first, flush it to disk, then commit. That is how the database never loses your data.

Let's wrap up what we've learned. You started with the idea of a filing cabinet. Every piece of data sits in a drawer. When two people open the same drawer at once, you need rules. Those rules are called isolation levels.

PostgreSQL gives you four choices. Read Committed is the default. It prevents dirty reads. You never see uncommitted changes. But you might see the same row give different answers in one transaction. That is a non repeatable read.

Repeatable Read gives you a stable snapshot. Think of it like a photocopy you hold for the whole transaction. You always see the same data. But new rows can still appear in later queries. That is a phantom read.

Serializable is the strongest level of all, and it behaves as if every transaction ran one at a time, completely alone. It does this in an optimistic way, quietly watching for conflicts and only cancelling a transaction when a real clash appears. When that happens, your code simply catches the error and tries again. This level is the safest you can get, though it is also the slowest, so save it for financial transfers and the moments when correctness matters most of all.

Now let’s talk about the all or nothing jars. That is atomicity. Either every change in a transaction saves, or none do. Durability means once a transaction commits, the data is safe even if the power dies. Consistency means your data always follows the rules you set. Isolation keeps concurrent transactions from interfering.

PostgreSQL uses multi version concurrency control. Each write creates a new version of the row. That is like adding a new page to a book instead of erasing the old one. Readers never block writers. Writers never block readers. This design makes PostgreSQL fast for many read heavy workloads.

You also met the gentle trade offs along the way, where higher levels of care quietly cost you some performance. Read Committed is fast and almost never has to abort. Repeatable Read holds its snapshot a little longer, so it leans on a touch more memory. Serializable does the most work and may cancel a transaction when many people clash at once. The simple habit that helps every level is to keep transactions short, and to move slow outside work, like a network call, well clear of the transaction.

Now that these ideas feel comfortable, you can explore the next chapters on this site. The database internals section explains MVCC and vacuum more deeply. The interview drills help you practice explaining these trade offs. Start with those when you are ready. You already understand the core concepts. The rest is just repetition and practice.

The task difficulty rating problem — a common one in Codility hiring assessments. Given tasks (id, name) and reports (id, task_id, candidate, score) with scores 0..100, label every task that has at least one solution:

Output task_id, task_name, difficulty ordered by increasing task_id; tasks with no solutions don't appear at all. Multiple submissions from the same candidate all count.

This walkthrough takes the canonical solution apart one syntax element at a time — the full worked exercise (with a traced dataset) lives on the Exercises tab.

CREATE TABLE tasks (
    id   INTEGER PRIMARY KEY,  -- unique task id
    name VARCHAR(30) NOT NULL  -- human-readable task name
);

CREATE TABLE reports (
    id        INTEGER PRIMARY KEY,           -- unique report (submission) id
    task_id   INTEGER REFERENCES tasks (id), -- foreign key → tasks
    candidate VARCHAR(30) NOT NULL,          -- same candidate may repeat
    score     INTEGER NOT NULL               -- 0..100 per submission
);

• INTEGER / VARCHAR(30) — column types; VARCHAR(30) is variable-length text capped at 30 characters.
• PRIMARY KEY — unique, non-null row identifier (implies an index).
• NOT NULL — forbids missing values.
• REFERENCES tasks (id) — a foreign key: every reports.task_id must exist in tasks.id. PostgreSQL does not auto-index the referencing column — that matters later.

SELECT
    t.id   AS task_id,    -- the task's primary key → first output column
    t.name AS task_name,  -- human-readable name   → second output column
    ...
FROM tasks t

• SELECT — the output column list, one expression per comma.
• t.id — a qualified column reference: t is the table alias declared in FROM tasks t. Qualification removes ambiguity when joined tables share column names.
• AS task_id — an output alias: renames the expression in the result set. AS is optional syntax, but the graded column names (task_id, task_name, difficulty) are exact — spell them.

CASE
    WHEN AVG(r.score) <= 20 THEN 'Hard'    -- exactly 20 is Hard (<=, not <)
    WHEN AVG(r.score) <= 60 THEN 'Medium'  -- only reached when avg > 20
    ELSE 'Easy'                            -- everything above 60
END AS difficulty

• CASE … END — SQL's conditional expression (an expression, not a statement: it yields one value per row/group).
• WHEN … THEN … — arms evaluated top to bottom, first match wins; ascending thresholds keep each arm one-sided (no BETWEEN 21 AND 60 needed).
• ELSE — the fall-through arm.
• Boundary gotcha: exactly 20 → Hard, exactly 60 → Medium. Writing < 20 / < 60 shifts both boundaries and fails the hidden edge-case tests.

• AVG(r.score) — an aggregate function: computed once per group (per task, thanks to GROUP BY), not once per row. Aggregates silently ignore NULLs (none here — score is NOT NULL).
• Integer-division gotcha: some engines return an integer for AVG(integer) — SQL Server truncates 20.5 → 20, misfiling the task as Hard. Portable fix:

WHEN AVG(CAST(r.score AS DECIMAL)) <= 20 THEN 'Hard'

• CAST(x AS type) — explicit type conversion. PostgreSQL is safe without it (AVG(integer) returns numeric), but know which engine the assessment runs.

FROM tasks t
JOIN reports r
  ON r.task_id = t.id   -- match every submission to its task

• FROM tasks t — the driving table, with t as its alias.
• JOIN … ON … — bare JOIN = INNER JOIN; ON gives the match predicate. Only matching pairs survive, so a task with zero reports contributes zero rows — "tasks with no solutions shouldn't appear" is handled by the join shape alone. No HAVING COUNT(*) > 0 needed.
• LEFT JOIN gotcha: a LEFT JOIN keeps report-less tasks with AVG(r.score) = NULL. NULL <= 20 and NULL <= 60 are both unknown (not true), so those rows fall through to ELSE and surface as bogus 'Easy' tasks — violating the requirement twice over.

GROUP BY t.id, t.name
ORDER BY t.id;

• GROUP BY — collapses the joined rows to one row per task; every report row feeds AVG() (repeat candidates deliberately not deduplicated — the statement counts all submissions).
• t.name rides along because it appears un-aggregated in SELECT. In PostgreSQL GROUP BY t.id alone is legal — t.name is functionally dependent on the primary key — but spelling both out is the portable habit (MySQL without ONLY_FULL_GROUP_BY silently picks arbitrary values for sloppier forms).
• ORDER BY t.id — the required ordering; ORDER BY is the only clause that can see SELECT aliases, because it runs last.

PostgreSQL's logical evaluation order (not the reading order):

1. FROM + JOIN — build the working set (report-less tasks already gone).
2. WHERE — filters individual rows, before grouping → WHERE AVG(r.score) <= 20 is illegal: no groups exist yet.
3. GROUP BY — collapse to one group per (t.id, t.name).
4. Aggregates — AVG(r.score) once per group.
5. SELECT / CASE — output expressions evaluated; aliases born here.
6. ORDER BY — sorts last; the only clause that can see the aliases.

Consequences: a filter on the average belongs in HAVING (after step 4), and you can't reference difficulty inside another expression at the same query level — compute it one level down if you need to reuse it (next chapter).

WITH task_avg AS (
    SELECT t.id, t.name, AVG(r.score) AS avg_score
    FROM tasks t
    JOIN reports r ON r.task_id = t.id
    GROUP BY t.id, t.name
)
SELECT
    id   AS task_id,
    name AS task_name,
    CASE
        WHEN avg_score <= 20 THEN 'Hard'
        WHEN avg_score <= 60 THEN 'Medium'
        ELSE 'Easy'
    END AS difficulty
FROM task_avg
ORDER BY task_id;

• WITH … AS (…) — a CTE (common table expression): a named subquery the outer query treats as a table. The outer level runs at its own step 5, so it may freely reference avg_score — the alias-visibility problem dissolves. Same plan, better readability.
• Why not a window function? AVG(score) OVER (PARTITION BY task_id) computes the same average but keeps every report row — you'd need DISTINCT to collapse them back. Window functions answer "each row alongside its group's aggregate"; this problem wants "one row per group" — exactly what GROUP BY is for.

The follow-up — "and at 100M reports?":

• EXPLAIN shows the expected plan: seq scan of reports → hash join against tasks → HashAggregate for GROUP BY → sort for ORDER BY. With no WHERE, every report is read regardless — indexes can't cut the row count.
• reports(task_id) — barely helps this query, but it's the index you want anyway: PostgreSQL gives the foreign key no index automatically, and without it every DELETE on tasks scans reports.
• reports(task_id, score) — a covering index: join + aggregate can be satisfied by an index-only scan. The detail that turns a pass into a strong pass.
• Hot path? Precompute: a materialized view over this exact query, refreshed on a schedule.