4 ms·

If you store your BLOBS outside the db, you'll have to write your own two-phase commit logic to ensure when row X is deleted, then /wherever/X.jpg is also delet

by blendo 8y ago

If you store your BLOBS outside the db, you'll have to write your own two-phase commit logic to ensure when row X is deleted, then /wherever/X.jpg is also deleted. Similar for inserts. And you'll need to provide a detailed README to your DBAs and your team to explain all your special backup and restore operations. Or, you can just live with a backup that takes longer and requires more disk space.

kbenson 8y ago
And your replication handles other headaches you might have to consider if you have a Sapa rate file storage system. DB as a file service is a horrible idea except for when it's the best choice.
hderms 8y ago
I think it's fair to ask whether the file's _contents_ need to be ACID transactional or could you just mark the row as deleted and schedule a job/Cron to clean them up? In light of that, and the fact that a binary blob isn't really a relational piece of data and I can see the logic behind not storing it in a relational DB.
- Matthias247 8y ago
  I agree with that. Doing some kind of asynchronous garbage collection (Dumping all uploaded files, dumping the filenames from DB, and deleting all that are not required anymore) might be a lot easier to get right and reliable than trying to do everything in the request path.
  - fgonzag 8y ago
    This is a such a good solution in many cases and is often ignored.
  - fatnoah 8y ago
    This is what I did at a previous company where the main product was a messaging app with daily active users in the millions. Deletions were bit flips that were then cleaned up later by a cron job.
nemothekid 8y ago
>you'll have to write your own two-phase commit logic to ensure when row X is deleted, then /wherever/X.jpg is also deleted I can't imagine a situation where you would need strict consistency with a BLOB and a database row unless you were also modifying bits inside the BLOB (which wouldn't be true if you are hosting images). If you just need to ensure that whenever user X says DELETE, you can put S3 infront of an API and just query the database before you return the URL. If you really need the file gone, I would rather just encrypt the image and store the key in the database row. In a world where S3 doesn't exist, I would still build something similar because coupling my object store with my actual data store just seems like asking for trouble. Having to wait hours to backup several kilobytes of changed data in a multi-terabyte database is just inviting people to cut corners.
- kbenson 8y ago
  Storing legal contracts associated with accounts. I implemented a system like this. Documents were simple and usually small, pure html with no images or hrml converted to PDF, occassionally it was an attached JPEG from something someone emailed or faxed. If you want a system where the documents are strictly linked to the account, easy to query based on account, consistent (in the DB sense), replicated and backed up with all the other very important customer info, and local so you aren't exposed to external risk factors it's the path of least resistance, and can be easily and securely implemented with very little effort. Different solutions for different needs.
- gravypod 8y ago
  One situation I had was storing consent forms for medical patients. If we enabled their care in the DB and lost their consent form we could be sued for quite a bit.
dmitryminkovsky 8y ago
It took me a long time to shake this line of thinking. Some things just won’t and don’t have to be perfect like that. You can’t store files in a DB, and you can’t be atomic across multiple systems like, say, a DB and a file system. It’s better to just figure out a working solution, like content address storing your file so that retrying is idempotent, then updating your DB once you’ve stored the file.
- fvdessen 8y ago
  If you content address your files then you have problems when tracking entities linking to the same file. (like email sender and recipient linking to the same attachement. Same file; different owners)
  - dmitryminkovsky 8y ago
    I don’t think so? Whatever entity you do add to the DB can reference that content address and can determine permissions. That DB entity is what makes access to the underlying content possible. The file is garbage collected when nothing in the DB references it.
kijin 8y ago
OP talks about having to wait 32 minutes to dump a 35GB database. That's nothing. I could do that every day without breaking a sweat. Thanks to ACID, it doesn't even matter if other users are modifying the database during those 32 minutes. It matters even less if you're dumping a slave. But how about waiting a whole day to dump a 5TB database? It would have been a major pain in the ass if the real-world customer I'm talking about had chosen to store all their files in the database. Thankfully the files are stored elsewhere, so the database only weighs about 80GB. That's small enough to dump, pipe to gzip, pipe to ssh, and store in a remote location every single day.
- 1337shadow 8y ago
  What desperate me, is that it takes 32 minutes to dump relational data of a baby project with not even 100k relations. Re-dumping binary files over and over again when it should not be necessary ... it's getting worse every day. Add to this that Bob the manager will get you a new server only when you fill up your server and you might get tired of it too. They also don't keep files 33 months old or something, which means that postgresql is also going to have to deal with space efficiently on removal on a regular basis. Files in PostgreSQL, works for PostgreSQL, but that becomes a bottleneck in automated deployment. It's 95% of the deployment time and growing, is this a timeframe where we should take down the service writes or no that's another question to deal with.
kissgyorgy 8y ago
Good frameworks or ORMs has hooks that can ensure things happened (e.g. file deletion) BEFORE the final commit. It's very easy to do this nowadays, you don't have to implement it yourself. You can't get away without files either. Suggesting that you only need a database for everything implies that you store every type of data in it which is not possible long term. Some type of data just doesn't fit there, especially files.
- AgentME 8y ago
  Doing the deletion before the commit means that if the commit fails, you're left with a row that refers to a non-existent file. If that's a problem, then it's better do the deletion afterward. And not just "step 1, commit to the database, step 2, erase the file from S3", because a failure between those steps will cause files to be left orphaned on S3. A good system would be to mark the row as having its file soft-deleted, and have a task the regularly looks for rows marked soft-deleted, make sure the file is deleted from S3, and then mark the row as actually deleted.
zvrba 8y ago
Or you can use a database that supports something like SQL server's FILESTREAM [1]: "FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data." [1] https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/bl...
kerng 8y ago
Depends on the database you are using, Microsoft SQL Server has a file stream feature, optimized for exactly that scenario. They store the actual files outside the DB. https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/bl...
1337shadow 8y ago
Actually in our case, users upload files with AJAX before they submit their form which data gets inserted in the relational DB. As such I fail to see how storing them in the DB helps at all because files are going to be added in an HTTP transaction that happens before the one that causes the meaningful write to the relational database.

by blendo 8y ago

kbenson 8y ago

And your replication handles other headaches you might have to consider if you have a Sapa rate file storage system. DB as a file service is a horrible idea except for when it's the best choice.

hderms 8y ago

I think it's fair to ask whether the file's _contents_ need to be ACID transactional or could you just mark the row as deleted and schedule a job/Cron to clean them up? In light of that, and the fact that a binary blob isn't really a relational piece of data and I can see the logic behind not storing it in a relational DB.

Matthias247 8y ago
I agree with that. Doing some kind of asynchronous garbage collection (Dumping all uploaded files, dumping the filenames from DB, and deleting all that are not required anymore) might be a lot easier to get right and reliable than trying to do everything in the request path.
- fgonzag 8y ago
  This is a such a good solution in many cases and is often ignored.
- fatnoah 8y ago
  This is what I did at a previous company where the main product was a messaging app with daily active users in the millions. Deletions were bit flips that were then cleaned up later by a cron job.

nemothekid 8y ago

>you'll have to write your own two-phase commit logic to ensure when row X is deleted, then /wherever/X.jpg is also deleted I can't imagine a situation where you would need strict consistency with a BLOB and a database row unless you were also modifying bits inside the BLOB (which wouldn't be true if you are hosting images). If you just need to ensure that whenever user X says DELETE, you can put S3 infront of an API and just query the database before you return the URL. If you really need the file gone, I would rather just encrypt the image and store the key in the database row. In a world where S3 doesn't exist, I would still build something similar because coupling my object store with my actual data store just seems like asking for trouble. Having to wait hours to backup several kilobytes of changed data in a multi-terabyte database is just inviting people to cut corners.

kbenson 8y ago
Storing legal contracts associated with accounts. I implemented a system like this. Documents were simple and usually small, pure html with no images or hrml converted to PDF, occassionally it was an attached JPEG from something someone emailed or faxed. If you want a system where the documents are strictly linked to the account, easy to query based on account, consistent (in the DB sense), replicated and backed up with all the other very important customer info, and local so you aren't exposed to external risk factors it's the path of least resistance, and can be easily and securely implemented with very little effort. Different solutions for different needs.
gravypod 8y ago
One situation I had was storing consent forms for medical patients. If we enabled their care in the DB and lost their consent form we could be sued for quite a bit.

dmitryminkovsky 8y ago

It took me a long time to shake this line of thinking. Some things just won’t and don’t have to be perfect like that. You can’t store files in a DB, and you can’t be atomic across multiple systems like, say, a DB and a file system. It’s better to just figure out a working solution, like content address storing your file so that retrying is idempotent, then updating your DB once you’ve stored the file.

fvdessen 8y ago
If you content address your files then you have problems when tracking entities linking to the same file. (like email sender and recipient linking to the same attachement. Same file; different owners)
- dmitryminkovsky 8y ago
  I don’t think so? Whatever entity you do add to the DB can reference that content address and can determine permissions. That DB entity is what makes access to the underlying content possible. The file is garbage collected when nothing in the DB references it.

kijin 8y ago

OP talks about having to wait 32 minutes to dump a 35GB database. That's nothing. I could do that every day without breaking a sweat. Thanks to ACID, it doesn't even matter if other users are modifying the database during those 32 minutes. It matters even less if you're dumping a slave. But how about waiting a whole day to dump a 5TB database? It would have been a major pain in the ass if the real-world customer I'm talking about had chosen to store all their files in the database. Thankfully the files are stored elsewhere, so the database only weighs about 80GB. That's small enough to dump, pipe to gzip, pipe to ssh, and store in a remote location every single day.

1337shadow 8y ago
What desperate me, is that it takes 32 minutes to dump relational data of a baby project with not even 100k relations. Re-dumping binary files over and over again when it should not be necessary ... it's getting worse every day. Add to this that Bob the manager will get you a new server only when you fill up your server and you might get tired of it too. They also don't keep files 33 months old or something, which means that postgresql is also going to have to deal with space efficiently on removal on a regular basis. Files in PostgreSQL, works for PostgreSQL, but that becomes a bottleneck in automated deployment. It's 95% of the deployment time and growing, is this a timeframe where we should take down the service writes or no that's another question to deal with.

kissgyorgy 8y ago

Good frameworks or ORMs has hooks that can ensure things happened (e.g. file deletion) BEFORE the final commit. It's very easy to do this nowadays, you don't have to implement it yourself. You can't get away without files either. Suggesting that you only need a database for everything implies that you store every type of data in it which is not possible long term. Some type of data just doesn't fit there, especially files.

AgentME 8y ago
Doing the deletion before the commit means that if the commit fails, you're left with a row that refers to a non-existent file. If that's a problem, then it's better do the deletion afterward. And not just "step 1, commit to the database, step 2, erase the file from S3", because a failure between those steps will cause files to be left orphaned on S3. A good system would be to mark the row as having its file soft-deleted, and have a task the regularly looks for rows marked soft-deleted, make sure the file is deleted from S3, and then mark the row as actually deleted.

zvrba 8y ago

Or you can use a database that supports something like SQL server's FILESTREAM [1]: "FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data." [1] https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/bl...

kerng 8y ago

Depends on the database you are using, Microsoft SQL Server has a file stream feature, optimized for exactly that scenario. They store the actual files outside the DB. https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/bl...

1337shadow 8y ago

Actually in our case, users upload files with AJAX before they submit their form which data gets inserted in the relational DB. As such I fail to see how storing them in the DB helps at all because files are going to be added in an HTTP transaction that happens before the one that causes the meaningful write to the relational database.