Storing files in the DB: story of an epic failure | Hacker News Reader

Storing files in the DB: story of an epic failure

blog.yourlabs.org

68 points1337shadow56 comments

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.

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.

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.

This is a such a good solution in many cases and is often ignored.

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.

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.

>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.

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.

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.

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.

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)

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.

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.

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.

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.

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.

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/bl...

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/bl...

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.

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

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 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?

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.

S3 is the complexity. anything added to a system doubles the complexity of said system...

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?

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.

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.

Deletions for one. You'll need a background job with retry logic.

Why a background job? Just delete the object when you delete the record.

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"

- 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.

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.

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.

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

2: https://2019.linux.conf.au/schedule/presentation/212/

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.

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.

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

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.

Latency I can see. I find it hard to believe S3 has a lower availability than a sata drive. Given that S3 is replicated.

S3 maybe has better availability. Your DSL/fiber and router, that are on the critical path, definitely not.

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.

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)

I do similar with my database ( https://github.com/amark/gun ) and get great performance even out of free Heroku instances.

0 dev ops to manage too, can actually sleep at night.

100GB / day (100M records) for $10 all costs: https://youtu.be/x_WqBuEA7s8

TrailDB much better than ours at this though.

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.

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.

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...

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

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?

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/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.

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)

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.

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.

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

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

…

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.

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

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.

Reply on news.ycombinator.com