Recetia: 80% of the time goes to fetching data

What this page measures

What follows are its real numbers, measured by running it against the database, not estimated, and what we found when we audited it by asking for faults to be looked for instead of confirmed.

Two different figures appear below and they should not be confused: the full generation flow is 40 queries, and the panel simulation is 16. They measure different things.

What is expensive in a calculation engine?

Not thinking. Fetching.

To propose a recipe you have to answer many questions at once: what each ingredient tastes like, which technique suits it, which recipes resemble what is in the fridge, how much it costs, how long it takes.

Written the direct way, each question goes to the database on its own. And many of them ask for the same thing: the list of cooking techniques is needed to calculate affinity, to calculate compatibility, to order the steps and to compare against existing recipes. The same query four times.

The engine separates two things that are usually mixed together. First it gathers all the data that will be needed, grouping repeated requests into a single one. Then it does the maths, without touching the database again.

Where exactly does the time go?

A full run completes in about nine hundredths of a second. Instrumenting each phase separately: fetching data, 74 ms, 80%. Pure calculation, 4.7 ms, 5%. Orchestration, the rest up to 92 ms, 15%.

If calculation is five milliseconds out of ninety-two, optimising formulas is pointless: you could do the maths twice as fast and nobody would notice the difference. All the headroom is in how the data is requested.

That is why the engine is built around that separation, and not around the algorithms. It is a decision you can only make after measuring: intuition says the opposite.

80% of the time goes to fetching data and 5% to calculating. That is what drives the design, not the algorithms.

How does it scale?

Measured, not extrapolated: the full engine with increasing volumes, three runs each, median. With 1 ingredient, 49 ms. With 10, 74 ms. With 50, 146 ms. With 100, 217 ms. With 200, 270 ms. With 400, 403 ms.

Multiplying the volume by 400 multiplies the time by ten. The number of queries does not grow with the ingredients; what grows is the work inside each one. The curve has no knee in the measured range.

What happened when we audited the rule?

The engine has one central rule: data is requested in one place, and the calculation part does not touch the database. It is written in the documentation, in the comments and in the review guide.

It was being broken in three places. One of them had come in months earlier, in a new function, doing exactly what the rule forbids. All the tests were still green.

There was a budget test —it counted requests and failed if they went up— and it worked. But it counted grouped requests, not real queries. A request with related data counts as one and runs two. So a handful of requests was hiding forty queries.

Why would a property test not have prevented it?

This is the uncomfortable part, and it is the one we take away most.

The easy conclusion would be "a test was missing". It is subtler than that. When we wrote the test that checks the rule, with the test data that already existed it only caught two of the three violations. With a single cooking technique in the table and no chaining, two branches of the code never run.

In other words: even if the test had existed from the start, the fault would have got in anyway, green. Not for lack of a test, but because the data it ran with did not reach that far. That is the mistake made by everyone who does write tests.

The fix had two parts. Enrich the test data until all three branches run. And add a second test that does not depend on data at all: it reads the source code of each part of the engine and looks for database accesses where they should not be, including the parts no test goes through.

The static one was verified by injecting a violation on purpose and checking that it fired. If you have not seen a test fail, you do not know whether it works.

Dynamic test with sparse data, sees 2 of 3. With complete data, sees all 3. Static test, no data, sees even what nobody runs.

Why are there more queries after the fix, not fewer?

From 39 to 40. It looks like a regression and it is the opposite.

Those queries were already running. What happened was that, by bypassing the grouping system, they evaded the counter: they did not show up in any metric or any test. Routing them back through the proper path made them visible.

The internal budget went up from 21 to 24, and that change was recorded with its reason in the test itself. A limit that is loosened without explanation stops being a limit.

The full balance: SQL queries per run, from 39 to 40, the same ones but now counted. Accesses breaking the rule, from 3 to 0. Columns fetched per query, from 19 to 12. Loading/calculation split, from 79/11 to 80/5. Tests in the suite, from 501 to 520, without breaking any.

The judgement call: warn instead of reject

The flavour profile is calculated by weighting each ingredient by its weight in grams. If the client does not send the quantities, they all weigh the same: a pinch of saffron counts as much as a kilo of potatoes.

The immediate reaction was to require the quantities and reject the request without them. Correct on paper and broken in practice: the two products that consume this engine do not send them today. They would have been left without service.

The right way out was different. The request is accepted, and the response states which weighting was actually applied: by grams, partial or unweighted. It achieves the same thing —nobody confuses a weighted profile with a flat one— without leaving anyone out.

And along the way something turned up that the warning would have hidden: the quantities arrived from the JSON with the key as a string and were silently discarded. Weighting by grams had never worked on that path. Without fixing it, the warning would have said "weighted by grams" over a flat profile, which is worse than not warning.

What do we take away?

Documentation describes intentions; code, facts. The documented architecture talked about six components and seventeen data sources. The code had twelve and twenty. Neither figure was a lie when it was written: nobody measured them again. Auditing is running, not reading.

A green test can be a blind test. The budget test existed and worked, but it measured the wrong unit. The property test, with sparse data, saw two of three. Before trusting a test, check that it fails when it should.

Correct that breaks everyone is not correct. Requiring the quantities was defensible in the abstract and disastrous in practice.

A note on the figures

All the numbers come from running the engine against the development database —1,506 ingredients, 22 techniques, 34 recipes— instrumenting each phase. The times are the median of three runs.

Two conditions worth stating: two affinity tables are empty, so these times are a floor and not a ceiling; and the recipe corpus is small, so the scaling curve describes the behaviour with many ingredients, not with many recipes.

Tech stack

• Python 3.12
• Django 5
• PostgreSQL 16
• pgvector
• Celery
• Redis