Introducing the KX NYSE TAQ Benchmark

Today we are introducing the KX NYSE TAQ benchmark: an open-source, reproducible benchmark suite built on real New York Stock Exchange trade-and-quote (TAQ) data, with a query set that reflects what capital markets practitioners actually run in production. The suite is available on the KX NYSE TAQ Benchmarks repo on GitHub (anyone can download the data, run the benchmark, and verify the results).

This first post covers the history and motivation behind the benchmark and how it is designed. The results will follow in subsequent posts.

Existing Benchmarks

The database world has no shortage of benchmarks, and several have shaped how the industry evaluates analytical and time-series engines. The table below lists the benchmarks in which KX has participated:

Of these, STAC-M3 is the benchmark closest to what our clients do day-to-day. Financial industry participants were involved in putting together its specification in 2010, and kdb+ has dominated it ever since. Hardware vendors have repeatedly announced new world records using kdb+. STAC-M3 is a great benchmark, but it is proprietary: The detailed specifications, implementations, and full results sit behind membership. Our clients kept asking for something even more representative and openly verifiable. So we created it.

Why Another Benchmark?

1. Representative Data

The benchmark targets the capital markets domain, so it needs data that looks like what capital markets firms process. What could be more representative of high-volume financial data than that published by the New York Stock Exchange itself? The benchmark uses the publicly available NYSE Daily TAQ sample data, consisting of three tables:

A single day of quotes is over 2 billion rows. Our clients routinely work with datasets of this scale, so the benchmark is sized to match what they see in production. For those without a terabyte-class machine at hand, the suite also supports smaller data sizes: from a tiny subset that fits in 1 GB of memory up to the full day, by restricting ingestion to a subset of instruments.

2. Representative Queries

Most benchmarks reduce analytics to a simple “select – filter – group by – aggregate” pattern. Real capital markets workloads are richer than that. The KX NYSE TAQ benchmark defines 84 queries (at the time of writing) spanning:

• Complexity: Simple, advanced, and complex queries
• Instrument filter: Single instrument, a few instruments, or no filter at all. The filter’s selectivity fundamentally changes how much work an engine must do
• Additional tags: As-of joins, pivots, and other operations characteristic of financial analytics

The as-of join is a good example of why this matters. It is one of the most heavily used operations in capital markets time-series analysis, joining every trade to the prevailing quote, yet no existing benchmark includes an id–time, pair-based, as-of join query.

3. Open and Reproducible

Two further requirements shaped the design:

• Open source and reproducible: The data is publicly downloadable, and the queries and harness are on GitHub. A design requirement from day one was an output-comparison tool: For an apples-to-apples comparison, every solution’s query must return the same result based on the exact same input, and the benchmark even specifies how each result should be sorted. By comparison, in TSBS and ClickBench only the fact that the query implementations are open source provides some level of certainty that the queries return the same result. The frameworks themselves offer no comparison tool, which introduces confusion for testers: was an engine faster, or did it just compute something different?
• Comprehensive coverage: Multiple engines, multiple data layouts and index/attribute configurations, and multiple thread counts. Beyond these, engine-specific environment variables let you test even more variants of an engine.

Beyond Speed: Expressiveness and Maturity

Execution time and memory need are only part of the story. This is also a benchmark of expressiveness and maturity. Complex logic is implemented in finance every day, and two questions matter as much as raw speed:

• Can a query engine express the logic at all?
• If so, how maintainable is the solution: how readable and elegant is the query?

SELECT * FROM quote WHERE time < $1 + INTERVAL '8 hours'

or simply

SELECT * FROM quote WHERE time::TIME < '08:00'

Do we need to choose between the simple and the fast? With KDB-X, the dilemma does not arise:

select from quote where time < 0D8

• Several columns hold single characters, like exchange IDs. Only kdb+ supports a single-character data type; the other engines must fall back to either ENUM/categorical or string (VARCHAR), and neither is optimal considering speed, memory need, or usability.

What the Queries Look Like

Two examples give a flavor of the query set and the expressiveness question.

Query 28

Pivot the 10-minute average liquidity-weighted mid-quote by symbol for 50 infrequent instruments, forward-filling missing values.

q-sql:

fills .pvt.pivot select
    avgLiqWMid: avg ((bsize * bid) + asize * ask)
                    % bsize + asize
    by 10 xbar time.minute, sym
    from quote
    where sym in fiftyInstrs

Polars:

quote
.filter(pl.col("sym").is_in(fiftyInstrs))
.with_columns(time = pl.lit(datadate).dt.combine(pl.col("time")))
.group_by_dynamic(index_column="time", every="10m",
                   closed="left", group_by=["sym"])
.agg(
    ((pl.col("bsize") * pl.col("bid") +
      pl.col("asize") * pl.col("ask"))
     / (pl.col("asize") + pl.col("bsize")))
    .drop_nans().mean().alias("avgLiqWMid")
)
.pivot("sym", values="avgLiqWMid", index="time")
.with_columns(pl.col("time").dt.time())
.sort("time")
.select(pl.all().forward_fill())

DuckDB:

WITH filtered AS (
    SELECT
        time_bucket(INTERVAL '10 minutes', time)::TIME AS time,
        sym,
        (bsize * bid + asize * ask) / (bsize + asize) AS liqWMid
    FROM quote
    WHERE sym IN $1
)
SELECT
    time,
    LAST_VALUE(COLUMNS(* EXCLUDE time) IGNORE NULLS)
        OVER (ORDER BY time ROWS
              BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM (
    PIVOT filtered
    ON sym
    USING AVG(liqWMid) AS avgLiqWMid
    GROUP BY time
)
ORDER BY time

Pandas:

quote
.query("sym in @fiftyInstrs")
.eval("LiqWMid=(bsize * bid + asize * ask) / (asize + bsize)")
.assign(time=lambda df: df["time"].dt.floor('10min'))
.groupby(["time", "sym"], observed=True)
.agg(avgLiqWMid=("LiqWMid", "mean"))
.pivot_table(index="time", columns="sym",
             values="avgLiqWMid")
.ffill()

The maturity gap shows up in the details. kdb+ is a battle-tested technology that has been used in production for 30 years; newer solutions sometimes lack basic features. In the Polars solution, group_by_dynamic does not support duration types, so the time column must first be combined with the date and an extra sort is needed because group_by_dynamic has no maintain_order parameter.

Also compare the number of parentheses and quotation marks you need to type in the Python-based solutions. Anything that does not contribute to understanding the query is just “noise”: A side effect of software engineering plumbing. The q language strives to keep this noise to a minimum by design.

Query 61

Select all rows where the sequence number decreases within an instrument.

q-sql:

ungroup select from
    (select seqDecr: seq where (<) prior seq
        by sym from trade)
    where 0 < count each seqDecr

Polars:

trade
.with_columns((pl.col("seq").diff().over("sym")).alias("seq_delta"))
.filter(pl.col("seq_delta") < 0)
.select("sym", pl.col("seq").alias("seqDecr"))

DuckDB:

SELECT sym, seq AS seqDecr
FROM (
    SELECT
        sym,
        seq,
        seq - LAG(seq) OVER (PARTITION BY sym ORDER BY time, rn) AS seq_delta
    FROM (
        SELECT *, ROW_NUMBER() OVER () AS rn
        FROM trade
    )
)
WHERE seq_delta < 0
ORDER BY sym

Pandas:

trade
.assign(seq_delta=lambda df: df.groupby("sym", observed=True)["seq"].diff())
.query("seq_delta < 0")[["sym", "seq"]]
.rename(columns={"seq": "seqDecr"})
.sort_values("sym")

Every engine expresses the query, but with quite different degrees of ceremony. What takes a multi-line window function in the SQL-dialect engines (DuckDB and KDB-X SQL) is a simple function call in the others. This readability and maintainability dimension is exactly what the “expressiveness” goal is about.

How to run the benchmark yourself

The benchmark suite is open source and designed to be reproduced and extended. Adding a new query engine or growing the query set are both documented workflows, and a built-in output-comparison tool verifies that every engine returns the same result for every query.

Get started at https://github.com/KxSystems/NYSETAQBenchmarks. The quickstart runs the in-memory benchmark on a tiny dataset that works with the free KDB-X Community Edition, so you can try it on a laptop before scaling up to the full 2-billion-row day.

Acknowledgments

We would like to thank DuckDB Labs for reviewing the DuckDB solutions, AMD for providing the benchmark hardware, and Data Intellect for contributing to the query set, drawing on their extensive experience with capital markets clients.

Stay tuned for the next post in this series, where we dive into the in-memory query engine results.