I’ve spent some time looking at distributed relational databases. These systems combine features from relational databases, like ACID transactions and SQL, with features from NoSQL systems, like scaling out on multiple servers. One system in particular is CockroachDB. Cockroach is Postgres compatible.
You can download an executable to run locally. There’s also a cloud version, as well as an enterprise version.
Once you download the EXE, you can run this code in Powershell (or the command line) to start a temporary session(I’m running Windows and have the EXE at C:\CockroachDB):
C:\CockroachDB\cockroach demo --no-example-database
I’ve added a command to not load the example databases, although those would come in handy if you don’t want to bother with creating your own tables.
Once a session is started, you can enter \? to see available commands, as well as links to more information.
You can run SQL in the command line (using the cockroach sql command), or run a SQL script.
Since DBeaver Community edition can connect to CockroachDB, I’ll use that, to take advantage of the query tool.
You can connect to localhost, the default port = 26257, and the system database. The user name and password will be generated at start-up and displayed in the Powershell window.
Once we connect to system, we can run a CREATE DATABASE command to make a test database. Then we can create tables, insert and select date, anything we would normally do, using Postgres SQL syntax.
Alternatively, you can start a single node as well:
C:\CockroachDB\cockroach start-single-node --insecure --listen-addr=localhost
Obviously, the real power with CockroachDB will be with multiple nodes, with the power to partition and replicate data across the nodes.
An interesting feature in the Enterprise edition is Geo-partioning. This lets you designate what data should be stored on what node, down to the row level. If you have multiple nodes spread across a wide area, you can keep a user’s data in the node closest to them, to reduce the latency of retrieving that data.