Instead of storing "metadata" in a separate regular SQLite table, you should be able to store non-vector columns like integers, text, and dates inside the vss0 virtual table like so:
create virtual table vss_articles using vss0(
headline_embeddings(384),
description_embeddings(384),
published_at date,
newsroom text
);
That way you don't need to do weird JOINs to supplement your vector queries. Could also be used to simplify pre-filtering.
things to consider:
Constructor needs to be able to distinguish between non-vector and vector columns
Probably need to limit the allowed column type names, probably only TEXT/INTEGER/DATETIME etc.
can extend the current *_data shadow table
Other notes:
store non-vector columns in a shadow strict table if in 3.37.0. Or maybe as an option?
Automatic indexes on data shadow table
Capture additional constraints on non-vector columns and use in pre-filters on KNN queries
Maybe store in some bespoke olap-stle columnar format?
Spinoff of #2.
Instead of storing "metadata" in a separate regular SQLite table, you should be able to store non-vector columns like integers, text, and dates inside the
vss0virtual table like so:That way you don't need to do weird
JOINsto supplement your vector queries. Could also be used to simplify pre-filtering.things to consider:
TEXT/INTEGER/DATETIMEetc.*_datashadow tableOther notes:
3.37.0. Or maybe as an option?