Immich uses postgres because it relies on VectorChord for smart search.

It's what powers the ability to search your photos for "a dog on a beach".

It's also a fairly sensible decision.

There exists https://github.com/asg017/sqlite-vec

And for small enough vector sets, holding them in memory is reasonable. Most personal photo collections would fit that scale.