.Net MEVD: Clarify thread-safety of the abstraction

[roji]

Most implementation of the abstraction provide thread-safety, i.e. the instances of IVectorStore and IVectorStoreRecordCollection can be used concurrently from multiple threads; but not all.

1. PostgresVectorStoreRecordCollection accepts an NpgsqlDataSource, and gets a new (pooled) connection for aech operation. This is a thread-safe design, where multiple threads can use PostgresVectorStoreRecordCollection concurrently.
2. In contrast, SqliteVectorStoreRecordCollection gets initialized with a DbConnection, which is not a thread-safe object.

IMHO we should be guaranteeing thread-safety, and fix the SQLite implementation to e.g. accept a connection string (or DbDataSource) instead of a DbConnection.

[dmytrostruk]

I'm wondering, if we change SQLite implementation to accept a connection string, it means that we will have to create a new instance of DbConnection on our side. It will probably force us to make SqliteVectorStoreRecordCollection as IDisposable. I don't think it's a problem, but it won't be aligned with other connectors. However, thread-safety would be more important than API alignment.

If we do that, does it mean that we won't recommend registering SqliteVectorStoreRecordCollection as Singleton in user applications to avoid long-lived connections?

[roji]

It will probably force us to make SqliteVectorStoreRecordCollection as IDisposable

I'm not sure - I was thinking we'd create (and open) a new SqliteConnection for each operation. After all, that's what the PostgreSQL provider does too - it just does it via NpgsqlDataSource.

[dmytrostruk]

I'm not sure - I was thinking we'd create (and open) a new SqliteConnection for each operation.

In that case we should be good. I didn't know about the existence of DbDataSource. If it's working with SQLite, then I guess it should be a good choice.