3 ms·

> Another option is going full-scale with CockroachDB Just beware that CockroachDB is not a drop-in replacement for PostgreSQL. Last time I looked it was miss

> Another option is going full-scale with CockroachDB Just beware that CockroachDB is not a drop-in replacement for PostgreSQL. Last time I looked it was missing basic stuff. Like stored functions. I don't call stored functions an "advanced feature".

frollogaston 2y ago
I also wonder what limitations you get on queries. Cause in Spanner, there's something about cross-directory queries, or Citus can't do cross-shard FKs iirc. There's no magic way to shard a relational DB, you have to deal with it one way or another.
- vvern 2y ago
  Cockroach really doesn’t have limitations when it comes to queries. It is kinda magic in that regard. The single partition queries are the special case and are treated as rare. Cockroach ranges are quite small compared to other systems (~256MiB).
  - frollogaston 2y ago
    Interesting, I'll have to try it out at some point.
benjiro 2y ago
> Just beware that CockroachDB is not a drop-in replacement for PostgreSQL. O, its way worse then that... A ton of small functionality tend to be missing or work differently. Sometimes even small stuff like column[1:2] does not even exist in CRDB and other times its things like ROW LEVEL SECURITY ... you know, something you may want on a mass distributed database then will be use for tenant setups (i hear they are finally going to implement it this year). The main issue is the performance... You are spending close to ~4x the resources on a similar performing setup. And this is a 2x Pgres (sync) vs 3x CRDB (where every node is just a replica). Its not the replication itself but the massive overhead on the raft protocol + the way less optimized query planner. To match Pgres, your required to deploy around 10 a 12 nodes, on hardware that needs about twice the resources. That is the point where CRDB performance on a similar level. The issue is, well, you increase the resources to your Postgresql server and the gap is back. You can scale Postgresql to insane levels with 64, 96 CPUs ... Or imagine, you know, just planning your app in advance in such a way, that you spread your load over multiple Postgresql instances. Rocket science folks ;) CRDB is really fun to watch, the build in GUI, the replication being very visual, but its a resource hog. The storage system (peble) eats a ton of resources to compact the data, when you can simply solve that with a PostgreSQL instance with zfs (with ironically, often better compression). I do not joke when i say, that seeing those step cpu spikes in the night hours of the compacter working, is painful. Even a basic empty CRDB instance, just logging its own usage, runs between 7 to 50% on a quad ARM N1, constantly. PostgreSQL? You do not even know its running. Barely any CPU usage, what memory usage? And we have not talked license/payment issues ... Of "free enterprise" version with FORCED telemetric on, AND your not allowed to hide the server (aka, if it can not call home, it goes into restricted mode in 7 days, with like 50 queries / second ... aka, the same as just shutting down your DB ). By the way, some people reported that its 125+ dollar/vcore payment. Given that even the most basic CRDB gets you 3x instances, with minimum 4 cores ... Do the math. Yea, after 10M income but they chance their licenses every few years, so who knows next year or the year after that. Interesting product, shitty sales company behind it. I am more interested to see when the postgresql storage extension orioledb comes out, so it solve the main issue that prevents postgresql scaling even more, namely the write/vacuum issue. And ofcourse a better solution to upgrade postgresql versions, there CRDB is leaps and bound better.

frollogaston 2y ago

I also wonder what limitations you get on queries. Cause in Spanner, there's something about cross-directory queries, or Citus can't do cross-shard FKs iirc. There's no magic way to shard a relational DB, you have to deal with it one way or another.

vvern 2y ago
Cockroach really doesn’t have limitations when it comes to queries. It is kinda magic in that regard. The single partition queries are the special case and are treated as rare. Cockroach ranges are quite small compared to other systems (~256MiB).
- frollogaston 2y ago
  Interesting, I'll have to try it out at some point.

benjiro 2y ago

> Just beware that CockroachDB is not a drop-in replacement for PostgreSQL. O, its way worse then that... A ton of small functionality tend to be missing or work differently. Sometimes even small stuff like column[1:2] does not even exist in CRDB and other times its things like ROW LEVEL SECURITY ... you know, something you may want on a mass distributed database then will be use for tenant setups (i hear they are finally going to implement it this year). The main issue is the performance... You are spending close to ~4x the resources on a similar performing setup. And this is a 2x Pgres (sync) vs 3x CRDB (where every node is just a replica). Its not the replication itself but the massive overhead on the raft protocol + the way less optimized query planner. To match Pgres, your required to deploy around 10 a 12 nodes, on hardware that needs about twice the resources. That is the point where CRDB performance on a similar level. The issue is, well, you increase the resources to your Postgresql server and the gap is back. You can scale Postgresql to insane levels with 64, 96 CPUs ... Or imagine, you know, just planning your app in advance in such a way, that you spread your load over multiple Postgresql instances. Rocket science folks ;) CRDB is really fun to watch, the build in GUI, the replication being very visual, but its a resource hog. The storage system (peble) eats a ton of resources to compact the data, when you can simply solve that with a PostgreSQL instance with zfs (with ironically, often better compression). I do not joke when i say, that seeing those step cpu spikes in the night hours of the compacter working, is painful. Even a basic empty CRDB instance, just logging its own usage, runs between 7 to 50% on a quad ARM N1, constantly. PostgreSQL? You do not even know its running. Barely any CPU usage, what memory usage? And we have not talked license/payment issues ... Of "free enterprise" version with FORCED telemetric on, AND your not allowed to hide the server (aka, if it can not call home, it goes into restricted mode in 7 days, with like 50 queries / second ... aka, the same as just shutting down your DB ). By the way, some people reported that its 125+ dollar/vcore payment. Given that even the most basic CRDB gets you 3x instances, with minimum 4 cores ... Do the math. Yea, after 10M income but they chance their licenses every few years, so who knows next year or the year after that. Interesting product, shitty sales company behind it. I am more interested to see when the postgresql storage extension orioledb comes out, so it solve the main issue that prevents postgresql scaling even more, namely the write/vacuum issue. And ofcourse a better solution to upgrade postgresql versions, there CRDB is leaps and bound better.