Coupling attachments/files to the database in a portable way.
I understand it's not best practice to store (especially large) attachments/files in a database (PostgreSQL in my case) as it could tank performance depending on how your queries are formatted. I would like to instead maybe store a pointer to the file location on disk, but this isn't portable between devices long term. I don't really have the means or manpower to run my own CDN for a project where only the project itself needs access to the files....what should I do?
One option that might maybe work is to devise and run an API for storing and retrieving attachments on the same network, but I'd be worried about testing and prod referencing the same set of files leading to potential collisions or other potential don't-touch-prod-data issues
11 Replies
Just ensure the file paths are relative
The issue is that if I'm developing on device A and deploying to device B, I'd then have to copy the file state between devices each time
or if I were debugging an issue (A), I'd have to copy all data from device B in case it is related to the file storage
I mean, you still have to get the application files there, and have to copy the database
What's copying a zip file too
Other than that, you'll have to rely on 3rd parties, or host a CDN yourself
Backblaze B2 has a nice free offer
Cloudflare R2 is decently cheap and has no egress costs
I'll look into backblaze, maybe. I'm talking like...between 5-10 attachments being created a month, and most are less than 10mb, some are even less than 1mb
I'd trust a company to do a better job storing and securing random files than my own dumb ass at least, as long as they have some sort of API interop
Oh yeah, use B2 then
If you proxy it through Cloudflare you also get free egress
Actually
Cloudflare R2 should also be free for you
https://developers.cloudflare.com/r2/pricing/
I'm a little confused by the pricing. 10 GB/month means I'm allotted 10 total GB of storage?
I guess what it means is that if you store 1 GB of data you can store it for free for 10 months
If you store 1 GB, you start incurring cost after a month
Etc
So, yeah, B2 might be a better option here
yeah, i'm not sure if it changes things but I do want to ensure these files persist indefinitely, so it seems that R2 might not be feasible for long term
thanks for the advice, probably better than rolling my own file storage nonsense
B2 is permanently free up to... some gigabytes of storage
You'd have to check out what are their free limits on egress and stuff, though
i think they're limited to 10gb for free and then $6/TB mo, dunno for sure
man those metrics are weird, hard to wrap my head around wtf a TB mo is LOL
Perfect if you're not going to store more than 10 GB