andydb

We're working on enabling PostgREST. There is some small-ish functionality that we need do in CockroachDB for it to fully work.

We're still discussing options there, so I don't have an answer on how we might handle them. We do recognize that many business customers will have such requirements.

We haven't run into that so far, but thank you for pointing it out as something to watch out for.

I think you're right on target with the multi-tenant SAAS services. They often suffer from the "long-tail" problem, where the tiny customers without much usage cost too much to host. And of course, that's one of the big problems the CockroachDB Serverless architecture solves.

There's no technical reason why CockroachDB Serverless couldn't support that scenario - it's more of a product/business/sales question. I think we'd be very open to talking with companies that have that kind of problem to solve.

At this point, only Postgres SSL connections are supported.

Our product roadmap will be heavily influenced by customer asks. So if there are things about CockroachDB Serverless that prevent you from using it (like requiring ingress through a public IP), we definitely want to hear about it.

Regarding a cluster limit, we currently allow up to 5 clusters per customer account. I'd like to hear what kind of use-cases you have in mind for having a lot more clusters. One I've thought about is CI runs, where you'd want dozens or hundreds of temporary clusters running at once, in order to run your testing in parallel.

Yes, definitely, we'll be expanding to more regions soon.

Sure, those are all good points. We'll definitely keep an eye on customer demand in these areas so we can make good prioritization decisions. It can be frustrating when there are so many things that we'd like to do, but we can only pick a small subset to actually do.

It's been something we've done since the start and plan to continue doing. If you read back over our engineering blog, you'll find a surprisingly thorough description of the entire CockroachDB stack, from the lowest storage layer to distributed transactions, Raft consensus, SQL => key-value mapping, online schema changes, cost-based SQL optimizer, Kubernetes usage, and so on. In fact, when we onboard new engineers, we typically point them to a stack of external blog entries to read in order to get up-to-speed on how CockroachDB works.

Being open on how we solve hard problems is the way to build our collective knowledge as a developer community. Certainly CockroachDB itself has benefited enormously from all that has gone before and been published in the open.

Oh boy, I'm an engineer, but I'll do my best to pretend I'm on the sales or marketing team for a minute...

First of all, CockroachDB Serverless is available on AWS, and should integrate quite well with that ecosystem, including with Serverless functions offered by AWS Lambda.

Here are a few advantages of CockroachDB Serverless that Aurora will struggle to match (note that we're still working on Serverless multi-region support):

1. Free-forever tier. We offer a generous "free forever" tier that doesn't end after a month or a year. As the blog post outlines, our architecture is custom-built to make this economical.

2. No ceiling on write scalability. Even non-Serverless Aurora runs into increasing trouble as the number of writes / second increases past what a single machine can handle. CockroachDB just keeps going. We've had multiple high-scale customers who hit Aurora limits and had to move over to Cockroach to support business growth.

3. True multi-region support. Aurora only allows read-only, stale replicas in other regions, while CRDB allows full ACID SQL transactions. If you want to move into other regions of the world and have latency concerns or GDPR concerns, CRDB is custom-built to make the full SQL experience possible.

4. No Cloud lock-in. Perhaps this is not a concern for you company, but many companies don't like getting completely locked in to a single Cloud provider. CockroachDB works on multiple cloud providers and doesn't have a monetary interest in locking you in to just one.

5. Online schema changes. CockroachDB supports operations like adding/removing columns, renaming tables, and adding constraints without any downtime. You can perform arbitrary schema changes without disturbing your running application workloads. SQL DDL "just works".

6. Cold start in an instant. CockroachDB clusters automatically "scale to zero" when they're not in use. When traffic arrives, they resume in a fraction of a second. Compare that to Aurora, where you need to either have a minimum compute reservation, or you need to endure multi-second cold starts.

7. Great support. We've got a friendly Slack room where you can get free support and rub shoulders with fellow CockroachDB users, as well as CockroachDB folks like myself. We also have 24/7 paid support for deeper problems you might encounter.

Taken altogether, CockroachDB can go wherever your business needs it to go, without all the constraints that traditional SQL databases usually have. Do you want thousands of clusters for testing/development/tiny apps at a reasonable cost? Could your business take off and need the scale that CRDB offers? Could your business need to expand into multiple geographic regions? Are some of your workloads erratic or periodic, but still should start up instantly when needed? It's not just about what you need now, but what you may need in the future. It makes sense to plan ahead and go with a database that has "got you covered" wherever you need to go.

It's not currently possible, partly because it complicates the deployment model quite a bit. Dynamically bringing SQL pods up and down requires a technology like Kubernetes. It takes some serious operational know-how to keep it running smoothly, which is why we thought it would be perfect for a managed Cloud service.

What would be your company's reasons for wanting this available in self-hosted CRDB? What kinds of use cases would it address for you?

You've run into a "rough edge" of the beta release that will be fixed soon. When you keep the Cluster Overview page open, it runs queries against your cluster so that it can display information like "# databases in the cluster". Unfortunately, those queries run every 10 seconds in the background, and are consuming RUs, which is why you see RU usage without having connected to the cluster yet. But never fear, we'll get that fixed.

One thing that may not be clear - you get 10M RUs for free, up front, but you also get a constant accumulation of 100 RU/s for free throughout the month. That adds up to >250M free RUs per month. This ensures that your cluster is always accessible, and that you never truly "run out" of RUs - at most you get throttled to 100 RU/s.

I hear you on the difficulty of understanding how your queries map to RUs. SQL queries can be enormously complex and differ by multiple orders of magnitude from one another in terms of their compute cost. That's why we built a near real-time dashboard that shows you how quickly you're consuming RUs. You can run your workload for a few minutes and then check back on the dashboard to see how many RUs that workload consumed.

Great question. We recognize how important this is and are actively working on it.

To any who might see this, I'm the author of the blog post, and led the engineering team that built CockroachDB Serverless. I'll be monitoring this thread in case there are any questions you'd like to ask me about it.

Re. 1, doesn't the majority set of logs for a given logical sequencer need to overlap with the majority set of logs for each other logical sequencer? If not, I don't see how you guarantee strict serializability without clocks. But if so, then all you're saying is that you're "averaging" the latencies to get better predictability. So if the average latency between any 2 DC's is 100ms, but the max latency between the two furthest DC's is 300ms, then you'll get closer to 100ms everywhere in the world by using this scheme. That's a good thing, but it's still 100ms!

However, since I may be misunderstanding something, a concrete example is helpful. Say you have 3 DC's in each of 3 regions (9 total DC's): Europe, Asia, and the US, with 10ms of intra-region latency and 100ms of inter-region latency. Say I want to commit a read-write transaction. What commit latency should I expect?

Re. 2, the reason Spanner (and soon CockroachDB) offer bounded-staleness snapshot reads is not due to the clock ambiguity window. You're working with old information from a paper published years ago. At this point, my understanding is that Google has reduced the window from ~7ms down to 1-2ms (maybe even less). Furthermore, these are reads, which don't need to wait in Spanner to begin with. There are at least 2 scenarios where bounded-staleness reads are useful:

  1. When you have heavy read load and want to distribute that load across all of your replicas.
  2. When you want to read from the nearest replica in order to reduce latency, in cases where replicas are geographically distributed.

You also have a misconception about CockroachDB. In practice, CockroachDB almost never waits when reading. It only does so when it tries to read a record at time T1, and notices that another transaction has written to that same record at time T2 (i.e. later in time, within the clock uncertainty window). So unless you're trying to read heavily contended records, you'll rarely see any wait at all.

You're also incorrect about the reasons for CockroachDB's lack of support for follower reads. Here is an RFC describing how bounded-staleness follower reads will work in an upcoming version:

  https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20180603_follower_reads.md

The real reason that CockroachDB does not yet support follower reads is that other things took priority in the development schedule. It's always been possible to support them. I expect they'll work with similar utility and latency to Spanner and FaunaDB follower reads.

As for your assertion that "Ones that do are much higher latency than Spanner and FaunaDB both", it's just ... wrong. Perhaps you're thinking of older versions, before CockroachDB had transaction pipelining:

  https://www.cockroachlabs.com/blog/transaction-pipelining/

Furthermore, the end of the article alludes to an upcoming feature called "parallel commits", in which most distributed transactions will have commit latency = ~1 round of distributed consensus.

FaunaDB has to make painful (to applications) tradeoffs between latency and consistency in global scenarios:

1. All read-write transactions pay global latency to a central sequencer. So, yes, FaunaDB is strictly serializable, but at the cost of high latency of read-write transactions.

2. Read transactions have to choose between:

  a. Strict serializability but high latency
  b. Stale reads but low latency

Anomalies absolutely can happen in FaunaDB if applications use option 2b. However, most users will not appreciate the subtlety here, and some will unwittingly go into production with consistency bugs in their application that only manifest under stress conditions (like data centers going down and clogged network connections). Their only other option is 2a, and that is just a no-go for global scenarios. You can't route your regional reads through a central sequencer that might be located on the other side of the world.

Your argument reduces to: "FaunaDB has no anomalies in global scenarios! That is, as long as you're OK with a global round-trip for every read-write and read-only transaction...". FaunaDB has not solved the consistency vs. latency tradeoff problem, but has simply given the application tools to manage it. A heavy burden still rests on the application.

By contrast, Spanner users get both low latency and strict serializability for partitioned reads and writes (that's and, not or, like FaunaDB). CockroachDB users get low latency and "no stale reads" for partitioned reads and writes. Partitioning your tables/indexes to get both high consistency and low latency is a requirement, but it's not difficult to do this in a way that gives these benefits to the majority of your latency-sensitive queries, if not all of them. After all, this is what virtually every global company does today - they partition data by region, so that each region gets low latency and high consistency. You only pay the global round-trip cost when you want to query data located across multiple regions, which is rare by DBA design.

The main point of the article is that the "no stale reads" isolation level is almost as strong as "strict serializability", and is identical in virtually every real-world application scenario. This means CockroachDB is equivalent to Spanner for all intensive purposes.

Database tuning tools are not on the near-term roadmap, but I think it's a good idea that we'll want to look at eventually. Using ML to self-tune databases is a hot research subject right now, so we'll be following developments on that front.

SELECT * FROM a, b WHERE a.x = b.x AND a.y='foo' AND b.z='bar'

The best plan for this simple query depends on the selectivity of the predicates. For example, if 20k rows have a.y='foo', but only 10 rows have b.z='bar', then it's best to scan a b.z index, then lookup matching rows in a. But if the #rows is reversed, then it's better to scan an a.y index and lookup in b. This is a simplified example, but we do see queries along these lines in real OLTP workloads.

You're correct that for many OLTP workload queries, simple heuristics are sufficient. However, even if that's true for 90% of queries, it's the last 10% that gets you. We've seen customers with 10 queries in their workload, where 9 work well but the last 1 gets a bad plan that is 10x slower than it could/should be. Maybe they can rewrite it, or maybe they don't have sufficient knowledge to do so. Or perhaps they're using an ORM and don't have control over the queries it's sending to the DB. In addition, many mostly-OLTP workloads contain a few OLAP reporting queries in the mix. Developers don't expect their OLTP database to perform like a dedicated OLAP data warehouse DB, but they also expect it not to fall over when it gets a more complex query.

We've talked about ideas along those lines. However, rather than altering the plan mid-query, we'd be more likely to have some kind of feedback mechanism that corrects estimates for the benefit of future plans.

Another commenter posted an interesting paper that's related to your question:

https://www.microsoft.com/en-us/research/uploads/prod/2018/0...

This is not a book, but we did write up some documentation for developers that gives a nice overview of how the optimizer works:

https://github.com/cockroachdb/cockroach/blob/master/pkg/sql...

Might be worth a read if you're interested in implementation techniques.

I think you're right; I'm a chess player, and working on the optimizer does hit some of the same neural pathways.

Interesting MSR paper. With regards to Orca, we did take a look at it. It's an OLAP optimizer written in C++ (and very lightly commented and documented, unfortunately), and we wanted a Go-based optimizer that was more integrated with our codebase, as well as targeted at OLTP scenarios (as example Orca supports parallel search, which makes sense in OLAP, but not so much in OLTP). There is quite a bit of overlap, though, and we read their excellent paper and consulted the code from time to time (I hereby invoke a curse on Hungarian notation, which makes reading their code quite painful). We also suspect that Greenplum did not open source some key bits of the optimizer code, such as their search prioritization algorithms.

Yes, that would be clearer language. I'll look into updating the text.

To be clear, the Exodus/Volcano/Cascades foundation includes more than just the Memo. Also important is the idea that you can declaratively define rewrite rules and generate parts of your optimizer. Also, optimization proceeds top-down rather than bottom-up, which allows you to prune the search space as you explore, as well as open up important lines of communication from nodes higher in the tree to nodes lower in the tree (i.e. required physical properties, which I explain in another HN comment).

You're correct on your points (search space can be smaller/faster, with less memory usage). In addition, when you're dealing with a piece of software as complex as a SQL optimizer, choosing modular, elegant abstractions can make a big difference in terms of understandability, maintainability and extensibility of the codebase. That in turn leads to fewer bugs, such as subtle violations of logical equivalence when transforming the tree. I think the more structured, general approach to optimization allows mere mortals to manage higher levels of complexity, in which the RDBMS defines 100's of transformations, offers many join ops (hash, merge, nested, zigzag, etc.) and data types (geospatial, json, etc), as well as incorporating data locality in the new world of geo-distribution.

As one example to illustrate why there's a practical correspondence between well-designed abstractions/architecture and correctness (i.e. fewer bugs), consider these PRs that we recently merged:

  https://github.com/cockroachdb/cockroach/pull/31977
  https://github.com/cockroachdb/cockroach/pull/30636

These PRs randomly disable transformation rules and perturb the cost of expressions in the optimizer, in order to test alternate (but equivalent) plans. It took just 100 lines of code to add this support, because it simply plugs into the general optimization framework. And in return, our existing test cases can now test multiple different plans, allowing us to shine light into the dark corners where bugs like to hide (cockroaches, of course!).

As another example, we have a testing mode called "optsteps", which will single-step the optimizer and print out the tree after each transformation to make it easy to track down bugs. Here's an example of that output, again made possible by the extensible, modular design of the optimizer:

  https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/opt/norm/testdata/rules/combo

Postgres and MySQL both do calculate costs. One difference is that they are not based around the Memo data structure (someone correct me if I'm wrong about that!), which gives an extra measure of flexibility, power, and elegance to solving the problem of SQL optimization. The Memo structure was introduced by Goetz Graefe in his seminal papers on top-down, rule-based query optimization (Exodus, Volcano, Cascades), which are the chief inspiration for CockroachDB's new optimizer. In the late 90's, Goetz joined Microsoft, and assisted the SQL Server team in rewriting their optimizer from scratch based on the techniques he had pioneered. I believe this foundation is a big reason why the SQL Server optimizer has become one of the best (if not the best) in the world. We are indeed building on the shoulders of giants.

Yes, that's a real problem that DBAs routinely grapple with. To address the problem, RDBMS vendors typically support hints and "plan management" features that allow DBAs to "pin" a particular plan once they've determined it's best. However, this usually requires a knowledgeable DBA; many developers would prefer that the database automatically chooses the plan, even if it changes from time to time. CockroachDB will definitely want to support both kinds of users.

Good question, and something I didn't have the space to cover in this article. The answer involves "physical properties". Physical properties are interesting characteristics of an expression that impact its layout, presentation, or location, but not its logical content. Examples include row order, column naming, and data distribution (physical location of data ranges in the cluster).

As an example, consider a memo group that contains both a MergeJoin and a HashJoin operator. The MergeJoin "requires" its inputs to be sorted on the same key. If an input is not already sorted, then we insert a "sort enforcer" operator that will provide the required ordering. Of course, that increases the cost of producing the input, which the optimizer must take into account.

So, getting back to your question, for any given memo group, we always select the equivalent plan with the lowest cost, for a given set of physical properties. However, that has the effect of sometimes selecting a memo group operator that has a higher cost, because it "pays off" higher up the operator tree. Back to my example, say the SQL query is something like this:

  SELECT * FROM a, b WHERE a.name=b.name ORDER BY a.name

The lowest cost plan might use a MergeJoin, because its output is already naturally sorted on "name". However, the input to the MergeJoin might be higher-cost than logical equivalents, because it must be sorted. Therefore, we have the situation you alluded to, where we choose a higher-cost subtree because it minimizes the cost of the larger tree that contains it.

Not sure I fully understand your question, but I'll try to answer:

FaunaDB (like other highly available databases) is set up to have multiple replicas of the same data. You can direct reads to any of the replicas. For example if replica #1 is unavailable due to a network outage, you can read replica #2 instead.

This thread is about the behavior of FaunaDB, which allows reads to replicas that have older stale values (i.e. they're not up-to-date with latest data). This causes the various consistency anomalies that I've been describing. The reason FaunaDB works like this is to make reads faster and to better distribute read load. Allowing secondary/follower/slave reads like this is a common tactic for increasing read performance.

Does that answer your question?

Sorry to put you on the spot. Perhaps your CTO can chime in when he gets back.

Serializable tends to sound really good to database people, because it's the "highest" isolation level available in most databases. However, those databases are built for a single machine, and they really offer "strict serializable" without calling it that, because they maintain a single log which serializes all writes, and all reads operate as of the latest time. With the advent of distributed databases, the "strict" part is becoming more relevant, because it's no longer something you just get without realizing it.

I gave an example of the "blog post" anomaly that occurs in a system that only guarantees serializability. As another example, imagine I am using FaunaDB to store bank account balances. As the developer, I design a system where any time a customer receives money to their account, I send them a text giving the amount and their new balance, along with a link to the website where they can get more details. Except, when they click the link and go to the website, they don't see any change to their balance. Furthermore, when they refresh the page, sometimes they do see their new balance, and then they refresh again, and it disappears!

More investigation reveals that FaunaDB is in a partitioned state, and was serving stale bank account balances from some replicas. The replicas were behind a load balancer, and sometimes the web client hit one replica, and sometimes another, which explains the alternating balances. Is it possible to solve this problem using clever application logic, causal tokens, or saying "don't do that"? Sure. But a consistent database is supposed to prevent this scenario, not the application. Also, note that the "this is no different than increased client-server latency" argument does not hold here. People expect that if they finish action A (like writing to the DB), then do action B (like clicking a web link that reads the DB), that B will always see the results of A. The stale bank account balance isn't a delayed value; it's a wrong value.

You're correct that most systems allow some variant of "secondary" or "follower" reads that can return stale data, in exchange for better performance. I'm all for this kind of feature, as long as the developer understands the tradeoff they're making. Moreover, I think it's appropriate for FaunaDB to offer the same. But I'd advise against making unqualified claims of "complete guarantees of serializability and consistency" and the "latency profile of an eventually-consistent system like Apache Cassandra". FaunaDB is able to offer one or the other, but not both at once.

But opting into the log for read-only txns would destroy performance for many apps, both in terms of latency and throughput. This is rarely practical since so many apps are read-heavy. I assume you don't recommend that to your customers, especially in global deployments.

Causal tokens are practical from a performance point of view, but do require application awareness and changes, which presents a different set of issues. Most application developers would not appreciate the subtleties and just use the default behavior, which does not guarantee "read your own writes". And causal tokens don't prevent the more general case of stale reads.

This would mean that in the default FaunaDB configuration, applications could see anomalies:

1. Inability to read own writes - described above, with the additional clarification that it can't be fully solved at the driver level. For example, say I call a web API that creates a blog post and returns a PostID. I then call another API that edits that post, passing it the PostID I got back from the first. If FaunaDB is in use, the second call could fail to find the PostID, because I happened to hit a different replica that did not yet have it.

2. Stale reads - could be arbitrary levels of staleness if replica/coordinator haven't been able to sync to the global log. If a coordinator node and a replica were stuck in their own little partition, cut off from the rest of the cluster, they'd still happily serve stale reads (until perhaps they detect a partition and shut down?).

These read anomalies aren't usually considered compatible with a consistent system, but instead are hallmarks of an eventually consistent system. It seems misleading to make an unqualified 100% consistency claim when it doesn't apply to read-only txns by default, and when the fully consistent mode is not practical to use in global deployments.