When The Data Has To Find The Query

A machine tightens one bolt.

It reports:

torque = 43.2

torque is how hard a bolt was twisted. Exact factory meaning is not important yet, we only need to know that machine produces number.

Three quality engineers have saved watches:

watch A: tell me when torque is between 42 and 45
watch B: tell me when torque is between 50 and 55
watch C: tell me when torque is below 40

New value 43.2 arrives.

Who should get an alert?

Watch A.

This is very easy as you can simply check all three watches against the new data.

Now a factory has 10,000 watches and thousands of machine results arriving each second, and this approach doesn’t work anymore.

A system can index every machine result beautifully. It can partition tables, cluster files, cache reads, and hire very serious database wizards.

None of this helps answer:

Here’s a new reading, which saved watches care about it?

Normal database index helps questions find database

question -> data

A real time system often needs data to find the questions instead

data -> question

This arrow difference in the sentence amounts to a large difference in work.

First Direction: Question Finds Data

The traceability system has a simple job to describe:

For one vehicle, show everything that happened to it.

Which parts went into the vehicle, which machines touched it, which supplier lots fed parts, which tests passed, which lab tests later found problems.

This is by no means a simple system, but the lookup direction is familiar.

Quality engineer asks for vehicle: VIN-2858

SELECT *
FROM tightening_results
WHERE vehicle_id = 'VIN-8271';

Index on vehicle_id helps the database find rows.

question -> data

Question arrives and data is already stored.

Most database design work points this way. Partition by a useful key, index useful column, keep related records together.

Now the client demands that the quality screen must be live.

Engineer saves watch:

station = ST-42
model   = SUV-A
torque  = 42 through 45

Whenever a matching result arrives, update the screen and send an alert.

Now the event arrives first while the system must discover which saved questions match it.

data -> questions

This simple arrow flip causes massive architecture headaches.

First Guess: Remember Old results

Let’s say a design remembers rows returned by each watch.

This helps when a known row changes. The system sees a row belongs to watch A, so we should refresh watch A.

But brand-new machine results belong to no old result.

Old results cannot tell which watch a new row should appear in. The same problem happens when correction moves an existing row into a range it did not match before.

So remembering old results is useful, but not enough.

The next obvious design is to rerun every watch or test every watch against each new event:

for each new event:
    for each saved watch:
        evaluate watch against event

This is correct. If your system has 50 watches and 10 events per second, stop here. A simple loop that is easy to understand and test is perfectly sufficient here.

But with 100,000 watches and 5,000 events each second, a blind scan can ask up to 500 million watch questions each second.

Maybe your hardware can handle some of this, maybe you implement batching to reduce cost, and maybe you keep watches extremely simple. But the system is still checking many watches that cannot possibly match.

Data has indexes but questions are an unorganized pile, leaving us with no good way to do this.

Number to Interval

Let’s return to one torque value:

43.2

Put all possible torque values on a number line:

The machine result is a point and the watch is an interval. The query is now:

Which stored intervals contain this point?

This is an established kind of problem. Database people call the broader problem event matching, subscription matching, or predicate indexing.

The name is less important than the inversion:

Store intervals first
Receive point later
Find intervals containing point

An interval index can reject watches far from 43.2 without evaluating each one. We have now indexed the question.

Second Number Makes a Rectangle

Real tightening watch may care about the torque and the angle. The angle is how far fastener turned while tightening. The new result says:

torque = 43.2
angle = 91.7

These two values make a point on the chart:

The watch says:

42 <= torque <= 45
90 <= angle  <= 94

The watch is now a rectangle around the point. This is not a metaphor. Constraints literally describe rectangular region in two-dimensional coordinate space.

We can now ask:

Which stored rectangles contain point (43.2, 91.7)

Spatial indexes answer this family of questions.

Nothing geographic is required. Axes can be longitude and latitude, but they can also be torque and angle, temperature and pressure, or price and rating.

One range gives interval, two ranges give rectangle, three give a box. But more dimensions are not always more clever.

Rectangle Math (Boring but Simple)

Suppose one watch stores:

torque_min = 42
torque_max = 45
angle_min  = 90
angle_max  = 94

…and event arrives:

torque = 43.2
angle  = 91.7

Event is inside rectangle when both coordinates fall inside their stored ranges:

42 <= 43.2 <= 45
90 <= 91.7 <= 94

Or more generally:

torque_min <= event.torque <= torque_max
and
angle_min  <= event.angle  <= angle_max

This is simple and cheap; with one watch, four comparisons solve problem.

With 100,000 watches, you could perform those comparisons 100,000 times for every event. However, a spatial index exists to avoid asking all rectangles. It organizes stored rectangles so system can quickly discard regions that could not possibly contain incoming point, then returns much smaller candidate set.

Exact condition is still checked against the candidates afterward.

The index does not change the math, it changes how many times one must do the math.

Cheap Filters Before Clever Geometry

A watch has other conditions:

plant   = A
station = ST-42
model   = SUV-A

These are equality checks. An event either has the same value or it doesn’t, but we don’t need to stuff every condition into a giant geometry abstraction. Use boring filters first. Partition watches by plant. Inside plant, group by station. Then group by model.

new normalized event
        |
        v
watches for Plant A
        |
        v
watches for ST-42
        |
        v
regions containing torque/angle point
        |
        v
exact full-rule check
        |
        v
matched watch IDs

Index Finds Candidates, Not Truth

Once watches are indexed (stored as data just like ordinary records, indexed on keys), a matcher can use equality fields and ranges to avoid checking every saved watch.

But the index should stay humble. It answers:

Which watches might match this event?

Then ordinary evaluation finds answers:

Which ones actually match?

Index Only Wins When It Removes Work

Suppose a system has 100,000 watches, but incoming event produces only 30 plausible candidates. This is good, the index removed almost all work.

Suppose almost every watch covers almost every possible torque and angle. Then almost every event falls inside every rectangle. The index returns nearly all watches and has accomplished nothing.

So measure before building. If saved questions are numerous, mostly equality and range predicates, and each event matches a small fraction, consider indexing.

The main lesson is small: ordinary database work usually looks like:

question  ->  data

Live subscription systems sometimes need:

data  ->  questions

When the arrow turns around, maybe the index should turn around too.