Databases curriculum

Only the server touches the files, which is what allows it to enforce locking, visibility and durability across every client at once. It also means client-side tricks cannot help: a library cannot make a query faster by reaching around the server, and two applications editing the same files directly would corrupt them.

A backend process per connection means each one costs memory and a scheduling slot, not merely a socket. That is why an application opening hundreds of idle connections degrades a server that is doing almost no work — and why the remedy is a pool rather than a larger connection limit.

One pool is shared by the whole cluster; the sort and hash allowance is charged per operation, per backend. Raising the second by a comfortable-looking amount multiplies by concurrency and by operations within a plan, which is the usual route to an out-of-memory event on a configuration change that looked small.

Many small indexed transactions and few enormous scans want opposite physical designs — narrow indexed access against wide sequential reads, row storage against column storage. A single schema serving both usually serves neither, which is why reporting gets moved off the transactional system.

Reading one narrow row still fetches a whole page, so the cost of a lookup is measured in pages rather than bytes. That is why packing more rows per page — narrow columns, fewer dead rows — makes scans faster, and why a wide row that spans pages costs more than its size suggests.

Physical order is a side effect of insert and update history and can change under you, so any query needing a particular order must say so. It also means a plan that appeared to return rows in insertion order was relying on an accident that an update or a vacuum can end.

Every row version carries a fixed header of transaction bookkeeping, which on a narrow two-column table can outweigh the data itself. That is the arithmetic behind a table being several times larger than the sum of its values, and it is why very narrow tables are relatively expensive per row.

Anything exceeding what a page can hold is compressed and relocated, then fetched only when the column is actually named. That is why selecting every column from a table holding a big text field is dramatically slower than selecting the handful you need — the difference is entirely in what gets read off disk.

Storing rows independently treats every index alike; storing them inside the primary-key structure makes that one index free to follow and every other one indirect. Neither is better in general, and the difference explains why the same schema performs differently on two engines.

In a clustered design a secondary index holds the primary key rather than a physical location, so a lookup that needs more columns descends a second tree. That is why a wide primary key makes every secondary index larger, and why covering the query in the index matters more here.

A random key scatters inserts across the whole structure, touching a different page each time, while a time-ordered key appends into a page already in memory. The difference shows up as write throughput and cache behavior, and it is the reason ordered identifier schemes exist.

Writes land in memory plus a log, are flushed as immutable sorted files, and are merged in the background. Nothing is updated in place, which makes writes fast and sequential; the cost moves to reads, which may consult several files, and to the compaction that keeps that number down.

Every engine trades three quantities against each other, and a design cannot minimize all of them. Naming which one you are buying is what makes a storage choice honest: the write-optimized design pays in read work and background compaction, and the read-optimized one pays on every write.

Because the structure stays balanced, reaching any row among millions costs a handful of page reads regardless of where it is. The rebalancing is what makes that guarantee hold, and it is also the per-write cost — every index on a table is work added to every insert, update and delete.

Sorted leaves mean one structure answers equality, ranges, prefix matches, extremes and ordering, which is why it is the default and why it is usually the right answer. Anything an alternative structure does better, it does at the cost of most of that list.

A single probe for exact match, and no help whatsoever for ranges, ordering or prefixes. The narrowness is the point — it is smaller and faster for the one case — and it is why the general-purpose structure is chosen unless that one case dominates overwhelmingly.

Storing one entry per contained element rather than per row is what makes arrays, document keys and full-text search indexable at all. Updates are correspondingly expensive, since one changed row can touch many entries, which is why bulk loading before indexing is the usual advice.

Keeping a summary per range of blocks makes the index tiny — kilobytes where a tree would be gigabytes — and it works only when the values are physically correlated with position. On unsorted data it eliminates nothing and every scan reads everything, so it is excellent or useless with little in between.

A multi-column index is sorted by the first column, then the second within it, so a predicate that skips the leading column has nothing to navigate by. That is why column order in a composite index is a design decision, and why a query filtering only on the second column gets no benefit at all.

When every column the query needs is in the index, the table is never visited, which removes an entire class of random reads. Adding a column purely to achieve that is a real technique, and it costs index size and write work — worth it for a hot query, wasteful as a habit.

The optimization still consults the table for pages that cleanup has not marked as fully visible, so on a heavily updated table it quietly degrades toward an ordinary index scan. That is why the same query is fast on a static table and disappointing on a churning one.

Indexing only the rows matching a predicate keeps the structure small enough to stay cached, which is dramatic when the interesting subset is a few percent of a large table. It also only helps queries whose own predicate implies the index's, which is the constraint people forget.

Wrapping a column in a function or casting it means the stored values no longer match what the predicate is comparing, so an ordinary index cannot be used. Either index the expression itself or rewrite the predicate to leave the column bare — the second is usually cheaper and clearer.

The same statement can be executed differently tomorrow because the data, the statistics or the configuration moved. That is the feature — the plan adapts as the table grows — and it is why a query that was fast for a year can change character overnight with no deployment.

The number is calibrated against one sequential page read rather than to time, so it means nothing on its own and everything in comparison with an alternative plan. Reading it as milliseconds is the common misinterpretation, and it leads to tuning the number instead of the query.

Past a small fraction of the table, reading everything in physical order beats scattered lookups, because sequential reads are far cheaper per page. A plan choosing a full scan is therefore often correct, and forcing an index there makes the query slower while looking more sophisticated.

It is excellent when the outer side is tiny and the inner side is indexed, and catastrophic when the outer row count was underestimated — the inner lookup runs once per row. Most dramatic query regressions are this join running with an outer estimate that turned out to be wrong by orders of magnitude.

Building a hash table on the smaller input handles equality only, and it spills to disk once the input exceeds the working memory allowance. That spill is a cliff rather than a slope, which is why a join that was fast becomes slow when the data grows past a threshold nothing announces.

It requires both inputs in sorted order, which is nearly free when an index already provides it and expensive when it means sorting. That is why it appears on queries joining on indexed keys and disappears when the plan would have to sort a large input first.

Every access path and join order rests on a guess about how many rows will match, so a bad plan is usually a bad estimate rather than a bad planner. That reframes tuning: the productive question is which estimate is wrong and why, not which plan to force.

Statistics come from a bounded random sample, so they are approximate by design and simply absent immediately after a bulk load. That is why the first queries after a large import can choose terrible plans, and why a manual collection is part of a loading procedure.

A list of the most common values plus a histogram of the rest is how the fraction of rows matching a predicate is estimated. It works well for a single column and evenly-shaped data, and it degrades exactly where distributions are skewed or values are correlated.

Selectivities are multiplied as though the columns were unrelated, so two correlated predicates produce an estimate far below reality — which then selects a plan sized for a handful of rows. It is the single most common cause of a nested loop running millions of times.

Declaring that one column determines another, or that a group of columns has a known number of distinct combinations, gives the planner information a per-column sample cannot contain. It is the direct remedy for the correlation problem above, and it has to be declared deliberately.

One prints what the planner believes will happen; the other runs the statement and reports what did. Reading the first and reasoning about timings is a common trap — the estimate is exactly the thing most likely to be wrong when a query is slow.

The measuring form performs the statement, including a modification, so running it on an update or a delete changes data. Wrapping it in a transaction you roll back is the standard precaution, and forgetting it is a mistake you make exactly once.

Compare predicted rows against observed rows first, before looking at anything else: where those two diverge tells you more than the shape of the tree does. Finding the deepest node at which they part company usually identifies the single estimate that has to be corrected.

The inner side of a nested loop reports its time and rows per iteration, so a small-looking number multiplied by the loop count is the real cost. Reading those numbers as totals makes the most expensive node in a plan look like the cheapest.

No observer ever sees a partial transaction, including after a crash partway through committing, which is what lets application code treat a multi-statement change as one event. The mechanism is the log: the commit record either exists or it does not, and recovery follows that.

The guarantee is that declared rules hold at commit, and it promises nothing about the data being sensible — an engine cannot know that a balance should be positive unless a constraint says so. That is why this letter is the one that describes your schema rather than the engine's behavior.

Commit returns only after the log record is on stable storage, so commit latency tracks the storage flush rather than anything about the statement. That is why batching work into fewer transactions is a throughput lever, and why storage flush performance dominates a write-heavy system.

A bare statement is its own transaction, so a script that dies halfway leaves everything before that point permanently committed. The absence of an explicit boundary is invisible in the code, which is why partial application of a multi-statement change is such a common surprise.

On one major engine every later statement in the transaction fails until it is rolled back; on another the transaction continues and the failed statement is simply skipped. Code written against the second silently commits partial work when run against the first, which is a genuine portability trap.

Seeing another transaction's uncommitted write means acting on data that may never exist — the writer can still roll back. It is the one anomaly nearly every engine prevents by default, which is why it is more often a textbook reference point than a production concern.

Reading the same row twice within one transaction and getting different values breaks any logic that assumed stability between the two reads. It arises whenever each statement takes its own snapshot, which is the default on most systems, and it is why multi-statement calculations need a stronger level.

Re-running the same predicate and matching rows that were not there before is different from a row changing — the set itself moved. That distinction matters because preventing row-level instability does not prevent this, which is what the higher levels exist to address.

The standard permits dirty reads at this level, and at least one major engine has no such mode and quietly gives the next level up instead. So requesting it is not an error and does not do what the name implies, which is worth knowing before relying on the name.

Each statement sees a fresh snapshot, so two statements in one transaction can legitimately disagree about the data. That is the default almost everywhere, and it is why a read-modify-write expressed as two statements is unsafe without explicit locking.

One snapshot serves the whole transaction, and it is taken at the first statement rather than at the opening command. That timing detail matters: work done between opening the transaction and its first read is not excluded from what the snapshot sees.

The guarantee is that the outcome matches some order of running the transactions one after another, which is stronger than forbidding a list of named phenomena. That is why it catches anomalies the named list misses, and why it is defined by the outcome rather than by mechanisms.

Defining levels by three phenomena leaves room for interpretation, and a loose reading permits behavior the strict one forbids. That is the critique's substance, and it is why modern discussion talks about what an engine actually guarantees rather than which level name it advertises.

Two read-modify-write sequences interleave and the later write overwrites the earlier one, with no error raised anywhere — the database did exactly what it was told twice. It is the anomaly most likely to be in production code right now, because nothing about it looks wrong.

Two transactions read overlapping data, each writes something different, neither conflicts with the other, and together they violate a rule each was individually checking. It is subtle because both transactions were correct in isolation, which is exactly what makes it survive review.

It prevents all three classical anomalies and still permits write skew, which is why preventing the named list is not the same as being serializable. An engine advertising the higher level name while providing snapshot semantics is a real and common situation worth checking.

A truly serializable engine resolves conflicts by aborting one transaction, so a serialization failure is a normal outcome rather than an error condition. Application code must be able to replay the whole transaction safely, which constrains what side effects can happen inside it.

Every reader works from a snapshot, so a long-running report does not stall the writes underneath it — which is the property that makes mixed workloads tolerable. The cost is that old versions must be kept while any snapshot might still need them, which is where the cleanup burden comes from.

Nothing is modified in place: an update inserts a new version and marks the previous one dead. That is why an update costs roughly what an insert does, why it can require a new page, and why a table of frequently-updated rows grows even though the row count is constant.

Each version records which transaction created it and which removed it, and whether you can see it is a comparison against your own snapshot. Visibility is therefore computed per reader rather than stored, which is what allows several transactions to see different versions of the same row simultaneously.

Keeping the current row in place and reconstructing older versions from an undo record moves the cost around: cleanup is cheaper, rollback is more expensive, and long-running readers put pressure on the undo store instead. Same guarantees, different operational failure modes.

Two transactions can correctly disagree about how many rows exist, so there is no single true number to cache. That is why counting is a scan rather than a lookup, and why an approximate figure from the statistics is the right answer for anything that only needs an order of magnitude.

Ordinary cleanup makes space reusable within the file and does not return it to the operating system, so a deletion frees nothing visible. That is why disk usage stays flat after removing many rows, and why the space is nonetheless genuinely available for new ones.

The form that does return space rewrites the table under an exclusive lock and needs room for a second copy, so it is an outage on a large table rather than a maintenance task. Knowing that is what stops it being reached for as the routine answer to bloat.

Cleanup triggers on a fraction of rows changed, so the largest tables need the most absolute churn before anything happens — which is exactly backwards from where the problem is worst. Per-table settings on big busy tables are the standard remedy.

A single session sitting idle inside a transaction, or an unconsumed replication slot, holds a horizon that stops dead rows being removed anywhere in the cluster. The bloat then appears on tables that have nothing to do with the offending session, which makes it hard to attribute.

Dead space is still read and still occupies cache, so a bloated table is slower for every query until it is physically rewritten. Cleanup restores reusability and not density, which is why the fix for severe bloat is a rewrite rather than more frequent cleanup.

Identifiers are finite, so rows must eventually be frozen, and an emergency freeze cannot be canceled once it starts. It arrives on the table that has been avoiding ordinary cleanup, which means it turns up at the worst moment on the busiest table — the situation the age metric exists to warn about.

Many holders of a compatible mode or one holder of a conflicting one — the matrix of which modes conflict is the entire mechanism, and everything about blocking follows from reading it. Most surprises come from a statement taking a stronger mode than expected rather than from the rules themselves.

Taking the write lock at read time is what makes a read-modify-write safe without an optimistic retry, because the row cannot change between the two halves. The cost is that concurrent readers of that row now queue, which turns a scalability question into a correctness one.

Each party holds what another needs, so no amount of waiting resolves it, and the engine breaks the cycle by aborting one transaction after a detection interval. The victim is chosen by the engine, so application code has to expect the abort rather than assume it will be the survivor.

Acquiring resources in the same order everywhere makes a cycle impossible, which prevents the situation rather than recovering from it. Retrying the aborted transaction is the fallback and not the fix — a system relying on retries deadlocks under load exactly when it can least afford to.

The lock queue is ordered, so a waiting schema change sits ahead of every query that arrives after it, and those queries block behind something that is itself blocked. That is why one blocked alteration can stall an entire application while the original blocker looks harmless.

Skipping rows another worker has locked lets many consumers pull disjoint items from one table without all serializing on the first row. It is what makes a database-backed job queue practical, and without it every worker contends for the same head of the queue.

The log record reaches durable storage before the data page does, and that ordering is what makes recovery possible at all — the log always describes at least as much as the data files contain. Every durability mechanism here is a consequence of that one invariant.

Restart replays the log forward from the last checkpoint until the data files are consistent, which is why recovery time depends on how much log there is rather than on database size. It is also why an unclean shutdown is survivable rather than a corruption event.

Flushing dirty pages establishes a point replay can start from, so frequent checkpoints shorten recovery and spread more steady write load. That trade is a deliberate configuration decision: optimizing for smooth running lengthens the worst-case restart.

A page half-written during a power loss cannot be repaired from an ordinary log record, so the entire page is logged the first time it is touched after a checkpoint. It is why log volume spikes right after a checkpoint, and why the setting exists at all on storage that writes whole pages atomically.

Returning before the flush completes removes the storage round trip from commit latency and accepts a bounded window in which committed transactions can be lost on a crash. Nothing else is weakened — the database still recovers consistently — which makes it a reasonable choice for some data and not for money.

One ships byte-level block changes and produces an exact copy tied to the same version; the other decodes row-level changes and can cross versions and select which tables travel. The second is what makes an upgrade or a partial copy possible, at the cost of more work and more constraints.

The primary commits without waiting for anyone, so a failover discards whatever had not yet shipped — transactions that were acknowledged to the client. That window is the recovery point, and it is a number the business should have agreed rather than one discovered during an incident.

Waiting for a standby's acknowledgment puts network latency into every commit and makes a slow or unreachable replica a production outage on the primary. That coupling is the price of losing nothing on failover, and it is why the number of synchronous replicas is a careful choice.

A replica serves data from the past, and under write load there is no ceiling on how far behind it gets. Designing around it means deciding which reads tolerate staleness, and monitoring it means alerting on the delay rather than on whether replication is running.

A read routed to a replica immediately after a write can legitimately miss it, which presents as an item that disappears right after being created. The remedies are pinning the session to the primary for a period, or making the read wait for the replica to catch up.

Promoting a standby is only safe once the old primary is guaranteed unable to accept another write, which is a separate mechanism from the promotion itself. Skipping it is how two nodes end up accepting writes, and the damage is discovered later when the divergence cannot be merged.

Two nodes each believing they are authoritative is the failure every high-availability design exists to prevent, because the resulting divergence has no automatic resolution. Odd-numbered voters exist so a majority is always determinable, which is what stops both halves of a partition claiming it.

A destructive statement replicates in milliseconds, so every copy is destroyed together — replication protects against hardware loss and not against a mistake or an attack. Only a delayed copy or a genuine backup survives the class of failure people most often assume replication covers.

The theorem constrains only what happens while the network is split: either refuse writes or serve data that may be stale. Outside a partition it says nothing, which is why quoting it as a permanent property of a system is a misreading of what it actually claims.

Even with no partition, a distributed store still trades latency against consistency on every request, because agreement costs a round trip. That extension is the more practically useful half — partitions are rare and the latency trade is paid continuously.

Requiring the read and write sets to be large enough to intersect guarantees a read sees the latest write without contacting every replica. The two numbers are a tuning surface: shifting weight toward reads or writes changes which one pays the latency.

Replicas converge only if writes stop, so during continuous traffic two reads can legitimately return different answers and neither is wrong. Designing on it means making the application tolerate that, usually with conflict resolution rules decided in advance rather than discovered.

Preparing then committing makes a write atomic across systems, and a coordinator that dies between the phases leaves participants holding locks with no way to decide. That blocking window is why it is avoided in high-availability designs in favor of patterns that tolerate partial failure.

Three strategies for three shapes of data: an ordered key such as time, a set of discrete values, or a hash when there is no natural boundary and even spread is what matters. The choice determines which queries can skip work, so it follows from the access pattern rather than from the data's shape alone.

A query whose predicate names the partitioning key skips entire partitions without reading them, which is the whole reason to partition. Everything else about it — more objects, more planning, constraints on keys — is cost paid to make that possible.

Leave the key out of the predicate and every partition is touched, with the planning work across all of them added on top — so the result is slower than one undivided table. Dividing a table whose queries never filter on that column therefore costs performance and buys nothing.

Removing an old time partition is a metadata change, while deleting the equivalent rows writes dead versions, bloats the table and creates cleanup work. That difference is usually the strongest practical argument for partitioning a table that ages out data.

Indexes are built per partition, so a uniqueness guarantee can only be enforced within one — which means any unique constraint has to contain the partitioning key. Discovering that after choosing a key is a schema redesign, which is why it belongs in the initial decision.

One splits a table across storage within a single server and a single transaction; the other splits data across servers and gives up both. Only the second adds capacity, and conflating them leads to partitioning a table in the hope of solving a throughput problem it cannot touch.

The key determines which queries can be answered on one node and which must fan out, and it is the hardest decision in the system to reverse — changing it means moving all the data. That is why it is chosen from the access patterns rather than from the data model.

A key whose values are unevenly distributed concentrates traffic on one node, so the cluster's effective capacity becomes that node's capacity however many others exist. The symptom is a cluster at low average utilization with one node saturated, which looks like a hardware fault.

A list packed into one column defeats indexing, constraints, joins and statistics simultaneously, which is four separate capabilities lost for one apparent convenience. Splitting it out is the single highest-value normalization step, and it is the one most often skipped for expedience.

With a composite key, a column depending on only part of it is duplicated for every row sharing that part, so the same fact is stored many times and can disagree with itself. Moving it to a table keyed by just that part removes the duplication and the possibility of disagreement.

A non-key column determined by another non-key column travels with it, so updating the determining value leaves the dependent one stale. Separating them means the derived fact has exactly one home, which is what makes an update a single write rather than a search.

Copying a value buys read speed and obliges every future writer — including ones nobody has written yet — to keep the copy correct. That obligation is the real cost, and it is why the decision needs to be recorded somewhere a future maintainer will find it.

The engine rejects an orphan even under concurrency, which an application-level check cannot do — two concurrent requests can each verify and then each write. That race is rare enough to survive testing and frequent enough to appear in production.

A persistent hash table gives immediate retrieval by key and no way to ask a question the key does not answer. That is the whole trade: the access pattern has to be known before the data is stored, and a new question means a new structure rather than a new query.

Self-describing records queried by nested field move schema enforcement from the engine into the application, which is flexibility and an obligation. The freedom is real during rapid change and the cost is that nothing prevents two documents in one collection from disagreeing about their own shape.

Rows are addressed by a partition key plus clustering columns, so the physical layout is the query pattern — you model the query rather than the entity. That is why adding a new access pattern usually means writing the data again under a different key.

Relationships are stored as real objects rather than reconstructed by matching keys, so following one is a pointer hop and depth stays affordable. The trade is that aggregate and scan-shaped work, which relational engines do very well, becomes comparatively awkward.

Embedding is right when the child is always read with the parent and stays bounded in size; it fails when the collection grows without limit, because the document eventually exceeds what can be read or updated efficiently. Unbounded growth inside a document is the classic modeling error here.

A document column inside a relational table gives the flexible part of the model without operating a second system to back up, fail over and keep consistent. It also keeps transactions and joins available for the parts that are genuinely relational, which is most of them.

The engine already caches pages, so a slow query is usually missing from that cache rather than missing an external cache layer. Adding one in front without understanding why the internal one is missing frequently caches the same data twice and fixes nothing.

Performance falls off sharply at the point where the hot data no longer fits in memory, rather than degrading smoothly, because every miss becomes a storage read. That cliff is why capacity planning here is about the working set rather than about total data size.

The application reads the cache, handles the miss, loads from the database and populates — the database never learns the cache exists. That independence is the appeal, and it puts every consistency question, including invalidation, entirely in application code.

Writing both places synchronously keeps them agreeing at the cost of write latency; acknowledging early and writing later is faster and accepts a window in which the durable copy is behind. The second needs an answer for what happens if the process dies inside that window.

A stale entry is a correctness bug the database cannot see, so nothing will alert on it — the system just serves an old answer confidently. An expiry is not a solution but a bound on how wrong you are prepared to be, which is a decision worth making explicitly.

A popular key expiring sends every concurrent request to the database at the same instant, which can be more load than the cache was saving. Jittering the expiry or letting a single request repopulate while others serve the old value are the two standard remedies.

Past roughly the number of cores, more concurrent backends means more context switching and lock contention and less completed work, so raising the limit to fix a connection error usually makes the system slower. The limit is a safety valve rather than a capacity setting.

A small pool of server connections serving many application clients turns a connection storm into a short queue, which is what keeps the server in its efficient range. The pool size is a capacity decision, and larger is reliably not better.

Reusing a connection per transaction rather than per session multiplexes far better and breaks anything that assumes session continuity — temporary tables, session settings, server-side prepared statements. Applications usually work until one of those is used, which makes the failure sporadic.

A per-statement ceiling is the cheapest protection against one runaway query consuming the system, and it converts an outage into a failed request. Setting it requires knowing the legitimate slow cases, which is itself a useful thing to have measured.

A pooled connection abandoned partway through a transaction holds its locks and its snapshot indefinitely, blocking cleanup for the whole cluster. It is caused by application code that opened a transaction and then did something slow, and only a timeout will end it.

Applying scripts in order exactly once, with the record kept in the database itself, is what makes two environments comparable and a deployment reproducible. The record has to live with the data rather than in the repository, or a restored copy no longer knows what it has.

Adding the new shape, backfilling, writing both, switching reads and finally removing the old one is several deploys rather than one, and each step is independently reversible. Compressing it into a single change is what makes a schema migration an outage.

During a rollout both old and new application code are running, so a rename breaks whichever half has not been updated. Adding the new name, migrating, and dropping the old one later is the same operation spread over deploys so that no moment requires both versions to agree.

One enormous update holds its locks for the whole duration, writes a dead version for every row and blocks cleanup meanwhile. Batching bounds each of those and makes the job resumable, which matters because a long backfill will be interrupted at some point.

Building without blocking writes costs two passes over the table, cannot run inside a transaction, and can leave an invalid index behind if it fails. That cleanup step is the part most often forgotten, and an invalid index is used by nothing while still being maintained on every write.

A portable dump rebuilds indexes on restore, which makes it slow to recover and flexible about versions and destinations. A byte-level copy restores quickly and pins you to the same major version and often the same platform. The recovery time difference is usually what decides it.

A physical copy plus the continuous log stream is what makes recovery to an arbitrary moment possible — without the log the only options are the instants at which backups were taken. Verifying that the log archive is actually arriving is as important as the backup itself.

Recovering to a specific timestamp or marker is the only real answer to a mistaken destructive statement, because the alternative is losing everything since the last backup. It requires having chosen to keep the log stream beforehand, which is the decision that makes the option exist.

The number that matters is how long a restore takes at real data volume, and that is unknown until it has been rehearsed. Most backup failures are discovered during the first genuine attempt — a missing file, an unreadable archive, a procedure nobody has run.

A backup that the compromised host can delete or encrypt is not protection against the attack that most often destroys data. Immutability and separation of credentials are what make the copy survive an intruder who has everything the production system has.

A statement taking a few milliseconds a million times costs more than one taking seconds twice, so ranking by aggregate time finds what is actually consuming the system. Slow-query logs tuned to a threshold miss exactly that case, because no individual execution looks slow.

Per-index scan counts are the evidence for dropping something, and the counters accumulate over the cluster's lifetime rather than recently. That matters: a zero count is strong evidence, and a small non-zero count might be one quarterly report that will fail next time.

A backend is slow because it is waiting on a lock, on storage, or on the client — three situations with entirely different remedies and no way to tell them apart from a duration. That is why the wait breakdown is usually the fastest route from a slow query to a cause.

During an incident many sessions are blocked and only one is the root, so the productive move is to find the transaction at the bottom rather than the loudest complainant. Killing a victim frees nothing, because the root is still holding what everyone needs.

The age of the oldest open transaction predicts the next bloat and cleanup incident, because everything downstream of it is held back by that one session. It is a leading indicator rather than a symptom, which makes it one of the few metrics worth alerting on before anything is wrong.