12 ms·

Nested Transactions in CockroachDB 20.1

100 points by orangechairs 6y ago

andy_ppp 6y ago
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...
EGreg 6y ago
Nested transactions can easily be done at the app layer, with a counter. As long as any rollback rolls back all outer layers.
- knz42 6y ago
  Author here - yes there are schemes to do it client-side. However there is enough legacy code and frameworks out there that use nested transactions via pg's savepoint protocol, that some compatibility with those is warranted.
sandstrom 6y ago
Interesting read! I like the depth of CDBs technical blog posts! I do wonder when they’ll add the BACKUP feature to the community edition. Dump isn’t very useful for larger datasets and frequent backup snapshots.
- namibj 6y ago
  Archive changefeeds, and replay later. Treat them like a WAL, and you should reach a loss-window down to <15 seconds with ease.
  - latch 6y ago
    Not sure that I'd want my job to depend on me doing this correctly. 1. The feature is still flagged as experimental 2. If you fail to sync within the ttl window (25 hours by default), you'll lose data 3. There are cases (which might not be an issue for you) where it simply won't work (multiple column families, data imports, ...) Much much simpler and safer to use PG+barman.
    - namibj 6y ago
      1. Yes. 2. No, you just loose the ability to rollback to an arbitrary position in that window. It might also be more expensive to re-start, but it's been a few months since I looked at the code. 3. I am aware of multi-column-families not working (but that should get tackled?): https://github.com/cockroachdb/cockroach/issues/28667 https://github.com/cockroachdb/cockroach/issues/28667 I know PG is easier in that respect, but the performance ceiling is much lower.
nightowl_games 6y ago
I'm technically using CockroachDB through Nakama[1], and it's been good to me. I integrated it into metabase under the guise of postgres. [1]: https://heroiclabs.com/nakama-opensource/ https://heroiclabs.com/nakama-opensource/
memexy 6y ago
> labeling every data update in a transaction with a marker that identifies the nested transaction. > upon ROLLBACK, mark the corresponding nested transaction marker as "to be ignored" > upon any read of the data, skip over any data updates with a (nested) transaction marker known to be ignored. Seems like a good trick. They go on to explain the main performance issue, which seems to be about traversing the ignore list. For long queries a longer ignore list adds constant time overhead to each operation in the transaction. > The expense in this approach is the extra work performed by data reads when there has been at least one ROLLBACK prior. ROLLBACKs add markers to an “ignore list” and cause it to grow, and the performance of each subsequent read operation is slightly slowed down by the increasing ignore list. It would be interesting to get a list of various databases and how they handle transaction logic for rollback operations. If this list also had examples of how the logic plays out in a concrete example then it would be a really useful resource. Engineers could use this list to figure out if their use cases and performance requirements were compatible with their chosen level of transaction isolation properties.
maxpert 6y ago
I have read so much about CockroachDB but I am still waiting for that one hero app that puts it on the map. The hard part to convince your company or somebody to put his serious stuff like on something CDB is the fear folks have that what if this company/technology is gone (hint hint RethinkDB). I believe even with all the cool stuff and the hype there are gonna be some paper cut scenarios. Now these cuts might be fatal for someone but for others it won’t be a big deal.
- knz42 6y ago
  The technology is open source though. (Its BSL license automatically switches to APL after a year or two.)
kardianos 6y ago
> Even though nested transactions are part of the SQL standard, PostgreSQL supports them and many of our users have demanded their inclusion in CockroachDB, we do not recommend their use in new applications. I agree with this. People want nested transactions in Go's database/sql, but the abstraction is fundamentally leaky. New applications should not be using nested transactions. Find a different way.
jlokier 6y ago
I love CockroachDB's blog; it's one of my favourites. But the part of this article that argues why nested transactions are a bad idea left me with more questions than answers. > Finally, nested transactions can run afoul of correctness in distributed apps. In fact, the idea of multi-component transactions in client code really evokes the idea of a bull in a china shop. As long as all is well and the transaction is due to commit, the idea somewhat makes sense. However, what happens when the database (and not the client) decides the transaction is un-committable and must be aborted, for example because of a serializability conflict or a node restarting for maintenance? It is not just the database state that must be rolled back; all the possible side effects performed by the components holding the transaction must also be rolled back. Think about payment transactions. Think about email notifications. For one, the programmer is unlikely to think about this properly before the app is deployed. Also, as the number of components involved grows, the chance they are collectively performing unabortable external side effects (logging, payments, notification emails or SMS, REST queries to external HTTP APIs, etc) increases dramatically. Is this really an argument against nested transactions? It seems like an argument against all kinds of transactions that are linked to irreversible side effects, regardless of whether any are nested. > Nowadays, such tight coupling has a bad rep. This is because two additional decades of software engineering have taught us that implicit global state really, really does not play well with distributed services where some components may fail even as part of normal load. It makes it hard to fail over a component gracefully by transferring its responsibility to a hot spare. It makes it hard to reason about the current state of a component and troubleshoot it by just looking at the log of its inputs and outputs. This is convincing, but I would love to know what the author (or others) think the better alternative is, which solves the hot spare and "normal load failure" problems. Is it highly-available message queues? (And assume they never fail or all fail at once, but never fail piecemeal because that would cause half-updated state?) Is it (distributed/microservices) application code stuck in (distributed) retry loops when there are failures, to get its job done without half-updated state? > Additionally, nested transactions can amplify performance anomalies. The row locks established by a large transaction whose latency extends across multiple components/services are more likely to incur transaction conflicts which, at best, increase the average latency of queries and, at worse, increase the error rate quadratically with the current query load. Is this not a CockroachDB-specific performance limitation, rather than an argument against nested transactions per se?
blackrock 6y ago
Ahh.. what’s in a name? Not much. Except when you name yourself something stupid.

100 points by orangechairs 6y ago

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...

EGreg 6y ago

Nested transactions can easily be done at the app layer, with a counter. As long as any rollback rolls back all outer layers.

knz42 6y ago
Author here - yes there are schemes to do it client-side. However there is enough legacy code and frameworks out there that use nested transactions via pg's savepoint protocol, that some compatibility with those is warranted.

sandstrom 6y ago

Interesting read! I like the depth of CDBs technical blog posts! I do wonder when they’ll add the BACKUP feature to the community edition. Dump isn’t very useful for larger datasets and frequent backup snapshots.

namibj 6y ago
Archive changefeeds, and replay later. Treat them like a WAL, and you should reach a loss-window down to <15 seconds with ease.
- latch 6y ago
  Not sure that I'd want my job to depend on me doing this correctly. 1. The feature is still flagged as experimental 2. If you fail to sync within the ttl window (25 hours by default), you'll lose data 3. There are cases (which might not be an issue for you) where it simply won't work (multiple column families, data imports, ...) Much much simpler and safer to use PG+barman.
  - namibj 6y ago
    1. Yes. 2. No, you just loose the ability to rollback to an arbitrary position in that window. It might also be more expensive to re-start, but it's been a few months since I looked at the code. 3. I am aware of multi-column-families not working (but that should get tackled?): https://github.com/cockroachdb/cockroach/issues/28667 https://github.com/cockroachdb/cockroach/issues/28667 I know PG is easier in that respect, but the performance ceiling is much lower.

nightowl_games 6y ago

I'm technically using CockroachDB through Nakama[1], and it's been good to me. I integrated it into metabase under the guise of postgres. [1]: https://heroiclabs.com/nakama-opensource/ https://heroiclabs.com/nakama-opensource/

memexy 6y ago

> labeling every data update in a transaction with a marker that identifies the nested transaction. > upon ROLLBACK, mark the corresponding nested transaction marker as "to be ignored" > upon any read of the data, skip over any data updates with a (nested) transaction marker known to be ignored. Seems like a good trick. They go on to explain the main performance issue, which seems to be about traversing the ignore list. For long queries a longer ignore list adds constant time overhead to each operation in the transaction. > The expense in this approach is the extra work performed by data reads when there has been at least one ROLLBACK prior. ROLLBACKs add markers to an “ignore list” and cause it to grow, and the performance of each subsequent read operation is slightly slowed down by the increasing ignore list. It would be interesting to get a list of various databases and how they handle transaction logic for rollback operations. If this list also had examples of how the logic plays out in a concrete example then it would be a really useful resource. Engineers could use this list to figure out if their use cases and performance requirements were compatible with their chosen level of transaction isolation properties.

maxpert 6y ago

I have read so much about CockroachDB but I am still waiting for that one hero app that puts it on the map. The hard part to convince your company or somebody to put his serious stuff like on something CDB is the fear folks have that what if this company/technology is gone (hint hint RethinkDB). I believe even with all the cool stuff and the hype there are gonna be some paper cut scenarios. Now these cuts might be fatal for someone but for others it won’t be a big deal.

knz42 6y ago
The technology is open source though. (Its BSL license automatically switches to APL after a year or two.)

kardianos 6y ago

> Even though nested transactions are part of the SQL standard, PostgreSQL supports them and many of our users have demanded their inclusion in CockroachDB, we do not recommend their use in new applications. I agree with this. People want nested transactions in Go's database/sql, but the abstraction is fundamentally leaky. New applications should not be using nested transactions. Find a different way.

jlokier 6y ago

I love CockroachDB's blog; it's one of my favourites. But the part of this article that argues why nested transactions are a bad idea left me with more questions than answers. > Finally, nested transactions can run afoul of correctness in distributed apps. In fact, the idea of multi-component transactions in client code really evokes the idea of a bull in a china shop. As long as all is well and the transaction is due to commit, the idea somewhat makes sense. However, what happens when the database (and not the client) decides the transaction is un-committable and must be aborted, for example because of a serializability conflict or a node restarting for maintenance? It is not just the database state that must be rolled back; all the possible side effects performed by the components holding the transaction must also be rolled back. Think about payment transactions. Think about email notifications. For one, the programmer is unlikely to think about this properly before the app is deployed. Also, as the number of components involved grows, the chance they are collectively performing unabortable external side effects (logging, payments, notification emails or SMS, REST queries to external HTTP APIs, etc) increases dramatically. Is this really an argument against nested transactions? It seems like an argument against all kinds of transactions that are linked to irreversible side effects, regardless of whether any are nested. > Nowadays, such tight coupling has a bad rep. This is because two additional decades of software engineering have taught us that implicit global state really, really does not play well with distributed services where some components may fail even as part of normal load. It makes it hard to fail over a component gracefully by transferring its responsibility to a hot spare. It makes it hard to reason about the current state of a component and troubleshoot it by just looking at the log of its inputs and outputs. This is convincing, but I would love to know what the author (or others) think the better alternative is, which solves the hot spare and "normal load failure" problems. Is it highly-available message queues? (And assume they never fail or all fail at once, but never fail piecemeal because that would cause half-updated state?) Is it (distributed/microservices) application code stuck in (distributed) retry loops when there are failures, to get its job done without half-updated state? > Additionally, nested transactions can amplify performance anomalies. The row locks established by a large transaction whose latency extends across multiple components/services are more likely to incur transaction conflicts which, at best, increase the average latency of queries and, at worse, increase the error rate quadratically with the current query load. Is this not a CockroachDB-specific performance limitation, rather than an argument against nested transactions per se?

blackrock 6y ago

Ahh.. what’s in a name? Not much. Except when you name yourself something stupid.