The trouble with Cassandra as an object storage metadata database

A while back we explored the use of Cassandra. We wanted to keep some event related data there and for it to be relatively fast read-wise in order for us to do all sorts of reporting based on it. So we wrote allot and wanted to read fast. Seemed like a perfect store for our timestamped events, especially since we wanted to not even use deletes and has in-build record deduplication via its primary key. Turns out, it is not that perfect.

Other than what the article described, I can also add:

1. It has a steep learning curve, but you do get to see the advantages while you learn it. But then, everything comes crumbling down.

2. The setup is a pain locally. Then it is a pain to set it up in prod and manage it. The tooling itself feels very unfinished and basic.

3. No querying outside primary index on AWS Keyspace if you want it managed. Also, any managed variants are EXPENSIVE. I mean, every database is fast if you only query by the primary index so why pay extra?

It is just not worth it. For example, we winded up using MongoDb and it turned out to be fast, scalable, had mature tooling and we can keep tons of event related metadata in it and it is easy to manage and doesn't cost a fortune.

Yes. the problem with "eventual consistency" is that "eventually" never happens about 1 in a million times, and with millions of storage objects to manage you can't have that. So what's the alternative as a metadata store for object storage ? a consensus algorithm (paxos, raft, mencius, ...) on top of local key value stores.

I would have hoped its obvious.

Your metadata database needs to be the fastest and most reliable store out of everything. It can't be eventually consistent without partitioning your datastore. Even then you'll end up partitioning your data neatly into the same failure zone.

Cassandra has basically one usecase: high volume writes, with a few batch reads.

Cassandra is not really optimised for high reads.

Most of the time postgres will do fine.

> Most of the time postgres will do fine.

For something that scale horizontally, I would probably recommend to use something like FoundationDB for this use case [^1].

A transactional Key-Value store is exactly what you want for this kind of use case.

[^1]: https://apple.github.io/foundationdb/

What does "Bottom line. Write your metadata atomically with your object. Never separate them." mean from the perspective of picking a system?