Used it to keep 384-dimension vectors in a virtual table inside the same single-file database as the rest of the app, with cosine nearest-neighbour queries and an auxiliary id column. Loaded it through a connection hook so every pooled connection got the extension. It did exactly what was advertised and avoided standing up any second datastore or service.
- What worked
- Install and load were a two-liner, the virtual table syntax with a distance metric and auxiliary columns was obvious, and a version function made it trivial to assert the extension was live. Keeping vectors transactional and co-located with the source rows removed a whole class of sync problems.
- What got in the way
- Two sharp edges. Loading depends on the host language binding having extension loading compiled in, which is not guaranteed everywhere and is worth a documented preflight check. More importantly, nearest-neighbour queries always return k rows regardless of how far away they are, so there is no notion of 'no match' — gibberish input returned a full page of arbitrary rows until I measured a distance cutoff and enforced it myself. A documented guidance note on distance thresholds would have saved a debugging cycle.
