Skip to content
AI Engineer Guide.

concept · data · intermediate · concept 1 of 17

SQL

0 of 17 complete

Why it matters

The data around an LLM application is relational even when the model is not. A RAG system has documents, versions, chunks, ingestion runs and per-tenant permissions. An agent platform has sessions, tool calls, approvals and cost per run. A vector index answers "which chunks are similar to this query"; SQL answers "which of those chunks is this user allowed to see, which version is current, and what did the run cost". In practice, a production retrieval path usually joins a similarity result back to rows for permissions or version filtering.

SQL also shows up as a feature. Text-to-SQL over a curated warehouse view is one of the few LLM features where correctness is checkable, because the database can execute the answer. That makes the failure modes concrete: a wrong join returns a plausible table, not an error.

Mental model

A query is a specification, not a procedure — like placing a delivery order by describing the destination and the deadline while a dispatcher chooses the route. You never drive the truck. The planner rewrites your query into a tree of operations, prices each alternative using statistics, and picks one.

The analogy breaks in one important way: the dispatcher can be wrong, and you can see the route. Estimates come from sampled statistics, so the same query text can be fast in the morning and slow after a bulk load changes the data distribution. EXPLAIN is the dispatch log — the only reliable way to know why a query is slow.

How it works

Mechanism, step by stepInteractive
  1. 1Logical order differs from written order. FROM and JOIN build the row source, WHERE filters rows, GROUP BY collapses them into groups, HAVING filters groups, window functions compute over the surviving rows, SELECT projects, ORDER BY sorts, and LIMIT truncates. This order is why a WHERE clause cannot reference a SELECT alias, and why LIMIT 10 does not save work when the sort happens first.
  2. 2Joins are set operations with three physical strategies. A nested loop is cheap when the outer side is small and the inner side has an index. A hash join builds a hash table on the smaller input and probes it once per row of the other. A merge join wins when both inputs arrive sorted. You do not choose; estimated cardinality does.
  3. 3Indexes are sorted access paths. A B-tree answers equality and range predicates, and composite indexes obey a leftmost-prefix rule: an index on (tenantid, createdat) helps a query filtering on tenantid alone, not one filtering only on createdat. Wrapping a column in a function — createdat::date = $1 — usually makes the index unusable. Partial indexes cover a stable predicate, and covering indexes (INCLUDE) avoid a heap fetch.
  4. 4Aggregation and windowing answer different questions. GROUP BY changes the grain of the result. A window function keeps every row and computes across a frame: running totals, rank within tenant, the previous event per session. Most loops written in application code are a window function that has not been noticed yet.
  5. 5Common table expressions are optimisation fences only when you say so. In current Postgres a CTE referenced once is normally inlined into the surrounding query; MATERIALIZED forces it to be computed once and reused, which helps when the same expensive aggregate appears several times.
  6. 6Transactions constrain what concurrent readers and writers see. In Postgres the default isolation level is read committed; MySQL/InnoDB defaults to repeatable read. Under either, two concurrent read-modify-write cycles can lose an update unless you take a row lock, use an atomic UPDATE ... SET n = n + 1, or rely on a unique constraint to make one writer fail.

Step 1 of 6

Logical order differs from written order. FROM and JOIN build the row source, WHERE filters rows, GROUP BY collapses them into groups, HAVING filters groups, window functions compute over the surviving rows, SELECT projects, ORDER BY sorts, and LIMIT truncates. This order is why a WHERE clause cannot reference a SELECT alias, and why LIMIT 10 does not save work when the sort happens first.

Step 1/6 · step 1

Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.

What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.

Text version

The page's steps: 1. Logical order differs from written order. FROM and JOIN build the row source, WHERE filte… 2. Joins are set operations with three physical strategies. A nested loop is cheap when the … 3. Indexes are sorted access paths. A B-tree answers equality and range predicates, and comp… 4. Aggregation and windowing answer different questions. GROUP BY changes the grain of the r… 5. Common table expressions are optimisation fences only when you say so. In current Postgre… 6. Transactions constrain what concurrent readers and writers see. In Postgres the default i…

  1. Logical order differs from written order. FROM and JOIN build the row source, WHERE filters rows, GROUP BY collapses them into groups, HAVING filters groups, window functions compute over the surviving rows, SELECT projects, ORDER BY sorts, and LIMIT truncates. This order is why a WHERE clause cannot reference a SELECT alias, and why LIMIT 10 does not save work when the sort happens first.
  2. Joins are set operations with three physical strategies. A nested loop is cheap when the outer side is small and the inner side has an index. A hash join builds a hash table on the smaller input and probes it once per row of the other. A merge join wins when both inputs arrive sorted. You do not choose; estimated cardinality does.
  3. Indexes are sorted access paths. A B-tree answers equality and range predicates, and composite indexes obey a leftmost-prefix rule: an index on (tenant_id, created_at) helps a query filtering on tenant_id alone, not one filtering only on created_at. Wrapping a column in a function — created_at::date = $1 — usually makes the index unusable. Partial indexes cover a stable predicate, and covering indexes (INCLUDE) avoid a heap fetch.
  4. Aggregation and windowing answer different questions. GROUP BY changes the grain of the result. A window function keeps every row and computes across a frame: running totals, rank within tenant, the previous event per session. Most loops written in application code are a window function that has not been noticed yet.
  5. Common table expressions are optimisation fences only when you say so. In current Postgres a CTE referenced once is normally inlined into the surrounding query; MATERIALIZED forces it to be computed once and reused, which helps when the same expensive aggregate appears several times.
  6. Transactions constrain what concurrent readers and writers see. In Postgres the default isolation level is read committed; MySQL/InnoDB defaults to repeatable read. Under either, two concurrent read-modify-write cycles can lose an update unless you take a row lock, use an atomic UPDATE ... SET n = n + 1, or rely on a unique constraint to make one writer fail.
  7. Read the plan, not the query. EXPLAIN ANALYZE shows estimated and actual rows per node. The first node where the two diverge is where the planner's model of your data is wrong.

A worked query

The example below is a small multi-tenant document store: current document versions, their chunk counts, and a rank by size. The index matters more than the query text.

CREATE TABLE documents (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     uuid  NOT NULL,
  title         text  NOT NULL,
  effective_at  date  NOT NULL,
  superseded_at date
);
 
-- Partial index matching the hot predicate, with the equality column first.
CREATE INDEX documents_current_idx
  ON documents (tenant_id, effective_at DESC)
  WHERE superseded_at IS NULL;
 
-- chunks(id, document_id, content) already exists; omitted for brevity.
 
SELECT d.id,
       d.title,
       count(c.id) AS chunks,
       rank() OVER (ORDER BY count(c.id) DESC) AS size_rank
FROM documents d
JOIN chunks c ON c.document_id = d.id
WHERE d.tenant_id = $1
  AND d.superseded_at IS NULL
GROUP BY d.id, d.title
ORDER BY chunks DESC
LIMIT 20;

Two details are worth reading twice. The WHERE clause matches the partial index predicate exactly, so the planner can use the small index instead of the full table. And rank() runs after GROUP BY, so it ranks groups, not raw rows.

The arithmetic of a plan

The planner compares rough costs, not exact runtimes. Two useful shapes:

  • Nested loop: C≈∣outer∣×(log⁡∣inner∣+h)C \approx |outer| \times (\log |inner| + h), where hh is the cost of one heap fetch. Excellent for tens of rows, terrible for millions.
  • Hash join: C≈∣build∣+∣probe∣C \approx |build| + |probe|, linear in the combined size but needing memory for the hash table, and spilling to disk when it does not fit.

Cardinality estimates are the input to both: E=∣table∣×sE = |table| \times s, where ss is the planner's selectivity for the predicate. When two predicates are correlated — city and postcode, tenant and plan — the planner multiplies independent selectivities and underestimates. That underestimate is what flips a hash join into a nested loop at the worst moment.

Trade-offs

ApproachGood forCostFails when
Normalised tables and joinsCorrectness, evolving access patternsMore joins, migration workThe read path is hot and join depth keeps growing
Materialised view or read modelDashboards and fixed reportsRefresh cost and stalenessSources change faster than the refresh interval
JSONB column for varying fieldsFast iteration on sparse attributesWeak constraints, awkward indexingYou filter and aggregate inside the JSON at scale
Application-side joinsTiny result sets behind a cacheExtra round tripsCollections grow past a few dozen rows and become N+1
Text-to-SQL over a curated viewInternal analytics, low-risk read pathsWrong-query risk and a permission surfaceThe schema is wide, ambiguous or not documented
Trade-off mapInteractive

Good for

Correctness, evolving access patterns

Cost

More joins, migration work

Fails when

The read path is hot and join depth keeps growing

  1. The read path is hot and join depth keeps growing
  2. Sources change faster than the refresh interval
  3. You filter and aggregate inside the JSON at scale
  4. Collections grow past a few dozen rows and become N+1
  5. The schema is wide, ambiguous or not documented

Figure. Each choice with what it buys, what it costs, and the case where it loses.

What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.

Text version

Four trade-offs: Normalised tables and joins is good for Correctness, evolving access patterns, costs More joins, migration work, and fails when The read path is hot and join depth keeps growing. Materialised view or read model is good for Dashboards and fixed reports, costs Refresh cost and staleness, and fails when Sources change faster than the refresh interval. JSONB column for varying fields is good for Fast iteration on sparse attributes, costs Weak constraints, awkward indexing, and fails when You filter and aggregate inside the JSON at scale. Application-side joins is good for Tiny result sets behind a cache, costs Extra round trips, and fails when Collections grow past a few dozen rows and become N+1. Text-to-SQL over a curated view is good for Internal analytics, low-risk read paths, costs Wrong-query risk and a permission surface, and fails when The schema is wide, ambiguous or not documented

Failure modes

  • A sequential scan where an index exists. Run EXPLAIN (ANALYZE, BUFFERS) and look at the filter. The common causes are a cast on the indexed column, a type mismatch between the parameter and the column, or an index whose leading column is not constrained. Fix the predicate before adding another index.
  • N+1 queries from an ORM. Count queries per request. If the count scales with the number of rows returned, replace lazy loading with an explicit join or a batched IN query. Measure query count, not just latency; latency hides the pattern until traffic grows.
  • A plan that flips after a data change. Compare estimated with actual rows at every node. If the estimate is off by more than an order of magnitude, run ANALYZE and consider extended statistics for correlated columns.
  • Lock contention during a migration or a hot update. Inspect pg_stat_activity and pg_locks for wait events. Long transactions hold locks; build indexes concurrently, add nullable columns, and backfill in batches rather than one large UPDATE.

In production

Three operational habits cover most incidents. Set a statement_timeout per role so a runaway query cannot occupy a connection indefinitely. Keep a connection pool in front of Postgres, because each backend is a process and connections are the first thing to exhaust under load. And enable pg_stat_statements so you rank queries by total time rather than by how slow one execution felt.

For multi-tenant systems, row-level security is a real option, but the policy predicate must be index-friendly and present on every query path. A policy that calls a function per row can turn an index lookup into a scan.

People think: SQL does not scale, so anything large belongs in a document store.

Actually: The language is not the bottleneck; the plan and the storage layout are. An indexed point lookup on a very large table is fast, and a full scan of a small table is fine. Choose a store by access pattern and consistency needs, not by table size.

People think: Using an ORM means you no longer need to know SQL.

Actually: An ORM generates SQL you still have to read when it is slow. The expensive failures — N+1, missing indexes, accidental full scans — are visible only in the generated statements.

Interview angle

SQL questions in AI interviews are rarely syntax quizzes. They ask you to model a multi-tenant document store, write the retrieval join, and then explain what happens when the tenants table grows. Strong answers describe the plan before the query, name the index that serves the predicate, and distinguish a correctness bug (wrong grain after a join) from a performance bug (bad estimate). Expect a follow-up on why you would keep metadata in Postgres rather than in the vector database. See SQL joins, data modelling, and the data interview category.

Sources