[grobbie]

CockroachDB is getting a lot of interest these days.

It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture well suited to running in a dynamic environment like cloud or k8s. Nodes can join dynamically and it can survive them leaving dynamically as long as there's a quorum. Data is distributed across the nodes without administrator needing to make any shard rebalance type interventions.

PGSQL is designed for deployment as a single server with replica servers for HA. It's not really designed for horizontal scalability like Cockroach. You can do it - the foreign data wrappers feature and table partitioning can give you poor man's scale out. Or you can use Citus which won itself a FOSS license earlier this year. And there are other Foss and proprietary approaches too.

MySQL is similar - you can do it, like with their recent router feature, but it has been retrofitted, and it's not as fluid as Cockroach. IIRC MySQL router is similar in configuration to Galera - that is, a static config file containing a list of cluster members.

Listen I'm sure that the design approach of Cockroach could be retrofitted to PGSQL and MySQL, but I'm pretty sure that doing a good job of it would be a lot of work.

So in answer to your question, I'm not sure that there's all that much RDBMS can't be made to do. Geospatial, Graph, Timeseries, GPU acceleration. Postgres has it all and often the new stuff comes to Postgres first.

By the way I love MySQL and PostgreSQL, and the amazing extensions for PGSQL make it extra awesome. Many are super mature and make pgsql perfect for many many diverse use cases.

For XXL use cases though, CockroachDB is taking a very interesting new path and I think it's worth watching.

[dvtkrlbs]

Not a database expert but here is my thoughts after using Cockroach DB for a mid sized: - Single cluster mode is really nice for local deployment - WebUI is a cool idea but I wish it had more info - The biggest problem is Postgres compatibility there are some quirks that was annoying examples being. The default integer types having different sizes, cockroachdb having a really nice if not exists clause for create type but having no way to maintain a Postgres compatible way of doing it (i think this one is on postgres only being able to do it with plsql scripts is cumbersome) - For me the neutral one. IT HAS ZERO DOWNTIME SCHEMA CHANGES. But if you are just coming from Postgres and just using a regular migration tool and transactions and schema changes having serious limitations and could end up your database in inconsistent state is scary. (Docs really document the behavior but still I would expect a runtime warning for it.

[Supermancho]

CDB was great until we started doing table creation on the minute and hundreds of inserts on those tables. When we tried to drop tables on a schedule, CDB never could catch up and would generally just crash (3 nodes). I really don't like the magic you have to do with CDB for things that your commodity DBs can be expected do.

[vvern]

CockroachDB dev here. We've gotten a good bit better in the last few versions in terms of schema change stability. We're still not very good at large schemas with more than 10s of thousands of tables but we've got projects under way to fix that which I expect will be in the release in the spring of 22.

I'd like to hear more about the magic.

[Aeolun]

To be fair, while I kind of agree the system should be able to handle it regardless, 10,000s of tables sounds outside the realm of 99.99% of all use cases.

[CydeWeys]

You might be surprised to learn how common the "store the results of the user's query into a temporary table" pattern is.

[vvern]

Temporary tables in cockroach exist, but the implementation was done largely to fulfill compatibility rather than for serious use.

The implementation effectively just creates real tables that get cleaned up; they have all the same durability and distributed state despite not being accessible outside of the current session.

Getting something done here turned out to be a big deal in order to get ORM and driver tests to run, which is extremely high value.

A better implementation would just store the data locally and not involve any of the distributed infrastructure. If we did that, then temp tables wouldn't run into the other schema scalability bottlenecks I'm raising above.

[np_tedious]

Can you tell me more about the use case where you'd create a new table every minute?

[valzam]

Not OP but for analytics use cases it is common to create new tables for each minute, hour, day or whatever granularity you collect data in. This makes it easier to aggregate later, you don't end up with extremely big tables, you can drop a subset of the data without affecting the performance of the table currently being written to etc..

[dafelst]

That sounds like what table row partitioning is for, I thought all the major databases supported that?

[sandstrom]

I agree, CockroachDB is overall very good!

A few things I'm missing from it though:

- Advisory Locks (only exist as a shim/no-op today)

- LISTEN/NOTIFY

- CTEs (Common Table Expressions)

- Introspection and management features (for example pg_locks, pg_stat_activity, pg_backend_pid(), pg_terminate_backend())

They are making good progress though, and more recently they've spent effort on making adapters for well, e.g. their ActiveRecord adapter.

I think this is clever, since the deciding factor for many companies will simply be "Can I easily replace this with PostgreSQL in my Rails/Django/etc. project?"

[traceroute66]

> It has broad PGSQL language compatibility

Depends how you define broad. :)

Many key features are missing, including but not limited to:

    - UDFs and sprocs.
    - Useful datatypes such as TSTZRANGE
    - More limited constraints

I was recently looking at Cockroach as a PGSQL replacement because of its distributed nature. But the equivalence featureset is still lagging badly, unfortunatley.

[rad_gruchalski]

May I suggest looking at YugabyteDB, which is a distributed SQL database built on actual PostgreSQL 11.2 engine? It's way ahead of CockroachDB in terms of feature support.

YugabyteDB has UDFs, stored procedures, distributed transactions, the range types are working from what I can tell, at least the example from here: https://wiki.postgresql.org/wiki/Extract_days_from_range_typ... works right out of the box, just copy paste. Postgres extensions are of course working. Orafce, postgis (with extra work needed for gin indexes, afair...), custom extensions.

YugabyteBD == Postgres. The query planner, analyzer and executor are all Postgres. Mind you, some features are not readily available because handling them properly in a distributed manner takes effort. Those unsupported features are disabled on the grammar level, before being worked on. But - unsupported features will not corrupt your data. Missing features are enabled very fast.

For example, I have recently contributed foreign data wrapper support: https://github.com/yugabyte/yugabyte-db/pull/9650 (enables postgres_fdw at a usable level) and working on table inheritance now.

Yugabyte is an amazing bit of technology and more people should know about it. By the way - it's Apache 2 with no strings attached.

For clarification: I'm not associated with Yugabyte in any way other than very happy user and contributor. Yugabyte organizes the Distributed SQL Summit later this month: https://distributedsql.org/. Might be a good opportunity to learn more about it. Second clarification: I'll be speaking at the event.

[tuckerconnelly]

Are you still using postgres? Thinking about a similar transition, would really appreciate any shared learnings :)

[traceroute66]

> Are you still using postgres?

At the moment yes.

The final decision has not been made yet, but I think the reality is we're getting tired of evaluating all these distributed databases that claim Postgres compatibility only to find its the usual clickbait marketing speak.

So the most likely outcome is we're going to stick with Postgres and give the emerging distributed tech a couple more years to pull their socks up. We're not going to go round changing all our Postgres code and schemas just to shoehorn into the limitations of a random definition of "postgres support".

[why-el]

I don't know if anybody from CockroachDB is reading this but their article[1] on serializable transactions is somewhat questionable, as it compares CockroachDB's Serializable to Postgres's Read Committed, which seems to imply CockroachDB is better. Of course, Postgres has serializable as well. The only novelty in the article is that Cockroach DB runs Serializable by default, so I am not sure what that comparison was doing.

[1] https://www.cockroachlabs.com/docs/stable/demo-serializable....

[ummonk]

I don't get that implication from the article at all. What I do get is that the only transaction isolation level available under CockroachDB is serializable. And to show why serializable is valuable, they have to demonstrate using Postgres' default of read committed (because they would be unable to demonstrate this using CockroachDB which doesn't allow any other transaction isolation levels.

[why-el]

Ah, fair reading, thanks. As Postgres supports that level of isolation as well, I was thrown off a bit. but your phrasing makes sense.

[AdamProut]

I don't think CockroachDB supports anything other then serializable isolation level, so of course they are going to be pushing this type of argument. Its an interesting philosophy really. It would be one thing if the performance penalty of running all transactions at serializable isolation level vs weaker isolation levels was reasonably low, but its not (see [1] - yes I know the entire difference in performance here is not due to CDB using serializable transactions for everything, but I'm guessing a very large part of it is). Its kind of like saying the only cars in the world should be ford pintos because we don't trust folks to safely drive faster cars.

[1] https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be...

[CRConrad]

> Its kind of like saying the only cars in the world should be ford pintos because we don't trust folks to safely drive faster cars.

Heh... The Pinto, for safety? https://en.wikipedia.org/wiki/Ford_Pinto#Fuel_system_fires,_...

[mirekrusin]

It always rubs me the wrong way - all those paxos/raft approaches (which are great, but...) simply elect the leader to pick writes. In that sense there is no distribution of computation at all. It's still single target that has to cruch through updates. Replication is just for reads. Are we going to have something better anytime soon? Like real distribution, when you add more servers writes distribute as well?

[josephg]

> Like real distribution, when you add more servers writes distribute as well?

There's a really interesting suggestion in The Mythical Man Month. He suggests that instead of hiring more programmers to work in parallel, maybe we should scale teams by keeping one person writing all the code but have a whole team supporting them.

I don't know how well that works for programming, but with databases I think its a great idea. CPUs are obnoxiously fast. Its IO and memory which are slow. I could imagine making a single CPU core's job to be simply ordering all incoming writes relative to each other using strict serialization counters (in registers / L1 cache). If the stream of writes coming in (and going out) happened over DPDK or something, you could probably get performance on the order of 10m-100m counter updates per second.

Then a whole cluster of computers sit around that core. On one side you have computers feeding it with writes. (And handling the retry logic if the write was speculatively misordered.) And on the other side you have computers taking the firehose of writes, doing fan-out (kafka style), updating indexes, saving everything durably to disk, and so on.

If that would work, you would get full serializable database ordering with crazy fast speeds.

You would hit a hard limit based on the speed of the fastest CPU you can buy, but I can't think of much software on the planet which needs to handle writes at a rate faster than 100m per second. And doesn't have some natural sharding keys anyway. Facebook's analytics engine and CERN are the only two which come to mind.

[yencabulator]

> I could imagine making a single CPU core's job to be simply ordering all incoming writes relative to each other

Calvin is an interesting alternate design that puts "reach global consensus on transaction order" as its first priority, and derives pretty much everything else from that. Don't even need to bottleneck through a single CPU.

http://cs-www.cs.yale.edu/homes/dna/papers/calvin-sigmod12.p...

Like VoltDB, the one huge trade-off is that there's no `BEGIN ... COMMIT` interaction where the client gets to do arbitrary things in the middle. Which would be fine, if programming business logic in the database was saner than with Postgres.

[hodgesrm]

What you suggest would work if you just want to order writes. However, it won't support an ACID transaction model, which a lot of applications expect, or even simpler models where you can read values before you write.

For instance: consider an application that looks at the current value of a counter and adds 1 if the counter is less than 100. You can execute this on the primary (resource bottleneck because you need to see current state and only the primary has the full picture) or do the operation on a local node and try to submit it to the primary for ordering (coordination required over the network, e.g., to ensure data are current).

There are other approaches but they generally result in the same kind of conflicts and consequent slow-downs. Or they limit the operations you can handle, for example by only allowing conflict-free operations. That's what CRDTs do. [1]

[1] https://hal.inria.fr/hal-00932836/file/CRDTs_SSS-2011.pdf

[josephg]

> However, it won't support an ACID transaction model

I think you could add ACID support, while the process doing ordering still not caring about the data. You do something like this:

- Split the keyset into N buckets. Each bucket has an incrementing version number. (The first change is 1, then 2, then 3, and so on). The whole system has a vector clock with a known size (eg [3, 5, 1, 12, etc] with one version per bucket.)

- Each change specifies a validity vector clock - eg "this operation is valid if bucket 3 has version 100 and bucket 20 has version 330". This "validity clock" is configured on a replica, which is actually looking at the data itself. The change also specifies which buckets are updated if the txn is accepted.

- The primary machine only compares bucket IDs. Its job is just to receive validity vector clocks and make the decision of whether the corresponding write is accepted or rejected. If accepted, the set of "write buckets" have their versions incremented.

- If the change is rejected, the secondary waits to get the more recent bucket changes (something caused it to be rejected) and either retries the txn (if the keys actually didn't conflict) or fails the transaction back to the end user application.

So in your example:

> consider an application that looks at the current value of a counter and adds 1 if the counter is less than 100

So the write says "read key X, write key X". Key X is in bucket 12. The replica looks at its known bucket version and says "RW bucket 12 if bucket 12 has version 100". This change is sent to the primary, which compares bucket 12's version. In our case it rejects the txn because another replica had a concurrent write to another key in bucket 12. The replica receives the rejection message, checks if the concurrent change conflicts (it doesn't), then retries saying "RW bucket 12 if bucket 12 has version 101". This time the primary accepts the change, bumps its local counter and announces the change to all replicas (via fan-out).

The primary is just doing compare-and-set on a small known array of integers which fit in L1 cache, so it would be obscenely fast. The trick would be designing the rest of the system to keep replica retries down. And managing to merge the firehose of changes - but because atomicity is guaranteed you could shard pretty easily. And there's lots of ways to improve that anyway - like coalescing txns together on replicas, and so on.

[hodgesrm]

I'm really sorry didn't see your comment earlier. I like your solution but am a little stuck on the limitations.

1.) It requires applications to specify the entire transaction in advance. ACID transactions allow you to begin a transaction, poke around, change something, and commit. You can derive the transaction by running it on a replica, then submitting to the primary, in which case this becomes an implementation of optimistic locking including transaction retries.

2.) As you pointed out the trick is to keep the array small. However, this only works if you have few conflicts. Jim Grey, Pat Helland, and friends pointed this out in 1996. [1] That in turn seems to imply a very large number of buckets for any non-trivial system, which seems like a contradiction to the conditions for high performance. In the limit you would have an ID for every key in the DBMS.

3.) Finally, what about failures? You'll still need a distributed log and leader election in case your fast machine dies. This implies coordination, once again slowing things down to the speed of establishing consensus on the network to commit log records.

Incidentally Galera uses a similar algorithm to what you propose. [2] There are definitely applications where this works.

[1] https://dsf.berkeley.edu/cs286/papers/dangers-sigmod1996.pdf

[2] https://galeracluster.com/library/documentation/certificatio...

[brotherofsteel]

>There's a really interesting suggestion in The Mythical Man Month. He suggests that instead of hiring more programmers to work in parallel, maybe we should scale teams by keeping one person writing all the code but have a whole team supporting them.

Sounds like mob programming!

[Nican]

There are two ways I see databases doing paxos. The basic way, like some databases like Percona, basically is a single raft across the whole database. It can help with high availability, but the database scale is still kind of constrained to the capability of a single writer.

What you really want is databases like CRDB/Yugabyte/TiDB, which are sharding+raft. Tables are sharded into 128MB chunks, and each chunk has their own raft. The database handles transactions, distributed queries, and auto-balancing transparently.

[rad_gruchalski]

Definitely, YugabyteDB falls into the second category. Tables are split into so called tablets. This splitting can be controlled by choosing the hashing algorithm for the primary key (or transparent row key hash, if no primary key for a table exists). Each tablet is replicated and has its own raft group. Different tables can have different replication factors, the number of tablets to split the table into can be selected and modified.

YugabyteDB recently added support for table spaces on steroids where table spaces are allocated to physical nodes in the cluster. This enables geo-replication features where tables or rows can be placed within selected geo locations.

All the data shifting is done transparently by the database.

[remram]

Doesn't CockroachDB work this way as well, with each "range" running a separate Raft? That is what I get from https://www.cockroachlabs.com/docs/stable/architecture/overv...

[skyde]

All those noSQL or newSQL have more than one leader per table they have a distinct leader for each partition.

So if you have 6 server and 2 partition you could have 3 servers for partition number #1 and 3 different servers for partition number #2.

If you want extra performance you could make the server simply store key->value mapping using quorum write like Cassandra is doing but to keep data consistency you still have 2 choice

#1 use Optimistic concurrency (an app performing an update will verify if the data has changed since the app last read that data).

#2 using some kind of Lease, elect one machine to be the only one allowed to write to that partition for some time period.

Option #1 do not give faster transaction throughput but could offer lower tail latency.

Option #2 bring you back to square one of having a leader so you better just use (Paxos/Raf)

[FpUser]

You are asking for a miracle or just simply - breach of the physics laws. The only way to horizontally scale write speed is to shard your data to be written somehow. You can easily do it but then your reading queries have to go to multiple servers to assemble single result. You can't scale both at the same time. There are some in between solutions like eventual read consistency that are relying on traffic at some point easing enough so that the conductor can actually synchronize servers. But if you have a steady stream of read/write requests the only way you really can scale is to tell amazon to sod off, buy yourself big fat multicore / multi CPU server with nice SSD array (preferably of Optane type) and watch your processing power suddenly shoot through the roof while the cost greatly decreases ;)

[zozbot234]

> There are some in between solutions like eventual read consistency that are relying on traffic at some point easing enough so that the conductor can actually synchronize servers.

You can use CRDT's to give a formally correct semantics to these "inconsistent" scenarios. And they might well be something that's best explored in a not-purely-relational model, since the way they work is pretty unique and hard to square with ordinary relational db's.

[FpUser]

CRDT is just a fancy term for the strategy that still has to eventually merge data. In case of CRDT the data organized / designed in a way that makes it easier. The keyword here is "merging" which by definition kills "infinite" scalability.

You can dance around all you want but you just can't beat laws of nature.

Sure you can always design something that works for your particular case but generic solution is not possible.

[mirekrusin]

Can't you arrange merging into ie. binary tree - in that setup you'd be collapsing merges into single one at the root and cummulative throughput at leaf nodes could be exponentially higher?

[riku_iki]

data is also sharded in those databases

[mirekrusin]

true, thanks for pointing out, but this is a bit cheating isn't it? ie. atomic transactions cross shards flip over, right? it's basically ergonomic equivalent of just using multiple databases?

[eis]

Not 100% sure what you mean by flipping over (fail?) but at least in CRDB you can of course do cross shard transactions. They wont be as fast as a transaction that can be completely handled by a single leader but it works fine and is transparent to the client.

[radicalbyte]

You move over into the world of distributed transactions, which can be really expensive.

Thankfully sharding works great for a large number of applications (or in other cases you can accept eventual consistency).

[GeneralMayhem]

Cross-shard transactions will use some sort of two-phase commit protocol internally. They still work just fine (the extra complexity is transparent to the client), but with somewhat worse performance. Most of the difficulty/expertise involved in using such systems optimally is in designing your schema and access patterns such that you minimize the number of cross-shard transactions.

[calmoo]

CAP theorem. Choose two, it's a fundamental limitation of scaling a database.

[j16sdiz]

CAP theorem. You can't have guarantee for having all three all the time. But you can still have nice thing most of the time.

Even better, sometimes you can change which guarantee you need.

We can do better then "pick two"

[pas]

yes, but no. because in reality what we really want is not true pure AP or CP (or CA, which you can't have anyway)

https://www.youtube.com/watch?v=hUd_9FENShA

(CAP is a very important result about a very strong assumption of consistency: linearizable events, but for that you can't lose any messages [if I remember it correctly], otherwise the system will become inconsistent)

[calmoo]

Thanks for the video, very interesting.

[ralusek]

I've always found CAP theorem to be better described as:

Given the need for Partitions, you must choose between prioritizing Consistency or Availability.

[Thaxll]

Google Spanner does the three.

[erik_seaberg]

> The purist answer is “no” because partitions can happen and in fact have happened at Google, and during some partitions, Spanner chooses C and forfeits A. It is technically a CP system.

— https://cloud.google.com/blog/products/databases/inside-clou...

[someelephant]

Make sure you thoroughly test your multi-region deploys. Last time we tried the system was not stable when a region went down. Also beware of this anti pattern: https://www.cockroachlabs.com/docs/stable/topology-patterns....

[bogomipz]

Interesting. So you followed the advice of deploying to greater than two regions and your DB didn't survive when you lost a region or you only deployed to two and got bit by the anti-pattern?

[someelephant]

Even with >2 when one goes down CDB performed poorly.

[alberth]

Sounds like Mnesia, which has existed for 20+ years.

https://erlang.org/doc/man/mnesia.html

[ramchip]

Mnesia isn't SQL-compatible, can't scale horizontally (each node has a full copy of the DB + writes hit all nodes), needs external intervention to recover from netsplits, and expects a static cluster.

It's pretty much the opposite of what the parent described IMHO. It's meant more like a configuration store than a proper DB. That's how RabbitMQ uses it for instance, it stores metadata in Mnesia and messages in a separate store, but they're working on replacing Mnesia with their own more modern store based on Raft.

[toast0]

> Mnesia isn't SQL-compatible,

I think this is accurate. There are some references to a masters project to do SQL with mnesia, but afaik, it's not supported or used by anyone.

> can't scale horizontally (each node has a full copy of the DB + writes hit all nodes),

This isn't accurate, each table (or fragment, if you use mnesia_frag) can have a different node list; the schema table does need to be on all the nodes, but hopefully doesn't have a lot of updates (if it does need a lot of updates, probably figure something else out)

> needs external intervention to recover from netsplits,

This is true; but splits and joijs are hookable, so you can do whatever you think is right. I think the OTP/Ericsson team uses mnesia in a pretty limited setting of redundant nodes in the same chasis, so netsplit is pretty catastrophic and they don't handle it. Different teams and tables have different needs, but it would probably be nice to have good options to choose from. There's also no built-in concept of logging changes for a split node to replay later, which makes it harder to do the right thing, even if you only did quorum writes.

> and expects a static cluster.

It's certainly easier to use with a static cluster, especially since schema changes can be slow[1], but you can automate schema changes to match your cluster changes. Depending on how dynamic your cluster is, it might be appropriate to have a static config defined based on 'virtual' node names, so TableX is on db101, but db101 is a name that could be applied to different physical nodes depending on whatever (but hopefully only one node at a time or you'll have a lot of confusion).

[1] The schema changes themselves are generally not actually slow, especially if it's just saying which node gets a copy, ignoring the part where the node actually gets the copy which could take a while for large enough tables or slow enough networks; it's that mnesia won't change the schema until all of the nodes are not currently doing a 'table dump' where up to date tables are written to disk so table change logs on disk can be cleared and those dumps can take a while to complete and any pending schema operations need to wait.

[rad_gruchalski]

That’s a huge oversimplification. Mnesia is distributed but that’s about it. There’s so much more to sql than selecting data. Transactions, user defined functions, stored procedures, custom types…

[alberth]

None of what you detail though is included in what the GP describes as a modern database.

I’m not trying to nitpick but what GP describes aligns to what Mnesia provides.

[rad_gruchalski]

The GP mentioned CockroachDB though. There’s a lot of implicit functionality behind that name. Even just behind mentioning Postgres.

The distributed part of modern sql is fairly recent.