I don't. S3-compatible storages usually are significantly cheaper, allow to offload HTTP requests. Also huge databases make backups and recoveries slow.
The only upside of storing blobs in the database is transactional semantics. Buf if you're fine with some theoretical trash in S3, that's trivially implemented with proper ordering.
> The only upside of storing blobs in the database is transactional semantics.
Plenty more advantages than that. E.g for SaaS you can deep copy an entire tenant including their digital assets. Much easier copying with just "insert into ... Select from" than having to copy S3 objects.
Or the opposite side of that, deletion. Pruning 38TB worth of S3 would be an unholy exercise unless the keys are 1TB each. I am aware of Lifecycle Policies, but they are a gargantuan PITA, IMHO
If you can't stream data the user is sending through the server to the database you have to buffer the entire payload in memory. This doesn't scale well but can work for smaller payloads.
You CAN stream bytea but:
* Good luck finding a client that supports it(npgsql does actually)
* It doesn't support support start the read/write at any location other than the start. So you'd have to basically build the Lob(Large Object) functionality yourself on top to support upload resume and other use cases.
With Lob even if your client doesn't support streaming, you can mimick it by writing chunks into the Lob.
J/K. It could be a really good back-end option for Supabase's S3 front end. A lot of PG clients don't support proper "streaming" and looking at the codebase it's TypeScript.. postgres.js is the only client nearing "performant" I'm aware of(last I looked) on Node.js but it's not clear it supports streaming outside "Copy" per the docs. Support could be added to the client if missing.
Edit: Actually it could be a good option for your normal uploads too. Docs talk about it being ideal for 6Mb or smaller files? Are you using bytea or otherwise needing to buffer the full upload/download in memory? Streaming with Lob would resolve that, and you can compute incremental hash sums for etags and etc. Lob has downsides and limitations but for a very large number of people it has many great benefits that can carry them very far and potentially all the way.
Will the files get deleted with ON CASCADE DELETE somehow? That would be awesome.
Then for GDPR, when you delete a user, the associated storage can be deleted.
One could cobble this together with triggers, some kind of external process, and probably repetititious code so there is one table of metadata per "owning" id, although it would be nice to be packaged.
We have discussed this internally before since we have seen some users delete the metadata in the storage schema and expect the underlying object to be deleted too and if we should convert our entire storage server to just be a Postgres extension.
The source of truth also matters here - if it's the database or the underlying s3 bucket. I think having the underlying storage bucket to be the source of truth would be more useful. In that scenario we would sync the metadata in the database to match what's actually being stored and if we notice metadata of a object missing, we add that in as opposed to deleting the object in storage. This would make it easier for you to bring in your own s3 bucket with existing data and attach it to Supabase storage.
That's an interesting concept. Of course you don't want to delete your record of the blob until the blog is deleted.. A trigger could add a "job" to delete the blob into another table when the file record is deleted though..
Lol. The most PG blob storage I've used in prod was a couple hundred GB. It was a hack and the performance wasn't ideal, but the alternatives were more complicated. Simple is good.
Yeah, it's a great place to start. I took the time to implement streaming reads/write via npgsql's client support for it (it can stream records, and of course the Lob storage is broken into page sized rows) and performance is pretty darn good.
The only upside of storing blobs in the database is transactional semantics. Buf if you're fine with some theoretical trash in S3, that's trivially implemented with proper ordering.