SQLite is the default answer whenever a Delphi application needs local storage, and for good reason. But many applications never actually need SQL. They store records, find them again by key or by index, and on mobile devices they need to keep that data encrypted. For exactly this job we built FLStore: a page-based CRUD storage engine that plugs into our TVectorDB interface, with encryption built in. This post explains how the engine works, how it performs against SQLite (with and without encryption), and how we tested it.
One Interface, Three Drivers
TVectorDB is our table-oriented data layer. The model is a plain Delphi record with attributes; TFLVectorTable<T> reads and writes it through the IFLTableStore interface. Behind that interface there are three drivers. The MySQL and SQLite drivers generate SQL. The third one, FLStore (TStorageTableStore), is a storage engine of our own with no SQL anywhere. The application code is the same for all three.
The model attributes and nullable types come from our FDK. A minimal example:
type
[VectorTable('customers')]
[VectorIndex('City')]
TCustomer = record
[DBField(true, 32)] GUID : string;
[DBField(26)] Name : string;
[DBField(29)] City : string;
end;
var
lStore : IFLTableStore;
lCustomer : TCustomer;
lTx : IFLVectorTransaction;
begin
lStore := TStorageTableStore.Open('customers.fls'); // encrypted: Open(FileName, KeyMaterial)
lStore.EnsureTable(TFLModel<TCustomer>.Schema);
lCustomer := Default(TCustomer);
lCustomer.GUID := 'G1';
lCustomer.Name := 'Miller';
lCustomer.City := 'Boston';
TFLVectorTable<TCustomer>.Construct(lStore).Create(lCustomer); // autocommit
lCustomer := TFLVectorTable<TCustomer>.Construct(lStore).Read('G1');
lTx := lStore.BeginTransaction;
TFLVectorTable<TCustomer>.Construct(lStore).Update(lCustomer);
lTx.Commit;
// Queries: TFLVectorTable<TCustomer>.Construct(lStore).Query.OrderBy('GUID').ToArray
end;
The same code runs unchanged against the SQLite or MySQL driver. Moving from SQLite to FLStore means changing a single line: the store you open.
Why Drop the SQL Text Layer?
With an SQL driver, every single operation takes a detour. The driver builds an SQL string, the database parses it and plans it, parameters are bound, and the result columns are converted back into record fields. For an application that only stores and retrieves records, all of that is overhead. In FLStore the record passes through a field table that is registered once and goes directly into the row format. Searches are structured conditions, not text.
How little overhead remains shows up in a sampling profile of a single insert with commit (Win64). Roughly three quarters to four fifths of the time is spent in the one WriteFile call that appends the commit to the journal. The engine code itself accounts for about 5%. What is left is essentially the cost of the storage hardware.
Under the Hood
- File layout: one main file, two alternating journals and a double-write area. Data pages are 16 KB and are grouped into segments of 64 pages. B+-tree index pages live in a shared index region at the front of the file.
- Redundant metadata: two header slots and two copies of the segment table. On open, the valid header with the higher sequence number wins.
- One file format everywhere: little endian with fixed sizes, so the same file works on Win32, Win64, Android64 and iOS64.
- Schema evolution for free: each value in a row is stored with its column id and type. A new column gets the next id, and old rows read it as NULL. ADD COLUMN never rewrites a single row.
- Indexes: a primary-key tree plus secondary indexes (
[VectorIndex]), composite ones included. They support equality on the leading columns, ranges and "starts with" on the next one, IN lists, and ORDER BY straight from the index order. - Commits: a commit appends the changed pages to the active journal as delta frames (only the bytes that changed) in one write call. A background thread syncs the journal once per second and copies a full journal into the main file (checkpoint). Because the two journals alternate, a commit never waits for a running checkpoint.
- Crash safety: a killed process loses nothing. A power loss can cost at most roughly the last second, and in both cases the file stays consistent. The double-write area protects against torn pages during a checkpoint.
- Threading: one instance per file and process, and every method runs under the instance lock. A transaction belongs to its thread, and other threads never see uncommitted rows.
Encryption Built In, Not Bolted On
Encryption in FLStore is not a layer on top. It is part of the page format. A page is encrypted wherever it leaves memory: in the main file, in the journal including its delta payloads, and in the double-write area.
- A key per file: every file has its own random 256-bit data key, stored wrapped in the file header. A check value detects a wrong key the moment the file is opened.
- Crash-safe by design: every page version is encrypted with its own unique parameters, and these are never reused, not even across crashes.
- Changing the key costs almost nothing: only the data key in the header is rewrapped, and no page is rewritten. Re-keying a large file takes the same time as re-keying an empty one.
- Only what is read gets decrypted: the engine decrypts in small blocks and touches only the bytes a read actually needs, instead of decrypting whole 16 KB pages.
The Test Setup
All numbers were measured on 18 November 2026 on a real, mid-range Android phone, not on a developer workstation. All configurations run in the same benchmark app, from a single build, through the same TVectorDB layer and on the same data:
| Parameter | SQLite | FLStore |
|---|---|---|
| Device | Samsung Galaxy A15 (SM-A155F), Android 16 / API 36 | |
| Build | Delphi 13.2, Android64, Release | |
| Data | 140,000 rows; Int64 key, 52 text columns of 48 characters each, two secondary indexes; plus a small table for single inserts | |
| Engine | SQLite 3.42.0 (FireDAC, statically linked) | FLStore, current version |
| Encryption | none | measured both off and on |
| Durability | journal_mode=WAL, synchronous=NORMAL: no fsync per commit | no fsync per commit, journal synced about once per second |
| Cache | 32 MB | 32 MB |
| Runs | mean of 4 runs | mean of 2 runs per mode (order: off, on, on, off) |
Each run has three rounds, and the reported value is the median per operation. Both engines are configured for the same durability class: consistent after every crash, with neither forcing an fsync on every commit. The details differ: FLStore limits the window of possible loss to about one second, while SQLite syncs its WAL at checkpoints. SQLite has no built-in encryption, so it appears only unencrypted.
The Numbers
Time per operation in µs (lower is better). The factor in brackets is SQLite divided by FLStore, so a higher factor means FLStore is faster:
| Operation | SQLite (unencrypted) | FLStore (unencrypted) | FLStore (encrypted) |
|---|---|---|---|
| Batch insert (1,000 per transaction), per row | 153.7 | 47.5 (3.2x) | 65.9 (2.3x) |
| Read by key | 283.2 | 63.1 (4.5x) | 81.1 (3.5x) |
| Range of 100 rows | 5,729 | 3,956 (1.4x) | 4,668 (1.2x) |
| Equality via secondary index | 2,414 | 2,015 (1.2x) | 2,130 (1.1x) |
| Full scan, per row | 45.3 | 25.0 (1.8x) | 31.8 (1.4x) |
| Single update with commit (total) | 1,199.2 | 182.8 (6.6x) | 314.7 (3.8x) |
| – median commit | 619.6 | 113.0 (5.5x) | 210.6 (2.9x) |
| – p99 commit | 1,901.4 | 186.0 (10.2x) | 385.8 (4.9x) |
| Single insert with commit (total) | 138.0 | 23.3 (5.9x) | 27.6 (5.0x) |
| – median commit | 100.4 | 21.8 (4.6x) | 25.1 (4.0x) |
| – p99 commit | 292.5 | 59.4 (4.9x) | 74.7 (3.9x) |
| Open database | 1,087 | 499 (2.2x) | 634 (1.7x) |
| File size after close | 178.7 MB | 174.3 MB | 174.3 MB |
Unencrypted against unencrypted, FLStore is faster in every case, between 1.2x and 6.6x, and up to 10x on the p99 commit. Even with every page encrypted, FLStore stays ahead of unencrypted SQLite in every case, between 1.1x and 5.0x. The biggest advantage shows up where interactive applications actually spend their time: small transactions with their own commit. The tail latency matters most for a responsive UI. Even encrypted, the p99 commit of a single update is 0.39 ms with FLStore, compared with 1.9 ms for SQLite.
The slowest single commit per round (at a journal switch or checkpoint) is a one-off event that varies a lot from run to run. It is therefore not listed in the table; median and p99 are the reliable figures. For the record: the slowest single update commit stayed below 9 ms in all FLStore runs, against roughly 80 to 90 ms for SQLite. The slowest single insert commit was 0.25 ms unencrypted and 0.36 ms encrypted, against 30 to 41 ms for SQLite.
What Encryption Costs
Comparing the two FLStore columns shows the price of encryption directly. It is highest on writes: the median commit of a single update goes from 113 µs to 211 µs, and batch inserts from 48 µs to 66 µs per row. On reads it is under 30% (63 µs to 81 µs per read by key), thanks to decrypting only what is actually read. The file size is identical with and without encryption.
For Reference: SQLite With Default Settings
The table above uses a tuned SQLite (synchronous=NORMAL, 32 MB cache), because that puts it in the same durability class as FLStore. An application that simply opens SQLite with the defaults of the FireDAC build gets different commit times. Same device, same benchmark, two further runs:
| Operation (µs) | SQLite, defaults | SQLite, WAL + NORMAL + 32 MB |
|---|---|---|
| Batch insert, per row | 157.5 | 153.7 |
| Read by key | 281.2 | 283.2 |
| Full scan, per row | 45.2 | 45.3 |
| Single update, median commit | 3,319.9 | 619.6 |
| Single update, p99 commit | 4,523.9 | 1,901.4 |
| Single insert, median commit | 421.4 | 100.4 |
Reads, scans and batch inserts are practically unchanged (within ±2%), so the larger cache brings nothing measurable at this data size. Single commits, however, are 4.2x to 5.4x slower at the median with the defaults. The timings are consistent with an fsync on every commit, which is a stricter durability class than FLStore's. These numbers show what an application gets out of the box, but they are not an equivalent comparison. That is why our headline figures use the tuned configuration.
How We Tested: The Part That Matters Most
Writing your own storage engine is easy to claim and hard to trust. That is why the speed figures are the smaller half of this post. A database is only worth using if it gives back exactly what was committed, after every kind of failure. Here is what stands behind FLStore.
1. Correctness first: 575 engine tests, green on Win32 and Win64. The DUnitX suite of the storage engine covers the file format, header A/B fallback, damaged tables and pages (CRC errors, wrong page numbers), journal scans with a torn or damaged tail, open transactions, recovery after a killed process, double-write, the background checkpoint, encryption (including known-answer tests against the published reference vectors), backup and restore, and multiple threads and handles on one file. All 575 tests pass on both Win32 and Win64.
2. Fault injection: a crash at every single write. We don't test "a crash somewhere". Dedicated tests simulate a crash at every write operation: the simulated crash writes only part of the data and aborts, the file is reopened, and the test checks that exactly one committed state is present. This runs for the commit path, the background checkpoint and the first write after open, both unencrypted and encrypted.
3. Red-green proof for every fix. A test that passes proves nothing if it would also have passed without the fix. For every bug fix, our tooling (delphi__red_green_check) reverts the production change in a throwaway copy, keeps the new test and runs the build. The test must turn red without the fix and green with it. Otherwise it does not test what it claims to test.
4. Tested in a real application. The first production application on FLStore brings its own suite of 925 DUnitX tests, a large share of which exercise its repositories on top of the engine. They check that IDs are never handed out twice (not even after a Clear), that deletes really delete, that key changes work, that NULL stays distinct from 0.00, and that everything survives a reopen.
5. A fair benchmark, by construction. The benchmark app (an FMX program) measures all configurations in the same process, one after the other on a background thread. The order alternates every round, so no configuration always gets the cooler device. The input is identical for all: a fixed random seed and a prebuilt text pool. Timing uses TStopwatch, and the report gives the median per operation, plus median, p99 and maximum of the individual commits for the commit cases. Every round starts on a fresh file, the screen is kept on, and partial results are written after each round so that an aborted run loses nothing.
6. SQLite as the control group. SQLite runs in every pass. Because its code never changes, it serves as a control: if its numbers drift between runs, the device is the cause and the run is not valid. Engine optimizations are verified in A–B–B–A order with a fresh install for every run, so that a warm or cool device cannot fake a gain.
7. Verify the result, not just the clock. After reopening, the benchmark checks that the file really contains all rows. If anything does not add up, the run aborts instead of printing an attractive number. A benchmark that stops is worth more than one that flatters.
8. Clean conditions or no numbers. Measurements are only accepted on an idle machine, and the background load is logged for every run. Measurements taken under load are discarded and repeated. Each optimization was found with a sampling profiler (one sample every 250 µs, resolved through the map file) and then measured on its own.
9. Long enough to see the truth. Effects that involve the background checkpoint only show up in long runs. 10,000 single updates were not enough to make one optimization visible; with 100,000 updates it was clear (168 → 106 µs per update). Short benchmarks can hide exactly the behaviour that matters in production.
Tooling
FLStore was developed with our own Delphi infrastructure: the FDK for the model attributes, nullable types and base classes, and a backend with 33 MCP servers. DelphiMCP builds every target (Win32, Win64, Android64, plus the benchmark app against any engine version), runs the unit tests and performs the red-green check. PlanMCP (MySQL-backed) keeps tasks and decisions, and hgMCP handles version control. Development and on-device measurement ran in parallel and were coordinated agent-to-agent. A complete benchmark series on real hardware takes about an hour, with no manual build or deployment steps.
Conclusion
If an application stores records rather than running ad-hoc SQL, the SQL text layer is a cost it pays on every call. FLStore removes that layer. In the same durability class it is up to 6.6x faster than SQLite, and with every page encrypted it is still up to 5.0x faster than unencrypted SQLite, with slightly smaller files and a much shorter commit tail. Because it sits behind the TVectorDB interface, switching is a one-line change.
Numbers like these are only worth something if you can trust how they were made. Hundreds of tests, a simulated crash at every write, red-green proof for every fix, alternating run orders with SQLite as the control group, and clean measuring conditions: that is the foundation FLStore stands on.
Note on the Use of AI: This article was created with the "assistance" of generative AI. The content, technical statements, and conclusions have been reviewed and revised by the author. The author bears full responsibility for the publication.

No comments:
Post a Comment