sql-query-conventions

name	sql-query-conventions
description	Read-path SQL conventions for raw SQL, PostgREST, and SQL-shaped ORMs. TRIGGER when: writing or modifying a SELECT query, PostgREST .from()/.select() chain, list-returning ORM call, or query-shape / pagination design. DO NOT TRIGGER when: writing write-path (INSERT/UPDATE/DELETE), DDL-only migrations, test fixtures, or document/KV stores.
user-invocable	false

SQL Query Conventions

Read-path conventions for relational stores. Each rule below names an AI anti-pattern that scales badly and is easy to miss in review because the query "works" at current data volume.

1. Every list query needs an explicit limit

Never emit an unbounded list query against a table that can grow. Always set a limit — LIMIT N in SQL, .limit(N) / .range(start, end) in PostgREST, take / first in ORMs. Do not rely on server defaults (no ceiling in PostgREST; unbounded for raw Postgres).

• If the caller supplies a page size, validate it against a hard max.
• Single-row queries need the right tool. .single() (PostgREST) errors if the result is not exactly one row — use it when the caller expects exactly one and wants to surface the violation. .maybeSingle() / LIMIT 1 returns zero-or-one silently — use that when absence is a valid outcome. Conflating them teaches a bug either way (spurious errors on zero rows, or silently-wrong answers on multiple).
• Stable pagination requires a total-ordered ORDER BY. Without one, LIMIT / OFFSET can return arbitrary rows and shuffle between pages. Always include a deterministic tiebreaker (primary key).
• Prefer keyset pagination over offset for large page numbers. OFFSET N is O(N) in Postgres — the database must scan and discard N rows. Keyset form: WHERE (created_at, id) < (:last_ts, :last_id) ORDER BY created_at DESC, id DESC LIMIT N. Constant cost per page.

Why: unbounded queries DoS the database and the caller's memory under growth. The bug appears at the scale where it hurts most.

2. No N+1 from iterating async DB calls

Never loop over records with per-item database calls. This is the most common AI anti-pattern in data-access code.

# WRONG
for id in ids:
    row = await db.from('table').select().eq('id', id)
    results.append(row)

Fix by batching:

• SQL/PostgREST: WHERE id IN (:ids) / .in('id', ids).
• Joins: if the loop is "for each row, fetch related row," use a join.
• Complex batching: wrap in a server-side function / stored procedure.

If the list can grow unbounded, chunk it. IN-list limits by backend:

Backend	Practical ceiling	When to switch approach
Postgres (driver bind params)	~1,000–5,000	Use = ANY($1::int[]) — one bind param, unbounded by protocol
PostgREST / Supabase .in()	~few hundred	URL-length bound (nginx/Kong default ~4–8 KB); switch to an RPC taking an array param
BigQuery	~1,000	Switch to UNNEST(@ids) or JOIN against a temp/staging table — 1 MB query text limit, planner broadcasts big IN lists
Snowflake	16,384 IN expressions	Switch to ARRAY_CONTAINS / temp-table join well before the hard limit

Why: loops over async DB calls scale to O(N) round-trips. The test suite sees 2 items and passes in 50ms; production sees 500 and takes 25 seconds. Big IN lists compound this with planner-side cost that grows with list length and round-trip-size that blows through driver limits.

3. Prefer explicit column selection over SELECT *

When the table may grow wide columns (blobs, JSON, vectors, or PII-sensitive fields), select only the columns the caller needs.

• Use SELECT col1, col2 / .select('col1, col2') for read paths with a known shape.
• SELECT * is fine for internal admin tooling and tests.
• When a new wide column is added, callers that use SELECT * silently start fetching extra bytes — detectable only by someone spotting the bandwidth change.

Why: SELECT * couples read-path performance to schema evolution. Explicit columns make the coupling local to the callsite that actually needs the new column.

Anti-patterns adjacent to these rules

• Check-then-insert: SELECT ... EXISTS followed by INSERT is racy. Use INSERT ... ON CONFLICT with a unique constraint.
• Read-then-write without locking: for the legitimate case of "read a row, decide based on its content, update it" in a transaction, use SELECT ... FOR UPDATE to lock the row until commit. Without the lock, a concurrent writer can change the row between your SELECT and UPDATE, and your decision is based on stale data.
• COUNT(*) for existence checks: use EXISTS (SELECT 1 FROM ... WHERE ...) or SELECT 1 ... LIMIT 1 — both short-circuit on the first matching row. COUNT(*) reads every matching row even when you only need to know "any?". For actual counts (dashboard "N total"), COUNT(*) is the right tool; with a matching index and WHERE clause Postgres can plan an index-only scan.
• Unindexed filter / sort columns: if a list query filters or orders by a column that isn't indexed, call it out. Add a migration, or document the known scan if it's acceptable.
• Reading a whole table to compute in app code what belongs in the database: if the answer is a scalar, an aggregate, or a ranked/partitioned result, compute it in SQL — do not fetch rows to JavaScript/Python and reduce there. Specifically: GROUP BY for aggregates, ROW_NUMBER() / RANK() / DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...) for ranking or "first N per group," SUM/AVG/MIN/MAX with FILTER (WHERE ...) for conditional aggregates. Most of these exist because AI agents don't reach for window functions by default.

Verifying query plans

When a query involves a new index, a large IN list, a join order that could be wrong, or a filter on a column whose selectivity is unclear, verify with EXPLAIN (plan only) or EXPLAIN ANALYZE (plan + actual execution) before declaring the query good. Specifically check:

• Did the planner use the expected index (Index Scan / Index Only Scan) rather than a Seq Scan?
• Is the join order sensible? A Hash Join over 10M rows when a Nested Loop over 20 indexed rows was intended is a red flag.
• Does the Rows estimate match reality (from ANALYZE output)? A large misestimate means out-of-date stats — run ANALYZE <table>.

Don't skip this step on the basis of "the query is simple" — planner surprises on simple queries are how indexes silently stop being used.