<story><title>File Attachments: Databases can now store files and images</title><url>https://xata.io/blog/file-attachments</url></story><parent_chain><item><author>bastawhiz</author><text>I think the one big problem with BLOBs, especially if you have a heavily read-biased DB, is you&amp;#x27;re going to run up against bandwidth&amp;#x2F;throughput as a bottleneck. One of the DBs I help maintain has some very large JSON columns and we frequently see this problem when traffic is at its peak: simply pulling the data down from Postgres is the problem.&lt;p&gt;If the data is frequently accessed, it also means there are extra hops the data has to take before getting to the user. It&amp;#x27;s a lot faster to pull static files from S3 or a CDN (or even just a dumb static file server) than it is to round trip through your application to the DB and back. For one, it&amp;#x27;s almost impossible to stream the response, so the whole BLOB needs to be copied in memory in each system it passes through.&lt;p&gt;It&amp;#x27;s rare that any request for, say, user data would &lt;i&gt;also&lt;/i&gt; return the user avatar, and so you ultimately just end up with one endpoint for structured data and one to serve binary BLOB data which have very little overlap except for ACL stuff, but signed S3 URLs will get you the same security properties with much better performance overall.</text></item><item><author>adrianmsmith</author><text>I think for smaller projects just storing images as BLOBs in e.g. PostgreSQL works quite well.&lt;p&gt;I know this is controversial. People will claim it&amp;#x27;s bad for performance.&lt;p&gt;But that&amp;#x27;s only bad if performance is something you&amp;#x27;re having problems with, or going to have problems with. If the system you&amp;#x27;re working on is small, and going to stay small, then that doesn&amp;#x27;t matter. Not all systems are like this, but some are.&lt;p&gt;Storing the images directly in the database has a lot of advantages. They share a transactional context with the other updates you&amp;#x27;re doing. They get backed up at the same time, and if you do a &amp;quot;point in time&amp;quot; restore they&amp;#x27;re consistent with the rest of your data. No additional infrastructure to manage to store the files.</text></item></parent_chain><comment><author>dfee</author><text>I’m sure you’re right, but it’s unbelievable to me how cost effective people claim S3 is. I’ve just never been able to get the pricing calculator to show me “cheap”. And I guess I’ve never really gotten comfortable with the access patterns, as it’s not a file system.&lt;p&gt;Where could I explore situations where people have used S3 with extremely favorable conditions relative to local storage(?), in terms of: price, access latency, and any other relevant column?&lt;p&gt;I want to believe, it’s just hard for me to go all in on an Amazon API.</text></comment>
<story><title>File Attachments: Databases can now store files and images</title><url>https://xata.io/blog/file-attachments</url></story><parent_chain><item><author>bastawhiz</author><text>I think the one big problem with BLOBs, especially if you have a heavily read-biased DB, is you&amp;#x27;re going to run up against bandwidth&amp;#x2F;throughput as a bottleneck. One of the DBs I help maintain has some very large JSON columns and we frequently see this problem when traffic is at its peak: simply pulling the data down from Postgres is the problem.&lt;p&gt;If the data is frequently accessed, it also means there are extra hops the data has to take before getting to the user. It&amp;#x27;s a lot faster to pull static files from S3 or a CDN (or even just a dumb static file server) than it is to round trip through your application to the DB and back. For one, it&amp;#x27;s almost impossible to stream the response, so the whole BLOB needs to be copied in memory in each system it passes through.&lt;p&gt;It&amp;#x27;s rare that any request for, say, user data would &lt;i&gt;also&lt;/i&gt; return the user avatar, and so you ultimately just end up with one endpoint for structured data and one to serve binary BLOB data which have very little overlap except for ACL stuff, but signed S3 URLs will get you the same security properties with much better performance overall.</text></item><item><author>adrianmsmith</author><text>I think for smaller projects just storing images as BLOBs in e.g. PostgreSQL works quite well.&lt;p&gt;I know this is controversial. People will claim it&amp;#x27;s bad for performance.&lt;p&gt;But that&amp;#x27;s only bad if performance is something you&amp;#x27;re having problems with, or going to have problems with. If the system you&amp;#x27;re working on is small, and going to stay small, then that doesn&amp;#x27;t matter. Not all systems are like this, but some are.&lt;p&gt;Storing the images directly in the database has a lot of advantages. They share a transactional context with the other updates you&amp;#x27;re doing. They get backed up at the same time, and if you do a &amp;quot;point in time&amp;quot; restore they&amp;#x27;re consistent with the rest of your data. No additional infrastructure to manage to store the files.</text></item></parent_chain><comment><author>avereveard</author><text>Also cost is a factor, databases are usually attached to expensive disk and their storage layer is tuned for iops, and blobs will be using gigs of that for just sitting there being sequentially scanned.</text></comment>