FILESYSTEM vs SQLITE, while storing up-to 10M files

Question

I would like to store up-to 10M files, 2TB storage unit. The only properties which I need restricted to filenames, and their contents (data).

The files max-length is 100MB, most of them are less than 1MB. The ability of removing files is required, and both writing and reading speeds should be a priority - while low storage efficiency, recovery or integrity methods, are not needed.

I thought about NTFS, but most of its features are not needed, while can't be disabled and considered to be an overhead concern, a few of them are: creation date, modification date, attribs, journal and of course permissions.

Due to the native features of a filesystem which are not needed, would you suggest I'll use SQLITE for this requirement? or there's an obvious disadvantage that I should be aware about? (one would guess that removing files will be a complicated task?)

(SQLITE will be via the C api)

My goal is to use a more suited solution to gain performance. Thanks in advance - Doori Bar

Inhaler

Answer 1

If your main requirement is performance, go with native file system. DBMS are not well suited for handling large BLOBs, so SQLite is not an option for you at all (don't even know why everybody considers SQLite to be a plug for every hole).

To improve performance of NTFS (or any other file system you choose) don't put all files into single folder, but group files by first N characters of their file names, or also by extension.

Also there exist some other file systems on the market and maybe some of them offer possibility to disable some of used features. You can check the comparison on Wikipedia and check them.

Correction: I've made some tests (not very extensive though) that show no performance benefit in grouping files into subdirectories for most types of operations, and NTFS quite efficiently handled 26^4 empty files named from AAAA to ZZZZ in a single directory. So you need to check efficiency for your particular file system.

Funch

May I ask what is your definition of a large blob? – Inhaler

In fact, any blob larger than page size (check DBMS manual for page size-relaed details) can be considered large. This is because when the data doesn't fit into the page, the procedure of storing it becomes more complicated than the procedure of handling short variable-size data. AFAIK some DBMS also store such blobs as files on the file system. This is very much similar to what Microsoft recommends for registry -- "you can store var-sized binary blocks in registry, but for blocks over 2 Kb put such blocks to files and keep a reference in registry". – Funches

So if most of the files in question don't go behind 1MB, and I set a page file of 1MB - you'd recommend SQLITE over a filesystem? (SQLITE has one file structure) – Inhaler

I said that SQLite was not an option AT ALL. The only thing it gives you is unnecessary overhead. – Funches

Then I'm sorry - I believed you meant that only large blobs are considered to be an issue for such a database. – Inhaler

I don't think SQLite has a page of 1 Mb (it would be very inefficient to manage the file), but I don't know. Even with one file SQLite gives an overhead. The backend storage of single-file databases is a virtual file system, similar to our SolFS or CodeBase File System. And you end up dealing with two filesysems - a virtual filesystem sitting in the file which resides on a real filesystem. This is where the overhead appears (not saying about DB structure overhead). – Funches

Well, I'm starting to think that both options (NTFS vs SQLITE) are inefficient and are not suited for the requirement. What do you say of the idea: using 100MB per file, while storing offsets for each 'sub file' at the db? this way each file will contain 200 'sub files' easily, reducing the overhead of the NTFS features to minimum, at the expense of wasting storage which is not useable prior to a 'optimize' operation to fill the gaps? – Inhaler

Well, if you are ready to make something custom, then you can use raw partition (no filesystem at all) and store your blocks on it. But you will end up re-inventing some kind of file system. This is why looking at wide choice of existing filesystems and choosing something for your needs seems to be better idea. – Funches

From my POV, NTFS answers all my needs, but the additional costy features. Do you happen to know of such basic filesystem which answers the requirement, with no additional unremoval features? – Inhaler

FAT32 might work, but it's limited in MAX size (and the limit is less than 1TB), so I guess you need to check the comparison table on Wikipedia (I posted the link in my answer). – Funches

"Eg. file name "song1.mp3" would look like "\mp3\so\ng1" on the disk. This approach would let you get rid of long directory listing which is slow to enumerate when accessing the file." - Have you done performance testing to verify this assumption? It's faster to access a file (by name) on modern filesystems if you put all files in one directory. Using a hashing structure means lots of dir lookups for the intermediate dirs. databasesandlife.com/flat-directories – Speedwriting

@Adrian The reference you posted talks about specific file system, and this is not NTFS the user was asking about. Also, that's a specific usage scenario of accessing a particular file by it's name. In other scenarios, such as directory enumeration or adding a file, performance will degrade dramatically as the number of files grows. For example, adding a file in a linear list might be relatively fast, but lookup will be slow. For indexed directory adding a file will require rebuilding the index or rebalancing the tree. So in general not overloading the directory with files is a great idea. – Funches

@Eugine, I suppose my main point is that it's important to verify assumptions through testing - extra complexity (non-flat) should only be introduced if it demonstrably solves a problem. NTFS uses an indexing/hashing structure for filenames, so should be fast too, but I haven't tested it. Enumerating all files takes a long time either way (flat or non-flat). True about re-balancing trees but on tru64 there was no noticeable drop-off in adding files, maybe NTFS is different but I doubt it. – Speedwriting

@DooriBar SQLite actually has a page that discusses whether it is more efficient to hold a file in a blob versus an external file: sqlite.org/intern-v-extern-blob.html. TL;DR for files below a certain size, it is faster to store it in the database (up to 2x faster) while for large files it can be a lot slower (5x as long to access). While the specific details will change with hardware speeds, for the specific use case quoted the optimal size to store in database ranged from < 25k for the default page size to up to < 100k for a more optimal page size for larger files. – Monty

@EugeneMayevski'Callback - From here - the maximum file size on a FAT32 partition is 4GB - probably no larger than 4,294,967,295 bytes! – Straighten

@Vérace so what? The question is about 100MB large files, and there exist many filesystems which are free from the size limit including exFAT. – Funches

Answer 2

The official SQLite site actually includes a page which documents the performance benefits of using a database over a native filesystem in various operating systems. When storing files of ~ 10 KiB sqlite is approximately 35% faster.

SQLite reads and writes small blobs (for example, thumbnail images) 35% faster¹ than the same blobs can be read from or written to individual files on disk using fread() or fwrite().

Furthermore, a single SQLite database holding 10-kilobyte blobs uses about 20% less disk space than storing the blobs in individual files.

The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is greater than the overhead of using the database. The size reduction arises from the fact that individual files are padded out to the next multiple of the filesystem block size, whereas the blobs are packed more tightly into an SQLite database.

The measurements in this article were made during the week of 2017-06-05 using a version of SQLite in between 3.19.2 and 3.20.0. You may expect future versions of SQLite to perform even better.

You may experience different results when using larger files, and SQLite site includes a link to kvtest which you may use to reproduce these results on your own hardware / operating system.

Furrier