Update PGVector Data

[j4care-christianhahn]

Hi !

I'm using Sprint AI with PGVector to index my product documentation to provide a support bot. The product documentation is chaning every X weeks. What is the best approach to update the pgvector ? If I delete everything and reindex it takes quite some time and also the tokens that I need from OpenAI are quite high. Is there some easier way to keep it updated ?

[iamroberthanson]

This seems related to my question in #452.

I don't know if your case is the same, but in my case when an "article" is updated I only want to redo the embeddings for only that article.

One way to do it is to add a source key to the metadata of the Document, which gets stored in a json column type in the DB, and then you can use postgres JSON queries to fetch/delete only those records by querying on the data inside of the json column. The issue is that the OOTB implementation used json and not jsonb, so this will not be performant if you have a lot of rows.

I opted to have a second table that connects my source documents to the individual records in the records in the vector_store table.

For me this table looks like this:

create table public.jive_passages
(
    id        uuid default uuid_generate_v4() not null
        primary key,
    contentid varchar(255)                    not null,
    vectorid  uuid                            not null,
    sequence  integer                         not null
);

So when I update an "article", I look up all of the Documents related to that content ID in my jive_passages table, and then I delete only those records from vector_store... and then I replace them with new embeddings based on the content in the updated article.