7 ms·

Storing files in the DB: story of an epic failure

68 points by 1337shadow 8y ago

Something1234 8y ago
That didn't really say much. Don't store file content in your databases, otherwise your backups will balloon to massive size.
- mcny 8y ago
  Personally, I think the answer is it depends. If all we are storing are profile pictures for a small app for a small user base, I think it is better to store things in the database rather than adding complexity of S3. I mean if you are making an image board like https://github.com/DangerOnTheRanger/maniwani https://github.com/DangerOnTheRanger/maniwani you should not listen to me and use something like S3 because the images are the bread and butter of your application but often user generated images only used for profile pictures might be okay to store in a database. Why make things more complicated than they have to be?
  - scarface74 8y ago
    What complexity? If you Store your files in an S3 you get redundancy for free, easy cross region replication, you can put them behind CloudFront as a CDN.
    - blondin 8y ago
      S3 is the complexity. anything added to a system doubles the complexity of said system...
      - scarface74 8y ago
        So instead of aphorisms, care to explain the complexity of issuing one command to copy a file and storing the URL in the database and letting AWS taking care of copying?
        BurningFrog 8y ago
        Doing something is always more complexity than doing nothing. Some seem to think "complexity" only happens when something is really hard. That's not how to think of it at all. Systems usually become overly complex by adding many little simple things. Each of those may be trivial, but together they can make your system impossible to understand and manage.
        scarface74 8y ago
        You still haven’t said how in this particular case it’s more complex. In fact, you are offloading sending images to AWS instead of you doing it yourself.
        0xFACEFEED 8y ago
        Deletions for one. You'll need a background job with retry logic.
        scarface74 8y ago
        Why a background job? Just delete the object when you delete the record.
        bencoder 8y ago
        You've skipped over quite a lot. - learning about and understanding S3 in the first place. - Billing and AWS account management (possibly requiring agreement/permission from others) - maintaining credentials securely for your app to access S3. - bug fixing (for e.g. it's easy to get the permissions wrong when you upload so that it's not visible) Since you're already storing the URL in your dB, why not just store the binary data you have? I don't think you're wrong about using something like S3 being a better solution, but it's not quite as simple as "issuing one command"
        scarface74 8y ago
        - there is literally one command and a quick Google search. - he brought up S3, I am assuming he’s already on AWS - you don’t have to. The role attached to your EC2 instance will have permission. If he is already on AWS, hopefully he knows that. - From the requirements it seems like the images should be publicly accessible. If not, when you create a bucket, it’s by default private. Yes if he wants to limit access he would have to either stream from S3 to the client - I had to do that it was a quick Google search - or generate presigned URL. Why not store binary data in the DB? It makes backups larger. That being said, SQL Server has the filestream type that does in fact store the binary data as a separate file on disk, but gets treated as if it is part of the database for backup and restore purposes.
  - basetop 8y ago
    If you don't have to worry about scale and you are sure your user base is going to remain small and stable, then you are fine. If you expect it to grow, then you should look into dumping your files on a file server. This way you can scale, backups of your database and file server can be done quickly, independently or in parallel and it allows for better use of network resources ( your app/server/etc can distribute requests to both db and file server instead of having one large request from the db). At the end of the day, if it works and you are aware of the business needs, that's all that matters.
  - manigandham 8y ago
    That means you also need to serve those images yourself and even the smallest HTTP server/route is more work to maintain instead of just serving directly from the URL you get with S3.
andr 8y ago
Relevant tweet [1]: "Do you know S3 throughput is higher than a SATA 3 controller and almost as much as PCIe 4.0 2x? Depending on the usecase, mounting S3 as a filesystem, not only makes sense, but it saves a LOT of money (and time) as well. You just need to use it a special kind of filesystem." [1] [1] https://twitter.com/dialtone_/status/1115130667677786112 https://twitter.com/dialtone_/status/1115130667677786112
- hedora 8y ago
  S3 latency and availability are many (3, 4, 5?) orders of magnitude worse than those other things. In other news, smart cars and supercars have comparable leg room, and are therefore interchangeable, depending on your use case.
  - sacheendra 8y ago
    Latency I can see. I find it hard to believe S3 has a lower availability than a sata drive. Given that S3 is replicated.
    - cafxx 8y ago
      S3 maybe has better availability. Your DSL/fiber and router, that are on the critical path, definitely not.
      - sacheendra 8y ago
        I was speaking in the context of the cloud. If you are using S3, I presume you are using AWS EC2 or other services. Otherwise network egress costs dwarf everything else for most use cases. Except maybe as a repo of database backups.
    - khc 8y ago
      Depends on what you mean by availability. AWS S3 does return 500 errors from time to time and you are supposed to retry. There are also opaque rate limiting. These are mostly hidden from you if you use the AWS sdk but still is something to be aware of if you are using it in scale. And we haven't even started to talk about eventual consistency. I am the author of goofys (which exposes s3 like a filesystem) so obviously I believe this can work, but you do need to understand the tradeoffs (or you use goofys and hope that the tradeoffs I've made are correct :-P)
xwdv 8y ago
When should we consider storing uploaded files in the file system instead of in S3?
siliconc0w 8y ago
Just a TLDR, don't. Store the database record with a pointer to a more scalable better suited datastore like an object storage system (i.e S3). Most frameworks have a pattern for this (i.e https://guides.rubyonrails.org/active_storage_overview.html https://guides.rubyonrails.org/active_storage_overview.html)
- kopos 8y ago
  Or if it's at the non webscale level just store it in your own filesystem and serve from your filesystem instead of doing it from S3.
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.
mattmein 8y ago
I've never understood this advice. To me it just makes a lot more sense to store everything in the database - much less complexity. What I've done in the past is just put a CDN in front of the file url, so the file content is stored in the db, but served from the CDN.
- bigiain 8y ago
  This is nearly always and for nearly everything, the right answer. Even that article sort of acknowledges this, when it talks about being ultra conservative in your estimations of when this solution will start to fall apart. Facebook can clearly not "just store all the photos and images in the db! It's less complex!" Same with Google. To 4 or 5 or more nines of certainty, whatever you're working on doesn't now and never will have Facebook scale problems. And if it _does_, hopefully your business plan has you wallowing in cash-on-hand to pay teams of other smart engineers to help you solve that problem. (I'll respect you more if that pile of cash comes from customers buying your shit, but I'll acknowledge that wallowing in VC money you've raised on hockeystick growth or engagement numbers is also a valid path.) If your business plan doesn't explain how you'll pay for a db.r4.16xlarge (or your cloud provider or on-prem db vendor's equivalent) with the number of users (or transactions/whatever-your-apporpriate-metric) that a topped-out vertically scaled db can support doing things the "easy and least complicated way", perhaps _that's_ a more important problem to be working on that ACID compliant deletion of user photos...
danielparks 8y ago
So it’s a pain to back up large databases. …
- 1337shadow 8y ago
  In this case, storing files in the database is a bottleneck at many levels, if you have are going to drop files older than X months then PG will have to deal disk space efficiently, anyway: you get your ever growing continuous delivery bottleneck identified.
rob-olmos 8y ago
Had to deal with this before. Really hampered things with bloated backups and extended restore times during restoration tests. Also not a good plan for the database to no longer fit within RAM, especially if the storage engine is evicting cached pages for blob content, only to populate a CDN.
reilly3000 8y ago
I wish there was a PG extension, FDW or something that would facilitate blobs being stored externally but managed with regular database operations. sure you can store a reference to it, but your application needs to handle everything in concert with database transformations. Bonus points if it can interface with S3(compatible) versioning to give these virtual blobs an audit trail and rollback. How sweet would it be to have a profile pic that could be updated and rolled back with just a little SQL?
etaioinshrdlu 8y ago
Maybe we should make our databases support this use case a little better? I don't see why not. Sounds fun!
- 1337shadow 8y ago
  Do you would mean like supporting open file handlers in SQL protocol ? This is still going to be the ever growing bottleneck and liability in your deployment pipeline, if you intend to practice Continuous Delivery.
ttctciyf 8y ago
Coincidentally, I came to this fresh from watching "Database as Filesystem"[1,2], which takes a different view: > Most network file systems are either a layer over an existing filesystem (NFS, CIFS), or are develped from scratch to have separate, replicated, purpose-designed databases for metadata and object store (GFS, Glusterfs). At the same time, most database engines provide (or can be coerced into providing) replication and all the ACID properties needed for a high-performance filesystem. > Idea: Use a database engine (Postgres, MariaDB) on raw partitions with a fast separate nVME log file; build POSIX file system semantics on top. It's pretty obvious that this could work; I'm just starting to implement it so performance and durability can be measured. Epic fail fans and optimists alike will be excited by an actual implementation attempt, I guess :) 1: https://www.youtube.com/watch?v=wN6IwNriwHc https://www.youtube.com/watch?v=wN6IwNriwHc 2: https://2019.linux.conf.au/schedule/presentation/212/ https://2019.linux.conf.au/schedule/presentation/212/
- Felz 8y ago
  S3QL does this with sqlite. An actual external DB would give you multiple users at once, though. I've been using a combination of S3/Postgres to get filelike semantics myself, but not with a POSIX interface. The biggest problem for general use is that S3 is really for immutable file storage, and not so much things that'll change a lot. That can be worked around, obviously.
- 1337shadow 8y ago
  Thanks for sharing this ! it's lovely from a technical perspective and researching with Peter Chubb sounds like a transcending experience ... but if you have Bob the manager instead of Peter Chubb, and that you intend to practice continuous delivery on your product codebase, making backups part of your automated deployment pipeline introduces an ever growing bottleneck.
abacate 8y ago
Dumping a big database continuously for any reason (including backups) is a terrible design decision.
kerng 8y ago
Why do people always reinvent and start from scratch and then complain always puzzles me. SQL Server Filestream Feature: 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... FILESTREAM integrates the SQL Server Database Engine with an NTFS or ReFS file systems by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
NikkiA 8y ago
That font (Oswald) hurts my eyes to read to the point where I couldn't read the bits of the article I wished to read.

68 points by 1337shadow 8y ago

That didn't really say much. Don't store file content in your databases, otherwise your backups will balloon to massive size.

mcny 8y ago
Personally, I think the answer is it depends. If all we are storing are profile pictures for a small app for a small user base, I think it is better to store things in the database rather than adding complexity of S3. I mean if you are making an image board like https://github.com/DangerOnTheRanger/maniwani https://github.com/DangerOnTheRanger/maniwani you should not listen to me and use something like S3 because the images are the bread and butter of your application but often user generated images only used for profile pictures might be okay to store in a database. Why make things more complicated than they have to be?
- scarface74 8y ago
  What complexity? If you Store your files in an S3 you get redundancy for free, easy cross region replication, you can put them behind CloudFront as a CDN.
  - blondin 8y ago
    S3 is the complexity. anything added to a system doubles the complexity of said system...
    - scarface74 8y ago
      So instead of aphorisms, care to explain the complexity of issuing one command to copy a file and storing the URL in the database and letting AWS taking care of copying?
      - BurningFrog 8y ago
        Doing something is always more complexity than doing nothing. Some seem to think "complexity" only happens when something is really hard. That's not how to think of it at all. Systems usually become overly complex by adding many little simple things. Each of those may be trivial, but together they can make your system impossible to understand and manage.
        scarface74 8y ago
        You still haven’t said how in this particular case it’s more complex. In fact, you are offloading sending images to AWS instead of you doing it yourself.
        0xFACEFEED 8y ago
        Deletions for one. You'll need a background job with retry logic.
        scarface74 8y ago
        Why a background job? Just delete the object when you delete the record.
      - bencoder 8y ago
        You've skipped over quite a lot. - learning about and understanding S3 in the first place. - Billing and AWS account management (possibly requiring agreement/permission from others) - maintaining credentials securely for your app to access S3. - bug fixing (for e.g. it's easy to get the permissions wrong when you upload so that it's not visible) Since you're already storing the URL in your dB, why not just store the binary data you have? I don't think you're wrong about using something like S3 being a better solution, but it's not quite as simple as "issuing one command"
        scarface74 8y ago
        - there is literally one command and a quick Google search. - he brought up S3, I am assuming he’s already on AWS - you don’t have to. The role attached to your EC2 instance will have permission. If he is already on AWS, hopefully he knows that. - From the requirements it seems like the images should be publicly accessible. If not, when you create a bucket, it’s by default private. Yes if he wants to limit access he would have to either stream from S3 to the client - I had to do that it was a quick Google search - or generate presigned URL. Why not store binary data in the DB? It makes backups larger. That being said, SQL Server has the filestream type that does in fact store the binary data as a separate file on disk, but gets treated as if it is part of the database for backup and restore purposes.
- basetop 8y ago
  If you don't have to worry about scale and you are sure your user base is going to remain small and stable, then you are fine. If you expect it to grow, then you should look into dumping your files on a file server. This way you can scale, backups of your database and file server can be done quickly, independently or in parallel and it allows for better use of network resources ( your app/server/etc can distribute requests to both db and file server instead of having one large request from the db). At the end of the day, if it works and you are aware of the business needs, that's all that matters.
- manigandham 8y ago
  That means you also need to serve those images yourself and even the smallest HTTP server/route is more work to maintain instead of just serving directly from the URL you get with S3.

andr 8y ago

Relevant tweet [1]: "Do you know S3 throughput is higher than a SATA 3 controller and almost as much as PCIe 4.0 2x? Depending on the usecase, mounting S3 as a filesystem, not only makes sense, but it saves a LOT of money (and time) as well. You just need to use it a special kind of filesystem." [1] [1] https://twitter.com/dialtone_/status/1115130667677786112 https://twitter.com/dialtone_/status/1115130667677786112

hedora 8y ago
S3 latency and availability are many (3, 4, 5?) orders of magnitude worse than those other things. In other news, smart cars and supercars have comparable leg room, and are therefore interchangeable, depending on your use case.
- sacheendra 8y ago
  Latency I can see. I find it hard to believe S3 has a lower availability than a sata drive. Given that S3 is replicated.
  - cafxx 8y ago
    S3 maybe has better availability. Your DSL/fiber and router, that are on the critical path, definitely not.
    - sacheendra 8y ago
      I was speaking in the context of the cloud. If you are using S3, I presume you are using AWS EC2 or other services. Otherwise network egress costs dwarf everything else for most use cases. Except maybe as a repo of database backups.
  - khc 8y ago
    Depends on what you mean by availability. AWS S3 does return 500 errors from time to time and you are supposed to retry. There are also opaque rate limiting. These are mostly hidden from you if you use the AWS sdk but still is something to be aware of if you are using it in scale. And we haven't even started to talk about eventual consistency. I am the author of goofys (which exposes s3 like a filesystem) so obviously I believe this can work, but you do need to understand the tradeoffs (or you use goofys and hope that the tradeoffs I've made are correct :-P)

xwdv 8y ago

When should we consider storing uploaded files in the file system instead of in S3?

siliconc0w 8y ago

Just a TLDR, don't. Store the database record with a pointer to a more scalable better suited datastore like an object storage system (i.e S3). Most frameworks have a pattern for this (i.e https://guides.rubyonrails.org/active_storage_overview.html https://guides.rubyonrails.org/active_storage_overview.html)

kopos 8y ago
Or if it's at the non webscale level just store it in your own filesystem and serve from your filesystem instead of doing it from S3.

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.

mattmein 8y ago

I've never understood this advice. To me it just makes a lot more sense to store everything in the database - much less complexity. What I've done in the past is just put a CDN in front of the file url, so the file content is stored in the db, but served from the CDN.

bigiain 8y ago
This is nearly always and for nearly everything, the right answer. Even that article sort of acknowledges this, when it talks about being ultra conservative in your estimations of when this solution will start to fall apart. Facebook can clearly not "just store all the photos and images in the db! It's less complex!" Same with Google. To 4 or 5 or more nines of certainty, whatever you're working on doesn't now and never will have Facebook scale problems. And if it _does_, hopefully your business plan has you wallowing in cash-on-hand to pay teams of other smart engineers to help you solve that problem. (I'll respect you more if that pile of cash comes from customers buying your shit, but I'll acknowledge that wallowing in VC money you've raised on hockeystick growth or engagement numbers is also a valid path.) If your business plan doesn't explain how you'll pay for a db.r4.16xlarge (or your cloud provider or on-prem db vendor's equivalent) with the number of users (or transactions/whatever-your-apporpriate-metric) that a topped-out vertically scaled db can support doing things the "easy and least complicated way", perhaps _that's_ a more important problem to be working on that ACID compliant deletion of user photos...

danielparks 8y ago

So it’s a pain to back up large databases. …

1337shadow 8y ago
In this case, storing files in the database is a bottleneck at many levels, if you have are going to drop files older than X months then PG will have to deal disk space efficiently, anyway: you get your ever growing continuous delivery bottleneck identified.

rob-olmos 8y ago

Had to deal with this before. Really hampered things with bloated backups and extended restore times during restoration tests. Also not a good plan for the database to no longer fit within RAM, especially if the storage engine is evicting cached pages for blob content, only to populate a CDN.

reilly3000 8y ago

I wish there was a PG extension, FDW or something that would facilitate blobs being stored externally but managed with regular database operations. sure you can store a reference to it, but your application needs to handle everything in concert with database transformations. Bonus points if it can interface with S3(compatible) versioning to give these virtual blobs an audit trail and rollback. How sweet would it be to have a profile pic that could be updated and rolled back with just a little SQL?

etaioinshrdlu 8y ago

Maybe we should make our databases support this use case a little better? I don't see why not. Sounds fun!

1337shadow 8y ago
Do you would mean like supporting open file handlers in SQL protocol ? This is still going to be the ever growing bottleneck and liability in your deployment pipeline, if you intend to practice Continuous Delivery.

ttctciyf 8y ago

Coincidentally, I came to this fresh from watching "Database as Filesystem"[1,2], which takes a different view: > Most network file systems are either a layer over an existing filesystem (NFS, CIFS), or are develped from scratch to have separate, replicated, purpose-designed databases for metadata and object store (GFS, Glusterfs). At the same time, most database engines provide (or can be coerced into providing) replication and all the ACID properties needed for a high-performance filesystem. > Idea: Use a database engine (Postgres, MariaDB) on raw partitions with a fast separate nVME log file; build POSIX file system semantics on top. It's pretty obvious that this could work; I'm just starting to implement it so performance and durability can be measured. Epic fail fans and optimists alike will be excited by an actual implementation attempt, I guess :) 1: https://www.youtube.com/watch?v=wN6IwNriwHc https://www.youtube.com/watch?v=wN6IwNriwHc 2: https://2019.linux.conf.au/schedule/presentation/212/ https://2019.linux.conf.au/schedule/presentation/212/

Felz 8y ago
S3QL does this with sqlite. An actual external DB would give you multiple users at once, though. I've been using a combination of S3/Postgres to get filelike semantics myself, but not with a POSIX interface. The biggest problem for general use is that S3 is really for immutable file storage, and not so much things that'll change a lot. That can be worked around, obviously.
1337shadow 8y ago
Thanks for sharing this ! it's lovely from a technical perspective and researching with Peter Chubb sounds like a transcending experience ... but if you have Bob the manager instead of Peter Chubb, and that you intend to practice continuous delivery on your product codebase, making backups part of your automated deployment pipeline introduces an ever growing bottleneck.

abacate 8y ago

Dumping a big database continuously for any reason (including backups) is a terrible design decision.

kerng 8y ago

Why do people always reinvent and start from scratch and then complain always puzzles me. SQL Server Filestream Feature: 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... FILESTREAM integrates the SQL Server Database Engine with an NTFS or ReFS file systems by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

NikkiA 8y ago

That font (Oswald) hurts my eyes to read to the point where I couldn't read the bits of the article I wished to read.