
Storing files in the DB: story of an epic failure - 1337shadow
https://blog.yourlabs.org/post/184290880553/storing-files-in-the-db-story-of-an-epic-failure
======
blendo
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.

~~~
nemothekid
> _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
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.

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

~~~
mcny
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
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
S3 is the complexity. anything added to a system doubles the complexity of
said system...

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

~~~
bencoder
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
\- 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.

------
ttctciyf
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/)

~~~
1337shadow
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.

------
andr
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
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
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
S3 maybe has better availability. Your DSL/fiber and router, that are on the
critical path, definitely not.

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

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

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

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

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

------
kerng
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...](https://docs.microsoft.com/en-us/sql/relational-
databases/blob/filestream-sql-server?view=sql-server-2017)

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.

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

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

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

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

…

~~~
1337shadow
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.

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

~~~
1337shadow
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.

