Specialized Processing Models
What do you build when a query predicate cannot be reduced to a range over an ordered key?
Who this mechanism is for: a developer who writes code, calls APIs and knows basic SQL, and has probably used a search box, a "similar items" feature or a map, but has never looked inside the systems that power them. You do not need any database or systems background. Every technical term is explained the first time it appears, and again in the glossary at the end. This mechanism assumes only the short primer under "Before you start" below.
The story we will follow
Amara Okafor is a backend engineer at Haatly, a neighbourhood marketplace app ("haat" means local market). People list second-hand clothes, furniture and handmade goods. Buyers search and buy. About 4,000 couriers deliver within the city. Haatly has roughly 10 million listings, 2 million users and 300,000 deliveries a month. Everything lives in one PostgreSQL database with ordinary indexes, and for orders, users and payments that works very well.
Then the product team asks for five features: a search box that understands words, a "similar items" strip, "friends of friends who bought this", "couriers near me", and live courier and queue metrics. Amara's CTO, Elin Lindqvist, asks a fair question: "Why can't these all be ordinary SQL queries?" Amara's colleague Diego Ferreira, a data scientist, builds the embeddings behind "similar items".
Each article of this mechanism opens with a Haatly feature that was built "the obvious way" in SQL and that looks like a bug: a query that takes 9 seconds, a result page with three items instead of ten, a metrics store that falls over when one label is added. Each article ends with the solution, built from the mechanisms taught in that article. The numbers in the story are illustrations. The numbers in the teaching text come from the source analysis.
The main idea of this mechanism is this:
(Prune means "skip data that cannot be part of the answer without reading it".)
How this mechanism is organized
- 1
When a predicate is not a range
Which one assumption do ordinary databases rely on, in which ways does it break, and what are the shared patterns of the answers?
CaseFive features Postgres cannot express
- 2
Text search
How can a system rank a million matches when no answer is "correct", without scoring all of them?
CaseOne extra word, 200 times slower
- 3
Vector search
How do you search when every record is a candidate and nothing can be eliminated by inspection?
CaseSimilar items: 3 results instead of 10
- 4
Graph
What do you do when the next record to read depends on the record you just read?
CaseFriends of friends that never finishes
- 5
Geospatial
How do you index objects that have area, on a curved Earth, using a one-dimensional key?
CaseA courier 10 metres away, never offered the job
- 6
Time series
When finding data is easy but there is too much of it, how few bytes can a point take?
CaseOne new label, and the metrics store fell over
- 7
What specialization adds
When does a workload earn its own system, and what does each new system cost beyond its engine?
CaseSeven systems: when does the next one earn its place?
After that: a summary, hands-on labs, interview questions and a glossary.
One request: do not skip the arithmetic. Many sections turn on a few lines of simple multiplication (a 30 GB scan, a 1,000x fetch, 50 segments). The arithmetic is what makes each mechanism feel necessary rather than arbitrary.
What do I do, and what does each option cost?
Can I see it myself?
Why `LIKE '%word%'` cannot use a B-tree, and what an inverted index does instead
A million Zipfian documents in PostgreSQL: LIKE '%word%' with a B-tree, then a GIN full-text index for a rare and for the most common word.
Real systemDocker, PythonBlock-max skipping on a toy engine
Postings for 50,000 documents and 5 Zipfian terms: exhaustive top-10 scoring against block-max skipping, postings read, and where skipping stops being safe.
SimulatePython with NumPyThe memory-bound arithmetic of brute-force vector search
400,000 random 768-dimensional vectors: one brute-force query as float32, float16 and int8, effective bandwidth, and float32 scaled to 10 million vectors.
MeasurePython with NumPyMeasure recall against brute-force truth, and see the filtered-search collapse
An HNSW index over 200,000 random vectors: recall@10 and queries per second for efSearch 10 to 200; then a 0.5% filter, post-filtered and pre-filtered.
MeasurePython with hnswlib, NumPyThree self-joins versus a frontier expansion
A power-law graph of a million vertices: 1- to 4-hop chained self-joins in PostgreSQL against a CSR breadth-first search, from a median vertex and from the top hub.
Real systemDocker, Python with NumPySupersteps and the straggler tax
10 PageRank supersteps over a power-law graph split across 8 workers by vertex and by edge, with and without a combiner; then PageRank iterations against connected components.
SimulatePython with NumPyFilter and refine, and the fill ratio
100,000 circles and buffered diagonal lines: bounding-box false positives by fill ratio, STRtree filter-and-refine against brute force, and the raw-degree distance trap.
MeasurePython with shapely, pyprojCardinality wall and Gorilla encoding
Series counts with and without a user_id label; delta-of-delta and XOR encoding of a million points, regular and noisy; minute rollups of an hour's p99.
MeasurePython with NumPy, datasketches
Can I reason about it under questioning?
Interview questions
Short questions on specialized processing, such as text search, vector search, graph traversal, geospatial queries, time series and ML features, each with an answer you could give aloud and a link to the full explanation.
Scenarios
Longer problems on specialized processing, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.
The tensions this mechanism takes a position on. Each recurs across mechanisms.
Space vs. time
For example: compression, indexing, materialization, caching.
Generality vs. specialization
For example: general engines vs. purpose-built structures.
Ordering vs. parallelism
Global order costs serialization; parallelism costs order.
Early binding vs. late binding
When structure is committed; determines where the failure surfaces, at write time or read time.
Read-optimized vs. write-optimized
For example: B-tree vs. LSM, merge-on-read vs. copy-on-write.
Ideas this mechanism meets that keep coming back, mechanism after mechanism.
Power laws and skew
Granularity mismatch
Granularity mismatch creates amplification.
The algebra decides
What can be skipped, pushed down, parallelized, merged or precomputed is determined by the algebra of the function, not the layout of the data.
Sound approximations
When exactness is too expensive, use a sound approximation.
Variance costs more than volume
Variance costs more than volume.
Show all 11 principlesShow fewer
Independence is assumed
Independence is assumed everywhere and is true almost nowhere.
Fixed cost per operation
Fixed cost per operation implies a natural batch size.
Absence is not evidence
Absence of an event is not absence of a failure.
Which way the error points
Masking moves failure
Masking a failure moves it from a channel with a schema to a channel without one.
Bound the answer, bound the state