Internal Versus External BLOBs in SQLite
sqlite.org
sqlite.org
A filesystem is the canonical means for storing files - just use it - it's incredibly handy to have regular tools at your disposal for managing those files outside of the database. The only case I can think where maybe files in the db makes sense is if you have millions of really tiny files (100-200 bytes).
Having files in the db maybe avoids an extra seek - but in any real system high throughput system, you're probably leaning towards SSDs where you get many more seeks/s than you do on a spinny disk.
If your application needs something more like a distributed filesystem, there are better things (gluster, moosefs, even nfs, etc).
Look at Ceph if you want to store files in a consistent and distributed fashion. Not the filesystem though, the object store (RADOS).
Sure but what about the many use cases where you're not really storing files but metadata or other generated data?
A simple example might be photo libraries. The photos themselves are files but are the 5kB small and 50kB medium thumbnails generated from the photos worth storing as files? Or should they be stored as blobs in the database along with their other metadata?
It's good to know where the cutoff should be.
But I think the binary thumbnails and so forth should almost always be files on the filesystem.
It says they used SQLite version 3.7.8. Since 3.7.8 came out on September 19, 2011 and 3.7.9 came out on November 1, 2011, it was probably in that range.
Why? Databases are all about storing data in a structure defined by the relationships between the records. In most cases with files, the relevant data is the metadata or other structured data, not the file.
If you do the math, it's rarely cost effective to store unstructured data in a structured database. Databases are always harder to scale.
On the file side, there are dozens of ways to effectively deliver files that are cheaper, more scalable and simpler for many use cases.
Tom Kyte has some insightful points in this thread: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUE...
"Do I want you to keep MY medical stuff in a directory, unsecured, unmanaged, not backed up like the database is - we have no idea if recovery is possible, who accessed it last? Who has access to it."
"if this data is valuable to your business, if this data is necessary for your business, if the loss of this data would damage your ability to do business - it has no business NOT being in the database."
Tom is Mr. Oracle, so I am not shocked that he's a big advocate of using that platform. But I don't like the straw man argument that anything that isn't managed by an Oracle DBA is automatically flawed and unreliable. Give me a break... Robust auditing, backup, professional management, etc can be delivered anywhere. (Or f'ed up on an Oracle platform)
That said, it's a business decision -- somebody, somewhere has probably justified putting user home directories on an Oracle database.
File systems are simpler and backups and replication are really much more straightforward. As anyone who did disaster recovery of Oracle databases can attest to, there are a lot of details to get wrong.
It boils down to what data you want to store and what your access patterns look like.
When you go to other platforms, you need solid process AND deep exterior in whether the database can do what you want. Can MySQL handle the same recovery scenarios that Oracle can? With which storage engines?
I work with SQL Server and while database corruption can/does happen, I often can get away with a row level restore from a backup, no problem.
I still dont like storing big ass files in a database though, I dont love the additional level of abstraction when you generally dont need to have a high transactional/relational system.
If your data is fairly small, and perf not the biggest issue (which is pretty common), you might as well go for the simplest solution, which is going to be purely DB or purely FS, not some mixture of the two.
It's not uncommon to lose an inode or two on certain filesystems during OS crashes. When one of those inodes is of a large, valuable file that you don't have a backup of, you've got problems.
Of course, the best solution is to have recent backups. Unfortunately, the more data you have, the harder, more time consuming, and more expensive your backups will get.
Sure, I could write a custom script that dumps the DB to a format that deltas better, but I'd really rather my filesystem level dedup and sync tools just work and not have to do something special-case for every application I use.
(I'm currently going through this exact process with my media library -- it's a dozen megabyte database. It's so small loading the entire thing into RAM is easier than not; indexing it is beyond unnecessary, etc. And yet because it doesn't diff, every time I sync it between laptop and desktop, I'm irritated to discover I'm waiting for the entire thing to cross the network every single time.)
It would be great if more music players like car entertainment systems, iTunes, iOS Music would show the stats rating that is stored in the mp3 files itself (metadata). Monolithic databased based music management systems may be convenient for some users. I personally prefer to copy the mp3 files (with embedded metadata) to my other devices.
My wish list: More intelligent playlist playback that e.g. recognises song skipping as down-voting and manual replaying a song as up-voting and plays similar songs based on the music fingerprint like iTunes Genius but as completely offline feature.
Casual people nowadays listen music on streaming platforms like Youtube, Spotify or old-school radio stations. So it's unfortunate but we saw no new exiting new features in music players for some years.
For the first ingestion of the data, be it from a CD or from the artist/label themselves, sure, that metadata can, and should, be stored in the files, but not your own.
I'm having a hard time seeing hashes[1] being very valuable for much of anything regarding music library management. Hashes won't be any good for comparisons of music files containing the same song across different recording sessions, bit rates, or file formats.
I would rather all of my music playing programs respect standard metadata formats (like ID3) - which is usually included in most digital downloads and which is supported across all the popular audio file formats - and leverage that metadata for organization. Off-hand, I can't think of a music organization system that relies on hashes that is not brittle.
1: I'm talking about "dumb" hashes like SHA-1. Fingerprinting can work well for this, but is more complicated: http://en.wikipedia.org/wiki/Acoustic_fingerprint
There are several metadata formats for mp3, flac, mp4, etc. formats. Windows Media Player e.g. stores the music star rating per user name and adheres to the metadata standard. Most media player apps adhere to the metadata standards. Some players only read, but write to an internal database with no export option - I burnt myself with iTunes back in 2004. Some apps write older metadata formats like IDv1 instead of v1 and v2.
If you mean CRC32/MD5/SHA1-hash, than you do something wrong. There are special hashes for music data like qhash and minhash that Google and other similar services use. The media formats like mp3 have seperate sections for the audio data and the metadata payload, so you should use a hash of only the audio data anyway, the metadata is meant to be edited (parts of it as described in the standards).
Only with mp3 files that contain proper metadata one can sync his audio with various devices from different manufacturers.
Also, I've stored lots & lots of photos in a filesystem, and backup is very hard. Backing up big files is just a lot easier.
I just dislike unnecessary "open" calls because it can get kind of crazy. For instance, if you happen to send a short filename to a samba server (which must enumerate every file in every folder up to the root), or use NFSv4 ACLs, etc.
So, several things to think about (make cache bigger, how much grows the overall db size) that can change the database behavior.
Of course those numbers can give some directions, but since only the blob data was taken into account, it can not give full information.
I personally would most of the time prefer smaller db sizes, at least, on edge cases. Relational databases are great for structured data -- unstructured data is not their specialty.
Since then I have not put large binary blobs in a database but I wonder if I would get reasonable performance by putting them in a _separate_ database instead of mixing them with other small-size rows.
I suspect there's some way in which your access pattern or usage was suboptimal. Perhaps you had big reads with small writes in a transaction, or your network streamed directly from db leaving connections open a long time, or... something, because there's no way this should have taken so long.
I believe SQLite also has to write the data twice, using the WAL. Maybe there is an optimization to avoid that though.
If I understand correctly, it writes the data twice if you're not using WAL. With WAL, it only writes the data once [1].
[1]: The "Performance Considerations" section of https://www.sqlite.org/wal.html
> The original content is preserved in the database file and the changes are appended into a separate WAL file.
The difference is whether the journal keeps the new data (WAL) or the old data (not WAL). If you are writing big chunks of data, WAL will probably be more I/O intensive.
Taking a wild guess I'll say that if you are adding data to the database, and not using WAL, the data will only be written once since the journal won't keep a reference to old data if no pages is being overwritten.