I'd like to hear more about the magic.
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.
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?"
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
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.
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".
[1] https://www.cockroachlabs.com/docs/stable/demo-serializable....
[1] https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be...
Heh... The Pinto, for safety? https://en.wikipedia.org/wiki/Ford_Pinto#Fuel_system_fires,_...
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.
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.
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
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.
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...
Sounds like mob programming!
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.
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.
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)
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.
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.
Thankfully sharding works great for a large number of applications (or in other cases you can accept eventual consistency).
Even better, sometimes you can change which guarantee you need.
We can do better then "pick two"
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)
Given the need for Partitions, you must choose between prioritizing Consistency or Availability.
— https://cloud.google.com/blog/products/databases/inside-clou...
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.
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.
I’m not trying to nitpick but what GP describes aligns to what Mnesia provides.
The distributed part of modern sql is fairly recent.
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.