6 ms·
SQLite 3.39.2
- coreyp_1 4y ago> 1. Add (long overdue) support for RIGHT and FULL OUTER JOIN. Yay!
- q-big 4y agoThese features were already added in version 3.39.0.
- btreecat 4y agoI once experimented with using SQLite as a file store for small images (10s of Kb). We had so many images on disk, that a recursive ls command wouldn't complete in a single day. At one point we even hit inode limits. I tested out storing the images in sqlite files across a few directories. I also testing using tar and zip storage with the same schema. Turns out the blog post they wrote was right, and SQLite was indeed faster than our normal IO. https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html
- malkia 4y agoI love sqlite, but if you need just store, and even smaller library - look no further than `lmdb` - https://www.lmdb.tech/ https://www.lmdb.tech/
- Lex-2008 4y agois it only me or does this site works only over plaintext http, while you gave an https link?
- malkia 4y agooh, interresting - try this - https://www.symas.com/lmdb https://www.symas.com/lmdb - something happened to my chrome months ago, and now it asks me everytime to switch to secure - so it resolved it somehow on mine, but didn't know it didn't work for others
- snoopy_telex 4y agoSame, curl spits out an error for me. "ST_CONNECT:tlsv1 alert protocol version"
- crest 4y agoLMDB is a really great read optimized key-value, transactional key-value store, but you have to be careful with when benchmarking memory mapped files. The problem is that a thread touching paged out memory just blocks without performing any system call. It looks like you're faster and it starts out very fast because as long as your data fits into main memory you can read it without any context switches, but as soon as you get under even slight memory pressure jitter goes through the roof and your userspace code is helpless because there is no load-if-mapped instruction to avoid blocking the thread if your accessed page has to be read in from the backing storage. Your database has no control over I/O parallelism and queue depth except very indirectly by delegating database accesses to a thread pool of a specific size and hoping for the best.
- malkia 4y agotrue dat, there was an excellent series from one University going over that in details - changed my opinion on the matter - https://db.cs.cmu.edu/mmap-cidr2022/ https://db.cs.cmu.edu/mmap-cidr2022/
- kylebarron 4y ago> I once experimented with using SQLite as a file store for small images Mapbox productionized this with their MBTiles [0] format, to store millions of small vector or raster map tiles in an SQLite database. Much easier to work with than millions of images on disk. [0]: https://github.com/mapbox/mbtiles-spec https://github.com/mapbox/mbtiles-spec
- jokoon 4y agohow large is the database file? aren't backup a bit long to perform?
- Skinney 4y agoNot if you use litestream
- snoopy_telex 4y agoBackups of multiple millions of files will take forever as well, even just figuring out the delta based on inode data.
- jokoon 4y agoMaybe splitting it into 10 or 50 sqlite files would help...
- btreecat 4y agoI genuinely don't remember what the ballpark number was, it was years ago when I did this. But I think the db was a tad larger than the raw files on disk because of the additional meta data I stored in the database for organization, but this is just a guess.
- dmitrygr 4y agoAnytime you think you need to invent a file format for your application, please save yourself the trouble and use sqlite. It'll handle consistency, searching, updating, OS-specific quirks, everything.
- dhsysusbsjsi 4y agoThey even have a page talking about this, and this is the best way to think about SQLite. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html > SQLite does not compete with client/server databases. SQLite competes with fopen()
- orangepurple 4y agoWhy do so many websites urge readers to avoid using sqlite as a production database? From what it looks like sqlite can handle a million daily users
- quaffapint 4y agoA couple of things come to mind. Scaling, specifically horizontally (though there are some solutions for that) and being write heavy like storing analytics. For many cases though it should work just fine for your average read heavy site.
- jerryjerryjerry 4y agoIMHO, going production also requires scalability, high availability, and tooling ecosystem (backup/restore/replication), etc, besides functionalities and performance.
- otoolep 4y agoAgreed. In the real world actually operating the database is just as important as its intrinsic query functionality.
- jerryjerryjerry 4y agoYes, that's also why certified Oracle DBAs can earn $$$ for their expertise on this.
- stonemetal12 4y agoI would add SQLite is missing access control, it is a big all or nothing. Usually it doesn't matter as much since the scope is so much smaller, but if you really want to back a major website with it some defense in depth would be nice.
- jerryjerryjerry 4y agoGood point! Security and regulation are other key requirements in real world for many industries.
- quaffapint 4y agoFor something used in so many places for so many reasons it's interesting to read everything they test for their releases... https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- dhsysusbsjsi 4y agoI was surprised to see this one given the size of the test suites. I mean, this is an incorrect result. Pretty significant bug. > Fix an incorrect result from a query that uses a view that contains a compound SELECT in which only one arm contains a RIGHT JOIN and where the view is not the first FROM clause term of the query that contains the view