8 ms·
35% Faster Than the Filesystem (2017)
- dale_glass 2y agoOut of curiosity, would cutting out the filesystem out of the equation entirely improve things further still? Just put the database on /dev/sda2 or a LV? Also, how much overhead does LVM have?
- sgbeal 2y ago> would cutting out the filesystem out of the equation entirely improve things further still? This recent forum post on that subject: <https://sqlite.org/forum/forumpost/66b4d47514b442af https://sqlite.org/forum/forumpost/66b4d47514b442af> doesn't answer your question but may be interesting to you.
- miohtama 2y agoOracle (and other databases?) did this in 00s, using block devices directly. I do not know if anyone is doing this anymore.
- sgc 2y agoIs 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 agoWell "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 agoFrom 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 agoSQLite 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 agoThat 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 agoI 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 agoYes, 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.
- dehrmann 2y agoSounds like we should all just be doing sqlite3 /dev/sda2 Filesystems and databases solve similar problems, so putting one on top of the other is a bit redundant.
- Vecr 2y agoI don't think so. I'm pretty sure sqlite relies on fsync barriers, something block devices don't have.
- wmf 2y agoBlock devices have barriers and sync; fsync is built on those primitives.
- Vecr 2y agoI'm pretty sure it wouldn't work. Sqlite commits by deleting a file in normal operation, and with WAL you need an ordering that I'm not sure Linux on standard hardware can provide without running both the database and the WAL on 1 filesystem.
- wmf 2y agoObviously you would need some changes to SQLite to make it work, but I think it would work. Putting the DB and WAL on the same block device doesn't sound like rocket science.
- sgbeal 2y ago> Sounds like we should all just be doing sqlite3 /dev/sda2 A recent forum post on that topic: <https://sqlite.org/forum/forumpost/66b4d47514b442af https://sqlite.org/forum/forumpost/66b4d47514b442af>
- unwind 2y agoMeta: (2017), it seems?
- wongogue 2y agoIt includes retests from 2022.
- deleted 2y ago[deleted]
- kardos 2y agoIf the speedup is due to fewer open/close syscalls and space savings due to avoiding block padding, tar would achieve the same yes?
- dang 2y agoRelated. Others? SQLite: 35% Faster Than the Filesystem - https://news.ycombinator.com/item?id=41085376 https://news.ycombinator.com/item?id=41085376 - July 2024 (193 comments) SQLite is 35% Faster Than The Filesystem (2017) - https://news.ycombinator.com/item?id=27897427 https://news.ycombinator.com/item?id=27897427 - July 2021 (127 comments) 35% Faster Than The Filesystem (2017) - https://news.ycombinator.com/item?id=20729930 https://news.ycombinator.com/item?id=20729930 - Aug 2019 (164 comments) SQLite small blob storage: 35% Faster Than the Filesystem - https://news.ycombinator.com/item?id=14550060 https://news.ycombinator.com/item?id=14550060 - June 2017 (202 comments) Since the July 2024 had a lot of attention, the current repost counts as a dupe. Reposts are ok after a year or so—this is in the FAQ: https://news.ycombinator.com/newsfaq.html https://news.ycombinator.com/newsfaq.html.
- James_K 2y agoI feel like some kind of compression is the way to go if you are interested in fast reads/writes. Depending on the algorithm used, you could probably decompress the data faster than it can be read off the disk. In an archive file, you would also have the same benefit of fewer read/write calls.
- wmf 2y agoSome filesystems will do this for you.
- LtWorf 2y agoIt's talking about comparing to reading the data from individual files. If you keep them in a single file and seek, the 35% no longer holds. Read the post :)
- bubblesnort 2y agoITT: people who never installed an RDBMS that used a storage device instead of a filesystem, such as Db2.