Supertoast tables

https://hatchet.run/blog/supertoast-tables

by abelanger • 5 months ago

56 12 5 months ago

debarshri• 5 months ago

I think anything interesting more cleaner route would be to create a plugin in postgres and introduce a type that upload the large file in s3.

It would reduce thr complexity.

Postgres plugins are very underrate and under utilized

levkk• 5 months ago

All queries run inside transactions, and a slow lane like S3 will cause delays, which will in turn block vacuum and cause more problems than it will solve. Most deployments of Postgres (e.g., RDS) won't let you install custom extensions either, although they do have their own S3 extension (which I wouldn't recommend you use).

The right place to manage this is either in the app or in a proxy, before the data touches Postgres.

carderne2• 5 months ago

How does this work with self-hosting? Is the assumption that self-hosters won’t run into this problem?

For most use-cases I’d probably prefer to just delete the payloads some time after the job completes (persisting that data is business logic problem). And keep the benefits of “just use Postgres”, which you guys seem to have outgrown.

abelanger• 5 months ago

Candidly we're still trying to figure that out: all of the plumbing is there in the open source, but the actual implementation of writes to S3 are only on the cloud version. This is partially because we're loath to introduce additional dependencies, and partially because this job requires a decent amount of CPU and memory and would have to run separate from the Hatchet engine, which adds complexity to self-hosted setups. That said, we're aware of multi-TB self-hosted instances, and this would be really useful for them - so it's important that we can get this into the open source.

The payloads are time-partitioned (in either case) so we do drop them after the user-defined retention period.

carderne2• 5 months ago

I guess you don't get the luxury of being opinionated enough to say: forget your old data.

Anyway great write-up, even though I'm sure it's painful having to run this system on top of your once-elegant Postgres solution.

philsnow• 5 months ago

Unexpectedly, I love the animated ascii diagrams, very cogmind-esque.

Anybody know how they designed those?

atombender3• 5 months ago

I really wish there was a seamless system for this. Once you try to do this kind of thing, you run into all sorts of rabbit holes and cans of worms.

For example, coalescing blobs into "superblobs" to avoid a proliferation of small objects means you invent a whole system for tracking "subfiles" within a bigger file.

And you'll need a compacting job to ensure old, deleted data is expunged, which may be more important than you think if the data has to be erased for privacy or legal reasons.

Object storage has no in-place mutation, so this compaction has to be transactionally safe and must be careful not to leave behind cruft on failure, and so on.

Furthermore, storing blobs in object storage without keeping a local inventory of them is, in my experience, a disaster. For example, if your database has tenants or some other structural grouping, something simple like finding out how much blob storage a specific tenant has is a very time-consuming operation on S3/GCS/etc. because you need to filter the whole bucket by prefix. So for every blob you store, you want to have a database table of what they are so that the only object operations you do are reads and writes, not metadata operations.

Sure, you have things like inventory reports on GCS that can help, but I would still say that you need to track this stuff transactionally. The database must be the source of truth, and the object storage must never be used as a database.

And so on.

This need to be able to store many small objects in object storage is coming up more and more for me, as is the desire to mutate them in-place or at least append. For example, imagine you want to build a kind of database which stores a replicated copy of itself in the cloud. There is no way to do this in S3-like object storage without representing this as a series of immutable "snapshots" and "deltas". It's fast to append this way, but you run into the problem of eventually needing to compact, and you absolutely have to batch up the uploads in order to avoid writing too many small objects.

So lately I've pondered using something else for this type of work, like a key/value database, like FoundationDB or TiKV, or even something like Ceph. I wonder if anyone else has tried that?

huntaub• 5 months ago

Well, I think this is what our company, Archil, is working on. We basically built an SSD clustering layer that proxies/caches/and assembles requests into object storage so that you can run a POSIX file system directly on top.

There's also some really great projects like SlateDB in this space, which could be more like what you're looking for (~RocksDB like API that runs on S3).

atombender3• 5 months ago

Your product looks very interesting, I will take a look!

ovaistariq• 5 months ago

Well we have made small objects work well on Tigris (https://www.tigrisdata.com/). And we have several use cases of folks using it as KV store. Funny that you mention FoundationDB, we use that for our metadata storage.

atombender3• 5 months ago

I've heard good things about Tigris. If that means I can store billions of objects without being bankrupted by request cost, and it has fast read access (GCS is quite poor here), then that helps a lot! I'm looking right now for a system that lets me store lots of very small blobs around 4KB each.

johnthescott• 4 months ago

no need to store blob in database. too expensive.

why is blob storage even a "problem"? a crypto digest of the blob as a key covers vast majority of cases.

for blobs that do not change store only the crypto hash (160bits) of the large blob in the database?

    ripemd(sha256(sha256(blob)))

for the blobs themselves use any object store you please.

yawn.

Supertoast tables

Related Stories