The Learned Query Optimizer
Status: planned for the Advanced Beta.
The Alpha answers every query the same way, whatever state the device is in. A device in the field is rarely in the same state twice: its battery runs down, its link comes and goes, its flash fills and wears, and its data turns from routine to alarming. The Learned Query Optimizer (LQO) is a planned, optional layer that lets AltSql take its situation into account.
It is a concept for after the Beta. Nothing described here is built yet, apart from two early measurements, and the benefits named below are expectations still to be tested.
It would work in two modes:
- Optimize. The same answer, reached more cheaply: from summaries instead of raw rows, sent in a better order, timed to the battery and the link. Results match the exact computation, up to rounding.
- Adapt. A different answer, only where the application has declared it acceptable, and always labeled: an average from a sample with its error bound, a forecast marked as a forecast, detail kept where it matters.
The idea in one line. The engine learns from its own data and its own situation, and changes how it works, and where allowed what it answers, within limits the application sets.
The Gateway Learns, the Device Decides
Training needs history, memory and processing power. Deciding needs only a few numbers. So learning happens on the gateway or in the cloud, and devices run small, fixed models with light updates as data arrives. Models go down to the devices and decisions come back up as ordinary records, checked, sequenced and synced like any other data.
Why It Fits AltSql
- The data is already there. Readings, summaries and decisions are records already; link events and context would join them.
- Models can travel as records. A learned model is a small table of numbers, stored and synced as ordinary records once sync also runs from gateway to device.
- The premise gets stronger. A device that adapts copes better when cut off, and sends what matters first.
Research optimizers mostly plan queries that join many tables, and AltSql has no joins. Its value lies elsewhere, in five decisions that today are fixed in code or left to the application:
| Decision | Alpha today | With the LQO |
|---|---|---|
| Where an answer comes from | The raw rows of the series a query names | Raw rows, summaries, a sample or a model, chosen by predicted cost and allowed error |
| What to keep | The oldest rows roll over first, whatever they hold | Detail kept around events that matter; routine periods kept as summaries |
| What to send, and when | Records go in order whenever the link allows | The most useful records first; uploads timed to predicted coverage and cost |
| How often to measure | A fixed interval set by the application | The engine suggests the next interval from recent change, battery and storage |
| When to raise an alert | Fixed thresholds in application code | A learned normal range per device, with the fixed threshold kept as a hard limit |
The Application Stays in Control
Nothing adapts unless the application says it may. Each series gets a contract in the device’s set-up file, and a query can ask for more. A sketch of the proposed syntax:
series excursion exact # never sampled, predicted or dropped
series raw summaries after 1 day; keep detail around alarms
series quarter approximate within 2% at 95% confidence
link mobile order by value; routine rows older than 7 days may wait
alert temp learned normal range; hard limit 8.0 C
SELECT AVG(temp) FROM raw WHERE time >= 1767225600
ERROR WITHIN 2% AT CONFIDENCE 95%
A query without an error or time bound gets an exact answer, as today. Every result the layer touches carries a label, and every decision it takes is stored as a record, so anyone can see later what happened and why:
| Label | Meaning | Example |
|---|---|---|
| Exact | Computed from all the rows | Every answer in the Alpha, and the default |
| Exact, from summaries | The same result, up to rounding, read from summaries kept while writing | Hourly averages from minute summaries |
| Approximate | Estimated from a sample, with its error bound and confidence | Average 4.2 °C, ±1.8% at 95% |
| Predicted | Produced by a model, with the model’s version | Moisture reaches 20% in 26 hours (model 7) |
| Reduced | Detail summarized or held back under the contract, with when and why | Raw readings of 2 to 3 May kept as minute summaries |
In the Three Industries
The scenarios below are expectations to test on the Beta devices and after.
Industrial machine monitoring. A fixed 60 °C rule misses a bearing running 8 °C hot at half load, yet fires on a machine that always runs warm in summer. The layer learns each machine’s normal temperature for its load and flags readings far from it, with the 60 °C rule kept as a hard limit. In routine hours only minute summaries go to flash; when the anomaly score rises, the device stores every reading until things settle. After a Wi-Fi outage, alarms go first.
Cold chain and logistics. Temperature logs and excursion records are compliance evidence, so the contract marks them exact: the layer never samples, predicts or drops them. Around them it can help. With the door open on a hot day and a half-empty load, a learned model predicts the load will pass 8 °C in nine minutes and warns the driver before the 15-minute rule would fire. On regular routes it learns where coverage ends and uploads before the truck loses signal.
Agriculture. The sensor learns how fast its soil dries at each soil temperature, predicts when moisture will reach 20% and opens the valve ahead of time, or waits when the farm system forecasts rain. It measures more often after rain and less at night, and each day it picks what the single 51-byte radio message should carry: the daily summary, a three-day forecast or an alert.
In every industry the layer changes what the device spends (bytes, energy, flash, attention) far more often than it changes an answer. Where it does change an answer, the application allowed it and the answer says so.
Two Early Measurements
Before designing further, two assumptions were tested.
Answering from summaries. One million readings, as in the Alpha benchmark. While writing, a small routine also kept minute and hourly summaries, as a device would. Two questions were asked from raw rows and from the summaries, best of five runs:
| Question | Raw rows | Summaries, same log | Summaries in their own area |
|---|---|---|---|
| Hourly averages | 200 to 207 ms | 92 ms | 5.3 ms: 38 times faster |
| Average per machine | 164 to 169 ms | 89 ms | 3.4 ms: 48 times faster |
The results matched: counts, minimums and maximums exactly, and averages to about one part in 1015. So the Optimize mode can deliver large, exact gains without any learning, once summaries get their own storage area. That tiered storage is the first engineering step.
The cost of small models on a device. Six typical models and learners, written in plain C and compiled for a Cortex-M4F with clang at -Os, came to 684 bytes of code, 280 bytes of model tables and 256 bytes of RAM for the largest state. A real module adds loading, checking and logging, so the budget for the whole device side is 3 to 5 KB of code. That figure is an estimate.
How It Would Be Built
| Phase | What it adds | Learning | Duration (estimate) |
|---|---|---|---|
| 1. Foundations | Tiered storage for summaries; statistics kept while writing; exact answers from summaries; a context call; a decision log; sync from gateway to device | None | 2 to 3 months |
| 2. Gateway learning | Models trained on the gateway (normal ranges, forecasts, the value of each record) and pushed to devices as records; approximate SQL answers with declared bounds | Gateway only | 3 to 4 months |
| 3. Device adaptation | Contracts in the set-up file; adaptive retention and sampling; learned alerts under hard limits; safe trials of policies on the three Beta devices | Small models on devices | 4 to 6 months |
| 4. Fleet learning | Models learned across many devices; ready model packs per industry; optional neural networks on larger chips | Gateway and cloud | Ongoing |
Each phase has to reach a target before the next one starts. For phase 3, on the three industry devices, the target is 30% fewer bytes sent and 20% less energy per day than the Beta, with no missed alarm and half the false alarms of fixed thresholds. None of these targets is a result yet.
What the Layer Will Not Do
- Change a series declared exact, or any answer without a label.
- Move an actuator, such as a valve or a fan, outside the hard limits the application sets.
- Run large language models on the device.
- Learn from anyone’s data without their consent.
What Research Says
Learned optimizers have a long history: LEO in IBM DB2 (2001) corrected its own estimates, BlinkDB (2013) answered from samples within declared error bounds, and Bao (2021) steered PostgreSQL’s optimizer with learned hints. A 2024 study found that current learned optimizers “do not systematically perform better than PostgreSQL”. The lesson taken here: learning works best when it steers a sound mechanism and a wrong guess costs little. So the LQO keeps the exact path as its base and its fallback, and its measured gains and failure rates will be published.