# SQLite Geo PoC: measured benchmark

Run: 2026-09-16T17:59:22.621545+00:00

Machine: macOS-26.3.1-arm64-arm-64bit-Mach-O; Apple M4. SQLite 3.51.3; 1,000,000 points; 64 MiB SQLite page cache per engine.

Redis comparator: Redis server v=8.2.9 sha=00000000:1 malloc=libc bits=64 build=516af531ee24aeba

## Native engine (no socket or Python)

| Workload | Method | Samples | p50 ms | p95 ms | p99 ms | Mean candidate points |
|---|---|---:|---:|---:|---:|---:|
| city_5km | best_first | 2000 | 0.112 | 2.698 | 3.900 | 20.0 |
| city_5km | bounding_cube | 200 | 8.562 | 10.778 | 12.420 | 9925.6 |
| city_5km | full_scan | 20 | 133.435 | 149.922 | 174.759 | 1000000.0 |
| city_50km | best_first | 2000 | 0.117 | 0.205 | 0.313 | 20.0 |
| city_50km | bounding_cube | 200 | 78.011 | 88.727 | 92.848 | 99988.8 |
| city_50km | full_scan | 20 | 131.632 | 141.903 | 148.901 | 1000000.0 |
| global_100km | best_first | 2000 | 0.029 | 0.045 | 0.109 | 12.4 |
| global_100km | bounding_cube | 200 | 0.028 | 0.038 | 0.045 | 18.7 |
| global_100km | full_scan | 20 | 126.380 | 132.553 | 141.393 | 1000000.0 |
| global_5000km | best_first | 2000 | 0.052 | 0.111 | 0.249 | 20.0 |
| global_5000km | bounding_cube | 200 | 113.656 | 421.254 | 457.218 | 207425.5 |
| global_5000km | full_scan | 20 | 131.053 | 139.749 | 140.891 | 1000000.0 |

## TCP, one client, nearest 20 with distances

| Workload | Engine | p50 ms | p95 ms | p99 ms | Requests/sec | Mean matches |
|---|---|---:|---:|---:|---:|---:|
| city_5km | SQLite FULL | 0.163 | 0.235 | 0.308 | 5,856 | 20.0 |
| city_50km | SQLite FULL | 0.177 | 0.243 | 0.335 | 5,553 | 20.0 |
| global_100km | SQLite FULL | 0.087 | 0.137 | 0.194 | 10,377 | 12.5 |
| global_5000km | SQLite FULL | 0.111 | 0.169 | 0.244 | 8,433 | 20.0 |
| city_5km | Redis (no persistence) | 2.997 | 4.068 | 5.056 | 334 | 20.0 |
| city_50km | Redis (no persistence) | 14.010 | 14.947 | 19.625 | 70 | 20.0 |
| global_100km | Redis (no persistence) | 0.060 | 0.082 | 0.112 | 12,397 | 12.5 |
| global_5000km | Redis (no persistence) | 91.800 | 108.087 | 117.994 | 11 | 20.0 |

## Pipeline throughput

16 requests per batch, same city_5km queries. Latency here is for a complete batch, not an individual request.

- SQLite FULL: 6,910 requests/sec; batch p95 4.002 ms.
- Redis (no persistence): 363 requests/sec; batch p95 47.790 ms.

## Concurrent traffic

Four reader connections and one writer connection; 250 requests per client. Updates change points in the queried collection. The PoC dispatches commands serially in one process. Values include client scheduling and queueing.

| Engine / durability | Read p50 ms | Read p95 ms | Write p50 ms | Write p95 ms | Total ops/sec |
|---|---:|---:|---:|---:|---:|
| SQLite FULL | 0.364 | 0.777 | 0.583 | 0.913 | 9,214 |
| Redis AOF always | 15.212 | 18.120 | 15.188 | 18.052 | 323 |
| SQLite NORMAL | 0.362 | 0.753 | 0.511 | 0.858 | 10,447 |
| SQLite FULL + F_FULLFSYNC | 4.699 | 9.019 | 6.020 | 9.795 | 776 |

## Native updates

200 transactions per case, changing coordinates in a separate 1,000-member collection. Batch construction is excluded from transaction latency and included in throughput.

| Durability | Points/transaction | p50 ms | p95 ms | Points/sec |
|---|---:|---:|---:|---:|
| full | 1 | 0.052 | 0.072 | 16,059 |
| full | 100 | 1.040 | 1.244 | 93,613 |
| normal | 1 | 0.026 | 0.035 | 27,743 |
| normal | 100 | 1.001 | 1.294 | 94,794 |

## Storage and ingestion

- Initial load: 121.03 seconds; 8,262 points/sec. Includes synthetic generation, FULL commits in 1,000-point batches, and final checkpoint.
- Database after native update tests: 208.0 MiB.
- Native benchmark process peak RSS: 83.6 MiB; this includes ingestion and all methods.

## Redis result cross-check

200 shared queries; 0 top-20 membership differences. Maximum distance difference among common members: 0.301 m. Redis quantizes coordinates; the PoC preserves input doubles. Independent Haversine correctness tests are separate from this cross-check.

## Method and limits

- Reproducible synthetic dataset: 80% clustered around eight cities, 20% broadly distributed; seed 20260916. Not production traffic.
- Native baselines share query prefixes. Best-first has 2,000 samples by default, bounding-cube 200, full scan 20; p99 from 20 samples is descriptive, not a reliable tail estimate.
- Native and TCP workloads use different PRNG implementations. Both TCP engines receive identical commands and exact same source coordinates. Compare within each section.
- Warm cache measurements; no result cache. Process/OS cold starts and sustained saturation are not measured. The full database need not fit in SQLite's configured cache.
- TCP includes the identical Python client's encoding, socket I/O, and response decoding; this is end-to-end observed throughput, not maximum server capacity.
- Redis read-only comparisons disable AOF and snapshots. SQLite remains disk-backed. Read results do not establish equivalent write durability.
- On macOS, Redis AOF always uses F_FULLFSYNC; SQLite FULL uses ordinary fsync by default. Those default rows are NOT equivalent durability comparisons. The separately measured SQLite FULL + F_FULLFSYNC row, when present, enables fullfsync and checkpoint_fullfsync for the closer comparison. Neither is a hardware power-failure certification.
- SQLite NORMAL can lose recent acknowledged writes after power loss. SIGKILL recovery was tested with FULL; actual power loss was not simulated.
- Nearest 20 allows early stopping. Large returned result sets, polygons, arbitrary metadata filters, multiple hosts, and huge numbers of collections are outside this benchmark.
- No CPU affinity, isolated machine, repeated-run confidence intervals, or open-loop arrival generator. Other applications and coordinated omission can affect the tails.

Raw timings and environment: `benchmark.json` and `native.json` beside this report.