At Kami (kamiapp.com) we had to make a similar transition last year - we'd seen usage grow over 20x in a 6-month period on our classroom product due to the pandemic, and we were storing all our content in a single-primary Postgres database. We had plenty of headroom before but we were suddenly running out of write capacity, even after scaling vertically up to ~150 vcpu. Our workload is super write heavy so scaling out replicas wouldn't have helped much at all, and I was spending a lot of time manually vacuuming in our daily off-peak as we were burning transaction ids at an astonishing rate, ~800 million per day.
I looked at a bunch of options but ultimately managing a sharded database seemed like too much overhead for our small (but expanding!) dev team - so we decided to move our heavily loaded tables to CockroachDB instead, as since it's mostly Postgres compatible our transition would be easier, and it would be much easier to manage as it automatically balances load and heals. We're running cockroach on GKE and it's a really nice fit for running on kubernetes.
Ended up working well for us - we still have our smaller tables on PG but we want to move it all to Cockroach over time. There were some teething issues as we learnt what to monitor on it (read amplification is the big one here, though pebble has helped a lot vs the previous rocksdb), and it does need a lot of hardware at high scale (we're running 50 16-vcpu nodes for it) but overall I'm happy with it.
How have you found moving from a default transaction isolation level of read committed on Postgres to serializable-only on CockroachDB? I know of some somewhat-standard usage patterns that lean heavily on more lax isolation levels for acceptable performance(and may just deadlock otherwise).
I'm also curious how you have found dealing with legacy schemas and queries that might cause a lot of data shuffling due to poor data locality. Was it necessary to put a lot of time into the sharding strategy? Have you experienced new query hotspots?
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.)
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.
I looked at a bunch of options but ultimately managing a sharded database seemed like too much overhead for our small (but expanding!) dev team - so we decided to move our heavily loaded tables to CockroachDB instead, as since it's mostly Postgres compatible our transition would be easier, and it would be much easier to manage as it automatically balances load and heals. We're running cockroach on GKE and it's a really nice fit for running on kubernetes.
Ended up working well for us - we still have our smaller tables on PG but we want to move it all to Cockroach over time. There were some teething issues as we learnt what to monitor on it (read amplification is the big one here, though pebble has helped a lot vs the previous rocksdb), and it does need a lot of hardware at high scale (we're running 50 16-vcpu nodes for it) but overall I'm happy with it.