Scenarios

A B-tree needs a fixed prefix; a leading wildcard has none, so every row is scanned.

A full-text index inside the database (GIN) for a first version, or a dedicated search cluster with an inverted index.

Analyzers (lowercasing, stemming), BM25 ranking with block-max skipping, and a second, case-preserving field for exact matches.

A larger index, a delay before new rows are searchable, and a rebuild whenever the analyzer changes.

Start with GIN if the needs for ranking quality are modest; move out when facets and relevance tuning dominate.

Facets need counts over all matches, which defeats top-k skipping, and they need a forward copy of each field.

Compute exact counts only for fields with few distinct values; sample the matches and show "about N"; precompute aggregates for common filters; and avoid facets on fields with many distinct values, which do not merge across shards.

Approximate counts may be off in either direction, which is acceptable because they are only a guide.

New data becomes visible at each flush. Each flush creates a segment, and every query pays a fixed cost per segment, so segments multiply and every reader pays.

Keep a refresh of about 1 s, merge more aggressively to keep a handful of segments per shard, and consider a separate small real-time index for the newest items.

Under heavy writes, merge work can pile up.

Brute force reads 30 GB per query: about 0.5 s, so 2 queries per second per machine, and about 250 machines.

IVF plus PQ codes (about 96 bytes per vector, about 0.96 GB in total) or HNSW, tuned from a measured curve of recall against throughput, using brute-force results on a sample as the truth (for example, target recall 0.95). Then rerank the survivors exactly.

Every shard may hold a neighbour, so every query goes to every shard, and tail latency is the maximum over shards.

Recall below 1, failures that are silent, and memory.

The filter selects about 0.5% of the corpus, in the hard middle band. Filtering after the search would need about 2,000 candidates to return 10 results.

If the filter leaves fewer than about 100,000 items, brute-force them (50,000 × 3 KB = 150 MB, about 3 ms at 50 GB/s). Otherwise use the approximate index with a large over-fetch, or a traversal that is aware of the filter.

Separate indexes per value for fields with few distinct values. State the recall you accept.

The embedding model is a schema: old and new vectors live in different coordinate systems, so comparing them is meaningless.

Build a second index over the full corpus, write new items to both for a period (for example two weeks), compare quality offline and with an A/B test, cut over, and delete the old index.

Double storage and a full re-embedding run. With no ground truth, reproducibility replaces correctness, so pin the model and index versions.

Run both and combine them with reciprocal rank fusion, which uses ranks and ignores score values that cannot be compared.

Two systems, two round trips, a constant to tune, and A/B testing to validate quality.

A shallow query (2–3 hops) from one starting user is an OLTP workload: use mutable adjacency lists, with forward and reverse copies.

With 100 connections each, hop 3 reaches about 1 million vertices before deduplication.

Cap or sample the expansion of celebrity accounts, or precompute their purchaser sets, because a hub's cost depends on the data, not on the query.

Deep or global analytics (PageRank, connected components) use an immutable CSR built per run, with bulk synchronous processing and vertex-cut partitioning.

The reverse copy doubles storage, and sampling hubs makes results approximate.

The cost of each round is set by the slowest worker. A hub vertex receives millions of messages, so its worker is the straggler in every round.

Vertex-cut partitioning; combiners that sum messages per destination; and fewer rounds (stop PageRank early, activate only vertices that changed).

Asynchronous execution, only if you accept non-determinism and harder recovery.

Index locations by grid cell (for example H3). Cover the 500 m circle with cells, over-covering on purpose, then refine with an exact geodesic distance in metres.

Store each zone polygon with a bounding box and an inner box, to accept or reject most points cheaply. Zones that cross partitions are duplicated, so deduplicate by zone ID before summing.

Enforce the coordinate system and geometry validity at write time, and fix one predicate (ST_Covers) for points on a boundary.

Depends on how well shapes fill their boxes, so test with real zones.

60 series × 2 million users is 120 million series; at 3 KB each, about 360 GB.

Remove the label, add a quota at write time, and move per-user debugging to logs.

Store t-digest sketches plus sum, count, min and max at each rollup level. Use delta-of-delta and XOR compression (for example 1 TB down to about 94 GB) and tiered retention.

Data already rolled up without sketches can never answer p99. Emit explicit "offline" events so gaps can be interpreted.

Immutable snapshots, retention policy and deletion conflict. The user must disappear from the inverted index, the vector index, rollups, graph projections and training snapshots. Some structures can only delete at rebuild, and some cannot delete without invalidating what they were fitted to.

Keep a deletion log and tombstone now; rebuild on a schedule, with the measured rebuild time as your deletion deadline; keep training datasets outside normal retention, but with an explicit expiry.

Retraining is the only complete answer. This tension is unresolved.

These structures do not combine: one answers, the rest are filters applied afterwards, and filtering afterwards degrades as selectivity falls.

Estimate the selectivity of each condition. If the combined selectivity is very low, run the most selective structure first (often location or time) and brute-force the rest, or accept lower recall and measure it.

Recall against brute force, relevance against judged result sets, and the business outcome, because improving one does not guarantee the others.

Can the predicate become a range or an equality? If yes, stay in the general engine.

If not, name the shape of the problem, the resource that binds and the knob that trades it.

A second write path, a freshness metric, reconciliation, a deletion path, and rebuild time as the recovery objective.

Whether an extension of the general engine fits the budget, since that line moves every year.

Say what you cannot verify, and who will notice first.