Prisma example can mislead if it doesn't support an index

[canadaduane]

I believe Prisma is currently incapable of supporting pgvector indices, which means the examples are more of a toy and could mislead people into thinking that Prisma and pgvector will work in production. Assuming a developer also needs prisma to handle database migrations, Prisma and pgvector do not get along.

Specifically:

• after manually adding a pgvector index to the database (such as hnsw), Prisma will helpfully remove it the next time you migrate, because it detects schema drift (see https://github.com/prisma/prisma/issues/8864)

Other friction:

• the pgvector extension is not among the supported extensions shortlist (this can be worked around via CREATE EXTENSION pgvector)
• the vector(N) column type is not supported in the schema.prisma file (this can be worked around via Unsupported(...), however, it means using queryRaw or queryRawUnsafe which is both an inconvenience and potentially a security risk)

Prisma engineers have declined to add features that will support this use case because their efforts and focus are elsewhere. In the meantime, I think it would be helpful to future developers to have a large warning explaining this constraint, something like:

NOTE: Prisma and pgvector should not be used in production, since indices are not supported as part of the regular schema migration process, and will be removed automatically by Prisma.

[ankane]

Hi @canadaduane, added a note about indexes in the commit above. fwiw, it's fine to use pgvector without indexes. You can also add indexes to your production environment directly.

[canadaduane]

fwiw, it's fine to use pgvector without indexes.

Can you expand on what you mean here?

My current understanding is more in line with this statement from supabase [1]:

"Without indexes, pgvector performs a full table scan when you run a similarity query. This means distance has to be computed against every row in your table. This is manageable at a small scale but becomes problematic as your table grows."

[1] https://supabase.com/blog/fewer-dimensions-are-better-pgvector