4 ms·

We started using Cockroach DB at an employment that I was at and people could not get date ordered queries working quickly. In the end they switched to Postgres

We started using Cockroach DB at an employment that I was at and people could not get date ordered queries working quickly. In the end they switched to Postgres, but it did give me pause, that while I'm sure Cockroach is fantastic, maybe for most people's use cases they should stick to Postgres until really they need to switch.

mjibson 6y ago
Which version of CockroachDB was that? A few years ago it had a rudimentary heuristic optimizer that made plenty of poor choices. Today it has a quickly improving cost-based optimizer that makes much better choices. Each release now has significant jumps in which kinds of queries it can produce fast plans for.
- andy_ppp 6y ago
  It was indeed a few years ago, maybe end of 2018!
vvern 6y ago
I'll pile on to what mjibson said that we have dramatically improved our PG compatibility with time types: https://www.cockroachlabs.com/blog/time-data-types-postgresql/ https://www.cockroachlabs.com/blog/time-data-types-postgresq.... If there concern was actually query performance due to writing time ordered data, we also have recently added hash-sharded indexes which can help with scalability there: https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlock-linear-scaling-for-sequential-workloads/ https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlo...
- andy_ppp 6y ago
  Okay! That’s cool to know, where are the current bodies buried that I might just randomly get slow performance? It’s more likely I’d choose Cockroach if there was some info on this floating around...
  - knz42 6y ago
    https://www.cockroachlabs.com/docs/stable/make-queries-fast.html https://www.cockroachlabs.com/docs/stable/make-queries-fast....
    - andy_ppp 6y ago
      This not what I asked for; to be even clearer, I want a list of queries that are fast in Postgres and slow (currently) in Cockroach. You sent me a guide on query optimisation. They are different things aren’t they.
      - vvern 6y ago
        I'm not sure that there's a simple answer here. One place where people often run in to trouble is that they deploy cockroach with a global topology and then are surprised when things are much, much slower than in postgres. I'm going to assume that you're only talking about a single-region deployment. One place cockroach radically differs from postgres is that it stores historical versions of rows and it stores them sequentially. This means that if you have an update-heavy workload and you do not lower your GC TTL for that data, you can see dramatic performance degradation that you would not see with postgres (see [1]). I suspect that there are some cases where our optimizer will not make the same or as good of choices as postgres, especially in cases of joins across large numbers of tables. Our optimization of CTEs is relatively primitive compared to PG IIRC. Another place where we do quite poorly that doesn't come up in production but commonly comes up in testing are schema changes. Cockroach schema changes are online and do not block concurrent writes. That being said, they require coordination and phases. Running schema changes in tight loops will yield relatively low throughput and performance deteriorates given some of the coordination is a function of the total number of schema elements. This almost always comes up in the context of testing things like ORMs where schema elements are created and destroyed in every test. I doubt this is the sort of thing you're looking for but it's definitely a thing that comes up. As noted in the hash-sharded index post, cockroach is not well suited to scale write traffic incident on a single key. In these cases the in-memory, local synchronization of postgres is likely going to be more efficient. Sequential workloads suffer from a similar problem that doesn't exist in postgres. In fact, sequential workloads are quite well suited to their B+-Tree. That being said, this comes up relatively infrequently and when it does we have tools to mitigate it. Our temp table implementation likely violates people's intuition. It primarily exists for compatibility to enable a broader suite of testing. Temp tables are as expensive as regular tables. In general some of our data-intensive queries are going to be less efficient due partially the efficiency of our execution engine, partially to the efficiency of our KV, and partially due to the fact that the KV is implemented in a distributed way requiring a certain amount of data copying and synchronization whereas postgres can get by just passing pointers around in memory. I suspect its ability to achieve access locality for some workloads is immense and leads to wins. We have done a lot of work recently to "vectorize" our execution engine and make it more efficient (see [2]) and are in the process of replacing our storage engine, in some small part to eventually improve our scan speed (see [3]) and more generally improve our efficiency. Maybe other can chime in but in my experience there isn't so much a general class of queries where we're orders of magnitude slower than postgres. Often when people see orders of magnitude differences it has to do with index selection in a plan for a specific query but this tends not to be a generalized problem and we're pretty active about rectifying these reported problems. We've also been focusing on adding tools to better understand query plans and performance. More often it's the case that on a per-core basis, cockroach is less efficient in terms of throughput than a single instance of postgres but in return for that you get a number of benefits, one of which being that you can scale the thing with your load. If you're looking to optimize $/query in a high-throughput setting and don't care about the HA or other properties, cockroach probably isn't the answer. Often when you're trying to run a cost-optimized $/query system, it's because the scale is very large so postgres might not be the answer either. It may make a lot of sense if it's a very update-heavy workload where the total data set size is small. Hope that was helpful. Maybe other who have had experience with cockroach and found it to be pathologically slower in some cases can chime in. We'd also love to hear about those cases in https://github.com/cockroachdb/cockroach/issues https://github.com/cockroachdb/cockroach/issues. [1] https://github.com/cockroachdb/cockroach/issues/17229 https://github.com/cockroachdb/cockroach/issues/17229 [2] https://www.cockroachlabs.com/blog/how-we-built-a-vectorized-sql-engine/# https://www.cockroachlabs.com/blog/how-we-built-a-vectorized...

mjibson 6y ago

Which version of CockroachDB was that? A few years ago it had a rudimentary heuristic optimizer that made plenty of poor choices. Today it has a quickly improving cost-based optimizer that makes much better choices. Each release now has significant jumps in which kinds of queries it can produce fast plans for.

andy_ppp 6y ago
It was indeed a few years ago, maybe end of 2018!

vvern 6y ago

I'll pile on to what mjibson said that we have dramatically improved our PG compatibility with time types: https://www.cockroachlabs.com/blog/time-data-types-postgresql/ https://www.cockroachlabs.com/blog/time-data-types-postgresq.... If there concern was actually query performance due to writing time ordered data, we also have recently added hash-sharded indexes which can help with scalability there: https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlock-linear-scaling-for-sequential-workloads/ https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlo...

andy_ppp 6y ago
Okay! That’s cool to know, where are the current bodies buried that I might just randomly get slow performance? It’s more likely I’d choose Cockroach if there was some info on this floating around...
- knz42 6y ago
  https://www.cockroachlabs.com/docs/stable/make-queries-fast.html https://www.cockroachlabs.com/docs/stable/make-queries-fast....
  - andy_ppp 6y ago
    This not what I asked for; to be even clearer, I want a list of queries that are fast in Postgres and slow (currently) in Cockroach. You sent me a guide on query optimisation. They are different things aren’t they.
    - vvern 6y ago
      I'm not sure that there's a simple answer here. One place where people often run in to trouble is that they deploy cockroach with a global topology and then are surprised when things are much, much slower than in postgres. I'm going to assume that you're only talking about a single-region deployment. One place cockroach radically differs from postgres is that it stores historical versions of rows and it stores them sequentially. This means that if you have an update-heavy workload and you do not lower your GC TTL for that data, you can see dramatic performance degradation that you would not see with postgres (see [1]). I suspect that there are some cases where our optimizer will not make the same or as good of choices as postgres, especially in cases of joins across large numbers of tables. Our optimization of CTEs is relatively primitive compared to PG IIRC. Another place where we do quite poorly that doesn't come up in production but commonly comes up in testing are schema changes. Cockroach schema changes are online and do not block concurrent writes. That being said, they require coordination and phases. Running schema changes in tight loops will yield relatively low throughput and performance deteriorates given some of the coordination is a function of the total number of schema elements. This almost always comes up in the context of testing things like ORMs where schema elements are created and destroyed in every test. I doubt this is the sort of thing you're looking for but it's definitely a thing that comes up. As noted in the hash-sharded index post, cockroach is not well suited to scale write traffic incident on a single key. In these cases the in-memory, local synchronization of postgres is likely going to be more efficient. Sequential workloads suffer from a similar problem that doesn't exist in postgres. In fact, sequential workloads are quite well suited to their B+-Tree. That being said, this comes up relatively infrequently and when it does we have tools to mitigate it. Our temp table implementation likely violates people's intuition. It primarily exists for compatibility to enable a broader suite of testing. Temp tables are as expensive as regular tables. In general some of our data-intensive queries are going to be less efficient due partially the efficiency of our execution engine, partially to the efficiency of our KV, and partially due to the fact that the KV is implemented in a distributed way requiring a certain amount of data copying and synchronization whereas postgres can get by just passing pointers around in memory. I suspect its ability to achieve access locality for some workloads is immense and leads to wins. We have done a lot of work recently to "vectorize" our execution engine and make it more efficient (see [2]) and are in the process of replacing our storage engine, in some small part to eventually improve our scan speed (see [3]) and more generally improve our efficiency. Maybe other can chime in but in my experience there isn't so much a general class of queries where we're orders of magnitude slower than postgres. Often when people see orders of magnitude differences it has to do with index selection in a plan for a specific query but this tends not to be a generalized problem and we're pretty active about rectifying these reported problems. We've also been focusing on adding tools to better understand query plans and performance. More often it's the case that on a per-core basis, cockroach is less efficient in terms of throughput than a single instance of postgres but in return for that you get a number of benefits, one of which being that you can scale the thing with your load. If you're looking to optimize $/query in a high-throughput setting and don't care about the HA or other properties, cockroach probably isn't the answer. Often when you're trying to run a cost-optimized $/query system, it's because the scale is very large so postgres might not be the answer either. It may make a lot of sense if it's a very update-heavy workload where the total data set size is small. Hope that was helpful. Maybe other who have had experience with cockroach and found it to be pathologically slower in some cases can chime in. We'd also love to hear about those cases in https://github.com/cockroachdb/cockroach/issues https://github.com/cockroachdb/cockroach/issues. [1] https://github.com/cockroachdb/cockroach/issues/17229 https://github.com/cockroachdb/cockroach/issues/17229 [2] https://www.cockroachlabs.com/blog/how-we-built-a-vectorized-sql-engine/# https://www.cockroachlabs.com/blog/how-we-built-a-vectorized...