6 ms·

I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sqlite seems like it is only appropriate for the knife’s edge

by mlthoughts2018 6y ago

I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sqlite seems like it is only appropriate for the knife’s edge boundary between small data, low reliability, in-memory situations (better served by server applications using tools like pandas or R) and bigger data, transactional structure, reliability constraints (better served by Postgres). I just can’t understand what use cases live in between them and are better served by sqlite.

zetalemur 6y ago
> I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sometimes you do not want to run an additional process/daemon like postgres. Your state is in essence now a file on some file system that you can atomically (full ACID) update using multi processing without the need for more complex machinery - you can get very far with this architecture.
CraigJPerry 6y ago
For interactive use, sqlite queries are snappier than pandas at the ~4gb dataset size. I’ve switched over for some logfile analysis tasks and both memory usage and speed are a bit better. They’re not massive differences but i definitely appreciate the snappiness. Interactive work is just nicer that way. I could do postgres - the docker version is pretty handy i find but it’s just more faffing about (-v /path/to/wherever:/var/postgres password, cleanup when you’re done etc.)
- iagovar 6y ago
  If you're using pandas, try duck db
- chrisweekly 6y ago
  For logfile analysis, take a look at https://lnav.org https://lnav.org -- it has SQLite embedded, along with a bunch of helpful utils for ETL on a small scale.
  - CraigJPerry 6y ago
    I'll need to give that a whirl, one feature i like the look of from glancing the docs - https://docs.lnav.org/en/latest/hotkeys.html#chronological-navigation https://docs.lnav.org/en/latest/hotkeys.html#chronological-n... Sure beats regexing my way to the next minute marker or whatever.
quietbritishjim 6y ago
Perhaps you could answer the opposite question: Why would I use flatfile (aside from the fact I've never heard of it) over SQLite? So far you've just said SQLite has some features you can't imagine I'd ever need, but even if that were true then it wouldn't be a problem in itself. What are things that are actually bad about SQLite compared to the alternative you're suggesting?
- dmd 6y ago
  You've never heard of using a file to store data in?
  - quietbritishjim 6y ago
    The question was what is the benefit of flatfile over SQLite. SQLite stores the database in a single file, so "it uses a file" is not an answer to that.
    - dmd 6y ago
      My comment was that you seem to be under the impression that there is a piece of software or file format named 'flatfile'.
      - quietbritishjim 6y ago
        Oh. It would've been easier if you'd just said that in your first comment. Looking back, you seem to be right. I thought mlthoughts2018 was referring to a specific file format, or at least a specific library because (1) They made up that weird terminology "flatfile" which looks like product name instead of just saying "plain text file" ... I wonder why they did that? (2) They made claims about transactional integrity with a WAL-like log. Now I realise they were suggesting you homebrew that anew for each fresh project that needs to store data! The idea that this would be easier than just using SQLite's existing mechanisms is so bizarre it didn't even enter my head they could've meant that.
    - nl 6y ago
ehvatum 6y ago
For example, SQLite is excellent for making an email client. Even on old embedded Arm devices with 64MiB memory, it’s fast enough to handle a gigabyte of messages and an address book with thousands of entries, provided the SQLite DB is located on a good CF or SD card. SQLite is really quite remarkable for the number of occasions where it has been immensely valuable to me and countless others, on such a variety of platforms and in such a variety of circumstances.
nodamage 6y ago
There are literally billions of smartphones in the world that fall in between those exact constraints... It's honestly bizarre to me to see someone claim that Sqlite has few use cases when there are likely hundreds of Sqlite files sitting in the phone in your pocket.
- milch 6y ago
  There’s probably files on their computer as well that use SQLite. Lightroom libraries are SQLite databases for example, which is a pretty leaky abstraction in their case
rodgerd 6y ago
Why on earth do I want the overhead and attack surface of a full, multi-user, networkable database with user-defined languages, extensions, and all of the other amazing things that PG can do, when all I need is a reliable, single-user structured data store?

zetalemur 6y ago

> I feel like if I am worried about those issues, when would I ever use sqlite instead of Postgres? Sometimes you do not want to run an additional process/daemon like postgres. Your state is in essence now a file on some file system that you can atomically (full ACID) update using multi processing without the need for more complex machinery - you can get very far with this architecture.

CraigJPerry 6y ago

For interactive use, sqlite queries are snappier than pandas at the ~4gb dataset size. I’ve switched over for some logfile analysis tasks and both memory usage and speed are a bit better. They’re not massive differences but i definitely appreciate the snappiness. Interactive work is just nicer that way. I could do postgres - the docker version is pretty handy i find but it’s just more faffing about (-v /path/to/wherever:/var/postgres password, cleanup when you’re done etc.)

iagovar 6y ago
If you're using pandas, try duck db
chrisweekly 6y ago
For logfile analysis, take a look at https://lnav.org https://lnav.org -- it has SQLite embedded, along with a bunch of helpful utils for ETL on a small scale.
- CraigJPerry 6y ago
  I'll need to give that a whirl, one feature i like the look of from glancing the docs - https://docs.lnav.org/en/latest/hotkeys.html#chronological-navigation https://docs.lnav.org/en/latest/hotkeys.html#chronological-n... Sure beats regexing my way to the next minute marker or whatever.

quietbritishjim 6y ago

Perhaps you could answer the opposite question: Why would I use flatfile (aside from the fact I've never heard of it) over SQLite? So far you've just said SQLite has some features you can't imagine I'd ever need, but even if that were true then it wouldn't be a problem in itself. What are things that are actually bad about SQLite compared to the alternative you're suggesting?

dmd 6y ago
You've never heard of using a file to store data in?
- quietbritishjim 6y ago
  The question was what is the benefit of flatfile over SQLite. SQLite stores the database in a single file, so "it uses a file" is not an answer to that.
  - dmd 6y ago
    My comment was that you seem to be under the impression that there is a piece of software or file format named 'flatfile'.
    - quietbritishjim 6y ago
      Oh. It would've been easier if you'd just said that in your first comment. Looking back, you seem to be right. I thought mlthoughts2018 was referring to a specific file format, or at least a specific library because (1) They made up that weird terminology "flatfile" which looks like product name instead of just saying "plain text file" ... I wonder why they did that? (2) They made claims about transactional integrity with a WAL-like log. Now I realise they were suggesting you homebrew that anew for each fresh project that needs to store data! The idea that this would be easier than just using SQLite's existing mechanisms is so bizarre it didn't even enter my head they could've meant that.
  - nl 6y ago

ehvatum 6y ago

For example, SQLite is excellent for making an email client. Even on old embedded Arm devices with 64MiB memory, it’s fast enough to handle a gigabyte of messages and an address book with thousands of entries, provided the SQLite DB is located on a good CF or SD card. SQLite is really quite remarkable for the number of occasions where it has been immensely valuable to me and countless others, on such a variety of platforms and in such a variety of circumstances.

nodamage 6y ago

There are literally billions of smartphones in the world that fall in between those exact constraints... It's honestly bizarre to me to see someone claim that Sqlite has few use cases when there are likely hundreds of Sqlite files sitting in the phone in your pocket.

milch 6y ago
There’s probably files on their computer as well that use SQLite. Lightroom libraries are SQLite databases for example, which is a pretty leaky abstraction in their case

rodgerd 6y ago

Why on earth do I want the overhead and attack surface of a full, multi-user, networkable database with user-defined languages, extensions, and all of the other amazing things that PG can do, when all I need is a reliable, single-user structured data store?