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.
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.
While you can vertically-scale a single PostgreSQL (with failover/ha) server to 50+TB of NVME and grow until you can throw other engineers at the problem.
Example: wasabi.com uses mysql for metadata.
That's how we use it in BigCompany(tm).
Cassandra holds (almost) all of our data. Most of it comes from batch insertions, but more complex (and lower volume) data comes from various REST APIs we expose and other teams use.
Most reads (60%) done on it are boring batch reads: analytics, reports, deltas, etc.
For more complex reads (30%) we copy relevant rows to Hive tables, then perform queries there without dealing with partition key shenanigans. So, batch with extra steps.
Then anything that requires random reads (10%) is read from ElasticSearch:
1. Coming from REST backend if it's time sensitive.
2. Coming from a batch that reads from Cassandra if not.
Yes. It's also a good choice for horizontal scaling, but only if the other DBs won't do.