Skip to content

QuestDB for time series data hands-on

Preamble

QuestDB is the next one in my journey of storing and handling Time Series data. This candidate is coming from Time Series DBMS ranking of DB-Engines.com:

questdb01
questdb01

I have already tested TimescaleDB and MongoDB (not listed but their Time Series feature is interesting to look at), Prometheus and Graphite are not what I would call RDBMS and they are more an interface to create charts (with Grafana for example).

Kdb being a commercial offer I prefer to set aside for now. Apache Druid is most probably my next trial…

Worth mentionning that QuestDB developer experience is quite rich with Python, Java, Node.js, C, C++, Rust, Go and .NET…

My test server is running Red Hat Enterprise Linux release 8.10 (Ootpa) has 12 cores (24 threads) and 64GB of memory. At the time of writing this post QuestDB is availabel with releease 8.2.3.

QuestDB installation

Installation is really simple, I have created a dedicated questdb account (in my Linux dba group) and a dedicated mount point:

[questdb@server01 ~]$ df -h /questdb/
Filesystem                Size  Used Avail Use% Mounted on
/dev/vx/dsk/vgperp/lvol9  5.0G   20M  4.7G   1% /questdb

Downloaded questdb-8.2.3-rt-linux-x86-64.tar.gz and unzip it in /questdb, add /questdb/questdb-8.2.3-rt-linux-x86-64/bin path to your PATH. I have also created those aliases to ease stop/start:

alias start_questdb='questdb.sh start -d /questdb'
alias status_questdb='questdb.sh status'
alias stop_questdb='questdb.sh stop'

Then start QuestDB with start_questdb alias and that’s it ! Never seen an installation that simple. You can now access to the graphical interface with http://server01.domain.com:9000 (or non routable IP address in my case):

questdb02
questdb02

You can also connect through the Postgres Wire Protocol (PGWire) with a PostgreSQL client on port 8812 (default admin account has quest as a password):

[questdb@server01 ~]$ psql --version
psql (PostgreSQL) 17.4
[questdb@server01 ~]$ export PGPASSWORD=quest
[questdb@server01 ~]$ psql -h server01.domain.com -p 8812 -U admin
psql (17.4, server 11.3)
Type "help" for help.

admin=> show tables;
 table_name
------------
(0 rows)

Data ingestion into QuestDB

To be able to compare what I have done with TimescaleDB and MongoDB I will obviously load the NYC Yeallow Cab 2023 trips information. I have file in CSV and Parquet formats.

COPY command

To use copy command you need to configure cairo.sql.copy.root in /questdb/conf/server.conf to point to the directory where your CSVs are:

cairo.sql.copy.root=/u01/nyc

Stop and restart QuestDB to validate the change…

I have started like it is suggested, means by not creating any table to see how it goes with PostgreSQL client:

admin=> copy trips from 'yellow_tripdata_2023-01.csv' with header true;
        id
------------------
 49f8d7ca8f29d6ea
(1 row)

admin=> select * from sys.text_import_log where id = '49f8d7ca8f29d6ea' order by ts desc;
             ts             |        id        | table_name |            file             | phase |  status  | message | rows_handled | rows_imported | errors
----------------------------+------------------+------------+-----------------------------+-------+----------+---------+--------------+---------------+--------
 2025-04-10 13:08:47.122608 | 49f8d7ca8f29d6ea | trips      | yellow_tripdata_2023-01.csv |       | finished |         |      3066766 |       3066766 |      0
 2025-04-10 13:08:44.071921 | 49f8d7ca8f29d6ea | trips      | yellow_tripdata_2023-01.csv |       | started  |         |              |               |      0
(2 rows)

admin=> show columns from trips;
        column         |  type  | indexed | indexBlockCapacity | symbolCached | symbolCapacity | designated | upsertKey
-----------------------+--------+---------+--------------------+--------------+----------------+------------+-----------
 tpep_pickup_datetime  | DATE   | f       |                256 | f            |              0 | f          | f
 tpep_dropoff_datetime | DATE   | f       |                256 | f            |              0 | f          | f
 passenger_count       | DOUBLE | f       |                256 | f            |              0 | f          | f
 trip_distance         | DOUBLE | f       |                256 | f            |              0 | f          | f
 tip_amount            | DOUBLE | f       |                256 | f            |              0 | f          | f
 total_amount          | DOUBLE | f       |                256 | f            |              0 | f          | f
(6 rows)

admin=>

Remark:
The sys.text_import_log table/view does not work on the graphical query interface, you have to be in the PGWrire interface !

One problem we see is the DATA datatype instead of the TIMESTAMP one, and a Time Serie QuestDB table must have a TIMESTAMP column. In other words I would be able to load all the CSV in a table but I would have, then, to move all the rows to my final table: not very efficient ! The final create table statement would be something like:

drop table if exists trips;

create table if not exists trips
   (
   tpep_pickup_datetime timestamp,
   tpep_dropoff_datetime timestamp,
   passenger_count float,
   trip_distance float not null,
   tip_amount float,
   total_amount float
) timestamp(tpep_pickup_datetime);

Launch the COPY command through Postgres Wire Protocol (PGWire):

admin=> copy trips from 'yellow_tripdata_2023-01.csv' with header true TIMESTAMP 'tpep_pickup_datetime' FORMAT 'yyyy-MM-dd HH:mm:ss';
        id
------------------
 74294279a5c875ad
(1 row)

admin=> select * from sys.text_import_log where id = '74294279a5c875ad' order by ts desc;
             ts             |        id        | table_name |            file             | phase |  status  | message | rows_handled | rows_imported | errors
----------------------------+------------------+------------+-----------------------------+-------+----------+---------+--------------+---------------+---------
 2025-04-10 10:55:22.311386 | 74294279a5c875ad | trips      | yellow_tripdata_2023-01.csv |       | started  |         |              |               |       0
 2025-04-10 10:55:35.595800 | 74294279a5c875ad | trips      | yellow_tripdata_2023-01.csv |       | finished |         |      3066766 |          1084 | 3065682
(2 rows)

admin=> select count() from trips;
 count
-------
  1084
(1 row)

I’m missing quite a few rows… Even in using a COPY command like the one above, I still got plenty of import error…

I have also tried with the Uploading Tabular Data HTTP Rest API with:

curl -F schema='[ \
    {"name":"tpep_pickup_datetime", "type": "TIMESTAMP", "pattern": "yyyy-MM-dd HH:mm:ss"}, \
    {"name":"tpep_dropoff_datetime", "type": "TIMESTAMP", "pattern": "yyyy-MM-dd HH:mm:ss"}, \
    {"name":"passenger_count", "type": "float"} \
    {"name":"trip_distance", "type": "float"} \
    {"name":"tip_amount", "type": "float"} \
    {"name":"total_amount", "type": "float"} \
  ]' \
  -F data=@/u01/nyc/yellow_tripdata_2023-01.csv 'http://server01.domain.com:9000/imp?name=trips'

And creating text_loader.json file in conf directory:

[questdb@server01 ~]$ cat /questdb/conf/text_loader.json
{
  "date": [
    {
      "format": "dd/MM/y"
    },
    {
      "format": "yyyy-MM-dd HH:mm:ss"
    },
    {
      "format": "yyyy-MM-ddTHH:mm:ss.SSSz",
      "locale": "en-US",
      "utf8": false
    },
    {
      "format": "MM/dd/y"
    }
  ],
  "timestamp": [
    {
      "format": "yyyy-MM-ddTHH:mm:ss.SSSUUUz",
      "utf8": false
    },
    {
      "format": "yyyy-MM-dd HH:mm:ss",
      "utf8": false
    }
  ]
}

But got:

curl: (55) Send failure: Broken pipe
Must be an object[

Adding that COPY command cannot insert data in a non empty table I have decided to give up and do it in Python using Parquet initial files like it is suggested in QuestDB blog…

Python

With more flexibility I plan to create the table directly with

drop table if exists trips;

create table if not exists trips (
  started_at timestamp not null,
  ended_at timestamp not null,
  passenger_count float,
  distance float not null,
  tip_amount float,
  total_amount float
) timestamp(started_at);

I have this requirements.txt file:

questdb
pandas
pyarrow
psycopg[binary]

And I use marvelous uv tool on my Linux server (on my desktop I use Minconda):

[questdb@server01 ~]$ export HTTPS_PROXY='http://proxy_account:proxy_password@proxy_server:proxy_port'
[questdb@server01 ~]$ pip3.9 install uv
[questdb@server01 ~]$ uv venv --python 3.13 questdb
Using CPython 3.13.2
Creating virtual environment at: questdb
Activate with: source questdb/bin/activate
[questdb@server01 ~]$ source questdb/bin/activate
(questdb) [questdb@server01 ~]$ python -V
Python 3.13.2
(questdb) [questdb@server01 ~]$ uv pip install -r requirements.txt
Using Python 3.13.2 environment at: questdb
Resolved 10 packages in 1.29s
Prepared 10 packages in 8.31s
Installed 10 packages in 107ms
 + numpy==2.2.4
 + pandas==2.2.3
 + psycopg==3.2.6
 + psycopg-binary==3.2.6
 + pyarrow==19.0.1
 + python-dateutil==2.9.0.post0
 + pytz==2025.2
 + questdb==2.0.4
 + six==1.17.0
 + tzdata==2025.2
(questdb) [questdb@server01 ~]$ uv pip list
Using Python 3.13.2 environment at: questdb
Package         Version
--------------- -----------
numpy           2.2.4
pandas          2.2.3
psycopg         3.2.6
psycopg-binary  3.2.6
pyarrow         19.0.1
python-dateutil 2.9.0.post0
pytz            2025.2
questdb         2.0.4
six             1.17.0
tzdata          2025.2

One of he first error I got is:

questdb.ingress.IngressError: Unsupported dtype datetime64[us] for column 'started_at'. Raise an issue if you think it should be supported: https://github.com/questdb/py-questdb-client/issues.

And effectively my dataframe was like:

>>> df.dtypes
started_at         datetime64[us]
ended_at           datetime64[us]
passenger_count           float64
distance                  float64
tip_amount                float64
total_amount              float64
dtype: object

When trying to convert to columns datatype I had the annoying SettingWithCopyWarning warning (that will be an error in future Pandas version if I understood well) that appear randomly doing things like:

>>> df['started_at'] = df['started_at'].astype('datetime64[ns]')
python-input-14:1: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['tpep_pickup_datetime'] = pd.to_datetime(df1['tpep_pickup_datetime']).astype('datetime64[ns]')

Or

>>> df['ended_at'] = pd.to_datetime(df['ended_at']).astype('datetime64[ns]')
python-input-17:1: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['tpep_dropoff_datetime'] = pd.to_datetime(df1['tpep_dropoff_datetime']).astype('datetime64[ns]')

Working with loc funtion and doing things like below did not change the datatypes (bug ?) !!:

df.loc[:, 'started_at'] = df['started_at'].astype('datetime64[ns]')

When writing the dataframe I also had randomly:

questdb.ingress.IngressError: Could not flush buffer: failed to parse line protocol:errors encountered on line(s):
error in line 1: table: trips; cannot insert in non-WAL table [id: c2bf92c874e6-6, code: invalid, line: 1] - See https://py-questdb-client.readthedocs.io/en/v2.0.4/troubleshooting.html#inspecting-and-debugging-errors#flush-failed

Or

[questdb@server01 log]$ head questdb-rolling.log.20250409
2025-04-09T08:37:34.678891Z E i.q.c.l.t.LineTcpMeasurementEvent could not write line protocol measurement [tableName=trips, message=[-1] cannot insert rows out of order to non-partitioned table. Table=/questdb/db/trips
io.questdb.cairo.CairoException: [-1] cannot insert rows out of order to non-partitioned table. Table=/questdb/db/trips
        at io.questdb.cairo.CairoException.instance(CairoException.java:339)
        at io.questdb.cairo.CairoException.nonCritical(CairoException.java:128)
        at io.questdb.cairo.TableWriter.newRow(TableWriter.java:2322)
        at io.questdb.cutlass.line.tcp.LineTcpMeasurementEvent.append(LineTcpMeasurementEvent.java:116)
        at io.questdb.cutlass.line.tcp.LineTcpWriterJob.drainQueue(LineTcpWriterJob.java:162)
        at io.questdb.cutlass.line.tcp.LineTcpWriterJob.run(LineTcpWriterJob.java:87)
        at io.questdb.mp.Worker.run(Worker.java:152)
]

So decided to partition my trips table and adding a nice TTL feature to keep not more than 5 years of history. So far the partition strategy is not fully clear to me:

drop table if exists trips;

create table if not exists trips
   (
   started_at timestamp not null,
   ended_at timestamp not null,
   passenger_count float,
   distance float not null,
   tip_amount float,
   total_amount float
) timestamp(started_at)
partition by month
ttl 5 years;

The Pyhton script I have to load full 2023 of NYC yeallow cab data is:

from questdb.ingress import Sender, TimestampNanos
import pandas as pd
import os
import psycopg


# Parquet file path
directory = "/u01/nyc/"
parquet_files = [f for f in os.listdir(directory) if f.endswith('.parquet')]
# parquet_files = glob.glob(os.path.join("data", "*.parquet"))
conf = f'http::addr=server01.domain.com:9000;'

print("Truncating trips table")
with psycopg.connect("dbname=questdb host=server01.domain.com port=8812 user=admin password=quest") as connection:
  with connection.cursor() as cursor:
    cursor.execute("truncate table trips")
    connection.commit()
    connection.close()

for filename in parquet_files:
  print("Loading " + filename)
  df = pd.read_parquet(directory + filename)
  df = df[["tpep_pickup_datetime", "tpep_dropoff_datetime", "passenger_count", "trip_distance", "tip_amount", "total_amount"]]
  df = df.astype({'tpep_pickup_datetime': 'datetime64[ns]', 'tpep_dropoff_datetime': 'datetime64[ns]'})
  df.rename(columns = {'tpep_pickup_datetime': 'started_at', 'tpep_dropoff_datetime': 'ended_at', 'trip_distance': 'distance'}, inplace=True)
  with Sender.from_conf(conf) as sender:
    try:
      sender.dataframe(df, table_name="trips", at='started_at')
    except Exception as e:
      print(f"Error loading {filename}: {e}")
  print("Loaded " + filename)

It has taken 4 minutes and 47 seconds to load one full year !

QuestDB queries

As above the best is to use Postgres Wire Protocol (PGWire) as the graphical interface is a bit buggy IMHO:

admin=> tables();
 id | table_name | designatedTimestamp | partitionBy | maxUncommittedRows | o3MaxLag  | walEnabled | directoryName | dedup | ttlValue | ttlUnit | matView
----+------------+---------------------+-------------+--------------------+-----------+------------+---------------+-------+----------+---------+---------
 23 | trips      | started_at          | MONTH       |             500000 | 600000000 | t          | trips~23      | f     |        5 | YEAR    | f
(1 row)

admin=> table_columns('trips');
     column      |   type    | indexed | indexBlockCapacity | symbolCached | symbolCapacity | designated | upsertKey
-----------------+-----------+---------+--------------------+--------------+----------------+------------+-----------
 started_at      | TIMESTAMP | f       |                  0 | f            |              0 | t          | f
 ended_at        | TIMESTAMP | f       |                  0 | f            |              0 | f          | f
 passenger_count | FLOAT     | f       |                  0 | f            |              0 | f          | f
 distance        | FLOAT     | f       |                  0 | f            |              0 | f          | f
 tip_amount      | FLOAT     | f       |                  0 | f            |              0 | f          | f
 total_amount    | FLOAT     | f       |                  0 | f            |              0 | f          | f
(6 rows)

aadmin=> table_partitions('trips');
index | partitionBy |  name   |        minTimestamp        |        maxTimestamp        | numRows | diskSize  | diskSizeHuman | readOnly | active | attached | detached | attachable | isParquet | parquetFileSize
-------+-------------+---------+----------------------------+----------------------------+---------+-----------+---------------+----------+--------+----------+----------+------------+-----------+-----------------
    0 | MONTH       | 2022-10 | 2022-10-24 17:37:47.000000 | 2022-10-25 13:03:37.000000 |      11 |       352 | 352.0 B       | f        | f      | t        | f        | f          | f         |              -1
    1 | MONTH       | 2022-12 | 2022-12-31 14:24:54.000000 | 2022-12-31 23:59:48.000000 |      25 |       800 | 800.0 B       | f        | f      | t        | f        | f          | f         |              -1
    2 | MONTH       | 2023-01 | 2023-01-01 00:00:00.000000 | 2023-01-31 23:59:59.000000 | 3066726 |  98140160 | 93.6 MiB      | f        | f      | t        | f        | f          | f         |              -1
    3 | MONTH       | 2023-02 | 2023-02-01 00:00:00.000000 | 2023-02-28 23:59:59.000000 | 2914003 |  93257728 | 88.9 MiB      | f        | f      | t        | f        | f          | f         |              -1
    4 | MONTH       | 2023-03 | 2023-03-01 00:00:00.000000 | 2023-03-31 23:59:59.000000 | 3403660 | 108920832 | 103.9 MiB     | f        | f      | t        | f        | f          | f         |              -1
    5 | MONTH       | 2023-04 | 2023-04-01 00:00:00.000000 | 2023-04-30 23:59:57.000000 | 3288248 | 105223936 | 100.3 MiB     | f        | f      | t        | f        | f          | f         |              -1
    6 | MONTH       | 2023-05 | 2023-05-01 00:00:06.000000 | 2023-05-31 23:59:56.000000 | 3513664 | 112437248 | 107.2 MiB     | f        | f      | t        | f        | f          | f         |              -1
    7 | MONTH       | 2023-06 | 2023-06-01 00:00:00.000000 | 2023-06-30 23:59:59.000000 | 3307259 | 105832288 | 100.9 MiB     | f        | f      | t        | f        | f          | f         |              -1
    8 | MONTH       | 2023-07 | 2023-07-01 00:00:01.000000 | 2023-07-31 23:59:55.000000 | 2907093 |  93026976 | 88.7 MiB      | f        | f      | t        | f        | f          | f         |              -1
    9 | MONTH       | 2023-08 | 2023-08-01 00:00:00.000000 | 2023-08-31 23:59:59.000000 | 2824201 |  90374432 | 86.2 MiB      | f        | f      | t        | f        | f          | f         |              -1
   10 | MONTH       | 2023-09 | 2023-09-01 00:00:00.000000 | 2023-09-30 23:59:59.000000 | 2846741 |  91095712 | 86.9 MiB      | f        | f      | t        | f        | f          | f         |              -1
   11 | MONTH       | 2023-10 | 2023-10-01 00:00:00.000000 | 2023-10-31 23:59:58.000000 | 3522269 | 112712608 | 107.5 MiB     | f        | f      | t        | f        | f          | f         |              -1
   12 | MONTH       | 2023-11 | 2023-11-01 00:00:00.000000 | 2023-11-30 23:59:59.000000 | 3339731 | 106881024 | 101.9 MiB     | f        | f      | t        | f        | f          | f         |              -1
   13 | MONTH       | 2023-12 | 2023-12-01 00:00:00.000000 | 2023-12-31 23:59:59.000000 | 3376527 | 108048864 | 103.0 MiB     | f        | f      | t        | f        | f          | f         |              -1
   14 | MONTH       | 2024-01 | 2024-01-01 00:01:34.000000 | 2024-01-03 19:42:57.000000 |       6 | 100663296 | 96.0 MiB      | f        | t      | t        | f        | f          | f         |              -1
(15 rows)

admin=> select count() from trips;
  count
----------
 38310164
(1 row)

admin=> select * from trips limit 10;
         started_at         |          ended_at          | passenger_count | distance | tip_amount | total_amount
----------------------------+----------------------------+-----------------+----------+------------+--------------
 2022-10-24 17:37:47.000000 | 2022-10-24 17:37:51.000000 |           1.000 |    0.000 |      0.000 |      120.300
 2022-10-24 20:01:46.000000 | 2022-10-24 20:01:48.000000 |           1.000 |    0.000 |      9.060 |       54.360
 2022-10-24 21:45:35.000000 | 2022-10-24 21:45:38.000000 |           1.000 |    0.000 |     15.060 |       90.360
 2022-10-24 23:15:32.000000 | 2022-10-24 23:15:42.000000 |           1.000 |    0.000 |     14.450 |       72.250
 2022-10-25 00:42:10.000000 | 2022-10-25 00:44:22.000000 |           1.000 |    0.970 |      0.000 |        6.550
 2022-10-25 00:59:02.000000 | 2022-10-25 01:09:02.000000 |           1.000 |    2.330 |      0.000 |       10.800
 2022-10-25 03:45:46.000000 | 2022-10-25 03:45:50.000000 |           1.000 |    0.020 |      5.000 |      120.300
 2022-10-25 07:48:15.000000 | 2022-10-25 07:48:18.000000 |           2.000 |    0.760 |     15.760 |       94.560
 2022-10-25 09:06:23.000000 | 2022-10-25 09:21:36.000000 |           1.000 |    6.480 |      0.000 |       21.550
 2022-10-25 11:17:19.000000 | 2022-10-25 11:17:21.000000 |           1.000 |    0.000 |     15.160 |       90.960
(10 rows)

After loading:

[questdb@server01 ~]$ df -h /questdb
Filesystem                Size  Used Avail Use% Mounted on
/dev/vx/dsk/vgperp/lvol9  5.0G  3.2G  1.8G  65% /questdb

Finally the QuestDB similar query to get average amount, minimum and maximum amount paid by NYC customers grouped by month only on year 2023 (the original dataset has some overlapping figures in 2022 and 2024):

select /* Yannick */
  started_at,
  avg(total_amount), 
  max(total_amount),
  min(total_amount)
from trips
where started_at >= '2023-01-01' and started_at <= '2023-12-31' and total_amount > 0
sample by 1M;

And the result:

admin=> \timing
Timing is on.

admin=> select /* Yannick */
admin->   started_at,
admin->   avg(total_amount),
admin->   max(total_amount),
admin->   min(total_amount)
admin-> from trips
admin-> where started_at >= '2023-01-01' and started_at <= '2023-12-31'
admin-> and total_amount > 0
admin-> sample by 1M;
         started_at         |        avg         |    max     |  min
----------------------------+--------------------+------------+-------
 2023-01-01 00:00:00.000000 | 27.448150713302457 |   1169.400 | 0.010
 2023-02-01 00:00:00.000000 |  27.34083284339691 |   2208.100 | 0.010
 2023-03-01 00:00:00.000000 | 28.269970540948066 |   2100.000 | 1.000
 2023-04-01 00:00:00.000000 | 28.763144300930357 |   2451.000 | 0.010
 2023-05-01 00:00:00.000000 |  29.46355366657552 |   6304.900 | 0.010
 2023-06-01 00:00:00.000000 | 29.596327423221652 | 386987.616 | 0.010
 2023-07-01 00:00:00.000000 | 29.166175342604756 |   1079.450 | 0.010
 2023-08-01 00:00:00.000000 | 29.253463029673103 |   1435.190 | 0.010
 2023-09-01 00:00:00.000000 | 30.381711802804357 | 187513.904 | 0.010
 2023-10-01 00:00:00.000000 | 29.767678798695812 |   6339.000 | 0.010
 2023-11-01 00:00:00.000000 | 29.306233930997777 |   1814.270 | 0.010
 2023-12-01 00:00:00.000000 | 29.229828334561315 |   4269.160 | 0.010
(12 rows)

Time: 278.285 ms

I have to say that the response time is quite amazing !! Beating with no photo finish TimescaleDB and MongoDB…

Over ten runs I got an average of 281 ms which compare to my previous testing:

questdb03
questdb03

References

About Post Author

Share the knowledge!

Leave a Reply

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