Data corruption shouldn't be an issue as far as SQLite goes, or at least not any more than another method of storing the data.
The main downside from my point of view would be vacuuming. Delete a bunch of external image files and you get the space right back. As is normal with a DB, if you drop tables or records from a SQLite file you can re-use the space, but the file doesn't shrink on the filesystem. "Normal" vacuuming in SQLite needs to basically make 2 extra copies of the database, so with huge databases that becomes a non-option. Though incremental vacuum is still available to drop the size. I'm not as familiar with the performance of that sort of vacuuming, but I believe it increases fragmentation, as you'll never be able to "put all the pages back in order" with it. Backup systems might be an issue with 1 gigantic file rather than more smaller files. One small change means the whole thing needs to be backed up again as opposed to only the bits that changed. Also, depending on the nature of the project remember the single-writer limitation. If there's one person working on it with one program needing to do writes then you're fine. If there's collaboration amongst several users wanting to write then you might run into network file system locking issues, or other similar problems. -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Mike Clark Sent: Tuesday, May 08, 2018 9:08 AM To: SQLite mailing list Subject: [sqlite] About storage of large amounts of image data Hi List! I'm developing a project that deals with image files and am considering storing all the images in the SQLite database itself, rather than (or in addition to) the file system. Since the prospective users will probably be dealing with hundreds of gigabytes in their use of the project, I am wondering if this is an effective or efficient use of SQLite -- or safe, because of the risk of data corruption. I know the documentation says that SQLite can handle up to 140 TB (do we know of anyone who is doing this?), so hundreds of gigs is clearly doable. Is it advisable, however? (Sent this about 2 wks ago, but it never posted to the list and no moderator response either). -- Mike Clark Twitter: @Cyberherbalist Blog: Cyberherbalist's Blog <http://www.cyberherbalist.co> --------- "Free will, though it makes evil possible, is also the only thing that makes possible any love or goodness or joy worth having." *- C. S. Lewis* _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
