Let's attach what we're using to the ticket. All of it. Why is the ticketing DB huge? Well, you attached all of it.
I can't imagine how much money we were paying in licensing fees and storage costs.
Wrote a small Go library to interface with it: https://github.com/mohamedattahri/pgfs
Large Objects: https://www.postgresql.org/docs/current/largeobjects.html
For example, if you are building something that will have thousands or millions of records, and storing small binaries in postgresql lets you avoid integrating with S3 at all, then you should seriously consider doing it. The simplification gain almost certainly pays for any 'badness' and then some.
If you are working on something that you hope to scale to millions of users then you should just bite the bullet and integrate with S3 or something, because you will use too much db capacity to store binaries (assuming they aren't like 50 bytes each or something remarkably small) and that will force you to scale-out the database far before you would otherwise be forced to do so.
Disk space is cheap, even when it's on a DB server and not S3. Why worry that much about it?
Disk I/O less so. An average RDBMS writing a 10MB BLOB is actually writing at minimum 20-30MB to disk - once to journal/WAL, once to table storage, and for updates/deletes a third copy in the redo/undo log.
You also get a less efficient buffer manager with a higher eviction rate, which can further exasperate disk I/O.
My setup is to post blobs over a small rest api which balances storage load over all the file servers using nginx. It responds with the serverSubdomainId which in turn gets saved in the database. So the url for file access can be generated https://{serverSubdomainId}.domain.tld/{resourceId} The only costly part is writing which with caching of available storage of all the servers isn’t bad. The whole system is horizontally and vertically scalable. What am I missing that s3 is still the defacto even with vendor lock in and high egress costs?
Why object storage? Its easier to backup, version and to scale horizontally. Most solutions will also provide encryption capabilities that can be commanded either via external key management systems or from the client application. Also, custom access policies are great for private documents and file uploads.
Using static files is a good solution in some cases, nothing against it. But in many scenarios, there are huge benefits on using object storage instead, even if it is quite slower.
Having good default solution saves a lot of problems with migration. Storing files outside of database today really isn't that more complicated, while benefits even for small files are significant: you can use CDN, you don't want traffic spikes to affect database performance, you will want different availability guarantees for your media than for database and so on.
That aside I've had good experiences for some applications. It's certainly a lot easier than keeping a filesystem hierarchy in sync w/ the database, particularly if you're trying to replicate the database's native access control semantics to the filesystem. (So many applications get this wrong and leave their entire BLOB store, sitting out on a filesystem, completely exposed to filesystem-level actors with excessive permission.)
Microsoft has an interesting feature in SQL Server to expose BLOBs in a FUSE-like manner: https://learn.microsoft.com/en-us/sql/relational-databases/b...
I see postgresqlfs[0] on Github, but it's unclear exactly what it does and it looks like it has been idle for at least 6 years.
S3 can be a bit of a mismatch for people who want to work with FS objects as well, but there are a couple options that are a lot easier than dealing with blob files in PGsql. S3cmd, S3fs; perhaps SSHfs to the backing directory of Minio or direct access on the host (direct routes untested, unsure if it maps 1:1).
You would think at least with a delete especially in PG with the way it handles MVCC the blob would just have an entry saying which blob was deleted or something then if you need rollback you just undelete the blob.
So just being able to move and delete the data after it is in there becomes a real problem with a lot of it.
How many BLOBs does one need to have, and how often to we need to touch them for this solution to become untenable ?
So, the idea here is this: databases that store structured information, i.e. such that needs to store integers, booleans, short strings are typically something like relational databases, eg. PostgreSQL.
Filesystems (eg. Ext4) usually think about whole blocks, but are designed with the eye for smaller files, i.e. files aren't expected to be more than some ten or hundred blocks in size for optimal performance.
Object stores (eg. S3) are the kinds of storage systems that are supposed to work well for anything larger than typical files.
This gives the answer to your question: blobs in a relational database are probably OK if they are under one block big. Databases will be probably able to handle bigger ones too, but you will start seeing serious drops in performance when it comes to indexing, filtering, searching etc. because such systems optimize internal memory buffers in such a way that they can fit a "perfect" number of elements of the "perfect" size.
Another concern here is that with stored elements larger than single block you need a different approach to parallelism. Ultimately, the number of blocks used by an I/O operation determines its performance. If you are reading/writing sub-block sized elements, you try to make it so that they come from the same block to minimize the number of requests made to the physical storage. If you work with multi-block elements, your approach to performance optimization is different -- you try to pre-fetch the "neighbor" blocks because you expect you might need them soon. Modern storage hardware has a decent degree of parallelism that allows you to queue multiple I/O requests w/o awaiting completion. This later mechanism is a lot less relevant to something like RDBMS, but is at the heart of an object store.
In other words: the problem is not the function of the size of the database. In principle, nothing stops eg. PostgreSQL from special-casing blobs and dealing with them differently than it would normally do with "small" objects... but they aren't probably interested in doing so because you already have appropriate storage for that kind of stuff, and PostgreSQL, like most other RDBMS sits on top of the storage for larger objects (filesystem), so they have no hopes of doing it better than the layer below them.
PostgreSQL just like most RDBMS uses filesystem as a backend. It cannot be faster than the filesystem it uses to store its data. At best, you may be able to configure it to use more caching, and then it will be "faster" when you have enough memory for caching...
It seems very clear that it is you who knows some vaguely relevant bit of trivia, but actually have no clue about the subject in general, and try to make up for that in arrogance. Which is, honestly, pretty embarrassing.
Sorry what?
I mean, ext4, as a special case, has some performance issues around multiple writers to a singe file when doing direct io, but I can't think of a single other place where your statement true... and plenty where it's just not ( xfs, jfs, zfs, ntfs, refs )
Yes, the goal of filesystem is to perform best when files are greater than one block and smaller than some couple hundreds of blocks. This is what it's optimized for. Can it deal with smaller or bigger files? -- Yes, but this is beside the point. Filesystem by design are meant for the sizes I mentioned. It's stupid to store information in files much smaller than single block because filesystems store a lot of file metadata per file. If your files are too small, then you start paying exorbitant price for metadata (filesystem don't optimize for storing metadata in bulk for multiple files because such optimization would mean a lot of synchronization when dealing with multiple files in parallel).
Similarly, filesystems, by and large aren't good for dealing with large chunks of data, like, eg. database backups, VM images etc. Typically, a storage system for large objects will try to optimize its performance by larger-than-block compression and deduplication. Neither makes sense when your target chunk size is in single to double digits of blocks -- there won't be enough overlap between different data chunks and you will pay more for decompression of data that you don't want to read, if the granularity of compressed chunks is too big.
And this is not a secret at all... talk to anyone who works on either database or filesystem or object store -- they will tell you exactly this. (I worked on a filesystem, for the record.) This is why these things all co-exist and are filling their respective niches...
> the longest possible character string that can be stored is about 1 GB.
· https://www.postgresql.org/docs/current/datatype-character.h...
See also: https://www.postgresql.org/docs/current/limits.html
Where do you have the 64KB number from?
A small test:
test=# create table test_table (test_field text);
CREATE TABLE
test=# insert into test_table select string_agg('x', '') from generate_series(1, 128*1024);
INSERT 0 1
test=# select length(test_field) from test_table;
length
--------
131072
(1 row)In MySQL, the TEXT (and BLOB) limit is 65KB, but you can get 4GB using LONGTEXT. According to their docs: https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.h...
https://medium.com/walmartglobaltech/building-object-store-s...
If the data is frequently accessed, it also means there are extra hops the data has to take before getting to the user. It'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's almost impossible to stream the response, so the whole BLOB needs to be copied in memory in each system it passes through.
It's rare that any request for, say, user data would also 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.
At the same time, I'm working with systems where we often store MBs of data in JSON columns and it's working fine so it's really up to you to make the tradeoff.
But if the usage pattern was such that the blog post and images were being transformed based on the time or something, then nothing would get cached, and back to the issue you describe.
That said... you could setup read replicas into groups and direct the blob stuff to a pool that is separate from the higher priority data requests for normal db stuff.
Not sure if that was an endemic problem or a specific SQL server shiftiness
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?
I want to believe, it’s just hard for me to go all in on an Amazon API.
That's the catch. S3 is ideal for when the sum total of your blobs can't easily fit on local storage - unless you want to use a NAS, SAN or something else with a load of spinning rust.
Storing your data on a single HDD you got off NewEgg will always win if you only use the one metric of $/GB.
S3's main draw isn't $/GB. It's actually more like ($/GB) * features
E.g. 12-9s of durability, Lambda events, bucket policies, object tags, object versioning, object lock, cross region replication
Doing that with anything over 100TB starts to get very expensive very quickly. Especially if you need that data for, you know, your business to survive...
That right there is the mistake you're making. Storage is not the only way that AWS charges you for S3. You're also billed for stuff like each HTTP request, each metadata tag, and data transferred out once you drop off the free tier. You're basically charged for every time you look at data you put in a S3 bucket the wrong way.
I strongly recommend you look at S3's pricing. You might argue that you feel S3 is convenient, but you pay through the nose for it.
Cloudflare's R2 pricing seems a lot more competitive, but it's still lock in: https://r2-calculator.cloudflare.com
Another pain is the testing story. I just want to be able to write to a FS. There are S3 fuse bindings, though. Maybe I'm just a dinsosaure these days.
The first part is easy enough, but how do you predict when you're going to hit a knee in the system's performance?
Generally I agree don't prematurely optimize. But for us, not even having huge files (5-25 MB on average), it slowed our application down unacceptably storing file data in the database, and we ended up moving file data to S3 with a streaming API in front of it.
Similar story with caches. Sometimes I've used Postgres as a cache, but writing the little logic for that is already more hassle than just plopping in memcached or Redis and using a basic client.
I know this, because this is exactly what I did. Put the images and the logfiles from an automated test suite directly into the DB. Brilliant, I thought! No more tracking separate files, performance was great, everything was rosy.
And then I tried to prune some old data.
I know this is controversial. People will claim it's bad for performance.
But that's only bad if performance is something you're having problems with, or going to have problems with. If the system you're working on is small, and going to stay small, then that doesn't matter. Not all systems are like this, but some are.
Storing the images directly in the database has a lot of advantages. They share a transactional context with the other updates you're doing. They get backed up at the same time, and if you do a "point in time" restore they're consistent with the rest of your data. No additional infrastructure to manage to store the files.