QuestDB 9.2: Exact arithmetic and smarter temporal joins

With QuestDB 9.2, you get native decimals for exact arithmetic and a faster engine for temporal joins.

This release introduces a new DECIMAL type for precise numeric work, a Dense ASOF JOIN algorithm for workloads with long distance matches, and makes symbol capacity auto scaling the default so high cardinality datasets work smoothly.

If 9.0 armed QuestDB with arrays and 9.1 added nanosecond timestamps and continuous profiling, 9.2 is about getting your numbers and temporal joins exactly right.

Let us dive in.

Exact arithmetic with DECIMAL

Fixed-point arithmetic is great for physics, not so great for ledgers.

If you have ever seen 0.30000000000000004 in a financial report, you know the problem. Floating point types like DOUBLE trade a bit of numerical accuracy for speed and range. That is usually fine, but not when you are reconciling accounts, PnL, or crypto balances.

QuestDB 9.2 introduces a native DECIMAL(precision, scale) type that stores fixed point numbers with exact arithmetic.

-- With DOUBLE:
SELECT 0.1 + 0.2;

-- With DECIMAL:
SELECT 0.1m + 0.2m;

• The first query uses DOUBLE and can produce 0.30000000000000004.
• The second uses DECIMAL literals (the m suffix) and returns 0.3 exactly.

No hidden rounding, no surprises.

Precision and scale, without thinking about storage

DECIMAL follows the classic SQL model:

• precision is the total number of significant digits.
• scale is how many digits live after the decimal point.

For example, DECIMAL(14, 2) gives you up to 999,999,999,999.99, which is usually enough for monetary amounts in a single currency.

CREATE TABLE transactions (
  id         LONG,
  account_id SYMBOL,
  amount     DECIMAL(14, 2),  -- up to 999,999,999,999.99
  quantity   DECIMAL(10, 3),  -- up to 9,999,999.999
  ts         TIMESTAMP
) timestamp(ts);

Under the hood, QuestDB chooses the smallest suitable storage size for you, from compact one byte decimals up to 32 byte decimal256 values. You do not have to think about which internal type to use.

You get:

• Precision up to 76 digits.
• Storage that scales with precision instead of a one size fits all type.
• Strict error handling. Operations that would silently lose precision with floating point will fail instead of rounding behind your back.

Decimal literals in queries

Decimal literals use the m suffix to distinguish them from floating point:

SELECT
  123.45m      AS price,
  0.0875m      AS rate,
  1000000.00m  AS notional;

Without the suffix, the same numbers are treated as DOUBLE.

This makes it clear which parts of your query must be exact, for example:

• Regulatory ratios.
• PnL ladders and VaR inputs.
• Fees and pricing tiers.

Real world use cases

A few examples where DECIMAL simplifies life:

• Portfolio PnL and reporting Store positions, prices, and PnL at the exact precision you need for portfolio level and account level reports.

• Ledgers and payouts Represent balances and payouts directly in their natural unit without integer scaling tricks or helper columns.

• Crypto balances Many tokens use high precision with many fractional digits. With DECIMAL you can store them directly with the correct number of fractional digits.

• Scientific and industrial measurements Sensor values that need exact rounding behavior, such as lab measurements or billing based on metered usage.

Combined with TIMESTAMP_NS from 9.1, you can now have both exact amounts and nanosecond accurate timestamps in the same database.

Dense ASOF JOIN for long distance matches

ASOF JOIN is very common in time series analytics. It lets you match each row from one table with the most recent row from another table at or before a given timestamp.

In less formal terms: give me the latest known value when this event happened.

QuestDB already provided several ASOF JOIN strategies that the optimizer can pick from. In 9.2

we add a new Dense algorithm for workloads where the matching rows are often far apart in time.

The problem: distant matches and rescans

Consider a classic market data example:

• An orders table with individual orders.
• A market data table with quotes or book snapshots.

For liquid symbols, orders and market data are interleaved tightly. For illiquid products, the next matching quote for an order may be thousands or millions of rows away.

Previous strategies were heavily optimized for nearby matches. In datasets with sparse matches, the engine sometimes had to rescan portions of the right hand table many times, which wastes work.

The Dense algorithm in plain English

The new Dense strategy behaves like a hybrid:

• It uses a search to jump close to the correct region in the right hand side.
• It then scans forward like the Light algorithm, taking advantage of temporal ordering.
• It reuses previous matches to avoid redoing work when the left hand side walks through similar timestamps.

You do not have to think about the details most of the time. QuestDB chooses an ASOF JOIN strategy based on your query and the data distribution.

When your workload has:

• Very frequent rows on the left hand side, and
• Sparse, distant matches on the right hand side,

the Dense algorithm can be significantly faster than previous strategies.

Example: dense ASOF JOIN hint

Here is a minimal example that uses the dense ASOF JOIN strategy explicitly:

SELECT /*+ asof_dense(orders md) */
    orders.timestamp,
    orders.symbol,
    orders.price
FROM orders
ASOF JOIN (md)
ON (symbol);

Hints like this give you a way to lock in a known good strategy for specific queries.

Symbol auto scaling is now the default

In 9.1 we introduced symbol capacity auto scaling as an optional setting. This allowed symbol tables to grow dynamically as you ingested more distinct values, instead of being limited by the capacity chosen at table creation time.

In 9.2, auto scaling is now enabled by default.

The practical effect is simple:

• High cardinality workloads ingest reliably without hitting capacity limits.
• You do not need to guess symbol capacity ahead of time for most tables.
• Existing configuration options remain available if you want to override the behavior.

If you are indexing large numbers of unique device identifiers, order ids, or session tokens, you get the more robust behavior out of the box.

Engine robustness and under the hood improvements

Beyond the headline features, QuestDB 9.2 contains a series of incremental improvements across the core engine and SQL layer.

Highlights include:

• More resilient async jobs: Edge cases where background jobs collided with table drops or extreme system load are handled more gracefully, so background processing remains stable even under stress.

• Safer deduplication: A subtle corner case in deduplicate writes that could lead to corruption has been fixed. This improves safety for high rate ingest workloads that rely on WAL and deduplication.

• ASOF JOIN performance tweaks: Dense ASOF JOIN is not just an extra algorithm. Existing strategies also see refinements for dense interleaving of left and right hand rows.

• Decimal plumbing and type system work: Decimal types are now fully wired into the engine, type tags, and numeric function behavior. You can mix DECIMAL with other numeric types according to clear rules.

• Graal CE JDK: The runtime has been updated to use Graal Community Edition as the bundled JDK, preparing the ground for future performance work.

None of these changes require configuration on your part, but they reduce surprises in long running systems or under heavy workloads.

Roundup

QuestDB 9.2 is a focused release that makes the database a better fit for precision heavy workloads and complex temporal analytics.

• DECIMAL gives you exact arithmetic with up to 76 digits of precision.
• Dense ASOF JOIN keeps temporal joins fast even when matches are far apart.
• Symbol auto scaling by default makes high cardinality ingest safer without manual capacity tuning.
• A host of smaller fixes and improvements make the engine more robust under stress.

Combined with nanosecond timestamps, arrays, and materialized views from recent releases, QuestDB continues to evolve as a powerful platform for capital markets, industrial telemetry, and any workload where time and precision matter.

Read the full 9.2.0 release notes →

Ready to try it?

👉 Download QuestDB 9.2

Join our Slack or Discourse communities to share feedback and results. We are excited to see what you build with native decimals and smarter ASOF joins.