Dear reader, this is not a detailed technical post. I started experimenting with CockroachDB what I found is enough to excite my curiosity. That’s why I am sharing a couple of snippets to you.
To be clear: I am not the kind of guy that sees X and claims that Y is obsolete. I will still use MySQL and MariaDB in the future, as well as PostgreSQL. I have very good reason for that. I don’t even have a use case for CockroachDB, currently. That said, there are some reasons why DBAs should take a look at this project – such reasons are beyond the scope of this post, but are clearly highlighted in Cockroach documentation.
Basically, I made some tests on DDLs whose results pleased me; incidentally, I’ll add some comments on SQL, the GUI and error handling. But note that I am a Cockroach newbie. I didn’t yet start to look at Cockroach internals – and the same goes for RocksDB, which handles Cockroach storage layer. So I observe behaviour, but I don’t know the underlying mechanisms. If you have questions, I am most probably not the best person to answer.
Transactional DDL
I executed these commands in node1:
START TRANSACTION; CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE IF NOT EXISTS t (a INT, b INT); INSERT INTO t VALUES (1, 1), (2, 2); ALTER TABLE t ADD COLUMN c INT DEFAULT 0; SHOW CREATE TABLE t; INSERT INTO t VALUES (3, 3); SELECT * FROM t; COMMIT;
I was very pleased to see that everything was rollbacked. At least CREATE/ALTER/DROP DATABASE/TABLE are transactional.
However I immediately found a caveat. Running CREATE DATABASE in a transaction puts a lock in the whole cluster – but not in the current node, curiously. So all SHOW DATABASEs executed on other nodes are locked. Not a big deal, but one has to be careful when doing some operations manually in production. CREATE TABLE doesn’t put that lock.
DDL is smartly online
Modifying a table is done without locking. But the columns become visible for different statements at different times. In my tests ALTER TABLE … ADD COLUMN performance was not bad. Adding an index was slower, as expected. But dropping an index is slower than adding an index, and dropping a column is only slightly faster than adding one.
But the speed itself is usually not the most important thing to consider. During ADD COLUMN, the only long stage was the one where the new columns are deletable and writeable. During DROP column the old column is only deletable from the start. So the only annoyance is that applications won’t be able to read form the new columns until the operation finishes. Table crdb_internal.schema_changes contains info about current state of the objects that are being modified.
I only tested a specific case with 5 millions rows, 3 dockerized nodes running on my laptop, 3 INT columns, adding 3 new columns, no indexes. Different tests can lead to different results, so don’t jump to general conclusions please – I didn’t.
When I crashed the node running the ALTER TABLE, other nodes switched to old schema version without troubles.
Now, based on my poor little tests, modifying columns is clearly better than running an ALTER TABLE with Galera, so another applause to Cockroach is well deserved. But it is still far from MySQL/MariaDB index drop, which is usually instantaneous,, and from MariaDB instant ADD COLUMN.
Notes on SQL dialect and the GUI
Cockroach uses PostgreSQL protocol, and they claim that their SQL dialect is also based on Postgres. However, in the snippets above you may have noticed some specific MySQL syntax. This is very important for compatibility. However, listing databases and table using Postgres syntax is not supported.
Some of those commands are actually aliases for different syntaxes. Using the official GUI, going back in the history you can see the actual command that was executed, instead of the alias you typed. This is useful for learning faster, when you come from a different DBMS.
Syntax error messages are also very clear and there is a good help online, similar to MySQL HELP command.
Speaking about the GUI: you are not forced to use SSL by default, but then you have to start the GUI with the –insecure option. This is not an irrelevant detail, it is rather a signal of a sane approach to security.
There are also usability flaws. Query output is tabular, and I didn’t find a way to make it vertical (\G in MySQL). At the end of lines, an NL character is shown. I didn’t find a way to eliminate it, and this is annoying for copy/paste.
Transactions and errors
In CockroachDB, any error (including syntax errors) make transactions fail. This is much more consistent and safer than MySQL behaviour, with some errors causing an implicit ROLLBACK and others being ignored.
If a transaction is failed, Cockroach wants to be sure that you are aware of this. Any command you send will be ignored until you issue COMMIT TRANSACTION or ROLLBACK TRANSACTION.
When we start a transaction, the GUI enters in multiline mode. But we can always send the SQL we typed until now by typing an empty line. This is useful to check if there is an error.
FALSE IS NOT NULL 