Time series databases: TimescaleDB, QuestDB, ClickHouse, ArcticDB and kdb+.

What is special about market data

A market data table does not look like the table of a business application. It is almost always written at the end (the next minute arrives, yesterday's is not modified), it is read by symbol and date range, it is aggregated into time intervals (5-minute, hourly, daily bars) and it is joined with other series on the closest previous timestamp, not on exact equality (a trade with the quote in force at that instant). Time series databases are built around those four patterns. Lesson 1.4.7 introduced two (TimescaleDB and ArcticDB) in one line each; this one compares them with QuestDB, ClickHouse and kdb+ based on their documentation, and runs ArcticDB, which is the only one that works as a Python library without a server.

The first difference between them is how they store rows. A row-based database (PostgreSQL) stores all the columns of each row together: it is good for inserting and reading individual rows. A columnar database (ClickHouse, QuestDB, kdb+, ArcticDB and the compressed part of TimescaleDB) stores the values of each column together, like Parquet (lesson 6.6.2): it is good for reading few columns of many rows and compresses much better.

flowchart LR
  F[Row-based] --> F1[ts, sim, precio, vol]
  F1 --> F2[ts, sim, precio, vol]
  C[Columnar] --> C1[ts ts ts ts]
  C --> C2[precio precio precio]
  C --> C3[vol vol vol vol]

ArcticDB: libraries, symbols and versions

ArcticDB, from Man Group, stores pandas DataFrames in object storage (S3, Azure) or on local disk with LMDB, without a server: the Python library talks directly to the storage11Note 1 · ArcticDB, introduction and Library and Arctic API (LMDB, LibraryOptions): https://docs.arcticdb.io/latest/ and https://docs.arcticdb.io/latest/api/library/, accessed October 23, 2026.. Its model has four levels:

flowchart TB
  A[Arctic: store] --> L[Library]
  L --> S1[Symbol BAGO]
  L --> S2[Symbol BAKA]
  S1 --> V0[Version 0]
  S1 --> V1[Version 1]
  V1 --> SG[Segments]

A store (Arctic) is opened with a URI (lmdb://folder, s3://…); it contains libraries, which group symbols (each one a DataFrame with a date index); each write creates a new version of the symbol, and each version is stored split into segments of rows and columns. ArcticDB is in the course's zipline environment because, up to its version 6.28.1, it requires pandas 2; its license is the Business Source License 1.1, which according to its repository converts each version to Apache 2.0 two years after its release and does not allow offering it as a database service to third parties22Note 2 · ArcticDB license (Business Source License 1.1): https://github.com/man-group/ArcticDB/blob/master/LICENSE.txt, accessed October 23, 2026.. The lesson uses an LMDB store in a temporary folder:

import tempfile

import arcticdb as adb
import exchange_calendars as xcals
import numpy as np
import pandas as pd
from arcticdb.config import set_log_level

set_log_level("OFF")      # no traces with time and process in the output
ruta = tempfile.mkdtemp(prefix="arctic_", dir=".")
ac = adb.Arctic(f"lmdb://{ruta}")
lib = ac.create_library("barras_1min")
print(adb.__version__, pd.__version__, ac.list_libraries())

6.28.1 2.3.3 ['barras_1min']

set_log_level("OFF") turns off ArcticDB's logging, which writes errors to the output with the time and the process number before raising the exception. In production it is better to leave it on.

The data: synthetic 1-minute bars (close and volume) for 50 stocks from the level 5 panel over the 60 sessions of the first quarter of 2025, with a daily volatility of 2%. So that the lesson tells a realistic story, the “vendor” first sends January and February, with an error: three BAGO minutes on February 14 with the price multiplied by ten. write_batch writes the 50 symbols in one call.

tickers = sorted(pd.read_csv("panel5_activos.csv")["ticker"])[:50]
cal = xcals.get_calendar("XNYS")
minutos = cal.minutes_in_range(
    cal.session_open("2025-01-02"),
    cal.session_close("2025-03-31") - pd.Timedelta("1min"))
rng = np.random.default_rng(664)

def barras(n):
    r = rng.normal(0, 0.02 / np.sqrt(390), n)   # 2% daily
    return pd.DataFrame({
        "cierre": (50 * np.exp(np.cumsum(r))).round(2),
        "volumen": rng.integers(100, 5000, n)}, index=minutos)

datos = {s: barras(len(minutos)) for s in tickers}
corte = pd.Timestamp("2025-03-01", tz="UTC")
# the vendor sends three wrong BAGO minutes on February 14
malos = slice("2025-02-14 15:00Z", "2025-02-14 15:02Z")
v0 = {s: d.loc[:corte].copy() for s, d in datos.items()}
v0["BAGO"].loc[malos, "cierre"] *= 10

r = lib.write_batch([adb.WritePayload(s, d) for s, d in v0.items()])
print(len(r), "symbols; BAGO version:", r[0].version)
print(len(minutos), "minutes in the quarter;",
      len(v0["BAGO"]), "written through February")
print(lib.get_description("BAGO").date_range)

50 symbols; BAGO version: 0
23400 minutes in the quarter; 15210 written through February
(Timestamp('2025-01-02 14:30:00+0000', tz='UTC'), Timestamp('2025-02-28 20:59:00+0000', tz='UTC'))

50 symbols in their version 0. Each symbol has 15,210 minutes (39 sessions × 390) of the quarter's 23,400. get_description reads only the metadata: columns, types, number of rows and the date range, from January 2 at 14:30 UTC to February 28 at 20:59 UTC (the last bar of the winter-time session). Reading accepts a range and some columns, like Parquet filters; ArcticDB only downloads the segments that fall in the range:

tramo = lib.read("BAGO", date_range=(pd.Timestamp("2025-02-14 14:58Z"),
                                     pd.Timestamp("2025-02-14 15:03Z")),
                 columns=["cierre"])
print(tramo.symbol, "version", tramo.version)
tramo.data

BAGO version 0
                           cierre
2025-02-14 14:58:00+00:00   53.29
2025-02-14 14:59:00+00:00   53.25
2025-02-14 15:00:00+00:00  533.20
2025-02-14 15:01:00+00:00  532.70
2025-02-14 15:02:00+00:00  532.00
2025-02-14 15:03:00+00:00   53.17

There is the error: from 53.25 to 533.20 and back to 53.17. A backtest that uses this version will see a 900% jump in BAGO. Before fixing it, a snapshot is saved: a named picture of the version of each symbol in the library, so that what a research study done with this data saw can be reproduced exactly.

Versions, corrections and snapshots

Then the March data arrives (append, which adds at the end) and the vendor's correction (update, which replaces the date range covered by the new data). Each operation is a version, and as_of reads any of them:

lib.snapshot("investigacion-2025-03-01")      # picture of the library
for s, d in datos.items():
    lib.append(s, d.loc[corte:])                 # version 1
lib.update("BAGO", datos["BAGO"].loc[malos])     # version 2
print("versions:",
      sorted(k.version for k in lib.list_versions("BAGO")))

def minuto_15h(**kw):
    d = lib.read("BAGO", date_range=(pd.Timestamp("2025-02-14 15:00Z"),
                                     pd.Timestamp("2025-02-14 15:00Z")),
                 **kw)
    return d.version, len(lib.read("BAGO", **kw).data), \
        float(d.data["cierre"].iloc[0])

pd.DataFrame([minuto_15h(as_of=v) for v in (0, 1, 2)]
             + [minuto_15h(as_of="investigacion-2025-03-01")],
             columns=["versión", "filas", "cierre 15:00"],
             index=["as_of=0", "as_of=1", "as_of=2", "snapshot"])

versions: [0, 1, 2]
          versión  filas  cierre 15:00
as_of=0         0  15210        533.20
as_of=1         1  23400        533.20
as_of=2         2  23400         53.32
snapshot        0  15210        533.20

The figure in the header plots BAGO's history:

• Version 0: January and February (15,210 rows), with the error.
• Version 1: the March append; 23,400 rows and the error is still there.
• Version 2: the update of the three minutes; the rows do not change and the 15:00 close becomes 53.32.
• The snapshot points to version 0: whoever wants to repeat the March 1 research study reads exactly the data it had, error included.

It is the same idea as point-in-time fundamental data (lesson 1.4.4) applied to the store itself: what matters is not only what the data says, but what it said on the day it was used. A backtest that saves the snapshot name (or the version number) alongside its results is reproducible; one that reads “the latest” is not, because the latest changes.

append requires the new data to start after the end of what is there. Appending two minutes that already exist fails:

lib.append("BAGO", datos["BAGO"].loc["2025-03-31 19:58Z":])

arcticdb_ext.exceptions.InternalException: E_ASSERTION_FAILURE Can't append dataframe with start index 2025-03-31 19:58:00.000000000 to existing sequence ending at 2025-03-31 19:59:00.000000001

To rewrite an existing range there is update, and to delete it, delete_data_in_range. The reason for the rule is the segment structure: appending at the end only creates new segments; inserting in the middle would require rewriting the existing ones, which is what update does explicitly.

Versions take up space: each update and each append stores new segments and the previous version keeps its own. prune_previous_versions deletes old versions, except those kept by some snapshot:

lib.prune_previous_versions("BAGO")
vivas = lib.list_versions("BAGO")
print(sorted(k.version for k in vivas))
print(lib.read("BAGO", as_of="investigacion-2025-03-01").version)

[0, 2]
0

Version 2 (the latest) and version 0, protected by the snapshot, remain; version 1 has been deleted. The usual policy: prune intermediate versions regularly and keep, with named snapshots, those used by research studies or models in production.

Queries inside the store: QueryBuilder

Reading an entire symbol to filter afterwards in pandas downloads everything. QueryBuilder describes filters, computed columns, groupings and resamplings that ArcticDB runs in its engine (C++) before returning the result33Note 3 · ArcticDB, “DataFrame Processing Operations API”: https://docs.arcticdb.io/latest/api/processing/, accessed October 23, 2026.:

q = adb.QueryBuilder()
q = q[(q["volumen"] > 4900) & (q["cierre"] > 50)]
grandes = lib.read("BAGO", query_builder=q).data
print(len(grandes), "minutes with volume > 4900 and close > 50")

q = adb.QueryBuilder().resample("1h").agg(
    {"cierre": "last", "volumen": "sum"})
horas = lib.read("BAGO", query_builder=q,
                 date_range=(pd.Timestamp("2025-03-03", tz="UTC"),
                             pd.Timestamp("2025-03-04", tz="UTC"))).data
horas[["cierre", "volumen"]]

244 minutes with volume > 4900 and close > 50
                           cierre  volumen
2025-03-03 14:00:00+00:00   51.93    83768
2025-03-03 15:00:00+00:00   51.25   161355
2025-03-03 16:00:00+00:00   50.97   167040
2025-03-03 17:00:00+00:00   50.01   159946
2025-03-03 18:00:00+00:00   50.46   141475
2025-03-03 19:00:00+00:00   50.45   148767
2025-03-03 20:00:00+00:00   50.97   159973

244 BAGO minutes with volume above 4,900 and a close above 50. The 1-hour resampling of March 3 labels each bar by its start, like pandas: the 14:00 UTC one contains only half an hour (the session opens at 14:30 UTC in winter), and that is why its volume is half that of the others. Resampling has a limit worth knowing: it only supports fixed frequencies.

q = adb.QueryBuilder().resample("ME").agg({"cierre": "last"})
lib.read("BAGO", query_builder=q)

arcticdb.exceptions.ArcticDbNotYetImplemented: Frequency string 'ME' not yet supported. Valid frequency strings are ns, us, ms, s, min, h, D, and multiples/combinations thereof such as 1h30min

Neither months (ME) nor weeks (W): a month has no fixed duration. For monthly bars, resample to days in the store and to months in pandas, or read and resample everything in pandas.

A different pattern is to store many assets in a single symbol, in long format (a simbolo column), like a table in a relational database. That is what groupby and membership filters are for:

marzo = pd.concat(
    [d.loc[corte:].assign(simbolo=s) for s, d in datos.items()]
).sort_index(kind="stable")
lib.write("panel_marzo", marzo)
q = adb.QueryBuilder()
q = q[q["simbolo"].isin(["BAGO", "BAKA", "BAKU"])]
q = q.groupby("simbolo").agg({"volumen": "sum", "cierre": "max"})
print(len(marzo), "rows in the long table")
lib.read("panel_marzo", query_builder=q).data.sort_index()

409500 rows in the long table
         cierre   volumen
simbolo
BAGO      54.21  21174687
BAKA      48.76  21086537
BAKU      48.72  20773982

409,500 rows (50 symbols × 8,190 March minutes) and the total volume and maximum close of three of them, computed in the store. The order of the groups ArcticDB returns is not guaranteed; hence sort_index(). One symbol per asset or a long table? One symbol per asset allows independent versions and append per asset and direct reads per asset; a long table simplifies cross-sections (all assets in one minute) at the cost of rewriting more when one changes. With ArcticDB the usual approach is one symbol per asset and read_batch to read several.

Static and dynamic schema

By default, all versions of a symbol have the same columns and types. Adding a new column (for example, the number of trades per minute, which the vendor starts sending in April) fails with StreamDescriptorMismatch. A library with dynamic_schema=True allows it:

nuevo = pd.DataFrame(
    {"cierre": [51.0], "volumen": [100], "operaciones": [7]},
    index=[pd.Timestamp("2025-04-01 13:30Z")])
try:
    lib.append("BAGO", nuevo)
except adb.exceptions.ArcticException as e:
    print(type(e).__name__)

dinamica = ac.create_library(
    "dinamica", library_options=adb.LibraryOptions(dynamic_schema=True))
dinamica.write("BAGO", datos["BAGO"].iloc[-2:])
dinamica.append("BAGO", nuevo)
dinamica.read("BAGO").data

StreamDescriptorMismatch
                           cierre  volumen  operaciones
2025-03-31 19:58:00+00:00   52.27     4416            0
2025-03-31 19:59:00+00:00   52.20      626            0
2025-04-01 13:30:00+00:00   51.00      100            7

The dynamic library accepts the new column, but watch what it puts in the old rows: 0, not a null. The two March bars appear with operaciones = 0, which is false data (there were not zero trades; nobody knew how many there were). With floating-point columns it fills with NaN; with integers, with zero. A model that uses that column would learn from invented zeros. The practical rule: new columns of type float, or a separate column that says since when the data exists.

As-of joins: the query that defines a time series database

The most characteristic operation on market data is to join each trade with the quote in force at that instant: the last previous one, not one with the same timestamp (lesson 1.1.1). It is called an as-of join, and it is the reason for being of a good part of the databases in this field: kdb+ has it as aj, QuestDB and ClickHouse as ASOF JOIN, and DuckDB too. In pandas it is merge_asof. The cell generates 120 quote changes and 25 trades in one minute and joins each trade with the previous quote:

rng_q = np.random.default_rng(6640)
t0 = pd.Timestamp("2025-03-14 15:00Z")
ts_q = t0 + pd.to_timedelta(
    np.sort(rng_q.choice(60_000, 120, replace=False)), unit="ms")
medio = 50 + np.cumsum(rng_q.choice([-0.01, 0, 0.01], 120))
cot = pd.DataFrame({"ts": ts_q, "bid": medio - 0.01,
                    "ask": medio + 0.01})
ts_t = t0 + pd.to_timedelta(
    np.sort(rng_q.choice(60_000, 25, replace=False)), unit="ms")
ops = pd.DataFrame({"ts": ts_t, "precio": 0.0})
unidas = pd.merge_asof(ops, cot.assign(ts_cot=cot["ts"]), on="ts",
                       direction="backward",
                       allow_exact_matches=False)
unidas["precio"] = np.where(rng_q.random(25) < 0.5,
                            unidas["bid"], unidas["ask"]).round(2)
unidas["retraso_ms"] = (unidas["ts"] - unidas["ts_cot"]) \
    .dt.total_seconds().mul(1000).round()
print(unidas["retraso_ms"].describe()[["mean", "50%", "max"]]
      .round(1).to_dict())
unidas[["ts", "ts_cot", "bid", "ask", "precio"]].head(4)

{'mean': 564.8, '50%': 368.0, 'max': 1667.0}
                                ts                           ts_cot    bid    ask  precio
0 2025-03-14 15:00:02.080000+00:00 2025-03-14 15:00:01.712000+00:00  49.99  50.01   49.99
1 2025-03-14 15:00:05.046000+00:00 2025-03-14 15:00:04.027000+00:00  49.99  50.01   50.01
2 2025-03-14 15:00:08.231000+00:00 2025-03-14 15:00:07.575000+00:00  49.98  50.00   50.00
3 2025-03-14 15:00:09.762000+00:00 2025-03-14 15:00:08.806000+00:00  49.96  49.98   49.96

Each trade gets the closest previous quote (ts_cot): on average, 565 milliseconds earlier, with a maximum of 1.7 seconds. allow_exact_matches=False excludes a quote with the same timestamp as the trade, which could be the one that trade has just caused. In a database with a native as-of join this query runs over billions of rows, per symbol, without bringing the data into Python.

TimescaleDB, QuestDB, ClickHouse and kdb+

All four need a server and are not run in the course. What follows comes from their official documentation, accessed on October 23, 2026; the code has been checked against it, not run.

TimescaleDB (from Tiger Data) is a PostgreSQL extension. Hypertables are PostgreSQL tables that are automatically split into chunks by time; its Hypercore engine stores recent data by rows and later converts it to compressed columns; time_bucket aggregates into arbitrary intervals, and continuous aggregates are materialized views that are refreshed in the background when data arrives44Note 4 · Tiger Data, hypertables, time_bucket, continuous aggregates and Hypercore: https://docs.tigerdata.com/use-timescale/latest/hypertables/, accessed October 23, 2026.. Lesson 1.4.7 showed how to create a hypertable; a continuous aggregate of hourly bars:

import psycopg

SQL = [
    """
    CREATE MATERIALIZED VIEW barras_1h
    WITH (timescaledb.continuous) AS
    SELECT simbolo, time_bucket(INTERVAL '1 hour', ts) AS hora,
           last(cierre, ts) AS cierre, sum(volumen) AS volumen
    FROM barras_1min
    GROUP BY simbolo, hora
    """,
    """
    SELECT add_continuous_aggregate_policy('barras_1h',
        start_offset => INTERVAL '1 week',
        end_offset => INTERVAL '1 hour',
        schedule_interval => INTERVAL '30 minutes')
    """,
]
with psycopg.connect("postgresql://localhost/mercado",
                     autocommit=True) as cx:
    for sentencia in SQL:
        cx.execute(sentencia)

TimescaleDB's strength is that it is PostgreSQL: full SQL, transactions, indexes, permissions and its whole ecosystem. Its weakness, that it is an adapted general-purpose database: for columnar analysis of billions of rows, natively columnar databases usually need less tuning.

QuestDB is a columnar database with one designated timestamp per table, which enables time partitioning, SAMPLE BY (aggregation into intervals), LATEST ON (the last value per key) and ASOF JOIN55Note 5 · QuestDB, designated timestamp, SAMPLE BY, ASOF JOIN and Python client: https://questdb.com/docs/concept/designated-timestamp/ and https://questdb.com/docs/clients/ingest-python/, accessed October 23, 2026.. Its Python client 5.x writes complete DataFrames and reads results as Arrow or pandas; the documentation describes the InfluxDB protocol (ILP) as a compatibility protocol and recommends its own protocol (QWP) for new installations:

import questdb   # pip install -U questdb

with questdb.connect("ws::addr=localhost:9000;") as db:
    db.dataframe(ops, table_name="operaciones",
                 symbols=["simbolo"], at="ts")
    db.dataframe(cot, table_name="cotizaciones",
                 symbols=["simbolo"], at="ts")
    unidas = db.query("""
        SELECT o.ts, o.precio, c.bid, c.ask
        FROM operaciones o ASOF JOIN cotizaciones c ON (simbolo)
    """).to_pandas()
    horas = db.query("""
        SELECT ts, simbolo, last(cierre), sum(volumen)
        FROM barras_1min SAMPLE BY 1h
    """).to_pandas()

ClickHouse is a general-purpose columnar analytics (OLAP) database. Its MergeTree engine sorts each table by the ORDER BY key, which acts as a sparse primary key: it does not point to rows but to blocks of 8,192 rows (granules), and discards blocks like Parquet statistics do. It has ASOF JOIN with one equality column and one inequality column66Note 6 · ClickHouse, MergeTree and sparse primary indexes, ASOF JOIN and Python client: https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree, accessed October 23, 2026.:

import clickhouse_connect   # pip install clickhouse-connect

cliente = clickhouse_connect.get_client(host="localhost")
cliente.command("""
    CREATE TABLE IF NOT EXISTS cotizaciones (
        simbolo LowCardinality(String), ts DateTime64(3, 'UTC'),
        bid Float64, ask Float64
    ) ENGINE = MergeTree ORDER BY (simbolo, ts)
""")
unidas = cliente.query_df("""
    SELECT o.ts, o.precio, c.bid, c.ask
    FROM operaciones o
    ASOF JOIN cotizaciones c
      ON o.simbolo = c.simbolo AND o.ts >= c.ts
""")

The ORDER BY (simbolo, ts) is the same decision as the write order in lesson 6.6.2: queries by symbol and time range discard almost all blocks.

kdb+ (from KX) is the historical reference of banks and funds for market data: an in-memory and on-disk columnar database with its own language, q. On disk, a splayed table stores each column in a file and a partitioned table spreads the splayed tables across folders (usually by date): the same design as a Parquet lake, decades earlier77Note 7 · KX, aj, splayed and partitioned tables, KDB-X Python and Community license: https://code.kx.com/q/ref/aj/ and https://code.kx.com/licensing/usage-restrictions.html, accessed October 23, 2026.. Its aj is the canonical as-of join. From Python it is used with PyKX (now “KDB-X Python”); KX offers a free Community edition for personal and commercial use with limits (16 GB of RAM for q, 24 cores, one instance)⁷:

import pykx as kx   # pip install pykx (requires a KDB-X license)

kx.q["t"] = ops          # pandas to a q table
kx.q["q"] = cot
unidas = kx.q("aj[`simbolo`ts; t; q]").pd()

aj[simbolots; t; q] joins each row of t with the last row of q with the same simbolo whose ts is less than or equal.

Comparison of models

The table summarizes what each one's documentation says, not its performance: the speed comparisons published by the vendors themselves measure what suits them and are not reproduced here. The choice depends more on the shape of the work than on microseconds. A researcher or a small team with DataFrames and a need for reproducible versions fits ArcticDB; a system that already lives in PostgreSQL, TimescaleDB; continuous tick ingestion with live SQL queries, QuestDB or ClickHouse; and a team with q experience and budget, kdb+. For research on your own, the Parquet lake with DuckDB from lesson 6.6.2 is still hard to beat.

Exercises

1. Read with read_batch the March closes of BAGO, BAKA and BAKU and compute the correlation of their 1-minute log returns. What value do you expect, knowing how they were generated?

marzo_ini = pd.Timestamp("2025-03-03", tz="UTC")
lotes = lib.read_batch(
    [adb.ReadRequest(s, date_range=(marzo_ini, None),
                     columns=["cierre"])
     for s in ["BAGO", "BAKA", "BAKU"]])
cierres = pd.DataFrame({r.symbol: r.data["cierre"] for r in lotes})
rend = np.log(cierres).diff().dropna()
print([r.version for r in lotes], len(rend), "returns")
rend.corr().round(3)

[2, 1, 1] 8189 returns
       BAGO   BAKA   BAKU
BAGO  1.000 -0.007  0.009
BAKA -0.007  1.000 -0.006
BAKU  0.009 -0.006  1.000

2. Compute BAGO's daily volume in March with QueryBuilder (resample("1D")) and check it with pandas on the data read.

q = adb.QueryBuilder().resample("1D").agg({"volumen": "sum"})
en_almacen = lib.read("BAGO", query_builder=q,
                      date_range=(marzo_ini, None)).data["volumen"]
leido = lib.read("BAGO", date_range=(marzo_ini, None)).data
en_pandas = leido["volumen"].resample("1D").sum()
en_pandas = en_pandas[en_pandas > 0]          # no weekends
print(len(en_almacen), "sessions;",
      "equal:", en_almacen.equals(en_pandas))
en_almacen.head(3)

21 sessions; equal: True
2025-03-03 00:00:00+00:00    1022324
2025-03-04 00:00:00+00:00     984597
2025-03-05 00:00:00+00:00     998865
Name: volumen, dtype: int64

import shutil
from pathlib import Path

del lib, dinamica, ac
shutil.rmtree(ruta)
print(sorted(p.name for p in Path(".").glob("arctic_*")))

[]

3. A small fund currently stores the ticks of 300 stocks in daily CSV files. It wants three things: that analysts can read bars for any period from Python, to be able to repeat a backtest a year from now with the exact data it used, and to join trades with quotes. What do you propose?