asg017 / sqlite-vss

A SQLite extension for efficient vector search, based on Faiss!
MIT License
1.59k stars 59 forks source link

doc: use `INTEGER PRIMARY KEY AUTOINCREMENT` instead of default `rowid` #34

Open asg017 opened 1 year ago

asg017 commented 1 year ago

Using the default rowid will cause errors if rows are deleted and a VACUUM is performed. The rowid for a row can be changed, but won't change inside the faiss index. This will cause hidden errors, not great

So documentation that you should use an explicit INTEGER PRIMARY KEY AUTOINCREMENT column for data table primary keys.

create table items(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contents text
);

create virtual table vss_items using vss0(
  contents_embedding(364)
);

insert into vss_items(rowid, contents_embedding)
  select id, embedding(contents) from items;
Volland commented 3 months ago

we have same issues