pgvecto.rs 0.4.0 support

[diogotcorreia]

Hey,

Opening this discussion just to track support for the newly released pgvecto.rs 0.4.0.

We're in the process of updating the extension in Nixpkgs.

Release notes don't appear to mention any breaking changes, but the release PR mentions a possible upgrade failure: tensorchord/pgvecto.rs#615
Hopefully support for this is as painless as for 0.3.0.

[zackpollard]

Hey, thanks for being proactive on this. I'll leave the decision down to @mertalev but my understanding is that we aren't going to bump to 0.4.0 for pgvecto.rs due to the same team releasing the vectorchord which is better in a number of ways. They expect to suggest people migrate from pgvecto.rs to vectorchord by the end of the year, so I expect we will wait for that 🙂

https://github.com/tensorchord/VectorChord

[mertalev]

We can support 0.4.0 once they fix the upgrade issues with the release.

[dotlambda]

Weren't those fixed by tensorchord/pgvecto.rs#617, which is included in 0.4.0?

[diogotcorreia]

@dotlambda pgvecto.rs 0.4.0 was re-released 3 times, at the time of this comment it hadn't been fixed iirc (they should've just made it a new patch release, but 🤷‍♂️ )

I'm mostly worried about tensorchord/pgvecto.rs#616 (comment), but I think Immich tries to recreate the index if it fails to upgrade. I'm testing it now since pgvecto.rs 0.4.0 was accidentally merged into nixpkgs, so we have to either test Immich now and patch it or revert the pgvecto.rs upgrade.

Just had a problem with my build "server" so I'm waiting for someone to physically restart it. 😅

[mertalev]

It doesn't drop the indices before updating, just reindexes them after. Relevant code

[diogotcorreia]

Okay, so I finally have managed to test the upgrade path with an external DB.

As expected, the direct upgrade doesn't work with the same error as in the comment linked in my previous comment. The solution is to delete the indexes:

DROP INDEX clip_index;
DROP INDEX face_index;
ALTER EXTENSION vectors UPDATE;
SELECT pgvectors_upgrade();

I was expecting Immich to recreate the indexes, but it's not detecting that a reindex is needed because it doesn't exist (?):

immich=# SELECT idx_status FROM pg_vector_index_stat WHERE indexname = 'clip_index';
 idx_status
------------
(0 rows)

immich/server/src/repositories/database.repository.ts

Line 148 in 6d49298

return res[0]?.['idx_status'] === 'UPGRADE';

The autofix wouldn't work either way because this query also returns empty:

immich/server/src/repositories/database.repository.ts

Line 170 in 6d49298

const tableQuery = `SELECT relname FROM pg_stat_all_indexes WHERE indexrelname = $1`;

So basically to support 0.4.0 we need to find a less destructive way to get rid of the indexes or have Immich recreate them if it detects they don't exist anymore.

cc @mertalev hope that helps!