The transaction isolation level hasn't been an issue for us - Our read/write patterns aren't super 'transactional' which may be why, we don't get a lot of conflicting updates as usually only one user has the permission to edit their content on the document. Arguably these tables could be on Scylla/Cassandra with eventual consistency even, though that would have been a harder transition and we'd have to spend more time making sure our application could handle that correctly.

We did have to make some schema changes in order to improve the data locality and avoid hotspots - CockroachDB did _work_ without doing those, but the changes improved performance massively. It wasn't super time consuming though, and the admin UI/monitoring in cockroach is nice for showing you any hot ranges.

The biggest one was our table which stores annotations on a document - on PG it's primary key was only (annotation_id UUID), and we had a secondary index on (document_id). This meant that on Cockroach the data for a document was spread across many nodes and querying all the annotations for a document would take ~40ms. We changed the primary key to (document_id, annotation_id) which co-located the data and that came down to ~2ms. Composite primary keys aren't ideal for ActiveRecord but the performance win was worth it.

We also had some tables with hotspots which was mostly just a matter of making sure they used uuid keys. It would be a problem though if we had a table with individual rows which were super hot or where the table didn't have enough data to get split into multiple ranges - though cockroach does have load based sharding to help here. It was only really necessary to worry about the higher load tables here, if it's below ~2000 qps or so it won't matter.

> Arguably these tables could be on Scylla/Cassandra with eventual consistency even

In a past company we managed to run a bank on Cassandra with eventual consistency. It was ... well, it was good CV experience for the DevOps guys. (I also didn't need much encouragement to take the 'no getting drunk while on-call' rule very seriously.)

You might consider switching UUID to a more sequential id, like ULID, to further improve index data locality.

I think that might be counterproductive on cockroach - the ideal key has locality for data accessed together, but also has writes generally spread across the key range. If all the new rows have a similar key prefix then the shard covering the end of the key range will be hot which limits scalability.

This is a difference from PG, where having all the writes be concentrated in the key range is helpful as the Btree nodes covering that will be more likely to be highly cached

does Cockroach really use key prefix, vs a hash of the key? I would have thought it would be important to avoid unexpected performance hits resulting from key generation approach.

It is easy enough--arguably easier, right?--to generate random keys, but if you always work with hashes of the key (which I am not claiming cockroach doesn't do, only that I would be frustrated if they do) then you lose the ability to do range queuries.

Cockroach lets you hash-shard your keys n-ways if you choose to through a feature called hash-sharded indexes (https://www.cockroachlabs.com/docs/stable/hash-sharded-index...) That's a trade-off: costlier range scans (a range scan turns into n scans) for avoiding a throughput-limiting hot spot.