111–120 of 208 posts
From OP: "SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows." I don't get this. If scanning the content is important (as acknowledged by the author), then bypassing the scan via blob storage is a security issue and th…
Earlier quoted context omitted.
> Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. Interesting. I would have thought the other way around, at least for crash-resistance: the I/O stack (including disk hardware) has a tendency to reorder writes and so updates that live within a single file will corrupt fairly easily. Separate files not so much. I vaguely remember a paper on that (Usenix?) and sqlite…
You have two consystency issues with storing the files in the filesystem: - rollbacks in the DB can lead to orphaned files on disk. One can try to add logic in the app (eg. a catch block that removes the file if the DB rolled back) but that is not gonna help on a crash - it is impossible to obtain a consistent backup of both the DB and the filesystem. You can backup the filesystem and the DB, but the two will not be…
Something which can abstract away the storage on-disk of large blobs and manage/maintain them over time to prevent a lot of the issues you talk about, but still give the ability for raw file access if/when it's needed.
I've given it all of 10 seconds of thought, but even something like a DB type of a file handle would be useful. Do a query, get back a handle to a file that you can treat just like you opened it yourself.
The reason I still use files rather then SQLite is that I don't know how SQLite handles concurrency. For example I have a PHP app that is used by 10k users a day and it happily handles 100k tmp files in a single directory. On each request, it checks the file age via filemtime() and if new enough, includes the tmp file with a simple include(). (I write PHP arrays into the tmp files). If too old, it recalculates the da…
But if you really need to change your design for some reason, sqlite should handle 10k users per day just fine with simple transactions.
I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…
Trivial example: it's easy to delete any file most recently accessed more than N months ago [+] with a trivial line in cron (the exact line escapes me -- it involves find), but doing that with a database requires that you roll your own access tracking logic. Incremental backups of a directory structure are easy (rsync or tarsnap); incremental backups of a database appeared to me to be highly non-trivial.
[+] Since we could re-generate PDFs or gifs from our source of truth at will (with 5~10 seconds of added latency), we deleted anything not accessed in N months to limit our hard disk usage.
Storing many small files into few (compressed) bundle files is common since at least the 90's for storing game assets, but usually not with SQLite, but some sort of compressed format (simple zip archives are quite common but there are faster alternatives now). The bundling gives you better overall compression, and faster read performance compared to individual files (at least on Windows) as long as you open the file…
Earlier quoted context omitted.
> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. This is especially true if you want to deliver them back to the users in the context of a web application. If you place this kind of content in a database, you'll need to serve them with your application. If you use files for this content, you get two interesting options. For…
If you place this kind of content in a database, you'll need to serve them with your application. Well, not necessarily: https://github.com/FRiCKLE/ngx_postgres/
But I second that is an interesting nginx module
Earlier quoted context omitted.
You have two consystency issues with storing the files in the filesystem: - rollbacks in the DB can lead to orphaned files on disk. One can try to add logic in the app (eg. a catch block that removes the file if the DB rolled back) but that is not gonna help on a crash - it is impossible to obtain a consistent backup of both the DB and the filesystem. You can backup the filesystem and the DB, but the two will not be…
I'm surprised that there aren't any tools provided by various databases to handle that usecase. Something which can abstract away the storage on-disk of large blobs and manage/maintain them over time to prevent a lot of the issues you talk about, but still give the ability for raw file access if/when it's needed. I've given it all of 10 seconds of thought, but even something like a DB type of a file handle would be u…
Filestream https://docs.microsoft.com/en-us/sql/relational-databases/bl...
File Tables https://docs.microsoft.com/en-us/sql/relational-databases/bl...
Remote Blob Storage https://docs.microsoft.com/en-us/sql/relational-databases/bl...
BFILE http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_...
I'm sure there are more. But rest assured, they do cost, and usually a lot.
I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…
If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? At a previous job we did a bit of experimenting with using a clustered FS but they all introduced a lot of problems. However, this was a couple of years ago so the situation may be different now.
Earlier quoted context omitted.
I swear to god, I don't understand why people don't think about this. total sadness about having to deal with a 150GB SQL database, of which 148 GBs are blob storage for PDFs "We have big data!! We need enterprise scale!" Nope. You have big stupid.
I don't see the sadness -- or the stupid. There are several very valid business cases to store files as blobs in a DB. What's the problem is the DB is 150GB? It's not like the working set (which for the files will just be the metadata) will be that big for storing file blobs. 10GB Database + 140GB of pdfs on the filesystem are not any different to a 150GB DB with everything in. And you have other issues (consistency,…
Earlier quoted context omitted.
You have two consystency issues with storing the files in the filesystem: - rollbacks in the DB can lead to orphaned files on disk. One can try to add logic in the app (eg. a catch block that removes the file if the DB rolled back) but that is not gonna help on a crash - it is impossible to obtain a consistent backup of both the DB and the filesystem. You can backup the filesystem and the DB, but the two will not be…
I'm surprised that there aren't any tools provided by various databases to handle that usecase. Something which can abstract away the storage on-disk of large blobs and manage/maintain them over time to prevent a lot of the issues you talk about, but still give the ability for raw file access if/when it's needed. I've given it all of 10 seconds of thought, but even something like a DB type of a file handle would be u…
Things are a bit more complex. For one, the trivial problem of client vs. server host. The DB cannot return a handle (a FD) from the server, because it has no meaning on the host running the app. The second problem is that any file manipulation must conform to the DB semantics for transactions, locking, rollback and recovery.
What you describe does exists, is the FileStream feature that dates back to 2007 if I remember correctly. I'm describing the SQL Server feature since this is what I'm familiar with. The app queries the DB for a token, using GET_FILESTREAM_TRANSACTION_CONTEXT[0] and then uses this token to get a Win32 handle for the 'file' using OpenSqlFilestream[1]. The result handle is valid for usual file handle operations (read, write, seek etc). There were great expectations on this feature, but in real life it flopped. For one it caused all sort of operational headache from the increased DB files size (increased backups size etc) or from problems like having to investigate 'filestream thumbstone status'[2]. But more importantly, adoption required application rewrite (to use the OpenSqlFilestream), which of course never materialized.
File Tables is a newer stab at this problem and this one does allow to expose the DB files as a network share and apps can create and manipulate files on this share and everything is backed by the DB behind the scenes. But turns out a lot of apps do all sort of crazy things with the files, like copy-rename and swap as means to do failure safe saves, but many such operations are significantly more expensive in DB context. And when the DB content is manipulated directly by the apps that 'think' they interact with the filesystem, a lot of useful metadata is never collected in the DB, since the file API used never requires it (think file author, subject etc).
[0] https://docs.microsoft.com/en-us/sql/t-sql/functions/get-fil... [1] https://docs.microsoft.com/en-us/sql/relational-databases/bl... [2] https://www.sqlskills.com/blogs/paul/filestream-garbage-coll...