Schedule Meeting

a

First impressions about immudb, the immutable database

by | May 31, 2021 | Other Databases

Need Help?  Click Here for Expert Support

I keep an eye on new database technologies, and I kept an eye on immudb for some time. So I was excited when I’ve read the immudb 1.0.0 release announcement! And I decided to make some experiments with it, and make my impressions public.

I would like to mention that, while experimenting with immudb, I created a Vagrant machine and made it public. Here’s the Vagrant box. If you want a custom machine, check the Vagrantfile. Yes, an official Docker image is available, but containers are not great when you want to do complete experiments, including starting, stopping, restarting a demon.

NOTE: This article was written after 1.0.0 was released, in May 2021. Some of the information written here quickly went outdated. We’ll keep the article as-is for historical purpose and because it’s still good to give you an idea of what immudb is. For up-to-date info, see immudb documentation.

immudb is an immutable database: we could not remove any data!
immudb is an immutable database: Blix can’t remove any data!

The immutable database

But first of all, what is immudb? It is both a key/value store and an transactional SQL database. It is open source, released unde the Apache License 2.0. It’s developed by CodeNotary, and its part of a range of immutable products.

Immu stands for immutable: it’s a database that allows us to add or replace data, but will never delete old data from disk. The SQL interface allows us to read data from the past. So deleting a row won’t prevent us from querying the database in the future, and see that the rows existed in the past.

State of the art

immudb is a young project. It is not difficult to find bugs or aspects that should be improved. But it is usable, stable and fast. The bugs I’ve found are about usability. I’ve reported them and I’ve seen an incredibly fast reaction, which makes me feel comfortable. It’s fine to find problems, as long as the vendor takes them seriously.

The documentation is not exhaustive, but I believe they are working on it.

An overview for DBAs and Devops

immudb runs on the most common operating systems, plus FreeBSD.

immudb server is distributed as a single executable called immudb that comprises:

  • The database server;
  • Commands to install as a service and operate that service;
  • A Prometheus exporter;
  • A web console.

The client is distributed as a separate single executable, immuclient. It can be invoked to run a single command and get its output, or it can be used in interactive mode.

Another client, immuadmin, is used to perform administrative operations, like creating databases or taking backups.

Both the server and the client are written in Go.

The server

The server reads a TOML configuration file. From my tests you can’t just change the values, restart immudb and expect the changes to take effect. I hope this will change. A related problem is that I wasn’t able to write a configuration file and use it when installing immudb as a service.

Important aspects we can configure include:

  • sync mode (it’s off by default, but I believe that users that require immutability will prefer to enable it);
  • The listening address;
  • Ports;
  • Paths;
  • Certificate authority.

To install immudb as a service and operate the service:

immudb service install
immudb service stop
immudb service start

Managing a server in this way it system-agnostic.

On the first start, immudb will take care of creating the necessary directories with proper permissions.

immuclient

As mentioned above, immuclient allows to run commands in interactive or non-interactive mode.

immuclient has several commands. If we only interact with immudb in SQL, we’ll only need: query, to run queries that return a resultset, and exec, for all other SQL statements.

Currently, immuclient only returns output in a human-readable form. This is not great for automation, but it’s not difficult to run queries in a programming language (like Go or Python) using immudb connectors.

The error messages come from Go, and are sometimes confusing. For example, if you try to create a table without a primary key, you’ll get this:

ERROR: rpc error: code = Unknown desc = syntax error: unexpected ')', expecting ','

Hopefully this will improve in the future.

Backups

immuadmin has some commands related to backups:

  • dump;
  • backup;
  • restore.

Currently dump is just a placeholder. The dump functionality is not yet implemented.

backup stops immudb to take a cold backup, and then restarts it. There are no hot backups yet. They should be at some point, see bug #742.

Unfortunately, currently taking a backup requires user interaction (#818).

Indexes

immudb tables must have a primary key. Primary keys can only be built on a single column. The unicity constraint is enforced. Autoincremental primary keys are not supported.

We can optionally build secondary indexes. All indexes are built on a single column. Keep in mind that currently indexes cannot be dropped.

This is probably normal for an immutable database, but at the same time it can be a problem for a dataset that can only grow. That’s why I suggested them to support some sort of future-drop operation.

An overview for Developers and Data Architects

Some words about what developers can do with immudb.

Key/value

I didn’t test this area extensively. I can say that immudb has most of the key/value commands one would expect.

Some commands have a “regular” version and a “safe” version, which is slower because it verifies the validity of the Merkle tree. I’d suggest to use safeset for writes and get for reads.

Thanks to data immutability, we can also see the whole history of a key with the history command.

immuclient> set organisation_name vettabase
tx:		1 
key:		organisation_name 
value:		vettabase 
hash:		7d8905b7d795c8d9ffb8a60606375d99cf6fb657d5b7fcfcc12c2dd121b89e4c 
 
immuclient> set organisation_name Vettabase
INFO: Redunant argument(s) | [Ltd] 
tx:		2 
key:		organisation_name 
value:		Vettabase 
hash:		f49a4536d8a2d11fbc13182034c8ba0e984936c67a7e824b74c97b30bc2454ab 
 
immuclient> get organisation_name
tx:		2 
key:		organisation_name 
value:		Vettabase 
hash:		f49a4536d8a2d11fbc13182034c8ba0e984936c67a7e824b74c97b30bc2454ab 
 
immuclient> history organisation_name
tx:		1 
key:		organisation_name 
value:		vettabase 
hash:		7d8905b7d795c8d9ffb8a60606375d99cf6fb657d5b7fcfcc12c2dd121b89e4c 

tx:		2 
key:		organisation_name 
value:		Vettabase 
hash:		f49a4536d8a2d11fbc13182034c8ba0e984936c67a7e824b74c97b30bc2454ab 

SQL

Again: immudb is an immutable DBMS, so you should only expect a limited subset of SQL:

  • CREATE TABLE;
  • CREATE INDEX;
  • SELECT and INNER JOIN;
  • INSERT;
  • UPSERT.

Don’t expect any ALTER or DROP statement.

Since the history of rows is preserved (in case you run an UPSERT), it’s possible that in the future UPDATE and DELETE statements may be implemented in the future. DBMSs that support temporal tables (which are something similar to immutable tables) allow any write operation on them.

If immutability is required, a limited set of commands is a necessity, not a limitation.

You can of course implement logical deletions: a column containing a timestamp in a very far future or the timestamp when a row was deleted. And optionally a boolean column that indicates if the row was deleted. You may also have another table with further information, like who performed the deletion and the reason. Sometimes applications benefit from this design pattern, and with immudb it will allow to “delete” data with UPSERT.

All basic operators are supported. Some SQL specific operators are missing. I’ve found the following missing operators:

  • IS [NOT] NULL;
  • <> (use != instead);
  • [NOT] IN (...);
  • [NOT] BETWEEN [SYMMETRIC] ... AND ...;
  • NOT LIKE;
  • No expressions in INSERT or UPSERT.

Basically you can do anything except working with NULL properly, but existing applications may use unsupported syntaxes.

immudb also supports special Time Travel syntax, to query the history of a table. Currently this is based on the transaction id, but they also plan to implement time travel by date (#788).

Transactions

SQL transactions are supported. They can involve multiple tables.

However, transactions can only be sent as a single statement, in this way:

BEGIN TRANSACTION INSERT ...; UPSERT; COMMIT

(Note the lac of ; after BEGIN TRANSACTION)

Because of this, applications need to compose the whole SQL statement before starting the transaction.

Conclusions and what’s next

These initial experiments were useful to understand what immudb can do and how it works. I’m excited about this new DBMS!

Of course there are many limitations, too. Some of them may be inherent to the idea of immutability. Others may be lifted in the future, possibly soon. So if you are interested in immudb, I suggest you keep an eye on it, as I’m doing.

Because of immutability limitations immudb can hardly be your primary DBMS. But if your organisation has certain requirements, you need an immutable database. So you could persist your data (all of it or just the most sensible part) into immudb, and use other DBMSs to run operations that are not supported by immudb, or are not fast enough in immudb, or would require too much disk space with immudb. Nowadays, integration between different technologies is vital.

I may do further tests, particularly in the areas of performance (both throughput and latency), and required space on disk.

Vettabase could add immudb the the range of technologies it supports, if there is enough interest about it. So please let us know your opinion: are you interested in immudb and in immutable data in general? Would you like us to help you automating immudb administration and integrating it with other technologies?

Related contents

Articles

If you need help deploying Immudb or integrating it with other databases, consider our Database Automation service.

Federico Razzoli

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Services

Need Help?  Click Here for Expert Support

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *