4 ms·

Is anybody depending on this for mission critical data? Can I throw 1TB of images, pdfs, and miscellanea in there, delete the originals, and keep humming along

by sgc 2y ago

Is anybody depending on this for mission critical data? Can I throw 1TB of images, pdfs, and miscellanea in there, delete the originals, and keep humming along in practical perpetuity? I would presume use lightstream to backup on top of other more general backup solutions, and use a few Attached Databases instead one monolithic for a bit more flexibility / safety?

turbocon 2y ago
Well "SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild."[1] And "SQLite has exhibited very high reliability in the field and a very low defect rate, especially considering how rapidly it is evolving. The quality of SQLite is achieved in part by careful code design and implementation. But extensive testing also plays a vital role in maintaining and improving the quality of SQLite."[2] [1] https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html [2] https://www.sqlite.org/testing.html#summary https://www.sqlite.org/testing.html#summary
samatman 2y ago
> Is anybody depending on this for mission critical data? There may be the occasional entity with mission critical data which does not, sooner or later, entrust some of it to SQLite. It would be difficult to populate such a list, however. > Can I throw 1TB Subject to this[0] list of constraints, yes. The theoretical upper bound is 281TiB, but this is untested due to a lack of the combination of hardware and filesystem which can create an SQLite file of that magnitude. What happens when the limit is reached is extensively tested, just not at the theoretical maximum, yet. As for its general reliability, this too is documented[1]. SQLite sets the bar for high-assurance practices in open source software, at a level which more programs should aspire to. [0]: https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html [1]: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- sgc 2y ago
  From that limits page, it looks like as-shipped it can have a blob of max 1gb, and a bit over 2gb if compiled from source. That's rather limiting for my data, since I have some static images, as well as some pdfs and of course videos that are larger than those limits. I guess every library has an oversized books shelf, but a higher limit would be more useful to use it in "everything but the kitchen sink" mode.
  - samatman 2y ago
    SQLite might indeed not be the best tool here. But there are ways to make it work. You could use the blob streaming API to chunk the files into and out of the database, although this is not fire-and-forget and would require a pinch of data modeling and some adapter code. Seems like the kind of thing where, given a compelling need to do it, one could work something out fairly readily and rely on the result. But it's probably not the ideal solution to the problem.
    - sgc 2y ago
      That was pretty much my thought, thanks for the confirmation. It sounds like a relatively simple but well thought out plugin could make it seamless during typical use.
deleted 2y ago
[deleted]
ozim 2y ago
I don't know about 1TB data but I think it was used in some missile system so I would say yes for mission critical data, not sure if we think about mission critical in the same terms. Edit: by pure chance I hit that just a moment ago: https://fractaledmind.github.io/2024/10/16/sqlite-supercharges-rails/ https://fractaledmind.github.io/2024/10/16/sqlite-supercharg...
xiphias2 2y ago
Yes, it's especially good for that. There are lots of small things missing from it (like boolwan support), but it's used as an archival format and backwards compatibility for the archive format is prioritized over new features.

by sgc 2y ago

turbocon 2y ago

Well "SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild."[1] And "SQLite has exhibited very high reliability in the field and a very low defect rate, especially considering how rapidly it is evolving. The quality of SQLite is achieved in part by careful code design and implementation. But extensive testing also plays a vital role in maintaining and improving the quality of SQLite."[2] [1] https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html [2] https://www.sqlite.org/testing.html#summary https://www.sqlite.org/testing.html#summary

samatman 2y ago

> Is anybody depending on this for mission critical data? There may be the occasional entity with mission critical data which does not, sooner or later, entrust some of it to SQLite. It would be difficult to populate such a list, however. > Can I throw 1TB Subject to this[0] list of constraints, yes. The theoretical upper bound is 281TiB, but this is untested due to a lack of the combination of hardware and filesystem which can create an SQLite file of that magnitude. What happens when the limit is reached is extensively tested, just not at the theoretical maximum, yet. As for its general reliability, this too is documented[1]. SQLite sets the bar for high-assurance practices in open source software, at a level which more programs should aspire to. [0]: https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html [1]: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html

sgc 2y ago
From that limits page, it looks like as-shipped it can have a blob of max 1gb, and a bit over 2gb if compiled from source. That's rather limiting for my data, since I have some static images, as well as some pdfs and of course videos that are larger than those limits. I guess every library has an oversized books shelf, but a higher limit would be more useful to use it in "everything but the kitchen sink" mode.
- samatman 2y ago
  SQLite might indeed not be the best tool here. But there are ways to make it work. You could use the blob streaming API to chunk the files into and out of the database, although this is not fire-and-forget and would require a pinch of data modeling and some adapter code. Seems like the kind of thing where, given a compelling need to do it, one could work something out fairly readily and rely on the result. But it's probably not the ideal solution to the problem.
  - sgc 2y ago
    That was pretty much my thought, thanks for the confirmation. It sounds like a relatively simple but well thought out plugin could make it seamless during typical use.

deleted 2y ago

[deleted]

ozim 2y ago

I don't know about 1TB data but I think it was used in some missile system so I would say yes for mission critical data, not sure if we think about mission critical in the same terms. Edit: by pure chance I hit that just a moment ago: https://fractaledmind.github.io/2024/10/16/sqlite-supercharges-rails/ https://fractaledmind.github.io/2024/10/16/sqlite-supercharg...

xiphias2 2y ago

Yes, it's especially good for that. There are lots of small things missing from it (like boolwan support), but it's used as an archival format and backwards compatibility for the archive format is prioritized over new features.