Introduction to QuestDB

1. Overview

In this tutorial, we’ll briefly introduce QuestDB, a relational column-oriented database for time series and event data. Then, we’ll learn how to send data from a Java application to the database.

2. What Is QuestDB?

QuestDB is a fast open-source time-series database with high-performance ingestion and SQL analytics. In short, QuestDB tackles difficulties when managing high throughput of time-based data and makes it easy to analyze the ingested data through simple SQL queries.

It is compatible with the InfluxDB Line Protocol, PostgreSQL Wire Protocol, and HTTP REST API. Additionally, it has a beautiful Web Console application that we can use to interact with the database directly. However, we’ll only focus on the InfluxDB Line Protocol, a text format that includes measurement, tags, fields, and a timestamp for each data point. In this case, we’ll use an easy-to-use java client library to send data and abstract away most of these details.

Some advantages of QuestDB are:

• It is easy to use and provides high performance
• It uses a relational model for time series data
• Ingestion of data is very scalable
• Provides immediate consistency

Next, we’ll create a local database instance using Docker containers. Alternatively, we can pay for the fully managed instance in the cloud that doesn’t require maintenance.

3. Installation

Let’s start using a containerized version installed using docker:

$ docker run -p 9000:9000 \
  -p 9009:9009 \
  -p 8812:8812 \
  -p 9003:9003 \
  -v "$(pwd):/var/lib/questdb" \
  -d --name questdb questdb/questdb:7.0.0

This will create a local instance of QuestDB on our machine. To check if it is up, we can access the web application exposed at port 9000:

User Interface of the QuestDB web application

Alternatively, QuestDB also exposes an endpoint at port 9003 we can use to check the database’s health.

4. Using QuestDB in Java Projects

4.1. Connecting to the Database

To begin with, we have to talk with the database from our application. For this purpose, QuestDB offers several methods of connection exposed on the following ports:

• InfluxDB Line Protocol: port 9009
• PostgreSQL Wire Protocol: port 8812
• HTTP REST API: port 9000
• Web Console: port 9000

For instance, we’ll only use the InfluxDB Line Protocol, which is the recommended method, and use the Java client to insert data:

<dependency>
    <groupId>org.questdb</groupId>
    <artifactId>questdb</artifactId>
    <version>7.0.0</version>
</dependency>

Then, we’ll send some data to the database.

4.2. Send Data

In short, we want to create a temperature tracking system that sends data to our database to the sensors table. Therefore, we need to create a Sender Object providing the correct address of the database to the builder:

Sender sender = Sender.builder().address("localhost:9009").build();

The Sender is closeable, so we use it inside a try-with construct.

Then, we can refer to the sensors table and add the data simply by providing the values for each column:

sender.table(SENSORS_TABLE_NAME)
    .stringColumn("id", "KTC")
    .stringColumn("name", "Kitchen temperature (Celsius)")
    .doubleColumn("currentValue", 20)
    .atNow();

In particular, we didn’t need to create the table beforehand, as the library will do that based on the data we send to the database.

4.3. Query Data

Once we have some data, we can use the SELECT statements on the web application to query data. For example, we can view the readings of our kitchen sensor using the following:

SELECT * from 'sensors' WHERE id = 'KTC'

We can see this inside the web application:

4.4. Deleting Rows

QuestDB doesn’t allow deleting single rows, but it is possible to work around this by creating a copy of the table without the required rows, dropping the table, then renaming the copy to the original table name.

Granted that it is possible to use this workaround, we suggest avoiding it if possible, as it is costly.

5. Conclusions

In this article, we learned how to install and connect to QuestDB, execute queries, and interact with it from our application.