5 ms·

I’m with you in general, but what about vector search? It really feels like the DB industry has taken a huge step backward from the promise of SQL. Switching f

I’m with you in general, but what about vector search? It really feels like the DB industry has taken a huge step backward from the promise of SQL. Switching from Postgres to SQLite is easy because the underlying queries are at least similar. But as soon as you introduce embeddings, every system is totally different (and often changing rapidly).

drittich 3y ago
Vector similarity searches can be done with simple SQL statements, and while the performance will not match a vector db, it's often good enough. For a db like SQLite that can run in-memory, I suspect they would be reasonably fast.
emseetech 3y ago
There are vector search solutions for sqlite that basically work, so if my project doesn't rely on that 100% then I'm willing to use those as stop-gaps until it does. Of course, if there's a shortcoming of sqlite that I know I need right out of the gate, that would be a situation where I start with postgres.
codingjaguar 3y ago
SQL was introduced in 1970s. Considering vector search was only adopted in the last 5 years, I’m not surprised by the lack of standards on vector API. At Google embedding as retrieval became popular in 2019-2020. This is the new kid in town so you would see soon all major SQL dbs will support vector. However, any serious user, O(10M) vectors or above, would still require a dedicated vector db for performance reasons.
randomdata 3y ago
Just use SQLite? Specialized vector indexes become important when you have a large number of vectors, but the reality of software is that it is unlikely that your application will ever be used at all, let alone reach a scale where you start to hurt. Computers are really fast. You can go a long way with not-perfectly-optimized solutions. Once you have proven that users actually want to use your product and see growth on the horizon to where optimization becomes necessary, then you can swap in a dedicated vector solution as needed, which may include using a vector plugin for SQLite. The vector databases you want to use may or may not use SQL, but the APIs are never that much different. Instead of one line of SQL to support a different implementation you might have to update 5 lines of code to use their API, but we're not exactly climbing mountains here. Know your problem inside and out before making any technical choices, of course.
- turnsout 3y ago
  You can of course use a vanilla database, read every row and just roll your own vector distance function, but it's just frustrating that there isn't a standardized pattern for this. There are plenty of proprietary databases and APIs, but now you're taking on a dependency and assuming a certain amount of risk.
  - randomdata 3y ago
    > it's just frustrating that there isn't a standardized pattern for this. Be the change you want to see, I suppose. No doubt convergence will come, but it is still early days. Six months ago, most developers didn't even know what a vector database is, let alone consider it something to add to their stack. It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. Even PostgreSQL itself was started under the name POSTGRES and was designed to use QUEL, only moving over to SQL much later in life when it was clear that was the way things were going. These things can take time.
    - dventimi 3y ago
      > It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. IBM had SQL in their database product in 1981, Oracle had it by v4 in 1984, ANSI picked SQL as its standard that same year, and completed the first version by 1986. Some time scientists say that the 1980s occurred before "well into the 1990s" but I mean, who can really say, right?
      - randomdata 3y ago
        "The standard", not "a standard". I know, reading is hard.
        dventimi 3y ago
        > I know, reading is hard. We're done here.
deleted 3y ago
[deleted]
nycodez 3y ago
Postgres has a vector search extension! https://supabase.com/docs/guides/database/extensions/pgvector https://supabase.com/docs/guides/database/extensions/pgvecto...

drittich 3y ago

Vector similarity searches can be done with simple SQL statements, and while the performance will not match a vector db, it's often good enough. For a db like SQLite that can run in-memory, I suspect they would be reasonably fast.

emseetech 3y ago

There are vector search solutions for sqlite that basically work, so if my project doesn't rely on that 100% then I'm willing to use those as stop-gaps until it does. Of course, if there's a shortcoming of sqlite that I know I need right out of the gate, that would be a situation where I start with postgres.

codingjaguar 3y ago

SQL was introduced in 1970s. Considering vector search was only adopted in the last 5 years, I’m not surprised by the lack of standards on vector API. At Google embedding as retrieval became popular in 2019-2020. This is the new kid in town so you would see soon all major SQL dbs will support vector. However, any serious user, O(10M) vectors or above, would still require a dedicated vector db for performance reasons.

randomdata 3y ago

Just use SQLite? Specialized vector indexes become important when you have a large number of vectors, but the reality of software is that it is unlikely that your application will ever be used at all, let alone reach a scale where you start to hurt. Computers are really fast. You can go a long way with not-perfectly-optimized solutions. Once you have proven that users actually want to use your product and see growth on the horizon to where optimization becomes necessary, then you can swap in a dedicated vector solution as needed, which may include using a vector plugin for SQLite. The vector databases you want to use may or may not use SQL, but the APIs are never that much different. Instead of one line of SQL to support a different implementation you might have to update 5 lines of code to use their API, but we're not exactly climbing mountains here. Know your problem inside and out before making any technical choices, of course.

turnsout 3y ago
You can of course use a vanilla database, read every row and just roll your own vector distance function, but it's just frustrating that there isn't a standardized pattern for this. There are plenty of proprietary databases and APIs, but now you're taking on a dependency and assuming a certain amount of risk.
- randomdata 3y ago
  > it's just frustrating that there isn't a standardized pattern for this. Be the change you want to see, I suppose. No doubt convergence will come, but it is still early days. Six months ago, most developers didn't even know what a vector database is, let alone consider it something to add to their stack. It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. Even PostgreSQL itself was started under the name POSTGRES and was designed to use QUEL, only moving over to SQL much later in life when it was clear that was the way things were going. These things can take time.
  - dventimi 3y ago
    > It took SQL well into the 1990s to fully solidify itself as "the standard" for relational querying. IBM had SQL in their database product in 1981, Oracle had it by v4 in 1984, ANSI picked SQL as its standard that same year, and completed the first version by 1986. Some time scientists say that the 1980s occurred before "well into the 1990s" but I mean, who can really say, right?
    - randomdata 3y ago
      "The standard", not "a standard". I know, reading is hard.
      - dventimi 3y ago
        > I know, reading is hard. We're done here.

deleted 3y ago

[deleted]

nycodez 3y ago

Postgres has a vector search extension! https://supabase.com/docs/guides/database/extensions/pgvector https://supabase.com/docs/guides/database/extensions/pgvecto...