12 ms·

This makes a strong case, but I've decided to start every new project with sqlite and not switch until absolutely necessary. If Postgres is the 90% case, then s

This makes a strong case, but I've decided to start every new project with sqlite and not switch until absolutely necessary. If Postgres is the 90% case, then sqlite is the 80% case and is also dead simple to get going and genuinely performant. So when vertical scaling finally fails me, I know I'll be at a wonderful place with what I'm building.

turnsout 3y ago
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?
- 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...
Ensorceled 3y ago
> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant. I don't understand this. PostgreSQL is ALSO dead simple to get going, either locally or in production. Why not just start off at 90%? I mean, I get there are a lot of use cases where sqlite is the better choice (and I've used sqlite multiple times over the years, including in my most recent gig), but why in general?
- EasyMark 3y ago
  it's much bigger and requires running a server, that's why I use sqlite3, and my needs are 99% modest most of the time.
  - zilti 3y ago
    It requires running a separate process.
- tvink 3y ago
  I think "dead simple" is not doing anyone any favors when it is being used to try to equate the simplicity of things. It's obviously a lot simpler to just have a file, than to have a server that needs to be connected to, as long as we're still talking about running things on regular computers.
  - Ensorceled 3y ago
    I guess that's really my point here. They difference in setup time is negligible so I'm not sure why people keep bringing it up as a reason to choose sqlite over PostgreSQL. For instance, "deployable inside a customer application" is an actual requirement that would make me loath to pick PostgreSQL. "Needs to be accessible, with redundancy, across multiple AWS zones" would make me very reluctant to pick sqlite. Neither of these decisions involve how easy it is to set up. It's like choosing between a sportbike and dump truck and focusing on how easy the sportbike is to haul around in the back of a pickup truck.
    - 3y ago
neovim_btw 3y ago
Not with concurrent writes, you're not! Even with a WAL or some kind of homegrown spooling, you're going to be limited by the rate at which one thread can ingest that data into the database. One could always shard across multiple SQLite databases, but are you going to scale the number of shards with the number of concurrent write requests? If not, SQLite won't work. And if you do plan on this, you're in for a world of headaches instead of using a database that does concurrency on its own. Don't get me wrong; SQLite is great for a lot of things. And I know it's nice to not have to deal with the "state" of an actual database application that needs to be running, especially if you're not an "infrastructure" team, but there's good reasons they're ubiquitous and so highly regarded.
- emseetech 3y ago
  It’s effortless to get 2-4K writes per second with SQLite on cheap commodity hardware. That will carry most early stage applications really far.

turnsout 3y ago

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?
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...

Ensorceled 3y ago

> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant. I don't understand this. PostgreSQL is ALSO dead simple to get going, either locally or in production. Why not just start off at 90%? I mean, I get there are a lot of use cases where sqlite is the better choice (and I've used sqlite multiple times over the years, including in my most recent gig), but why in general?

EasyMark 3y ago
it's much bigger and requires running a server, that's why I use sqlite3, and my needs are 99% modest most of the time.
- zilti 3y ago
  It requires running a separate process.
tvink 3y ago
I think "dead simple" is not doing anyone any favors when it is being used to try to equate the simplicity of things. It's obviously a lot simpler to just have a file, than to have a server that needs to be connected to, as long as we're still talking about running things on regular computers.
- Ensorceled 3y ago
  I guess that's really my point here. They difference in setup time is negligible so I'm not sure why people keep bringing it up as a reason to choose sqlite over PostgreSQL. For instance, "deployable inside a customer application" is an actual requirement that would make me loath to pick PostgreSQL. "Needs to be accessible, with redundancy, across multiple AWS zones" would make me very reluctant to pick sqlite. Neither of these decisions involve how easy it is to set up. It's like choosing between a sportbike and dump truck and focusing on how easy the sportbike is to haul around in the back of a pickup truck.
  - 3y ago

neovim_btw 3y ago

Not with concurrent writes, you're not! Even with a WAL or some kind of homegrown spooling, you're going to be limited by the rate at which one thread can ingest that data into the database. One could always shard across multiple SQLite databases, but are you going to scale the number of shards with the number of concurrent write requests? If not, SQLite won't work. And if you do plan on this, you're in for a world of headaches instead of using a database that does concurrency on its own. Don't get me wrong; SQLite is great for a lot of things. And I know it's nice to not have to deal with the "state" of an actual database application that needs to be running, especially if you're not an "infrastructure" team, but there's good reasons they're ubiquitous and so highly regarded.

emseetech 3y ago
It’s effortless to get 2-4K writes per second with SQLite on cheap commodity hardware. That will carry most early stage applications really far.