Hacker News Reader

Comment by EMM_386 | Hacker News Reader

"Databases can now store files and images"

Just with SQL Server alone, for decades now you can store BLOB data.

Need more than 2GB?

There's FILESTREAM, where enormous files can sit on the disk, outside the DB, but be referenced as if they were in the database via a varbinary(max) column.

"Think of it as having a new database column type where you can store files of any size"

We have that. It might not be AWS S3 and cached on a CDN, but that is just adding additional cost, complexity, and lock-in.

View on HN

> Just with SQL Server alone, for decades now you can store BLOB data.

Yes, and for decades, it's been a bad idea for SQL Servers that grow over time.

When you store files in the database, you massively inflate the size of the data file and the transaction log. It makes all your maintenance tasks take longer: backups, restores, high availability, disaster recovery, corruption checking.

To make matters worse, when a new version of the file arrives, people never overwrite the existing file - they store the new version as a new row or object, and keep the old version around too. Space requirements explode exponentially.

If you're dealing with a 10-100GB database, sure, it seems fine at first. But after you start storing files in there, you won't have a 10-100GB database - you'll have a terabyte-sized one, and that's when your maintenance task heartaches begin.

> Yes, and for decades, it's been a bad idea for SQL Servers that grow over time.

I never said storing BLOBs was a great idea, but for smaller databases, like you say, and with small files, it is an option and it won't wreck your performance like you think in many cases. The profiler knows how to handle this, and if you query doesn't need it, they aren't going to impact it like you are saying. As always test, test, test.

For large databases, that's why I meantioned FILESTREAM. Those are not in the database, they are essentially pointers to files on disk.

> To make matters worse, when a new version of the file arrives, people never overwrite the existing file - they store the new version as a new row or object

This may be a requirement? And if it is, you have to store it somewhere. Using FILESTREAM you are free to put the actual files wherever you want. The database column says where to find it.

yes, I remember 10 - 20 years, long time ago, Microsoft made something similar to Google Earth to showcase how SQL Server could store and serve massive amounts of binary files.

Reply on news.ycombinator.com