5 ms·

How does SQLite fare against PostgreSQL for this particular use case?

by rane 3y ago

shortrounddev2 3y ago
Why do I see so much discussion about SQLite on HN? I've literally never seen it used in production, my usage of it is purely as a database for local testing
- bshipp 3y ago
  Sqlite is extremely powerful for any locally-embedded databases, such as apps with an offline component. For example, searching text in notes, books, cached news, etc. It's very simple to implement inside Sqlite with much less overhead than including a Postgres/Mysql instance.
- klysm 3y ago
  It’s used in production quite a bit, probably just not in your field as much. Anytime I’m thinking about storing something in a file I use SQLite instead
- TX81Z 3y ago
  Lol, you’re using it “in production” right now! SQLite is everywhere, almost definitely in the browser you’re using to view this post.
  - shortrounddev2 3y ago
    I guess it's a difference in industry. Everything I do is accessed over a network, and for caching we use simpler key-store libraries rather than relational databases. I don't develop client software
    - TX81Z 3y ago
      Oh I don’t currently have it in my stack per se, more that it is all over the damn place and can do a ton.
- samwillis 3y ago
  With the move to the "edge" for compute, having your database at the edge reduces latency and basically makes it viable. So the theory is that SQLite read replicas will be the answer to a lot of these plans. See what Fly and Cloudflare are investing in with the edge network and SQLite plans. On top of that SQLite is now viable in the browser with WASM and the "origin privet file system" api. And so people are increasing looking at moving more state and persistence to the browser with an eventually consistent sync. This is what the "Local First" movement, myself included, are excited about.
  - vmfunction 3y ago
    It is what Deno is doing, by putting what traditionally known as the middleware on the edge with a db (localStore). Essentially emulating the browser stack in some ways. Be very interesting to see different kind of code sharing amongst Deno edge nodes and browser.
- Mustachio 3y ago
  We do natural language processing with some custom tokenisation, the data it uses at runtime is put into an sqlite db at build-time. Works fine for our usecase.
- 0xbadcafebee 3y ago
  HN has fads like everything else. If this were 10 years ago they'd be asking about MongoDB. 5 years ago they'd be asking if the Blockchain could power their search engine. Now they want an AI search engine.
  - appletrotter 3y ago
    I feel like MongoDB was on the wane even 5 years ago.
    - callalex 3y ago
      Also, the interest may not have all been…organic.
  - thewataccount 3y ago
    Sqlite has nothing to do with AI searching in the way I think you're suggesting. It was not designed for "AI usage" or as a "AI database" SQlite is from 2000 and is more comparable to a single file postgres/mysql database. It's also on literally billions of devices including whatever you're reading this on.
  - dgb23 3y ago
    Tech that has stood the test of time is the opposite of a fad.
- thewataccount 3y ago
  Put simply - SQLite is incredibly powerful and simple - it can handle a lot more then people might think - and AFAIK it is the most "used in production" database whether you're aware of it or not. Depending on the use case it can scale incredibly well, and is tiny and battle hardened. It's the most deployed database, it's on every smartphone, "smart device", computer, it's inside of many of your apps, powers many of the websites you use, etc. --- tl;dr - If you're following "use the simplest, but reliable tool for the job" then sqlite is a valid option for a crapload of use cases, most people aren't aware of what it can actually do because it doesn't "web scale". Sqlite isn't new, it's old, and it's "used in production" count is in the literal billions.
- osrec 3y ago
  It is used in a huge number of production environments, including within your browser (most probably). Probably not something you want to run a multi tenant SaaS database with, but it is useful if you are going the one tenant per database route.
- hot_gril 3y ago
  SQLite has been the default store for CoreData in iPhone apps for years afaik. I imagine that's a lot of apps. Of course, CoreData sucks, but that's for unrelated reasons.
rahkiin 3y ago
There is https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html but I cannot deduce if it has the same features; the way of working is completely different
bshipp 3y ago
I've had good luck using FTS5 in Sqlite for small-scale use scenarios, such as examining scraped data, etc. It's remarkably powerful and quick. https://www.sqlitetutorial.net/sqlite-full-text-search/ https://www.sqlitetutorial.net/sqlite-full-text-search/ https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html
- anitil 3y ago
  I put together a demo of this for work where I pulled all our private repos and built an index, then used datasette as the front end. It was incredibly fast, so much so that I found it more useful than github search. The index is pretty out of date now but I still use it purely based on convenience and speed

How does SQLite fare against PostgreSQL for this particular use case?

by rane 3y ago

How does SQLite fare against PostgreSQL for this particular use case?

Why do I see so much discussion about SQLite on HN? I've literally never seen it used in production, my usage of it is purely as a database for local testing

bshipp 3y ago
Sqlite is extremely powerful for any locally-embedded databases, such as apps with an offline component. For example, searching text in notes, books, cached news, etc. It's very simple to implement inside Sqlite with much less overhead than including a Postgres/Mysql instance.
klysm 3y ago
It’s used in production quite a bit, probably just not in your field as much. Anytime I’m thinking about storing something in a file I use SQLite instead
TX81Z 3y ago
Lol, you’re using it “in production” right now! SQLite is everywhere, almost definitely in the browser you’re using to view this post.
- shortrounddev2 3y ago
  I guess it's a difference in industry. Everything I do is accessed over a network, and for caching we use simpler key-store libraries rather than relational databases. I don't develop client software
  - TX81Z 3y ago
    Oh I don’t currently have it in my stack per se, more that it is all over the damn place and can do a ton.
samwillis 3y ago
With the move to the "edge" for compute, having your database at the edge reduces latency and basically makes it viable. So the theory is that SQLite read replicas will be the answer to a lot of these plans. See what Fly and Cloudflare are investing in with the edge network and SQLite plans. On top of that SQLite is now viable in the browser with WASM and the "origin privet file system" api. And so people are increasing looking at moving more state and persistence to the browser with an eventually consistent sync. This is what the "Local First" movement, myself included, are excited about.
- vmfunction 3y ago
  It is what Deno is doing, by putting what traditionally known as the middleware on the edge with a db (localStore). Essentially emulating the browser stack in some ways. Be very interesting to see different kind of code sharing amongst Deno edge nodes and browser.
Mustachio 3y ago
We do natural language processing with some custom tokenisation, the data it uses at runtime is put into an sqlite db at build-time. Works fine for our usecase.
0xbadcafebee 3y ago
HN has fads like everything else. If this were 10 years ago they'd be asking about MongoDB. 5 years ago they'd be asking if the Blockchain could power their search engine. Now they want an AI search engine.
- appletrotter 3y ago
  I feel like MongoDB was on the wane even 5 years ago.
  - callalex 3y ago
    Also, the interest may not have all been…organic.
- thewataccount 3y ago
  Sqlite has nothing to do with AI searching in the way I think you're suggesting. It was not designed for "AI usage" or as a "AI database" SQlite is from 2000 and is more comparable to a single file postgres/mysql database. It's also on literally billions of devices including whatever you're reading this on.
- dgb23 3y ago
  Tech that has stood the test of time is the opposite of a fad.
thewataccount 3y ago
Put simply - SQLite is incredibly powerful and simple - it can handle a lot more then people might think - and AFAIK it is the most "used in production" database whether you're aware of it or not. Depending on the use case it can scale incredibly well, and is tiny and battle hardened. It's the most deployed database, it's on every smartphone, "smart device", computer, it's inside of many of your apps, powers many of the websites you use, etc. --- tl;dr - If you're following "use the simplest, but reliable tool for the job" then sqlite is a valid option for a crapload of use cases, most people aren't aware of what it can actually do because it doesn't "web scale". Sqlite isn't new, it's old, and it's "used in production" count is in the literal billions.
osrec 3y ago
It is used in a huge number of production environments, including within your browser (most probably). Probably not something you want to run a multi tenant SaaS database with, but it is useful if you are going the one tenant per database route.
hot_gril 3y ago
SQLite has been the default store for CoreData in iPhone apps for years afaik. I imagine that's a lot of apps. Of course, CoreData sucks, but that's for unrelated reasons.

rahkiin 3y ago

There is https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html but I cannot deduce if it has the same features; the way of working is completely different

bshipp 3y ago

I've had good luck using FTS5 in Sqlite for small-scale use scenarios, such as examining scraped data, etc. It's remarkably powerful and quick. https://www.sqlitetutorial.net/sqlite-full-text-search/ https://www.sqlitetutorial.net/sqlite-full-text-search/ https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html

anitil 3y ago
I put together a demo of this for work where I pulled all our private repos and built an index, then used datasette as the front end. It was incredibly fast, so much so that I found it more useful than github search. The index is pretty out of date now but I still use it purely based on convenience and speed