# Techniques and limits

What an optimizer may propose, and what no proposal may break. Every optimizer brief carries this file's content.

## Allowed

- **Indexes:** a new, changed, covering or dropped index; an ordering index for a list read in pages. Each is decided by `indexes.md` from the read and write ledgers.
- **Cache keys:** new keys, changed values and lifetimes, a version in the key.
- **Merged tables:** folding a one-to-one or a rarely-separate table into its owner.
- **Stored cache columns:** a copied or computed value on the row its reader already reads, with the one statement that keeps it true.
- **Common techniques:** cache-aside, an assembled object cached whole, removing duplicate reads within a request, one statement where several ran, batching, moving work off the request path, a request-scoped copy.

## Placement by traffic

What the workload decides, beyond the indexes and keys above. The traffic-independent design decisions are the `nustack` skill's `database.md`; a proposal never trades one of them away.

- **Place data by its write velocity, and cut a slow-changing read inside the database before lifting it out.** A cache earns fast-moving state; a slow-changing aggregate is usually one statement away. A product page is assembled by one aggregate statement (`json_group_array` on SQLite, `JSON_ARRAYAGG` on MySQL), not cached whole.
- **Write-behind is admitted only for fields no security decision reads** — absorb a hot counter in the cache and flush it on an interval, so a cache loss costs one flush window of a display field; anything that grants or revokes is written through. An article's `view_count` is incremented in the cache and flushed every minute.
- **Copy inside the database to remove a hot read**, under the guard in the `nustack` skill's `database.md`: written in the same transaction as the source, never deciding uniqueness or security. `comments.post_author_id` is copied for a hot moderation query.
- **No copy where the reader already holds a snapshot.** A table served from a per-process in-memory snapshot gains nothing from a database-side copy: the column removes no read and becomes a third copy to keep true.
- **Count the lookups before giving up one home for a head column** — a covering index on the child often reaches the parent in as many index descents as a denormalized column would, and a unique fact split across two homes can disagree with itself. An index `(email, customer_id)` on `customer_emails` beats a copied `customers.primary_email`.
- **Partition a high-volume log by its retention unit, and purge by dropping the oldest partition** (MySQL, Postgres) — instant, where a nightly `DELETE` takes locks and bloats the write-ahead or binary log. Logs split by how long they live, not by what they describe: one table cannot carry a per-row retention class, because a partition drop cannot keep some rows of a day. `page_views` is partitioned by day and dropped at 14 days, beside `billing_events` kept for seven years.
  - **Create partitions ahead and keep a catch-all** (`PARTITION p_future VALUES LESS THAN (MAXVALUE)`), which the maintenance job reorganizes: a stalled job then never refuses a write, and rows found in the catch-all are the alarm that it stalled.
  - **A partitioned log's references are soft** — MySQL refuses foreign keys on partitioned tables, the outcome the append-only foreign-key policy in the `nustack` skill's `database.md` reaches on its own; its key form is that file's § Keys & the shared vocabulary.

## Out of bounds

Stored procedures, alternative storage engines, optimizer hints, triggers that maintain a cache, and any technique specific to one database's internals.

## Correctness limits

- **A hot call reads nothing from the database it already knows.** The target for every high-volume call; a remaining read is named and justified.
- **A count shown on a hot object is stored, and recounted on write.** A recount from the source rows is idempotent; an increment drifts the first time a writer is missed. A periodic reconcile backs it.
- **A cached object is keyed by its version, not deleted on write.** When many writers change an object, a key carrying the object's version (its `updated_at`, advanced on every change) makes a stale entry unreachable without a census of writers, and closes the race where a reader writes back a row loaded before the change. Delete-on-write is for an object with one or two writers.
- **A miss is never cached, and neither is a row strangers create.** A cached "not found" outlives the insert that follows it; a self-registered row has no writer that clears it.
- **Revocation reads stay live or are re-checked on every hit.** A session's state, a role, a key's owner and expiry, a refresh token's client and expiry are read live or checked on each use of a cached entry; a revoked actor stops on the next call.
- **Polling writes nothing to the database.** Poll state lives in short-lived cache keys; a poll that races an approval can be wrong for at most the key's lifetime.
- **Nothing scans the cache's keyspace.** A flush finds its work in a set of changed ids, never a key scan, which blocks the cache for every caller.
- **A cache outage is a cold path.** It is not a design input; no proposal is scored on it.
- **A design that needs a full census of writers loses to one that does not.** If a census is unavoidable, the boundary tracers enumerate it before the design is adopted.