Recent content on EXPLAIN ANALYZE
TL;DR A data-technology migration is five migrations stacked on top of each other: the query surface, the coupling around it, the data itself, the operational model, and the long tail. Teams that estimate the first one and skip the other four end up running both systems in production, indefinitely. This is a cost model rather than an argument against moving; a reason that survives all five layers…
TL;DR The 2025 DORA report found AI raises delivery throughput and hurts delivery stability at the same time, and its AI Capabilities Model names seven things that decide which way that goes for a team. Read the seven from the database layer and four of them are schema-quality work wearing DevOps names. The report never says “database,” but that is where a lot of its stability gap gets…
TL;DR A MySQL schema that lived through the 5.7-to-8.0 upgrade carries two eras of collation: old VARCHAR columns on utf8mb4_general_ci , newer ones on utf8mb4_0900_ai_ci . Join across that seam and MySQL either refuses outright or silently wraps one column in a per-row charset conversion that takes its index out of play. Converging fixes it, but the same conversion changes which rows count as…
TL;DR A join across two schemas on the same MySQL server costs nothing to write, which is exactly why nobody counts it. Every one of those joins is a hard coupling that pins both schemas to one instance, so the day you need to split for scale, the joins are the wall. And the referential integrity you assumed the boundary carried was never enforced to begin with. Somewhere in the codebase, since…
TL;DR If you run Consul , or any mesh with real health checks, you already own the primitives for narrow database self-healing: a check on a pressure signal, a watch that fires a handler, a handler that runs one vetted remediation and files evidence. That covers a class of pages the current pitch reserves for autonomous ops agents. The line that keeps it safe is narrow: grow a buffer, purge…
TL;DR Backups, replicas, and point-in-time recovery are all built for loss: a failure you notice roughly when it happens. Corruption is the opposite. It’s copied by replication in milliseconds, faithfully included in every backup, and usually found weeks later, past every retention window you sized for cost. Surviving it takes provenance, knowing which writes to remove, not a restore to a…
TL;DR Staying sharp in the LLM era isn’t nostalgia and it isn’t prompt-craft. The “agentic loop / context engineering” genre is largely a marketing story told by the parties who profit from your subscription and your dependence. The move that holds up is to keep sweating hard problems by hand, because skill you didn’t earn through friction doesn’t stick, and…
TL;DR Most production fixes patch the occurrence in front of you (reboot this box, clean these rows, bump this instance), which clears today and leaves the class of problem intact to recur. The fix that holds lives one level up, where one change covers every future occurrence. The skill is telling a recurring class from a true one-off before you’ve been burned three times. A development…
TL;DR The catastrophic AI failure (the agent that DROPs a table) is the recoverable one: loud, attributable, and on a gated pipeline it mostly can’t happen anyway. What actually bleeds you is the change that clears every gate because the gates check correctness at review time, and the failure is a function of volume and time, both of which are zero when the PR is open. Plot failures on…
TL;DR The agents that hallucinate least are the ones calling narrow, opinionated tools and given the smallest possible job. A “diagnostic” endpoint that returns a present-state boolean plus a lag number beats a generic query(sql) surface, and a scoped agent with three tools beats a wide agent with twenty. Even with both halves in place, the output still has inconsistencies on some…
TL;DR Two alarms ride the same dashboard tile: the disk filling up, and the disk slowing down. Both have query-level and schema-level fixes that hold for years. Capacity is partition-and-archive, not DELETE . IOPS is covering indexes and the access patterns that go with them. The cloud’s autoscaling and burst credits mask both, and the bill is where the symptom finally surfaces. On-call gets…
TL;DR A database alerting on memory pressure is almost never a workload that needs more RAM. The dangerous allocation is per-sort, per-hash, per-connection and transient, so the instance-level memory graph never shows it until data size and concurrency line up at the same instant. The exception is a genuinely large working set with a low cache hit ratio, and that one is real. A reporting endpoint…
TL;DR A relational database pinned at 100% CPU is almost never running one expensive query. It’s running a cheap one too many times. The slow-query log and mean-time sorting both look right past it; total_exec_time is the only view that finds it. A client portal has a status dropdown at the top of the orders page. It shows a count next to “Open”: SELECT COUNT(*) FROM orders WHERE…
TL;DR The judgment that lets an engineer override a model is built in the slow work the model now offers to do for them. Accept enough of that help on the work that would have built the judgment, and the agent’s speed arrives without the quality, security, scalability, maintainability, or operational sense that the slow work used to deposit alongside the code. Three months after shipping,…
TL;DR Database troubleshooting is a learnable skill with a repeatable sequence: observe what’s happening now, categorize the wait, narrow to the specific cause, then act. The sequence matters more than the tools. This article walks through it for engineers who don’t do this often. Your APM lights up. Endpoint latency on /api/checkout has tripled in the last three minutes. The graph…
TL;DR Infrastructure alerts on a relational database almost always trace to query and schema choices, not capacity. Scaling the box rents the bug. The exceptions are real but smaller than most teams assume. What unites replication lag, CPU at 100%, dashboard timeouts, disk filling, and the server that crashes every Tuesday afternoon? Almost always the same thing: bad queries. Throwing metal at it…
TL;DR MCP is a wire protocol; what sits behind it decides the blast radius. In non-prod, pointing it at the database tends to be fine, because unbounded exploration is worth more than the occasional mistake. In prod, the shape that holds up is having the MCP server’s tools call an agent-specific API that enforces allowlisted operations, row caps, column masking, and per-prompt audit, rather…
TL;DR AI coding agents do hit 10x or better on a specific slice of work: greenfield internal tools where the agent authors the code and the conventions it later re-reads. Outside that envelope (existing services, cross-team consumers, regulated code paths) the gain compresses to 10–30% at best and turns net negative on mature codebases, because verification cost dominates typing cost. A DBRE has…
TL;DR Pulling six months of evidence from Jira, GitHub, Slack, and 1:1 notes is mechanical work an agent can do in an hour. Making the judgment calls (rating, promotion, raise, PIP, fire) stays with the manager and is the part that should consume the saved hours. Done with verification (every claim traced to source, no decision delegated) the bookkeeping that used to take a week takes a day, and…
TL;DR Adding RAG to your existing Postgres usually 5x’s the storage on the affected tables, drives the HNSW index off a memory cliff that doesn’t degrade gracefully, and pollutes the buffer cache hard enough that unrelated OLTP queries regress at p95. Halfvec, binary quantization with rerank, and a separate replica recover most of it, after the bill has already arrived. A pgvector user…
TL;DR Autonomous agents are the wrong abstraction for alert triage. A scripted playbook of RE-curated queries plus one LLM call to summarize the structured output gives the responder a triage hint with the raw data attached. The summary saves time on the easy pages; the raw data carries them through the cases the LLM gets wrong. 3:14am page. p99 latency on /api/orders checkout past the 1500ms SLO…
TL;DR A prompt instruction biases the next-token distribution. It cannot bound it. Real guardrails for agents holding production credentials sit below the prompt, in layers the model cannot read or override: scoped identities, vetted tool surfaces, harness hooks, wire-level statement filtering, provenance-tagged logs, behavioral monitoring. The agent’s instructions said staging only,…
TL;DR AI can propose and ship index changes against a database where the SME has built two things: a context system (comments on indexes, recorded history, workload evidence surfaced into the prompt) and a guardrails layer (performance regression tests, catalog-redundancy checks, drop-safety rules, post-deploy monitors) that catches the corruption-floor errors any LLM produces. Without both, the…
TL;DR Frontier LLMs corrupt at least 25% of delegated multi-step document work in lab conditions. The rate rises with document size and turn count, and tool use doesn’t help. Corruption is a property of the architecture, not a defect to be patched, and the only thing that closes the gap is a best-in-class domain expert at every checkpoint. Microsoft Research has a number on it. Laban,…
TL;DR For teams whose week is shaped by inbound work, the sprint should hold only what is being worked on now plus what gets pulled next. No forward estimates, no velocity commitments. Priority lives in labels; the team pulls from the labeled backlog as in-flight work completes. Tuesday at 2pm. Sprint planning. The team has been here for ninety minutes. Eighteen tickets on the board, points being…
TL;DR Forward estimation breaks for any team whose week is shaped by work that arrives. The discipline that scales is to point after the fact: when the ticket closes, the person who did the work writes down what it took. Over a quarter the team has real data about where time actually goes. Standup Monday. A 5-point ticket lands in the engineer’s column. Wednesday the engineer is still on it,…
TL;DR For teams whose week is operational by nature (SRE, DevOps, platform, database and storage reliability, anyone whose sprint work coexists with a steady stream of alerts and partner-team asks), a weekly responder rotation breaks silos only when the operational rules force the responder to actually do the work, not route it. Five rules carry the load: a fixed resort order (runbooks → docs →…
TL;DR Partition by the primary key, not by created_at , and let a background service manage boundaries based on observed growth. Queries keep using the keys they already have, partition pruning works automatically, and the partition column never leaks into application code. The same “service watches and adjusts” pattern applies to hash and list partitioning with different operations.…
TL;DR Teams ship more when each IC declares one focused goal in writing every morning and the team holds a brief sync at roughly 3pm to surface what isn’t going to plan and offer help on it. The morning declaration creates focus and commitment. The mid-day sync catches problems while there is still a work day left to fix them, with help that’s actionable now rather than tomorrow. It is…
TL;DR What most teams call “database tests” are application tests with a database underneath. They cover whether the code reads and writes correctly, not whether the database does what its catalog claims. Real database testing covers five distinct categories, each requiring a different tool, and each invisible to the others. The CI suite has 600 tests. Every one is green. Judged on…
TL;DR A team’s output and motivation come from designing the work as a process: each person in a role that uses them well, an owned outcome attached to it, a cadence chosen deliberately. Every team’s process is custom; the operating principle is universal. Break the work into chunks the team can consume, run them on a schedule the team can hold, and the team ships like a clock. Six…
TL;DR AI removed the implicit human review that used to catch database bugs. The engineer writing the migration was the test, and that test was never written down or runnable in CI. Once an LLM writes any portion of your data layer, the test suite is the only line between hallucinated SQL and a production incident. A senior engineer asks an assistant to write a query returning each user’s…
TL;DR LLMs generate SQL from the catalog (column names, types, whatever constraints have been declared), and the silent-failure rate is set by the gap between what the schema describes and what the database actually contains. Close the gap with declared FKs, column comments, named constraints, and enforced conventions: the same work that makes the schema describe itself to any reader. An analyst…
TL;DR A CTO declares “full agentic” off a vendor demo. Without an SME watching the rollout, corruption ships and surfaces a year later when a customer reports a wrong number. With an SME, the work is information infrastructure first (so agents have enough context to make high-probability decisions) and guardrails for the cases where context isn’t enough. A CTO sits through a…
TL;DR Cloud bills creep up because nobody owns bringing them down. The largest leaks are S3 versioning without a lifecycle policy, backup retention set when the database was a fraction of its current size, cross-AZ traffic on chatty services, lower environments running 24/7 at production sizes, and old workloads on instance generations the cloud now surcharges. An annual one-day audit by one…
TL;DR A TEXT or JSON column moves the schema out of the database catalog and into application code; the data inside has a shape, but the DDL won’t tell you what it is. Promote the fields that actually get queried into real columns, and treat the rest as genuinely opaque. An AI assistant is asked to “find customers who upgraded to enterprise in the last quarter.” It reads the…
TL;DR A schema describes the shape the database enforces; the data inside follows a second set of conventions (soft-delete coverage, sentinel values, encoding quirks, format drift) that live nowhere the catalog can show. Queries written from the DDL alone run clean and return results that look right and mean something different. Treat the data as a second source that has to be read, sampled, and…
TL;DR UUIDv4 primary keys are globally unique and coordination-free, and the cost is paid every time you write a row: random B-tree positions, page splits, secondary indexes bloated with 16- or 36-byte key copies, and a working set that stops fitting in the buffer pool once the table is large enough. UUIDv7 fixes the insert-locality problem (time-ordered, sortable) without changing storage size;…
TL;DR A wide table looks cheap because every column was added for a real reason; the expensive part is that rows grow, every write amplifies, and every secondary index inherits the bloat. The fix is splitting by access pattern (columns read together stay together, rarely-touched columns move out), not aggressive normalization that trades one wide table for six-way joins on every read. The schema…
TL;DR An index-only scan is the fastest way a relational database can answer a query: the engine reads the index and never touches the table. Adding a single column to the SELECT list that isn’t in the index silently breaks the optimization, and the query that ran in a millisecond now takes seconds. The SELECT list is part of the query’s performance contract with the index.…
TL;DR A legacy schema looks like a design and reads like a sediment: layers of decisions from different eras, where names that once described the data no longer do and conventions that look uniform aren’t. Renaming is prohibitively expensive once every caller depends on the current names. The workable fix is documenting the drift so the next reader (human or LLM) can navigate what’s…
TL;DR A predicate is SARGable (Search ARGument able) if the database can use an index to evaluate it. Wrapping a column in a function makes the predicate non-SARGable: the engine has to compute the function on every row before it can filter, which means a full table scan no matter what indexes exist. The fix isn’t always to rewrite the predicate (sometimes the column’s type or…
TL;DR A bare id primary key on every table makes a.id = b.id valid SQL between any two tables, which means neither a human reviewing the query nor an LLM generating one can tell which of those equalities are meaningful. Name primary keys after the table they identify, and the schema describes its own relationships. Here’s a query an AI assistant generated against a real production schema: 1…
TL;DR A polymorphic reference is resource_id plus resource_type where the type string chooses which table the ID points to. ORMs make it a one-liner; the database enforces nothing. Reads need conditional joins, orphans accumulate silently, and for most uses (comments, notifications, attachments) per-target tables or mutually-exclusive FKs are the better trade. What the pattern looks like 1 2 3 4 5…
TL;DR An ORM is a coupling between schema shape and code shape, not an abstraction over it. The coupling pays off in year one and compounds against you in year five. For long-lived OLTP systems, a thinner layer over raw SQL (sqlc, jOOQ, typed query builders) ages better. There’s a period early in a project where an ORM feels like pure upside. You define a model, the framework generates a…
TL;DR Schema conventions only survive when automation enforces them. A rule a linter, ORM, migration runner, or IaC module checks will hold for years; a rule the team merely agreed to won’t outlast the people who agreed. Pick the conventions your automation needs and skip the purely subjective ones, because they’ll drift regardless of how strongly anyone feels. A new engineer adds a…
TL;DR Keep the database narrow: NOT NULL , UNIQUE , FK within a service, simple CHECK for per-row invariants, generated columns for stable derived values. Put everything else (orchestration, computation, rules that change weekly, anything crossing services) in an application-layer library every writer uses. “Dumb database” is half right: dumb across service boundaries, narrowly smart…
TL;DR Part 1 covered the patterns. This post is the operational half: reading the deadlock log to identify which pattern fired, designing retries that fail loudly instead of hiding the real bug, isolating hot rows before they become incidents, and the prevention primitives ( NOWAIT , SKIP LOCKED , isolation-level changes, lock_timeout ) that remove entire categories from the workload. A nightly…
TL;DR A deadlock is two transactions each holding a lock the other needs, caught in a cycle the engine breaks by killing one. The patterns are finite and repeatable: inconsistent lock ordering across workers, InnoDB gap locks under REPEATABLE READ , foreign-key shared locks on hot parent rows, unique-index conflicts, index-scan lock amplification, and parallelism patterns that only surface on…
TL;DR NULL is the absence of a value, and SQL evaluates expressions involving it under three-valued logic (TRUE / FALSE / UNKNOWN). Most operators return UNKNOWN when one of their operands is NULL, so rows with NULLs silently drop out of != , IN , and NOT IN filters and behave inconsistently across JOIN , GROUP BY , DISTINCT , and aggregate functions. The rules are consistent if you know them, and…