4 ms·

Once in memory, the flatfile’s data can be mutated in place, and creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss

by mlthoughts2018 6y ago

Once in memory, the flatfile’s data can be mutated in place, and creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve.

tmpz22 6y ago
Until you deploy this custom database of yours and customers start raising tickets reporting data loss on remote devices you can no longer access. Much of the value in SQLite is that it has achieved the level of clout that big players deploy it remotely to devices that are hard (or impossible) to debug without much second thought.
- mlthoughts2018 6y ago
  We did deploy it. This whole architecture was chosen in an adtech business I worked in before, supported by data platform teams, analytics teams and more. The RFC process was intense and the decision was vetted with seriously heavy effort.
  - deleted 6y ago
    [deleted]
tlb 6y ago
I've done the flatfile thing several times on various projects, and I'm starting to appreciate the wisdom of letting sqlite handle it. Things it can handle correctly include: - crashes/power failures - multiple processes opening the same file - transactional updates: it rolls back if you throw an exception in the middle of writing the update. This seems like a common failure mode while under development - scaling to huge size: many gigabytes are no problem. These get slow with a flatfile - manual surgery on data: it comes with handy command line tools for manipulating its databases - upgrading format: you can check a version number when opening the file and do "alter table add column" for any new features.
- 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.
duskwuff 6y ago
> ...creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve That's a pretty bold claim. Judging by the number of applications (including "real" databases) which fail to get this right, I'd have to say it's probably harder than you think. Besides, SQLite has done that work already, and has done so very thoroughly. I would definitely trust SQLite over something home-grown.
quietbritishjim 6y ago
Sounds like you can't have multiple processes concurrently access it while it's being updated, even if the updates are small and infrequent (you'd have to load the whole database into memory and write it back out again if I've understood your comment correctly?). With SQLite you can have multiple processes access the same database and they can make changes to it that are immediately seen by the other processes. The nature of SQLite locks means this doesn't scale well if you have lots of processes all wanting to make heavy updates, but so long as you're not in that situation it works very well.

tmpz22 6y ago

Until you deploy this custom database of yours and customers start raising tickets reporting data loss on remote devices you can no longer access. Much of the value in SQLite is that it has achieved the level of clout that big players deploy it remotely to devices that are hard (or impossible) to debug without much second thought.

mlthoughts2018 6y ago
We did deploy it. This whole architecture was chosen in an adtech business I worked in before, supported by data platform teams, analytics teams and more. The RFC process was intense and the decision was vetted with seriously heavy effort.
- deleted 6y ago
  [deleted]

tlb 6y ago

I've done the flatfile thing several times on various projects, and I'm starting to appreciate the wisdom of letting sqlite handle it. Things it can handle correctly include: - crashes/power failures - multiple processes opening the same file - transactional updates: it rolls back if you throw an exception in the middle of writing the update. This seems like a common failure mode while under development - scaling to huge size: many gigabytes are no problem. These get slow with a flatfile - manual surgery on data: it comes with handy command line tools for manipulating its databases - upgrading format: you can check a version number when opening the file and do "alter table add column" for any new features.

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.

duskwuff 6y ago

> ...creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve That's a pretty bold claim. Judging by the number of applications (including "real" databases) which fail to get this right, I'd have to say it's probably harder than you think. Besides, SQLite has done that work already, and has done so very thoroughly. I would definitely trust SQLite over something home-grown.

quietbritishjim 6y ago

Sounds like you can't have multiple processes concurrently access it while it's being updated, even if the updates are small and infrequent (you'd have to load the whole database into memory and write it back out again if I've understood your comment correctly?). With SQLite you can have multiple processes access the same database and they can make changes to it that are immediately seen by the other processes. The nature of SQLite locks means this doesn't scale well if you have lots of processes all wanting to make heavy updates, but so long as you're not in that situation it works very well.