3 ms·

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

by rusanu 9y ago

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 consistent between them unless you froze the app during the backup. When you restore the two backups (filesystem, DB) you may encounter any anomaly: orphaned files (exists on filesystem but no entry in DB), broken links (entry in DB referencing a non-existent file) etc. This is because the moment at which the backup 'views' the file and the DB record referencing it are distinct in time. As for write reordering: write-ahead log systems relies on correct write order. All DBs worth their name enforce this one way or another (via special API, via config requirements etc etc)

Klathmon 9y ago
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 useful. Do a query, get back a handle to a file that you can treat just like you opened it yourself.
- rusanu 9y ago
  To name just a few: Filestream https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server https://docs.microsoft.com/en-us/sql/relational-databases/bl... File Tables https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server https://docs.microsoft.com/en-us/sql/relational-databases/bl... Remote Blob Storage https://docs.microsoft.com/en-us/sql/relational-databases/blob/remote-blob-store-rbs-sql-server https://docs.microsoft.com/en-us/sql/relational-databases/bl... BFILE http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_bfile_ops.htm#ADLOB012 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.
- rusanu 9y ago
  > Do a query, get back a handle to a file that you can treat just like you opened it yourself 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-filestream-transaction-context-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/get-fil... [1] https://docs.microsoft.com/en-us/sql/relational-databases/blob/access-filestream-data-with-opensqlfilestream https://docs.microsoft.com/en-us/sql/relational-databases/bl... [2] https://www.sqlskills.com/blogs/paul/filestream-garbage-collection/ https://www.sqlskills.com/blogs/paul/filestream-garbage-coll...
  - Klathmon 9y ago
    I figured there was something massively annoying with it. I'm hoping that PostgreSQL can try to tackle this use-case somehow because it's so damn common and 99% of the time the solution that is used completely throws out all the guarantees that the database gives you.

by rusanu 9y ago

Klathmon 9y ago

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 useful. Do a query, get back a handle to a file that you can treat just like you opened it yourself.

rusanu 9y ago
To name just a few: Filestream https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server https://docs.microsoft.com/en-us/sql/relational-databases/bl... File Tables https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server https://docs.microsoft.com/en-us/sql/relational-databases/bl... Remote Blob Storage https://docs.microsoft.com/en-us/sql/relational-databases/blob/remote-blob-store-rbs-sql-server https://docs.microsoft.com/en-us/sql/relational-databases/bl... BFILE http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_bfile_ops.htm#ADLOB012 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.
rusanu 9y ago
> Do a query, get back a handle to a file that you can treat just like you opened it yourself 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-filestream-transaction-context-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/get-fil... [1] https://docs.microsoft.com/en-us/sql/relational-databases/blob/access-filestream-data-with-opensqlfilestream https://docs.microsoft.com/en-us/sql/relational-databases/bl... [2] https://www.sqlskills.com/blogs/paul/filestream-garbage-collection/ https://www.sqlskills.com/blogs/paul/filestream-garbage-coll...
- Klathmon 9y ago
  I figured there was something massively annoying with it. I'm hoping that PostgreSQL can try to tackle this use-case somehow because it's so damn common and 99% of the time the solution that is used completely throws out all the guarantees that the database gives you.