Notesdata-models🌱 seedling

Declarative Query Language

Also: Declarative Query · Query Language · SQL · Cypher · Query Optimizer

A declarative query describes the result you want, not the steps to compute it, and the engine's optimizer chooses the plan. That indirection lets plans change with data, indexes and hardware while the query stays the same.

Why it exists

Imperative data access (walk this pointer, loop in this order) bakes the access path into the program, so every physical change means a code change, and a loop can't be parallelized behind your back.

How it works

  • The query states conditions and shape (WHERE city = ?); the planner enumerates strategies (full scan, Secondary Index scan, join orders) and costs them from statistics.
  • The same query can get a different plan when an index is added or selectivity changes: an index for a rare city, a parallel full scan for a common one.
  • Examples: SQL, Cypher and GQL, SPARQL, Datalog; MongoDB's aggregation pipeline is a declarative list of stages the server may reorder.
  • Lazy DataFrame APIs (Polars, Spark) build a plan first and optimize it with predicate and projection pushdown, like a SQL planner.

Trade-offs

Gains Costs
plans improve without code changes plans can change unexpectedly (regressions)
engine can parallelize and reorder harder to force a specific strategy
concise, composable queries optimizer depends on fresh statistics

In practice

  • Postgres picks between sequential, index and bitmap scans per query from table statistics; EXPLAIN shows the choice.
  • Polars' lazy API pushes filters and column selection into the file scan before reading any data.

Pitfalls

  • Assuming the plan is stable: a stats change or data growth can flip it. Watch plans for hot queries.
  • Writing imperative loops in application code around a declarative engine (N+1 queries), throwing the optimizer away.

Recall

These cards are also in your review deck, scheduled with spaced repetition.