Introduction

Harnessing the power of a database server for data analytics into a single shared library is the first step on the road to benefit from the servers’ performance features. MonetDB/e is such a core library of MonetDB. We started out with a small C-API a few years back to build our first embedded applications using it.

However, the MonetDB eco system also provides APIs to Python, Java, Ruby, R, PhP… They all rely on the JDBC/ODBC layer for interaction with the server. A library that simply launches a database server in the background and then connect to it using the JDBC/ODBC layer is evidently not the way to go. It would create too much interprocess communication and potentially a lot of data shipping. A truly embedded database system dares to live in the same address space as the application. Of course, this comes with shared responsibilities.

Therefore, our next target was to make the MonetDB functionality available as a simple drop-in library in Python, called monetdbe. Python has a clearly defined database interface, called DB-API 2.0, and an interface aligned with SQLite. Furthermore, with a claimed installed base of SQLite of more than one billion there is a lot of code out there and users comfortably with its programmatic interface. The goal for MonetDB/e was set to follow the Python/SQLite3 interface as much as possible.

We followed a test-driven approach by starting with the test suite for Python/SQLite 3 and working our way through all the unit tests covered. The complete list is included in the …/tests directory. Several tests where skipped, because they are too tightly coupled with the SQLite semantics, or approach to database management. We keep a list of open issues and the planning for the next milestones.

For example, the row factory functions are not supported. They coped with the limited set of data types supported by SQLite, which triggers an (expensive) call back method when accessing the elements in a result set. MonetDB/e has a much richer basic type system that includes e.g. the temporals, UUIDS, JSON ond DECIMAL out of the box. Likewise, transaction management in MonetDB/e is based on MVCC without explicit levels of isolations. The latter a remnant of the past when systems were based on explicit locking.

How does it work

Using the library starts with simply getting the module into your Python environment, ‘pip install monetdbe’. A minimal example to see if everything works as expected:

import monetdbe
conn = monetdbe.connect(':memory:')
c = conn.cursor()
c.execute('SELECT count(*) FROM tables')
print(c.fetchone())
A collection of example programs written in C and Python

can be found in the monetdbe-examples repository.

What are the perks

Capitalizing the MonetDB server code base makes amongst others the following features available for an embedded version:

  • No client-server communication overhead.

  • No result-set serialiszation, but binary column-ary access.

  • Full use of the multi-core parallel query execution.

  • Hassle free data auto-indexing.

  • Dictionary compressed string handling.

  • Seamless integration with the programming language, e,g, dataframes/numpy.

  • Single user control over the resources.

  • Working with :memory: databases with controlled RAM footprint.

  • Hybrid set up with concurrent :memory:, local storage and server-based storage.

  • Boosting your data analytics programs with stateful User Defined Functions.

  • Extended base types, DECIMAL, JSON, blob, uuid, boolean

  • Statistics, windowing functions, grouped sets, cube, and rollup.

  • Persistent stored modules of SQL procedures/functions

  • SQL-standard compliance and many Postgresql extensions.

  • Streaming input/output handling of compressed files.

  • Hot snapshots for backup/recovery.

Storage options

One of the key factors in an embedded database system is the location of the persistent data, if it needs to persist at all. In MonetDB/e we opted for a simple interface based on URLs, :memory:, /OSpath/directory, file://filename?withoptions, monetdb://mapi_host:port/db?withoptions. The benefit of this approach is that one can start developing the application with an in memory only, ‘:memory:’, storage and switch to the local directory or server version by simply changing the connection point. The pre-release implementation of MonetDB/e is limited to a single ‘:memory:’ or a local directory.

And how about debugging and stability

Using an embedded database library also comes with mutual responsibility. Unlike a server solution, your program could easily lead to a corrupted database with peek/pook in the underlying database structures (using C-functions). Such a corruption may be not immediately be detected. This can be countered by taking regular database backups.

For debugging you can rely on the logging scheme available in every MonetDB/e instance or the Python logging module. Alternatively, the Python debugger gives information up to the point the code switches to the underlying C function. Consider this a natural barrier not to cross, because the database kernel code is highly complex.

For stability we deploy SQLsmith and SQLancer on a daily basis to isolate corner cases that might havoc the system. As an aside, we use a Continuous Integration framework based on buildbot for the stability and regression testing on two dozen platforms.

What are the caveats

There are a few caveats to the approach presented. SQLite and MonetDB/e do not align 100% on the same interpretation of the SQL standard. MonetDB/e is much more strict. This may result in minor differences in error handling or even some surprises in the results. An overview is provided in the `Application Migration`_ section.

REMINDER, the Python package monetdbe is an pre-release made available from PyPi. You don’t need to install MonetDB itself. Several rough edges and features deemed urgent are being dealt with before MonetDB/e becomes an official release, but users are more than welcome to try it out. Issues and feature requests can be left behind in the issue tracker on GitHub.

When to use an embedded database

In addition to the list above, use an embedded system if you want a quick starter on using a SQL-based system. In particular if you are working with large tables calling for a pre-filterings, aggregation, and transformation of a relational table before you hand it over to e.g. a machine learning model.

However, a database system that shares the memory space with the application code may cause unexpected interference. For example, it is relatively easy to overwrite its internal structures and thereby leaving a corrupted database behind. Or, there may be resource wars between application logic and the database kernel as it fights over RAM and CPU cores.

MonetDB/e comes with shared responsibility. It works best if you need a database for analytics where you can either easily reload the database from an archive or use the snapshot/backup methods. It is also a great tool to embark on application development without the need for a shared server in the background.

Some people also translate ease of use into providing a single file as the storage container for the database to simplify sharing a database. Even SQLite relieved this requirement by packaging databases into a single archive file. MonetDB/e works with a local/remote directory. There are many tools to package and transfer them to enable sharing the database with your peers.

MonetDB/e roadmap

  • MonetDB/e as proxy to a server

  • Java jar drop=in

  • Embedded version for R

  • Import of (mini)parquet and Arrow files

  • Remote query processing over multiple :memory: instances

  • Using MonetDB/e as a JDBC/ODBC endpoint