QuestDB for High-Ingestion Time-Series: SIMD Columnar Engine + PG Wire Protocol

questdb is the pick-one architectural decision that decides whether a fintech tick pipeline, an IoT telemetry stream, or an adtech event bus lands its writes at a million rows per second on a single node with sub-second query latency — or whether it silently drops backpressure onto the source and forces a Kafka-shaped detour. Every senior data engineer evaluating a time series database in 2026 asks the same axis-questions: does the engine sustain sub-millisecond ingest at the write path we actually have, does the SQL dialect handle time-bucket aggregation and as-of joins without hand-rolled GROUP BY floor(ts) gymnastics, does it speak the Postgres wire protocol so every existing BI tool and JDBC driver just works, and does it fit our operational constraint (single-node vs multi-node, self-hosted vs managed cloud). The engineering trade-off does not live in "should we adopt a time-series database" — every write-heavy workload eventually needs one — but in which engine fits the (ingest × SQL × ecosystem × distribution) profile of the workload in front of you.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through QuestDB's SIMD engine and why it clocks 1.4M rows/sec per instance", or "what does ASOF JOIN do that a normal JOIN cannot", or "how does QuestDB's SAMPLE BY compare to TimescaleDB's time_bucket and InfluxQL's GROUP BY time()." It walks through the QuestDB architecture — the column-per-file storage layout, the SIMD-vectorised aggregation kernels, the zero-GC off-heap hot path, the mmap-backed page cache — the three ingestion protocols (ilp over TCP/UDP, PG wire, REST) with the O3 commit strategy that handles out-of-order writes, the time-series SQL extensions (SAMPLE BY, LATEST ON, ASOF JOIN, FILL, TIMESTAMP designation) that make QuestDB's dialect strictly more expressive than plain Postgres, and the decision matrix (QuestDB vs TimescaleDB vs InfluxDB 3 vs ClickHouse) that interviewers use to test whether you understand the axis space. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse aggregation on the aggregation practice library →, and sharpen the joins axis with the joins practice library →.

---

On this page

• Why QuestDB matters in 2026
• Columnar storage + SIMD engine
• Ingestion protocols — ILP, PG-wire, REST
• SQL extensions for time-series
• When to pick QuestDB + interview signals
• Cheat sheet — QuestDB recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why QuestDB matters in 2026

An open-source Java + C++ time-series database that clocks a million rows per second on one box — and speaks Postgres wire natively

The one-sentence invariant: QuestDB is the open-source, Apache-2.0-licensed, single-node time-series database written in Java plus C++ that combines a SIMD-vectorised columnar storage engine with the Postgres wire protocol on the read path and the InfluxDB Line Protocol on the write path — giving you ~1.4M rows/sec ingest per instance, sub-second time-bucket aggregation via SAMPLE BY, and drop-in compatibility with every psql / JDBC / BI tool you already own, all without operating a distributed cluster. Every senior data-engineering interviewer in 2026 who asks about time-series databases eventually gets to QuestDB because it collapses two axes — ultra-high ingest and standard SQL — that competing systems typically force you to trade off.

The four "must-answer" axes.

• Ingest throughput. Can the engine sustain the write rate of the source, not just the benchmark? QuestDB's headline number is ~1.4M rows/sec per instance on modest cloud hardware (published on questdb.io/blog and reproduced on TSBS — the Time Series Benchmark Suite). InfluxDB 3 is comparable on the ingest axis; TimescaleDB is one order of magnitude lower; ClickHouse is comparable but distribution-first. Interviewers open here because ingest rate maps directly to hardware bill.
• SQL compatibility. Does the query dialect look like Postgres or does it invent a new DSL? QuestDB is SQL-first with time-series extensions layered on top (SAMPLE BY, LATEST ON, ASOF JOIN). InfluxQL is DSL-first and requires learning. Flux is a scripting language. TimescaleDB is full Postgres. ClickHouse is SQL-adjacent with heavy dialect drift. The senior signal is naming the dialect delta up front.
• Ecosystem fit. Does it plug into every BI tool, ORM, and JDBC driver you already own? QuestDB speaks the Postgres wire protocol on port 8812; every psql, DBeaver, Metabase, Tableau, Grafana, JetBrains DataGrip, Django-ORM, SQLAlchemy, and Spring Data connection just works with no bespoke driver. TimescaleDB is actually Postgres so this is trivially true. InfluxDB and ClickHouse need vendor connectors.
• Single-node vs distributed. QuestDB is single-node-first with QuestDB Cloud handling the operator concerns (backups, replicas, monitoring). ClickHouse is distribution-first (shards, replicas, ZooKeeper/Keeper). InfluxDB 3 is object-storage-native (S3 + DataFusion) which changes the operational shape. TimescaleDB is Postgres so it has Postgres's HA story (streaming replication, Patroni). Naming which axis you're optimising is the senior signal.

The 2026 reality — four canonical time-series databases in the interview.

• QuestDB. The single-node ultra-high-ingest champion when you want Postgres SQL and don't need horizontal writes. QuestDB Cloud (managed since 2023) removes the operational lift while keeping the engine's throughput. Best fit: fintech ticks, IoT telemetry, adtech events, industrial sensor logs — anywhere a single big box plus replicas beats a distributed cluster.
• TimescaleDB. The "your existing Postgres, plus time-series superpowers" answer. Hypertables partition Postgres tables under the hood; continuous aggregates materialise time-bucket rollups; every Postgres feature (transactions, foreign keys, PL/pgSQL, extensions) still works. Best fit: teams that live in Postgres already and want time-series ergonomics without a new database.
• InfluxDB 3. The 2024+ rewrite that moved from an in-house columnar engine to a Rust + Apache Arrow + DataFusion stack backed by object storage (S3). Ingests via the same line protocol, queries via SQL, storage on Parquet in S3. Best fit: teams that want cloud-native, object-storage-first, and don't mind Rust ops.
• ClickHouse. The multi-node OLAP + time-series champion. Very fast on high-cardinality analytics, distribution-first (shards + replicas), SQL dialect drifts from ANSI. Best fit: analytics-heavy workloads that also happen to be time-series; not the answer for pure high-ingest single-node.

What interviewers listen for.

• Do you name QuestDB as "SIMD-vectorised columnar with PG wire" in sentence one, not "another time-series DB"? — senior signal.
• Do you distinguish ILP (write path) from PG wire (read path) — two protocols, two purposes? — required answer.
• Do you name ASOF JOIN as the primitive that aligns two time-series on nearest-earlier timestamp, not "a fancy join"? — senior signal.
• Do you name the ~1.4M rows/sec per instance figure and immediately caveat it as "modest cloud hardware, published benchmark, TSBS-verifiable"? — senior signal.
• Do you describe the engine as "zero-GC hot path with off-heap Unsafe + mmap" rather than "just Java"? — senior signal.

Worked example — the four-engine comparison table

Detailed explanation. The single most useful artifact for a QuestDB interview is a memorised 4×4 comparison table across QuestDB, TimescaleDB, InfluxDB 3, and ClickHouse on the four axes (ingest rate, SQL dialect, ecosystem, single-node/distributed). Every senior time-series discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical fintech tick-store that needs to write 500k rows/sec and support quant queries.

• Workload. Fintech tick store — trades(ts, symbol, price, volume, side) and quotes(ts, symbol, bid, ask) — writes at 500k rows/sec peak, queries include intraday VWAP, LATEST book snapshot per symbol, ASOF JOIN of trades against the most recent quote.
• Constraints. Single-region deployment, 30-day retention hot + 1-year cold, Postgres-shaped BI (Metabase + Grafana), team of 6 engineers with strong Postgres experience but no distributed-systems specialist.
• Cost budget. ~$8k/month for the primary DB tier.

Question. Build the four-engine comparison and pick the winner for the workload above.

Input.

Engine	Ingest rate (typical)	SQL dialect	Ecosystem	Distribution model
QuestDB	~1.4M rows/sec/instance	SQL + time-series extensions	PG wire compat	single-node + replicas
TimescaleDB	~100-200k rows/sec/instance	full Postgres SQL	native Postgres	Postgres HA (Patroni)
InfluxDB 3	~1M+ rows/sec/instance	SQL (was InfluxQL/Flux)	vendor driver + PG-wire preview	object-storage native
ClickHouse	~1M+ rows/sec/instance	SQL-adjacent dialect	vendor driver	shards + replicas

Code.

# QuestDB config snippet — single-node fintech tick deployment
# server.conf
cairo.max.uncommitted.rows=500000
cairo.commit.lag=1000
line.tcp.msg.buffer.size=1048576
line.tcp.io.worker.count=8
line.tcp.writer.worker.count=4
pg.enabled=true
pg.net.bind.to=0.0.0.0:8812
http.enabled=true
http.net.bind.to=0.0.0.0:9000

Step-by-step explanation.

1. The comparison table forces the four axes into one view: ingest rate, SQL dialect, ecosystem, distribution model. QuestDB wins the (ingest × single-node × PG wire) trifecta; TimescaleDB wins the "we live in Postgres" trifecta; InfluxDB 3 wins the (object-storage × cloud-native) trifecta; ClickHouse wins the (multi-node × high-cardinality analytics) trifecta. Never rank them on one axis alone.
2. TimescaleDB's ingest ceiling (~100-200k rows/sec on comparable hardware) is roughly one order of magnitude below QuestDB. This is because Postgres's row-store MVCC storage was not built for append-heavy time-series workloads, whereas QuestDB's column-per-file layout is purpose-built for exactly that access pattern. For 500k rows/sec, TimescaleDB starts to require careful chunk tuning and eventually multi-node coordination.
3. QuestDB's PG-wire compatibility is not a Postgres emulation — it is the actual PostgreSQL wire protocol, so every psql, DBeaver, Metabase, Tableau, and JDBC connection just works. The engine underneath is QuestDB's own, but the on-the-wire protocol is Postgres-shaped. This is the ecosystem win.
4. ClickHouse and InfluxDB 3 both hit the 500k rows/sec bar comfortably but bring different operational shapes. ClickHouse is distribution-first with ZooKeeper/Keeper coordination — overkill for a single-region tick store. InfluxDB 3 is object-storage-native with a Rust operator — a smaller team can run it but the Postgres-tool ecosystem is not first-class.
5. For the fintech tick store: QuestDB wins. Single-node fits the deployment shape; ~1.4M rows/sec headroom over the 500k rows/sec workload gives comfortable margin; PG wire matches Metabase + Grafana + BI without vendor drivers; QuestDB Cloud lifts the operator burden if the team's distributed-systems expertise is thin.

Output.

Consumer	Recommended engine	Why
Fintech ticks 500k/sec	QuestDB	single-node ingest + PG wire + ASOF JOIN
Postgres-first team + time-series	TimescaleDB	zero-migration cost from existing PG
Object-storage-native lakehouse tie	InfluxDB 3	Parquet on S3; DataFusion queries
Multi-node analytics + time-series	ClickHouse	shards + replicas + high-cardinality wins

Rule of thumb. Never pick a time-series database on ingest rate alone. Pick it based on (ingest × SQL × ecosystem × distribution) — the four axes. Write the table on a whiteboard first; the engine falls out of the constraints.

Worked example — what interviewers actually probe on QuestDB

Detailed explanation. The senior data-engineering QuestDB interview has a predictable structure: the interviewer opens with an ambiguous question ("how would you build a time-series store for our tick data?"), then progressively narrows to test whether you understand the SIMD engine, the ILP write path, the PG-wire read path, and the ASOF JOIN semantics. The candidates who name the pattern in sentence one score highest; the candidates who describe "a time-series database" without naming the engine score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you store 500k rows/sec of market ticks?" — invites you to name QuestDB and the SIMD engine.
• Follow-up 1. "What's the write protocol?" — probes ILP knowledge (TCP/UDP, line protocol).
• Follow-up 2. "How do you query them?" — probes PG-wire + SAMPLE BY + ASOF JOIN.
• Follow-up 3. "How does it handle out-of-order writes?" — probes O3 commit + commit-lag + max-uncommitted-rows.
• Follow-up 4. "How does it compare to TimescaleDB / InfluxDB / ClickHouse?" — probes the four-axis matrix.

Question. Draft a 5-minute senior QuestDB answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Engine named	"a time-series DB"	"QuestDB — SIMD columnar with PG wire"
Write path	"we insert rows"	"ILP over TCP with commit-lag O3 tuning"
Query path	"we run SQL"	"PG wire; SAMPLE BY 1m + ASOF JOIN quotes"
Out-of-order	"we sort them"	"O3 commit; commit-lag=1s, max-uncommitted=500k"
Vs alternatives	"similar to Timescale"	"QuestDB for single-node ingest; Timescale for PG-first; InfluxDB 3 for object-storage; ClickHouse for multi-node OLAP"

Code.

Senior QuestDB answer template (5 minutes)
==========================================

Minute 1 — name the engine up front
  "I'd default to QuestDB — the open-source, Apache-2.0, single-node
   SIMD-vectorised columnar time-series database that speaks Postgres
   wire on port 8812 and InfluxDB Line Protocol on port 9009. On a
   modest 8-vCPU / 32-GB box it sustains ~1.4M rows/sec sustained,
   which gives comfortable margin over 500k rows/sec workloads."

Minute 2 — write path (ILP)
  "Writes go over the InfluxDB Line Protocol on TCP port 9009. The
   client sends 'trades,symbol=AAPL price=180.5 volume=100 <ts>' one
   line per row; QuestDB batches them into partitions by the
   TIMESTAMP() column, flushes on commit-lag or max-uncommitted-rows,
   whichever fires first. Out-of-order writes are handled by the O3
   commit strategy — the engine sorts and merges within the commit
   window."

Minute 3 — read path (PG wire + time-series SQL)
  "Reads go over port 8812 via the Postgres wire protocol. Every
   psql, JDBC, DBeaver, Metabase, Grafana just connects. The dialect
   is SQL plus time-series extensions: SAMPLE BY for time-bucket
   aggregation without GROUP BY floor(), LATEST ON for the newest
   snapshot per key, ASOF JOIN to align two time-series on nearest-
   earlier ts, FILL(NULL|PREV|LINEAR|N) for gap-filling."

Minute 4 — engine internals
  "Under the hood: column-per-file storage partitioned by day, month,
   or year; SIMD-vectorised aggregation kernels for SUM/AVG/MIN/MAX;
   zero-GC hot path via off-heap allocation + Unsafe; mmap-backed
   direct OS page cache access. The engine avoids the two things that
   slow Postgres on this workload — row-store MVCC and JVM GC pauses."

Minute 5 — decision + operations
  "QuestDB wins for single-node ultra-high ingest + PG SQL. Deploy on
   an 8-16 vCPU box with fast NVMe, retention 30 days hot; snapshot
   cold to S3 monthly. QuestDB Cloud takes care of replicas + backups
   + monitoring if the team doesn't want to run stateful services.
   Alternatives: TimescaleDB if we already run PG; InfluxDB 3 if
   object-storage-native matters; ClickHouse if multi-node OLAP is
   the primary axis."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming the engine immediately — "QuestDB, SIMD columnar with PG wire" — signals you're an architect, not a task-runner. Weak candidates dive into schema design ("we'd have a trades table with…") before naming the engine choice.
2. Minute 2 addresses the write path with the specific protocol (ilp on port 9009) and the specific tuning knobs (commit-lag, max-uncommitted-rows). Naming the O3 commit strategy without prompting demonstrates that you've read the engine internals, not just the marketing page.
3. Minute 3 covers the read path with the four time-series SQL extensions (SAMPLE BY, LATEST ON, ASOF JOIN, FILL). These are the primitives that make QuestDB's dialect strictly more expressive than plain Postgres for time-series workloads; naming them shows you know why the extensions exist.
4. Minute 4 is the engine internals paragraph: column-per-file, SIMD kernels, zero-GC, mmap. Every one of these is a real engine feature that maps to a real throughput benefit. Say all four unprompted.
5. Minute 5 covers the decision + operational story. Naming QuestDB Cloud, the retention tiering (30 days hot + monthly cold to S3), and the three alternative engines with one-line differentiators demonstrates you understand the full trade-off space, not just the QuestDB happy path.

Output.

Grading criterion	Weak score	Senior score
Names engine in minute 1	rare	mandatory
Names ILP + PG wire split	rare	required
Names 4 SQL extensions	rare	senior signal
Names O3 commit + knobs	rare	senior signal
Names 3 alternative engines	rare	senior signal

Rule of thumb. The senior QuestDB answer is a 5-minute monologue that covers all four axes (ingest, SQL, ecosystem, distribution) without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — the "pick the engine" decision tree

Detailed explanation. Given a new time-series workload, the senior architect runs a 4-question decision tree in their head. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk the tree out loud. Walk through the tree with three canonical scenarios: fintech ticks (single-region, high-ingest), IoT telemetry (multi-region, cloud-managed), and a Postgres-native team that wants time-series superpowers.

• Q1. Is the workload single-node friendly (< ~2M rows/sec sustained peak)? → yes = go to Q2; no = ClickHouse (multi-node OLAP + time-series).
• Q2. Does the team already live in Postgres and refuse to add a new DB? → yes = TimescaleDB (hypertables); no = go to Q3.
• Q3. Does object-storage-native matter (Parquet on S3, DataFusion queries)? → yes = InfluxDB 3; no = go to Q4.
• Q4. Is Postgres wire compatibility for BI tools required? → yes = QuestDB; no = QuestDB anyway (still the strongest single-node SQL + time-series answer in 2026).

Question. Walk the decision tree for the three scenarios and record the engine each ends up with.

Input.

Scenario	Q1 (single-node?)	Q2 (PG-first team?)	Q3 (object-storage native?)	Q4 (PG wire needed?)
Fintech ticks 500k/sec	yes	no	no	yes
IoT telemetry 200k/sec	yes	no	yes	no
Postgres shop + time-series	yes	yes	—	—

Code.

# Decision-tree helper (illustrative)
def pick_time_series_engine(single_node_friendly: bool,
                             postgres_first_team: bool,
                             object_storage_native: bool,
                             pg_wire_required: bool) -> str:
    """Return the primary time-series engine for a workload."""
    if not single_node_friendly:
        return "ClickHouse"
    if postgres_first_team:
        return "TimescaleDB"
    if object_storage_native:
        return "InfluxDB 3"
    return "QuestDB"


# Walk the three scenarios
print(pick_time_series_engine(True,  False, False, True))
# → 'QuestDB'

print(pick_time_series_engine(True,  False, True,  False))
# → 'InfluxDB 3'

print(pick_time_series_engine(True,  True,  False, False))
# → 'TimescaleDB'

Step-by-step explanation.

1. Scenario 1 — Fintech ticks 500k rows/sec, no object-storage-native requirement, needs PG wire for Metabase + Grafana. The decision tree short-circuits at Q4 → QuestDB. This is the modern default for single-node ultra-high-ingest workloads.
2. Scenario 2 — IoT telemetry 200k rows/sec with a strong "S3 is the storage tier" requirement (data lake tie-in, Parquet interop). Q1 = yes, Q2 = no, Q3 = yes → InfluxDB 3. The object-storage-native architecture is the deciding axis.
3. Scenario 3 — a Postgres-first team already running Postgres 16 with Patroni HA, adding time-series superpowers to an existing OLTP workload. Q2 = yes → TimescaleDB. The migration cost (zero) dominates every other axis.
4. The decision tree does not attempt to rank engines on raw performance — the ranking depends on the workload. Instead it asks the axis-questions in order of decisiveness. This mirrors how a senior architect actually decides.
5. If Q1 = no (workload requires multi-node), the answer is ClickHouse — no other engine in the list handles horizontal writes cleanly. QuestDB's answer to multi-node is "add read replicas and shard by symbol at the application layer" — technically viable but not architecturally native.

Output.

Scenario	Primary engine	Rationale
Fintech ticks 500k/sec	QuestDB	single-node + PG wire + ASOF JOIN
IoT telemetry 200k/sec	InfluxDB 3	object-storage-native + DataFusion
Postgres shop + time-series	TimescaleDB	zero migration + Postgres HA

Rule of thumb. The four-question decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any scenario and get an engine name in under 60 seconds.

Senior interview question on QuestDB engine choice

A senior interviewer often opens with: "You inherit a growing fintech tick pipeline that currently writes ~200k rows/sec into Postgres and is starting to backpressure the source under load spikes. The business wants sub-second freshness, ASOF-JOIN of trades against quotes, and full BI-tool compatibility. Walk me through the engine you'd migrate to, the schema you'd design, and the failure modes you'd guard against."

Solution Using QuestDB single-node with partitioned tables, ILP ingest, and PG-wire queries

-- Step 1 — create the trades table with TIMESTAMP designation + PARTITION BY DAY
CREATE TABLE trades (
    ts       TIMESTAMP,
    symbol   SYMBOL CAPACITY 4096 CACHE,
    price    DOUBLE,
    volume   LONG,
    side     SYMBOL CAPACITY 8 CACHE
) TIMESTAMP(ts) PARTITION BY DAY WAL;

-- Step 2 — create the quotes table analogously
CREATE TABLE quotes (
    ts       TIMESTAMP,
    symbol   SYMBOL CAPACITY 4096 CACHE,
    bid      DOUBLE,
    ask      DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL;

-- Step 3 — set commit-lag + max-uncommitted-rows for high-ingest tuning
ALTER TABLE trades SET PARAM commitLag = 1s;
ALTER TABLE trades SET PARAM maxUncommittedRows = 500000;
ALTER TABLE quotes SET PARAM commitLag = 1s;
ALTER TABLE quotes SET PARAM maxUncommittedRows = 500000;

# Step 4 — ILP producer (Python) — writes trades at 500k rows/sec
from questdb.ingress import Sender, TimestampNanos
import time

with Sender.from_conf("http::addr=questdb:9000;") as sender:
    for tick in tick_stream():   # yields (symbol, price, volume, side, ts_ns)
        symbol, price, volume, side, ts_ns = tick
        sender.row(
            "trades",
            symbols={"symbol": symbol, "side": side},
            columns={"price": price, "volume": volume},
            at=TimestampNanos(ts_ns),
        )
        # sender auto-flushes every 100 rows or 1s

-- Step 5 — the money query: intraday VWAP per symbol via SAMPLE BY 1m
--          + ASOF JOIN against the most recent quote
SELECT t.ts,
       t.symbol,
       sum(t.price * t.volume) / sum(t.volume) AS vwap,
       q.bid,
       q.ask
FROM   trades t
ASOF JOIN quotes q ON t.symbol = q.symbol
WHERE  t.ts IN '2026-07-27'
SAMPLE BY 1m
FILL(PREV);

Step-by-step trace.

Step	Before (Postgres)	After (QuestDB)
Ingest rate ceiling	~100-200k rows/sec	~1.4M rows/sec
Query freshness	seconds to minutes	sub-second
ASOF JOIN	manual LATERAL subquery	native ASOF JOIN primitive
Time-bucket aggregation	GROUP BY floor(ts / 60)	SAMPLE BY 1m
Storage layout	row-store MVCC	column-per-file, partitioned by day
Out-of-order handling	rejected or costly	O3 commit; commit-lag=1s
BI-tool compatibility	native Postgres	PG wire compat on port 8812

After the migration, the tick pipeline lands writes at ~500k rows/sec with headroom to 1.4M; VWAP queries return in ~40ms over a full trading day; ASOF-JOIN of trades against quotes returns in ~80ms for 10M-row joins on the aligned time axis; Metabase and Grafana connect via the PG-wire endpoint with no config change beyond the port.

Output:

Metric	Before (Postgres)	After (QuestDB)
Sustained ingest	200k rows/sec (with backpressure)	500k rows/sec (30% engine capacity)
VWAP query p99	~800 ms	~40 ms
ASOF JOIN feasibility	complex LATERAL subquery	one-liner primitive
Storage/day	~50 GB (row-store + WAL)	~18 GB (columnar compressed)
BI integration	native	PG wire compat
Operational shape	Postgres + Patroni	single QuestDB + Cloud replicas

Why this works — concept by concept:

• Columnar storage — each column is stored in its own file, so aggregation queries over one column (sum(price * volume)) touch only that file, not the whole row. Storage bandwidth and CPU cache pressure both drop by roughly the column-count factor for typical wide tables.
• SIMD-vectorised aggregation — QuestDB's SUM/AVG/MIN/MAX kernels process 4/8/16 values per CPU instruction using AVX/AVX2/AVX512 depending on the host. This is a straight multiplier over scalar aggregation and is a large fraction of why the engine hits ~1.4M rows/sec.
• Zero-GC hot path — the ingest and query paths allocate off-heap (via Unsafe) so they never trigger a JVM GC pause. A stop-the-world pause during a market open would drop hundreds of thousands of rows; the off-heap design removes that failure mode.
• ASOF JOIN primitive — aligning two time-series on nearest-earlier timestamp is the fundamental time-series join. In plain SQL it requires a LATERAL correlated subquery per row (O(N × log M)); QuestDB implements it as a single merge-walk of two already-sorted streams (O(N + M)). The difference is the whole reason to adopt a time-series engine for this workload.
• Cost — one QuestDB node (~$500/mo on cloud), NVMe storage sized for retention, one QuestDB Cloud add-on for backups/replicas. The eliminated cost is the Postgres cluster tuning, Patroni ops, and the LATERAL-subquery-heavy application code. Net O(1) per ingest row instead of O(log N) per Postgres commit; O(N + M) per ASOF join instead of O(N × log M).

SQL
Topic — sql
SQL time-series and ASOF-JOIN problems

Practice →

Aggregation Topic — aggregation Aggregation problems on time-bucketed data

Practice →

---

2. Columnar storage + SIMD engine

Column-per-file layout, SIMD-vectorised aggregation kernels, zero-GC off-heap hot path — the three levers behind ~1.4M rows/sec

The mental model in one line: QuestDB's throughput comes from three compounding architectural choices — a column-per-file storage layout partitioned by time, SIMD-vectorised aggregation kernels that process 4/8/16 values per CPU instruction, and a zero-GC hot path that allocates off-heap via Unsafe so JVM garbage collection never pauses ingest or query — and every senior architect must be able to name and defend all three without prompting. Every senior DE has built a Postgres-first time-series pipeline that eventually hit a wall; the wall is the row-store MVCC storage layout that QuestDB's columnar engine explicitly avoids.

The four axes for the QuestDB engine.

• Storage layout. One file per column per partition. A trades(ts, symbol, price, volume, side) table partitioned by day produces five files per day (one per column). Aggregation queries over one column touch one file; range scans over one partition touch one folder. Storage bandwidth scales with the touched-columns × touched-partitions product, not with total table size.
• Aggregation kernels. SUM/AVG/MIN/MAX/COUNT are implemented in vectorised C++ that dispatches to AVX-512, AVX2, or scalar depending on the host's CPU flags. A single AVX-512 instruction sums 8 doubles; QuestDB's kernel loops over the column file in 512-bit chunks. This is a straight 4-16× multiplier over a naive for (i = 0; i < n; i++) sum += a[i] loop.
• Hot path allocation. Ingest and query paths allocate memory from off-heap arenas via Unsafe.allocateMemory. Nothing on the hot path escapes to the JVM heap; nothing triggers GC. The JVM is essentially a runtime for the query planner and API surface, not for the execution engine.
• File access. Column files are mmap'd directly into the process address space. Reads go through the OS page cache with zero-copy semantics — the kernel decides what stays in memory, the engine reads via pointer arithmetic. This matches the pattern Redis uses for RDB persistence and PostgreSQL uses for shared buffers.

The column-per-file layout — what the on-disk shape looks like.

• Partition folder. db/trades/2026-07-27.0/ — one folder per partition (day, month, or year depending on PARTITION BY).
• Column files. ts.d, symbol.d, symbol.k, symbol.v, price.d, volume.d, side.d, side.k, side.v — .d for the column data, .k and .v for symbol (string) column index + values.
• TIMESTAMP designation. One column is the time axis (declared TIMESTAMP(ts) in DDL). Data is stored append-only in TIMESTAMP order within each partition. Range queries on the timestamp become O(1) partition-pruning + O(log N) binary search within the partition.
• Symbol columns. String-typed columns declared as SYMBOL are dictionary-encoded — the string value stored once in a .v file and referenced by 32-bit id in the .d file. This is the same trick Parquet uses for low-cardinality strings and it saves ~10-100× storage for symbol columns like ticker or event_type.

The SIMD kernels — where the CPU instructions actually run.

• Detection. At startup, QuestDB probes the host for AVX-512, AVX2, or plain SSE support and picks the widest available. On a 2020+ Xeon or EPYC box you get AVX-512 (16 float32 or 8 float64 per instruction); on ARM64 you get NEON (4 float32 or 2 float64).
• Dispatch. Each aggregation function has multiple implementations — one per instruction width. The planner picks the widest at query compile time; the kernel processes the column file in chunks of that width.
• Vector-native ops. SUM, AVG, MIN, MAX, COUNT, and predicate filtering (WHERE price > 100) all use the vectorised kernels. Non-vector ops (regex, complex CASE expressions, window functions) fall back to scalar.
• The measured win. On a 24-core AMD EPYC box, SUM over a 100M-row DOUBLE column runs in ~30 ms via AVX-512 vs ~200 ms scalar — a ~7× speedup on this specific measurement (numbers vary by CPU + column type).

The zero-GC hot path — why Java time-series engines are unusual.

• The problem. JVM GC pauses can hit hundreds of milliseconds under load. During those pauses, ingest halts and queries stall. For a 500k-rows/sec workload, a 200ms pause drops 100k rows or forces the ILP client to backpressure the source. Unacceptable.
• The solution. All hot-path memory is allocated off-heap via sun.misc.Unsafe (or the JDK 21+ MemorySegment API in newer builds). The JVM heap holds only long-lived objects (the query cache, the connection pool, the planner metadata) that are essentially never touched during ingest or query execution.
• The result. GC pauses drop to microseconds and are effectively invisible. The engine runs at the throughput a well-written C++ engine would — QuestDB benchmarks show it competing directly with ClickHouse and InfluxDB on wall-clock throughput.
• The trade-off. Off-heap code is harder to write and debug. Memory leaks in off-heap code look like Linux OOM kills, not JVM heap dumps. QuestDB pays this cost so downstream users don't have to.

Common interview probes on the QuestDB engine.

• "Why columnar for time-series?" — required answer: aggregation queries touch one column, not the whole row; storage bandwidth × CPU cache both benefit.
• "What does SIMD do here?" — required answer: process 4/8/16 values per CPU instruction via AVX/AVX2/AVX-512.
• "Why zero-GC?" — required answer: JVM GC pauses would drop rows under high-ingest load.
• "Why mmap?" — required answer: OS page cache decides what's in memory; engine reads via pointer arithmetic; zero-copy.
• "What's the ingest ceiling?" — required answer: ~1.4M rows/sec sustained on modest cloud hardware per instance.

Worked example — create a partitioned trades table and inspect the on-disk column files

Detailed explanation. The canonical way to internalise the columnar layout is to create a small table, insert a few rows, and inspect the on-disk files. Every senior DE should be able to do this by heart. Walk through the DDL, the insert, and the resulting file tree.

• Table. trades(ts TIMESTAMP, symbol SYMBOL, price DOUBLE, volume LONG, side SYMBOL), TIMESTAMP designation on ts, partition by DAY.
• Data. 3 rows across 2 partitions.
• Inspect. ls db/trades/ shows one folder per partition; ls db/trades/2026-07-27.0/ shows one file per column.

Question. Create the table, insert 3 rows, and describe the resulting on-disk file tree.

Input.

Row	ts	symbol	price	volume	side
1	2026-07-27 09:30:00	AAPL	180.5	100	B
2	2026-07-27 09:30:01	AAPL	180.6	200	S
3	2026-07-28 09:30:00	GOOG	145.2	50	B

Code.

-- 1. Create the columnar, time-partitioned trades table
CREATE TABLE trades (
    ts       TIMESTAMP,
    symbol   SYMBOL CAPACITY 4096 CACHE,
    price    DOUBLE,
    volume   LONG,
    side     SYMBOL CAPACITY 8 CACHE
) TIMESTAMP(ts) PARTITION BY DAY;

-- 2. Insert three rows across two partitions
INSERT INTO trades VALUES
  ('2026-07-27T09:30:00.000000Z', 'AAPL', 180.5, 100, 'B'),
  ('2026-07-27T09:30:01.000000Z', 'AAPL', 180.6, 200, 'S'),
  ('2026-07-28T09:30:00.000000Z', 'GOOG', 145.2, 50,  'B');

# 3. Inspect the resulting file tree (server root: /var/lib/questdb/db)
$ tree db/trades/
db/trades/
├── 2026-07-27.0/
│   ├── ts.d        # 8 bytes * 2 rows = 16 bytes  (nanosecond timestamps)
│   ├── symbol.d    # 4 bytes * 2 rows = 8  bytes  (symbol ids)
│   ├── symbol.k    # symbol dictionary key file
│   ├── symbol.v    # symbol dictionary value file (contains "AAPL")
│   ├── price.d     # 8 bytes * 2 rows = 16 bytes  (doubles)
│   ├── volume.d    # 8 bytes * 2 rows = 16 bytes  (longs)
│   ├── side.d      # 4 bytes * 2 rows = 8  bytes  (symbol ids)
│   ├── side.k      # side dictionary key file
│   └── side.v      # side dictionary value file (contains "B", "S")
├── 2026-07-28.0/
│   ├── ts.d        # 8 bytes * 1 row = 8 bytes
│   ├── symbol.d    # 4 bytes * 1 row = 4 bytes
│   ├── symbol.k
│   ├── symbol.v    # contains "GOOG"
│   ├── price.d
│   ├── volume.d
│   ├── side.d
│   ├── side.k
│   └── side.v
└── _txn            # transaction log for the whole table

Step-by-step explanation.

1. The CREATE TABLE statement declares five columns plus the TIMESTAMP(ts) designation and PARTITION BY DAY. The designation tells QuestDB which column is the time axis; range queries and partition pruning use this column. The partition unit (DAY, MONTH, YEAR) controls how big each on-disk folder gets.
2. On insert, QuestDB routes each row to the partition folder matching its timestamp — the two 2026-07-27 rows land in 2026-07-27.0/ and the one 2026-07-28 row lands in 2026-07-28.0/. The .0 suffix is a partition version; splits and merges bump this number.
3. Each column becomes its own file inside the partition folder. Fixed-width types (TIMESTAMP, DOUBLE, LONG) get a single .d file with sizeof(type) × rows bytes. Variable-width types (SYMBOL, STRING) get an additional .k (key) and .v (value) pair for dictionary encoding.
4. Symbol columns store the string once in .v and reference it by 32-bit id in .d. For a column with 4 distinct symbols across 100M rows, the naive storage would be ~400-800 MB (with per-row string headers); the dictionary storage is ~400 MB (4 bytes × 100M rows) + a tiny dictionary. On real fintech workloads with a few thousand tickers, this saves 10-100× storage.
5. The _txn file is the table's transaction log — every commit appends a record. It's what makes commits atomic across all column files and lets read-only queries see a consistent snapshot without blocking writers.

Output.

Partition	Rows	Total column-file size	Compression ratio vs row-store
2026-07-27.0	2	~80 bytes across 9 files	~10× smaller (dictionary + fixed-width)
2026-07-28.0	1	~40 bytes across 9 files	~10× smaller (dictionary + fixed-width)

Rule of thumb. For any QuestDB deployment, always declare TIMESTAMP(ts) on the time axis, always PARTITION BY DAY (or larger unit for slow tables), and always declare high-cardinality string columns as SYMBOL. Skipping any of these three leaves 5-10× throughput on the table.

Worked example — measuring the SIMD win with a SUM over 100M rows

Detailed explanation. The SIMD win is easy to demonstrate: create a 100M-row single-column table, run SUM(price), then compare against a scalar-only build or against a competing engine. Every senior architect should be able to reproduce this benchmark end-to-end. Walk through the setup and measurement.

• Table. bench(ts TIMESTAMP, price DOUBLE) with 100M rows across 100 daily partitions.
• Query. SELECT SUM(price) FROM bench; — should touch only the price.d file, ~800 MB total.
• Measurement. EXPLAIN shows the vectorised plan; timing shows sub-100ms wall-clock on a modern Xeon.

Question. Populate a 100M-row benchmark table and measure the SUM wall-clock, then explain what makes it fast.

Input.

Parameter	Value
Table	bench(ts TIMESTAMP, price DOUBLE)
Rows	100,000,000
Partitions	100 (one per day)
Total DOUBLE column size	~800 MB
CPU	24-core AMD EPYC (AVX-512)
Expected wall-clock	~30-50 ms

Code.

-- 1. Create the benchmark table
CREATE TABLE bench (
    ts    TIMESTAMP,
    price DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY;

-- 2. Populate with 100M synthetic rows via long_sequence + rnd_double
INSERT INTO bench
SELECT dateadd('s', x::int, '2026-04-18T00:00:00Z') AS ts,
       rnd_double() * 100                            AS price
FROM   long_sequence(100000000);

-- 3. Warm the OS page cache with a light scan
SELECT count(*) FROM bench;

-- 4. Run the SUM benchmark
SELECT SUM(price) FROM bench;
-- → ~40 ms wall-clock on 24-core EPYC

-- 5. Explain shows the vectorised aggregation plan
EXPLAIN SELECT SUM(price) FROM bench;
-- GroupByRecordCursorFactory
--   valueTypes: [DOUBLE]
--   filter: null
--   vectorized: true                    <-- the SIMD signal
--   dataFrameCursorFactory:
--     Row forward scan
--     Frame forward scan on: bench

# 6. Reproduce from Python via PG wire
import psycopg
import time

with psycopg.connect("host=questdb port=8812 dbname=qdb user=admin password=quest") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM bench")   # warm cache
        _ = cur.fetchone()

        t0 = time.perf_counter()
        cur.execute("SELECT SUM(price) FROM bench")
        result = cur.fetchone()
        t1 = time.perf_counter()

        print(f"SUM(price) over 100M rows: {result[0]:.2f} in {(t1-t0)*1000:.1f} ms")

Step-by-step explanation.

1. The CREATE TABLE + INSERT populates 100M synthetic rows using QuestDB's long_sequence(N) pseudo-table plus rnd_double() for the price. This is the standard QuestDB micro-benchmark pattern — no Python data generation needed.
2. The SELECT count(*) warms the OS page cache. The first count(*) takes ~200-400 ms because the kernel must actually read the columns from disk; the second and subsequent scans hit the page cache and run in single-digit milliseconds.
3. SELECT SUM(price) touches only price.d — the DOUBLE column file, roughly 800 MB on disk. The engine mmaps the file, reads it in AVX-512-sized chunks (64 bytes = 8 doubles per instruction), and accumulates the partial sums into 8 lanes of a single SIMD register. Final reduction combines the 8 lanes into a single scalar.
4. EXPLAIN reveals the vectorized: true flag on the aggregation plan — this is the confirmation that QuestDB picked the SIMD kernel. If your CPU lacks AVX support, the flag would be false and the query would fall back to scalar.
5. On a 24-core EPYC with AVX-512, the measured wall-clock is ~30-50 ms — a factor of 5-10× faster than a scalar implementation of the same query. The scan bandwidth is roughly at NVMe read speed (2-3 GB/sec), which means the engine is storage-bound not CPU-bound. This is the ideal shape for a well-tuned columnar aggregation.

Output.

Query	Wall-clock (AVX-512)	Wall-clock (scalar)	Speedup
SUM(price)	~40 ms	~250 ms	~6×
AVG(price)	~45 ms	~280 ms	~6×
MAX(price)	~35 ms	~200 ms	~6×
COUNT(*)	~10 ms	~10 ms	~1× (no data touch)

Rule of thumb. Whenever you write a QuestDB aggregation query, check EXPLAIN for vectorized: true. If the flag is false — usually because of a complex WHERE predicate, a non-aggregable type, or a window function — refactor the query to expose the SIMD-friendly pattern (aggregate first, then join or window on the aggregate).

Senior interview question on QuestDB storage + SIMD

A senior interviewer might ask: "You need to sustain 800k rows/sec of IoT sensor telemetry into QuestDB and query AVG(temperature) per hour per device across a 30-day retention window. Walk me through the schema design, the ingest path, the query pattern, and where the SIMD engine buys you throughput versus a naive Postgres design."

Solution Using time-partitioned columnar table, SYMBOL dictionary encoding, ILP ingest, and SAMPLE BY hourly aggregation

-- 1. Schema — TIMESTAMP designation, PARTITION BY DAY, SYMBOL for device_id + metric
CREATE TABLE sensor_metrics (
    ts          TIMESTAMP,
    device_id   SYMBOL CAPACITY 100000 CACHE,   -- ~100k devices
    metric      SYMBOL CAPACITY 32     CACHE,   -- {temperature, humidity, ...}
    value       DOUBLE,
    unit        SYMBOL CAPACITY 8      CACHE
) TIMESTAMP(ts) PARTITION BY DAY WAL;

-- 2. Ingest tuning — high-throughput knobs
ALTER TABLE sensor_metrics SET PARAM commitLag           = 2s;
ALTER TABLE sensor_metrics SET PARAM maxUncommittedRows  = 1000000;

-- 3. The money query — hourly AVG(temperature) per device, last 30 days
SELECT ts,
       device_id,
       AVG(value) AS avg_temp_c
FROM   sensor_metrics
WHERE  metric = 'temperature'
  AND  ts >= dateadd('d', -30, now())
SAMPLE BY 1h;

# 4. ILP producer — sustained 800k rows/sec across 16 threads
from questdb.ingress import Sender, TimestampNanos
import concurrent.futures

def produce_shard(shard_id, tick_stream, endpoint):
    with Sender.from_conf(f"tcp::addr={endpoint};auto_flush_rows=10000;auto_flush_interval=1000;") as sender:
        for reading in tick_stream:      # (device_id, metric, value, unit, ts_ns)
            device_id, metric, value, unit, ts_ns = reading
            sender.row(
                "sensor_metrics",
                symbols={"device_id": device_id, "metric": metric, "unit": unit},
                columns={"value": value},
                at=TimestampNanos(ts_ns),
            )

with concurrent.futures.ThreadPoolExecutor(max_workers=16) as ex:
    for shard_id in range(16):
        ex.submit(produce_shard, shard_id, shard_streams[shard_id], "questdb:9009")

-- 5. Retention — drop partitions older than 30 days (O(1))
ALTER TABLE sensor_metrics DROP PARTITION
  WHERE ts < dateadd('d', -30, now());

Step-by-step trace.

Step	Value	Reasoning
Ingest protocol	ILP TCP, 16 threads, 10k rows/batch	saturates NIC + writer worker pool
commit-lag	2s	absorbs late arrivals inside the O3 window
max-uncommitted-rows	1M	large batch commits amortise WAL cost
Table layout	5 files/partition × 30 partitions	~150 column files hot
SYMBOL cardinality	device_id 100k, metric 32	dictionary size fits in RAM
Aggregation kernel	SAMPLE BY 1h + AVG(value)	vectorised over value.d per partition
Retention	DROP PARTITION nightly	O(1) file-tree deletion

After deployment, the pipeline sustains 800k rows/sec into a single QuestDB node with ~40% CPU headroom; hourly AVG queries over the 30-day window return in ~150 ms; the _txn file remains under 1 MB because commits batch to 1M rows; partition drops complete in seconds regardless of retention size.

Output:

Metric	Value
Sustained ingest	800k rows/sec (57% engine ceiling)
Query p99 (hourly AVG, 30 days)	~150 ms
Storage per day	~28 GB (columnar + SYMBOL dict)
Retention drop	O(1) folder delete
CPU utilisation	~55% (ingest + query mixed)
Disk bandwidth	~1.5 GB/sec (well under NVMe ceiling)

Why this works — concept by concept:

• Column-per-file layout — the hourly AVG query touches only value.d (one column) across 30 partition folders (30 files). Total scanned bytes ≈ 30 × (rows/partition) × 8 bytes. On 800k rows/sec × 86400 sec × 30 days = 2 trillion rows total, but the query scans only 8 bytes/row × 720 hourly buckets after SAMPLE BY, so the actual data path is dominated by the aggregation kernel not the disk.
• SYMBOL dictionary encoding — 100k distinct device_id values encoded as 32-bit ids means the device_id.d file uses 4 bytes/row instead of the ~20 bytes/row a raw string would need. Storage drops by ~5×; L1/L2 cache pressure drops similarly, which speeds every downstream scan.
• O3 commit + commit-lag — high-throughput ingest inevitably produces some out-of-order rows (device clocks drift, network jitter, retry buffers). The commitLag = 2s window tells the engine to buffer commits for up to 2 seconds and sort within that window before writing to the partition. This keeps the on-disk timestamp order strictly monotonic without dropping rows.
• Vectorised AVG kernel — AVG(value) per SAMPLE BY bucket sums the DOUBLE column in AVX-512-wide chunks (8 doubles per instruction) then divides by the count at the end. The count itself comes from the row count per bucket — free, no data touch. Compared to a scalar loop this is ~6-8× faster.
• Cost — one 16-vCPU / 64-GB QuestDB node with a 4-TB NVMe (~$800/mo cloud), one QuestDB Cloud managed replica for HA (~$400/mo), one nightly partition-drop cron. Compared to a Postgres/TimescaleDB deployment that would need 4-8 nodes to hit the same ingest, this is 5-10× cheaper on hardware. Compared to a distributed ClickHouse cluster it's operationally simpler (no ZooKeeper/Keeper, no shard rebalance). O(1) per ingest row; O(hours × devices) per query, all vectorised.

Aggregation
Topic — aggregation
Aggregation problems on high-volume telemetry

Practice →

SQL Topic — sql SQL time-bucket and SAMPLE BY problems

Practice →

---

3. Ingestion protocols — ILP, PG-wire, REST

Three write paths, one commit pipeline — ILP for volume, PG wire for tools, REST for scripts

The mental model in one line: QuestDB exposes three ingestion protocols on three separate ports — the InfluxDB Line Protocol (ILP) on TCP/UDP 9009 for high-throughput, per-row line-oriented writes; the Postgres wire protocol on TCP 8812 for SQL-shaped INSERT statements and JDBC clients; and the HTTP REST endpoint on TCP 9000 for curl-friendly one-off writes and CSV imports — and all three funnel into the same O3 commit pipeline with the same commit-lag and max-uncommitted-rows tuning knobs. Every senior DE evaluating QuestDB must be able to pick the right protocol for each producer and explain the O3 commit strategy without prompting.

The three ingestion protocols.

• ILP (InfluxDB Line Protocol). The high-throughput write path. Line-oriented: one row per newline-terminated line, format table_name,tag1=v1,tag2=v2 field1=v1,field2=v2 <ts_ns>. Ships over TCP 9009 for reliable delivery or UDP 9009 for fire-and-forget (used by IoT devices that can't afford TCP overhead). Official client libraries in Python, Java, Go, Rust, C, C++, C#, Node.js, Julia. This is the protocol you use for anything above ~10k rows/sec.
• PG wire. The Postgres wire protocol on port 8812. Every psql, JDBC, DBeaver, Metabase, Grafana, Django, SQLAlchemy, Spring Data connection just works. INSERT INTO trades VALUES (...) from a Python script goes here. Not the fastest write path but the most compatible.
• REST /exec /imp. HTTP endpoints on port 9000. /exec runs a SQL statement and returns JSON — good for one-off inserts and interactive tools. /imp accepts CSV uploads for bulk file imports. Both are curl-friendly and require no client library.

The O3 commit strategy — how out-of-order writes are handled.

• The problem. Real-world time-series data is not strictly ordered by timestamp — device clocks drift, network jitter reorders packets, retry buffers commit out-of-order. A naive "reject any row older than the last committed row" policy drops data; a "sort every commit" policy is too slow.
• The solution. QuestDB buffers commits in an in-memory ring for up to commit-lag milliseconds (default 300ms, tunable per table). Rows that arrive within the lag window are sorted together and merged into the partition; rows that arrive after the lag window trigger a partition split-and-merge if they land in an older partition.
• The tuning knobs. commitLag (how long to buffer before commit — larger absorbs more out-of-order at the cost of freshness) and maxUncommittedRows (how many rows can accumulate before forcing a commit — larger amortises WAL cost but delays visibility). For fintech ticks with tight ordering, 500ms + 500k rows is typical; for IoT telemetry with looser ordering, 2s + 1M rows is typical.

ILP over TCP vs UDP — when to use each.

• TCP (9009). Reliable delivery with automatic retries. Every ILP client (Python questdb, Java questdb-client, etc.) uses TCP by default. Recommended for anything where dropped rows are unacceptable.
• UDP (9009). Fire-and-forget. Used by embedded IoT devices where the TCP handshake + retransmit overhead is too costly. Trades reliability for latency + resource usage. Requires application-level idempotency because packet loss is invisible.
• The rule. Always TCP unless you're on constrained hardware. QuestDB's TCP path is optimised enough that the difference is often <5% throughput.

Common interview probes on QuestDB ingestion.

• "Which protocol for 500k rows/sec?" — required answer: ILP over TCP with a client library.
• "How does QuestDB handle out-of-order writes?" — required answer: O3 commit with commitLag window.
• "What tuning knobs matter?" — required answer: commitLag and maxUncommittedRows on the table.
• "How do you dedupe on ingest?" — required answer: DEDUP clause on the table PARTITION BY specification.
• "Can I write from Python?" — required answer: pip install questdb; use Sender.from_conf(...).

Worked example — ILP producer + PG-wire reader end-to-end

Detailed explanation. The canonical two-role setup: a Python ILP producer writes sensor readings to sensor_metrics at 200k rows/sec, and a psql / JDBC analytics reader queries hourly aggregates over the same table. Every senior DE should be able to write both roles from memory. Walk through the setup.

• Writer role. Python + questdb client library, ILP over TCP 9009, batches of 10k rows, auto-flush every 1 second.
• Reader role. psql / DBeaver / Metabase, PG wire on 8812, SQL queries with SAMPLE BY and LATEST ON.
• Contract. Writer emits sensor_metrics(ts, device_id, metric, value, unit) — reader queries the same table without coordinating.

Question. Write the producer + reader end-to-end and show the freshness contract.

Input.

Component	Value
Writer	Python + questdb ILP
Writer rate	200k rows/sec
Writer batch	10k rows or 1s
Reader	psql (any PG client)
Reader freshness	sub-second (after commit-lag = 1s)

Code.

# 1. Writer — Python ILP producer
from questdb.ingress import Sender, TimestampNanos
import time

def sensor_stream():
    """Yield (device_id, metric, value, unit, ts_ns) tuples at 200k/sec."""
    while True:
        for i in range(200000):
            yield (f"device_{i % 1000}",
                   "temperature",
                   20.0 + (i % 100) * 0.1,
                   "celsius",
                   time.time_ns())
        time.sleep(1)

with Sender.from_conf(
    "tcp::addr=questdb:9009;auto_flush_rows=10000;auto_flush_interval=1000;"
) as sender:
    for device_id, metric, value, unit, ts_ns in sensor_stream():
        sender.row(
            "sensor_metrics",
            symbols={"device_id": device_id, "metric": metric, "unit": unit},
            columns={"value": value},
            at=TimestampNanos(ts_ns),
        )

-- 2. Reader — psql / DBeaver / Metabase — PG wire on port 8812
-- Latest reading per device
SELECT device_id, value, ts
FROM   sensor_metrics
WHERE  metric = 'temperature'
LATEST ON ts PARTITION BY device_id;

-- Hourly average per device over the last day
SELECT ts, device_id, AVG(value) AS avg_temp
FROM   sensor_metrics
WHERE  metric = 'temperature'
  AND  ts >= dateadd('h', -24, now())
SAMPLE BY 1h;

# 3. psql connection string — literally the same as Postgres
$ psql -h questdb -p 8812 -U admin -d qdb
Password for user admin:
psql (16.1, server 11.3 (QuestDB compat))
qdb=> \d sensor_metrics
                     Table "public.sensor_metrics"
   Column     |            Type             | Collation | Nullable
--------------+-----------------------------+-----------+---------
 ts           | timestamp without time zone |           |
 device_id    | symbol                      |           |
 metric       | symbol                      |           |
 value        | double precision            |           |
 unit         | symbol                      |           |

Step-by-step explanation.

1. The writer uses the questdb Python library configured via a connection string (tcp::addr=questdb:9009;auto_flush_rows=10000;auto_flush_interval=1000;). The auto_flush_rows and auto_flush_interval params control the client-side batching — the library buffers up to 10k rows or 1 second (whichever fires first) before flushing to the socket. This amortises TCP overhead.
2. sender.row(...) accepts symbols (dictionary-encoded string columns) and columns (typed value columns) plus the at= timestamp. The library serialises this into the on-wire ILP format (table_name,sym=v val=v <ts_ns>\n) and appends to the buffer.
3. On the QuestDB side, the ILP writer worker deserialises the lines and passes them through the O3 commit pipeline: buffer for commit-lag (1s default), sort by TIMESTAMP, merge into the partition file. Committed rows are visible to PG-wire queries within the lag window.
4. The reader connects via port 8812 with any PG client. The LATEST ON ts PARTITION BY device_id query returns the most recent row per device — a native QuestDB extension that would be a slow subquery in plain Postgres. The SAMPLE BY 1h query buckets rows into hourly windows — another native extension.
5. The psql -h questdb -p 8812 line is the punchline: nothing about the client-side connection changes from a plain Postgres. The server responds with a server 11.3 (QuestDB compat) banner but every SQL statement, prepared statement, and cursor works. Existing BI tools and ORMs are drop-in compatible.

Output.

Metric	Value
Writer rate	200k rows/sec
Client-side batching	10k rows / 1s
Server commit-lag	1s
End-to-end freshness	~1-2s
Reader connection	PG wire on port 8812
BI tool compatibility	Metabase / Grafana / Tableau — drop-in

Rule of thumb. For any QuestDB deployment, always use ILP for writes above ~10k rows/sec and PG wire for reads. Never write via PG-wire INSERT at scale — it works but is 5-10× slower than ILP for the same row rate. And never read via ILP; the protocol is write-only.

Worked example — tuning commit-lag and max-uncommitted-rows for different workloads

Detailed explanation. The O3 commit strategy has two tuning knobs — commitLag (buffer time before flushing) and maxUncommittedRows (row-count trigger for flush) — and picking them wrong costs either freshness or throughput. Walk through the tuning for three canonical workloads.

• Fintech ticks. Tight ordering, sub-second freshness required. commitLag = 500ms, maxUncommittedRows = 500k. Flushes on either trigger; typical latency 300-500ms.
• IoT telemetry. Loose ordering (device clock drift), seconds of freshness OK. commitLag = 2s, maxUncommittedRows = 1M. Larger batches amortise WAL cost.
• Adtech events. Bursty ingest, per-minute freshness OK. commitLag = 10s, maxUncommittedRows = 5M. Prioritises throughput over freshness.

Question. Set the tuning knobs for each workload and explain the trade-off.

Input.

Workload	commitLag	maxUncommittedRows	Freshness	Throughput priority
Fintech ticks	500ms	500,000	sub-second	high
IoT telemetry	2s	1,000,000	seconds	balanced
Adtech events	10s	5,000,000	~10s	maximum

Code.

-- Fintech ticks — sub-second freshness, tight ordering
CREATE TABLE trades (
    ts     TIMESTAMP,
    symbol SYMBOL,
    price  DOUBLE,
    volume LONG
) TIMESTAMP(ts) PARTITION BY DAY WAL;

ALTER TABLE trades SET PARAM commitLag = 500ms;
ALTER TABLE trades SET PARAM maxUncommittedRows = 500000;

-- IoT telemetry — loose ordering, larger batches
CREATE TABLE sensor_metrics (
    ts        TIMESTAMP,
    device_id SYMBOL,
    metric    SYMBOL,
    value     DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL;

ALTER TABLE sensor_metrics SET PARAM commitLag = 2s;
ALTER TABLE sensor_metrics SET PARAM maxUncommittedRows = 1000000;

-- Adtech events — bursty, throughput-first
CREATE TABLE ad_events (
    ts         TIMESTAMP,
    ad_id      SYMBOL,
    user_id    LONG,
    event_type SYMBOL,
    cost_cents LONG
) TIMESTAMP(ts) PARTITION BY DAY WAL;

ALTER TABLE ad_events SET PARAM commitLag = 10s;
ALTER TABLE ad_events SET PARAM maxUncommittedRows = 5000000;

Step-by-step explanation.

1. The two knobs are OR-triggered — whichever fires first causes a commit. commitLag is a wall-clock timer; maxUncommittedRows is a row-count trigger. Setting both means "flush at most every N milliseconds or every N rows, whichever comes sooner."
2. Fintech ticks need sub-second freshness for market-making + dashboards. 500ms lag caps latency; 500k rows is a reasonable batch that amortises the WAL cost without waiting so long that a burst backs up the client.
3. IoT telemetry can tolerate 2-second lag because devices report at ~1Hz per device and dashboards refresh at 5-second intervals. 2s lag lets more out-of-order arrivals settle into the same commit; 1M rows batches heavily for throughput.
4. Adtech events are the classic "we want max throughput and don't care about freshness for another 10 seconds" workload. 10s lag + 5M rows means each commit is a heavy write but the total commits per second are lower — the WAL cost per row is minimised.
5. The WAL clause on the CREATE TABLE enables the write-ahead log (default in QuestDB 7.3+); this decouples the ILP writer from the partition writer and is what makes the O3 commit strategy safe. Without WAL, a crash mid-commit would corrupt the partition; with WAL, replay recovers cleanly.

Output.

Workload	Freshness p99	Commit cost	WAL size (steady-state)
Fintech ticks	~500ms	~50k rows/commit	~40 MB
IoT telemetry	~2s	~200k rows/commit	~80 MB
Adtech events	~10s	~1M rows/commit	~400 MB

Rule of thumb. Never leave commitLag and maxUncommittedRows at defaults for a high-ingest table. Compute the acceptable freshness, set commitLag to that value, and set maxUncommittedRows to expected_rate × commitLag × safety_factor (typical factor: 2). Re-tune whenever the ingest rate doubles.

Senior interview question on QuestDB ingestion

A senior interviewer might ask: "You have a fleet of 50k IoT devices, each reporting temperature/humidity/pressure at 1Hz. Some devices go offline for hours and burst-retransmit when they reconnect. Design the QuestDB ingestion pipeline, pick the protocol, tune the commit strategy, and explain how you'd guarantee no rows are dropped even during a 4-hour device outage + burst-retransmit."

Solution Using ILP over TCP with commit-lag tuned for late arrivals + client-side idempotent retry

-- 1. Schema — WAL-enabled table with DEDUP on (ts, device_id, metric)
CREATE TABLE sensor_metrics (
    ts        TIMESTAMP,
    device_id SYMBOL CAPACITY 100000 CACHE,
    metric    SYMBOL CAPACITY 32     CACHE,
    value     DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, device_id, metric);

-- 2. Commit tuning — commit-lag large enough to catch bursty late arrivals
ALTER TABLE sensor_metrics SET PARAM commitLag           = 5s;
ALTER TABLE sensor_metrics SET PARAM maxUncommittedRows  = 2000000;

# 3. Client-side idempotent ILP producer with local buffer + retry
from questdb.ingress import Sender, TimestampNanos, IngressError
import time, collections

BUFFER_CAP = 1_000_000     # rows held on-disk before dropping
local_buffer = collections.deque(maxlen=BUFFER_CAP)

def producer_loop(device_reader):
    """Read from the device; buffer locally; flush to QuestDB with retry."""
    while True:
        try:
            with Sender.from_conf(
                "tcp::addr=questdb:9009;auto_flush_rows=10000;auto_flush_interval=1000;"
            ) as sender:
                # 1. Drain the local buffer first (catch-up after outage)
                while local_buffer:
                    reading = local_buffer.popleft()
                    _emit(sender, reading)

                # 2. Stream new readings
                for reading in device_reader:
                    _emit(sender, reading)

        except IngressError as e:
            # 3. Server unreachable / TCP reset — buffer locally, retry backoff
            print(f"ILP error {e}; buffering + retry in 5s")
            for reading in device_reader:
                if len(local_buffer) < BUFFER_CAP:
                    local_buffer.append(reading)
            time.sleep(5)


def _emit(sender, reading):
    device_id, metric, value, ts_ns = reading
    sender.row(
        "sensor_metrics",
        symbols={"device_id": device_id, "metric": metric},
        columns={"value": value},
        at=TimestampNanos(ts_ns),
    )

# 4. Device-side reconnect handling (embedded firmware pseudocode)
def device_loop():
    """On device: buffer to flash if offline; drain on reconnect."""
    flash_buffer = FlashBuffer(capacity_mb=100)   # ~50k rows

    while True:
        reading = sample_sensor()
        try:
            send_to_gateway(reading)   # gateway then does the ILP producer above
        except NetworkError:
            flash_buffer.append(reading)

        if network_online() and not flash_buffer.empty():
            for buffered in flash_buffer.drain():
                send_to_gateway(buffered)

Step-by-step trace.

Layer	Component	Purpose
Device firmware	flash buffer + reconnect drain	survive network outages
Gateway	Python ILP producer + local queue	absorb server outages
Wire	ILP TCP 9009 with retry backoff	reliable delivery
QuestDB WAL	write-ahead log per commit	crash-safe partition writes
Commit pipeline	commit-lag = 5s, max-uncommitted = 2M	absorb 5s of out-of-order
DEDUP UPSERT	primary key (ts, device_id, metric)	drop duplicates on retry storms
Retention	DROP PARTITION nightly	O(1) archival

After deployment, a 4-hour device outage results in ~14,400 buffered readings per device on the device's flash storage; on reconnect, the device drains its flash to the gateway; the gateway drains to QuestDB via ILP; the 5-second commitLag absorbs the out-of-order arrival of readings across multiple devices reconnecting simultaneously; the DEDUP UPSERT clause guarantees no double-counting even if the gateway retries a batch that partially landed.

Output:

Failure scenario	Buffer point	Recovery
Device offline 4h	Flash buffer (100MB)	Drain on reconnect
Gateway offline 1h	Gateway RAM buffer (1M rows)	Retry with backoff
QuestDB restart	WAL replay	Zero rows lost
ILP TCP reset	Client retry with backoff	Same as gateway offline
Duplicate delivery	DEDUP UPSERT KEYS	Silent replace

Why this works — concept by concept:

• ILP over TCP with client-side buffering — the TCP protocol handles per-connection retries automatically; the client-side buffer handles server-side outages that outlast TCP's retry window. Together they cover every failure mode where the network exists but QuestDB doesn't respond immediately.
• WAL (write-ahead log) — every commit lands first in the WAL, then applies to the partition. A crash mid-commit replays the WAL on restart and re-applies; no partial writes. This is what makes the commit-lag buffer safe — the buffer plus the WAL together bracket the durability contract.
• commit-lag = 5s absorbs late arrivals — bursty reconnects produce writes with timestamps from hours ago. The 5-second commit window is not the tolerance for old timestamps (the O3 engine handles arbitrarily old timestamps by triggering a partition split-and-merge) — it's the tolerance for within-batch out-of-order, which is what actually costs performance.
• DEDUP UPSERT KEYS(ts, device_id, metric) — declares (ts, device_id, metric) as the natural primary key. On insert, if a row with the same key already exists, QuestDB upserts (replaces) rather than appending a duplicate. This makes retries idempotent without any application-side dedup logic. Available in QuestDB 7.3+ on WAL tables.
• Cost — one QuestDB node (16-vCPU / 64-GB, ~$800/mo), one 100MB flash buffer per device (~$0), one gateway process per site (~$50/mo VPS). Compared to a Kafka + Flink + downstream sink stack that would need 3-5 stateful services to guarantee no drops, this is 10× simpler and cheaper. O(1) per ingest row; O(1) per DEDUP UPSERT check thanks to the sorted partition layout.

SQL
Topic — sql
SQL ingestion and dedup problems

Practice →

Aggregation Topic — aggregation Aggregation problems on retry-heavy streams

Practice →

---

4. SQL extensions for time-series

SAMPLE BY, LATEST ON, ASOF JOIN, FILL — four primitives that make QuestDB's dialect strictly more expressive than plain Postgres

The mental model in one line: QuestDB's SQL dialect is standard SQL plus four time-series-native primitives — SAMPLE BY for time-bucket aggregation (no more GROUP BY floor(ts / interval)), LATEST ON for the newest snapshot per key (no more DISTINCT ON with correlated subqueries), ASOF JOIN for nearest-earlier alignment of two time-series (no more LATERAL gymnastics), and FILL(NULL|PREV|LINEAR|N) for gap-filling missing buckets — and every senior DE writing time-series analytics must be able to reach for each primitive without hesitation. These extensions are what elevate QuestDB's dialect above TimescaleDB's time_bucket() and InfluxQL's GROUP BY time().

The four time-series primitives.

• SAMPLE BY <interval>. Time-bucket aggregation. Replaces GROUP BY floor(EXTRACT(EPOCH FROM ts) / 60) * 60 with SAMPLE BY 1m. Supports any interval (1s, 1m, 5m, 1h, 1d, 1M, 1y). Always paired with an aggregation function (AVG, SUM, COUNT, MIN, MAX) and the TIMESTAMP column.
• LATEST ON <ts_col> PARTITION BY <key>. Newest row per key. Replaces SELECT DISTINCT ON (key) * FROM t ORDER BY key, ts DESC (Postgres) with a single-line QuestDB primitive. Optimised via the sorted TIMESTAMP designation — O(1) per key rather than a full scan.
• ASOF JOIN ... ON <key>. Align two time-series on nearest-earlier timestamp per key. Replaces a LATERAL correlated subquery in Postgres. Semantics: for each row in the left table, find the most-recent row in the right table with right.ts <= left.ts matching the join key. Foundational for financial workloads (trade × quote) and IoT (event × device state).
• FILL(NULL|PREV|LINEAR|N). Gap-fill missing buckets in a SAMPLE BY result. NULL leaves gaps as NULL; PREV carries forward the previous bucket's value; LINEAR interpolates linearly between neighbouring buckets; N fills with a constant. Without FILL, missing buckets are simply absent from the result set.

How SAMPLE BY beats manual bucket arithmetic.

• Postgres pattern. SELECT date_trunc('minute', ts) AS bucket, AVG(value) FROM t GROUP BY bucket ORDER BY bucket — requires date_trunc or floor() gymnastics, doesn't align buckets to a specific origin, doesn't fill gaps.
• TimescaleDB pattern. SELECT time_bucket('1 minute', ts) AS bucket, AVG(value) FROM t GROUP BY bucket — cleaner, but still requires LEFT JOIN generate_series() for gap-filling.
• InfluxQL pattern. SELECT MEAN(value) FROM t WHERE ... GROUP BY time(1m) FILL(previous) — first mainstream implementation of time-bucketed aggregation with fill.
• QuestDB pattern. SELECT ts, AVG(value) FROM t SAMPLE BY 1m FILL(PREV) — same expressiveness as InfluxQL but in standard SQL grammar.

ASOF JOIN semantics — the fundamental time-series join.

• The problem. Given trades(ts, symbol, price) and quotes(ts, symbol, bid, ask), for each trade find the most-recent quote on the same symbol at or before the trade's timestamp. This is the base case for computing implied spread, slippage, or price-improvement stats.
• The naive SQL. In plain Postgres: SELECT t.*, (SELECT bid FROM quotes q WHERE q.symbol = t.symbol AND q.ts <= t.ts ORDER BY q.ts DESC LIMIT 1) FROM trades t — a correlated subquery per row, O(N × log M) with a good index.
• The QuestDB primitive. SELECT t.*, q.bid, q.ask FROM trades t ASOF JOIN quotes q ON t.symbol = q.symbol — a single merge-walk of two already-sorted streams, O(N + M).
• The speedup. For N = 10M trades, M = 5M quotes, the naive subquery in Postgres takes ~30 seconds; the ASOF JOIN in QuestDB takes ~80ms. A ~400× speedup for the primitive that dominates every fintech reconciliation query.

FILL modes and when to use each.

• FILL(NULL). Explicit gaps. Useful when the consuming dashboard should render gaps as broken lines.
• FILL(PREV). Carry-forward. Useful when the metric is a state (device status, book price) that stays valid until the next update.
• FILL(LINEAR). Straight-line interpolation. Useful for slowly-varying signals (temperature, pressure) where linear interpolation is a reasonable approximation.
• FILL(N). Constant fill. Useful when a missing bucket has a known default (e.g., FILL(0) for a count of events per minute — no events means zero, not missing).

Common interview probes on QuestDB SQL.

• "How do you time-bucket in QuestDB?" — required answer: SAMPLE BY 1m (or the appropriate interval).
• "What does ASOF JOIN do?" — required answer: nearest-earlier alignment of two time-series on a join key.
• "How do you get the latest row per key?" — required answer: LATEST ON ts PARTITION BY key.
• "How do you handle gaps in a SAMPLE BY result?" — required answer: FILL(NULL|PREV|LINEAR|N).
• "How does SAMPLE BY compare to TimescaleDB time_bucket?" — required answer: same semantics, cleaner syntax, native FILL (Timescale requires LEFT JOIN generate_series).

Worked example — intraday VWAP per symbol via SAMPLE BY 1m + FILL(PREV)

Detailed explanation. The canonical time-series aggregation: volume-weighted average price (VWAP) per symbol per minute, with gap-filling for minutes with no trades. Every fintech dashboard has some variant of this query. Walk through the QuestDB one-liner and compare against the Postgres equivalent.

• Input. trades(ts, symbol, price, volume) with ~1M trades/day.
• Output. Per-symbol VWAP per minute for one trading day, with FILL(PREV) so quiet minutes carry the previous VWAP forward.
• Speedup. ~10ms in QuestDB vs ~2s in Postgres for the same data.

Question. Write the intraday VWAP query in QuestDB SQL and explain each clause.

Input.

Column	Type	Purpose
ts	TIMESTAMP	trade timestamp (nanosecond)
symbol	SYMBOL	ticker symbol
price	DOUBLE	trade price
volume	LONG	trade size in shares

Code.

-- Intraday VWAP per symbol per minute, one trading day, gap-filled
SELECT ts,
       symbol,
       sum(price * volume) / sum(volume) AS vwap,
       sum(volume)                        AS total_volume,
       count(*)                           AS trade_count
FROM   trades
WHERE  ts IN '2026-07-27'
SAMPLE BY 1m
FILL(PREV);

-- Equivalent in plain Postgres (for comparison — no FILL)
SELECT date_trunc('minute', ts)                                  AS ts,
       symbol,
       SUM(price * volume)::DOUBLE PRECISION / NULLIF(SUM(volume), 0) AS vwap,
       SUM(volume)                                                AS total_volume,
       COUNT(*)                                                   AS trade_count
FROM   trades
WHERE  ts >= '2026-07-27' AND ts < '2026-07-28'
GROUP  BY date_trunc('minute', ts), symbol
ORDER  BY date_trunc('minute', ts), symbol;
-- To get FILL(PREV), add a LEFT JOIN generate_series + LAG() — ~20 more lines

Step-by-step explanation.

1. WHERE ts IN '2026-07-27' is QuestDB shorthand for ts >= '2026-07-27' AND ts < '2026-07-28' — the IN <partition> syntax is a partition-pruning optimisation. The query only touches the 2026-07-27.0/ partition folder, not the whole table.
2. SAMPLE BY 1m buckets rows into 1-minute windows aligned to the epoch (or a specified ALIGN TO CALENDAR origin). This replaces the date_trunc('minute', ts) + GROUP BY clause in Postgres. Every aggregation function in the SELECT list is computed per (bucket × symbol).
3. sum(price * volume) / sum(volume) is the standard VWAP formula: total dollar volume divided by total share volume. QuestDB's SIMD kernels compute both sums in one pass over the price.d and volume.d files, then divide at the end.
4. FILL(PREV) fills minutes with no trades by carrying forward the previous minute's VWAP for each symbol. Without this, quiet minutes are simply absent from the result and downstream dashboards render broken lines. FILL(NULL) would emit explicit NULLs; FILL(0) would emit zero.
5. The result set is sorted by (ts, symbol) automatically — QuestDB's SAMPLE BY guarantees bucket order. No ORDER BY needed.

Output.

ts	symbol	vwap	total_volume	trade_count
2026-07-27 09:30:00	AAPL	180.55	15,200	87
2026-07-27 09:30:00	GOOG	145.23	8,400	42
2026-07-27 09:31:00	AAPL	180.61	22,100	132
2026-07-27 09:31:00	GOOG	145.28	12,300	68
2026-07-27 09:32:00	AAPL	180.61	22,100	132
2026-07-27 09:32:00	GOOG	145.34	15,700	91

Rule of thumb. For any per-minute-or-larger aggregation over a QuestDB TIMESTAMP-designated table, reach for SAMPLE BY immediately. Never manually GROUP BY date_trunc(...) — it works but ignores the engine's partition-pruning and vectorised-aggregation paths.

Worked example — ASOF JOIN of trades against the most-recent quote per symbol

Detailed explanation. The foundational fintech query: for each trade, find the most-recent bid/ask on the same symbol at or before the trade's timestamp. This computes the implied spread paid on each trade. In plain Postgres this is a correlated LATERAL subquery per row; in QuestDB it's a single-line ASOF JOIN.

• Left table. trades(ts, symbol, price, volume).
• Right table. quotes(ts, symbol, bid, ask).
• Join semantic. For each trade, the most-recent quote with quotes.ts <= trades.ts matching symbol.
• Result. One row per trade with the aligned bid/ask + implied spread.

Question. Write the ASOF JOIN query and explain why it is O(N + M) instead of O(N × log M).

Input.

Table	Rows	Column shape
trades	10M	(ts, symbol, price, volume)
quotes	5M	(ts, symbol, bid, ask)

Code.

-- ASOF JOIN — the fundamental time-series join
SELECT t.ts,
       t.symbol,
       t.price,
       t.volume,
       q.bid,
       q.ask,
       (q.ask - q.bid)                          AS spread,
       (t.price - (q.bid + q.ask) / 2.0)        AS price_vs_mid
FROM   trades t
ASOF JOIN quotes q ON t.symbol = q.symbol
WHERE  t.ts IN '2026-07-27';

-- Equivalent in plain Postgres — correlated LATERAL subquery per row
SELECT t.ts,
       t.symbol,
       t.price,
       t.volume,
       q.bid,
       q.ask,
       (q.ask - q.bid)                          AS spread,
       (t.price - (q.bid + q.ask) / 2.0)        AS price_vs_mid
FROM   trades t
LEFT   JOIN LATERAL (
    SELECT bid, ask
    FROM   quotes
    WHERE  quotes.symbol = t.symbol
      AND  quotes.ts    <= t.ts
    ORDER  BY quotes.ts DESC
    LIMIT  1
) q ON TRUE
WHERE  t.ts >= '2026-07-27' AND t.ts < '2026-07-28';
-- Wall-clock: ~30 seconds for 10M x 5M on Postgres 16 with (symbol, ts DESC) index
-- Wall-clock: ~80 ms   for 10M x 5M on QuestDB           (native ASOF)

Step-by-step explanation.

1. The ASOF JOIN clause tells QuestDB to align the two time-series on nearest-earlier timestamp per join key. The join key is t.symbol = q.symbol; the alignment is on the TIMESTAMP columns of both tables (implicit — no explicit ON ts <= ts needed).
2. QuestDB implements the join as a two-pointer merge walk: both tables are already sorted by (symbol, ts) thanks to the TIMESTAMP designation, so the engine advances two cursors in lock-step and matches each left-row against the highest right-row whose ts <= left.ts. Complexity is O(N + M), not O(N × log M).
3. The Postgres equivalent uses LATERAL correlated subquery per row: for each of 10M trades, seek into the quotes table for the max ts matching symbol and ts <= t.ts. With an ideal index this is ~O(log M) per row → O(N × log M) total. Wall-clock: ~30 seconds.
4. QuestDB's ASOF JOIN handles the "no matching right row" case by emitting NULL for the right columns — semantically identical to LEFT JOIN. If a right table has no rows earlier than a given left row, the left row still appears with NULL right-columns.
5. The spread and price-vs-mid expressions in the SELECT list are trivial arithmetic on top of the joined row. QuestDB computes them in the projection stage after the join — no additional pass over the data.

Output.

ts	symbol	price	volume	bid	ask	spread	price_vs_mid
2026-07-27 09:30:00.001	AAPL	180.50	100	180.48	180.52	0.04	0.00
2026-07-27 09:30:00.003	AAPL	180.55	200	180.52	180.58	0.06	0.00
2026-07-27 09:30:01.002	GOOG	145.23	50	145.20	145.26	0.06	0.00
2026-07-27 09:30:02.005	AAPL	180.60	150	180.58	180.62	0.04	0.00

Rule of thumb. For any query that aligns a stream of events against a slowly-updating reference series (trades × quotes, orders × prices, sensor readings × calibration constants), reach for ASOF JOIN in QuestDB. The 100-400× speedup over the correlated-subquery equivalent is a large fraction of why senior fintech engineers pick QuestDB in the first place.

Worked example — LATEST ON for per-device current-state snapshots

Detailed explanation. The dashboard query: "show me the current temperature for every device." This is a "newest row per key" query — trivial to state but famously slow in vanilla Postgres. QuestDB's LATEST ON primitive handles it in one line. Walk through the query and the plain-Postgres equivalent.

• Input. sensor_metrics(ts, device_id, metric, value) with 100M rows across 100k devices.
• Output. One row per (device_id, metric) with the most-recent value.
• Wall-clock. ~50ms in QuestDB; ~2s in Postgres with DISTINCT ON.

Question. Write the LATEST ON query and compare it to the Postgres DISTINCT ON equivalent.

Input.

Column	Type
ts	TIMESTAMP
device_id	SYMBOL
metric	SYMBOL
value	DOUBLE

Code.

-- Newest reading per (device_id, metric) — one line
SELECT ts, device_id, metric, value
FROM   sensor_metrics
LATEST ON ts PARTITION BY device_id, metric;

-- Equivalent in plain Postgres
SELECT DISTINCT ON (device_id, metric) ts, device_id, metric, value
FROM   sensor_metrics
ORDER  BY device_id, metric, ts DESC;
-- Requires (device_id, metric, ts DESC) index to be fast
-- Wall-clock: ~2 s on 100M rows / 100k devices even with the ideal index

Step-by-step explanation.

1. LATEST ON ts PARTITION BY device_id, metric tells QuestDB to return the newest row per (device_id, metric) group, using the TIMESTAMP-designated ts column as the recency axis. The engine walks each partition from newest to oldest, emitting the first hit per group and then skipping the rest.
2. Because the TIMESTAMP designation guarantees rows within each partition are stored in TIMESTAMP order, the walk is O(distinct keys) not O(rows). For 100k devices × 32 metrics, the engine emits 3.2M rows after touching only the newest slice of each partition.
3. The Postgres equivalent uses DISTINCT ON (device_id, metric) ... ORDER BY device_id, metric, ts DESC. This works but requires the exact (device_id, metric, ts DESC) index to avoid a full sort; even with the index, the query planner must walk 100M rows to identify the first row per group.
4. On the same hardware, QuestDB's LATEST ON returns in ~50ms; Postgres's DISTINCT ON returns in ~2s. The difference is the columnar + partitioned layout — QuestDB touches ~1% of the data because it knows where the newest rows live.
5. LATEST ON also composes with WHERE filters: SELECT ... FROM sensor_metrics WHERE metric = 'temperature' LATEST ON ts PARTITION BY device_id returns the newest temperature per device only — the predicate is pushed into the partition scan.

Output.

ts	device_id	metric	value
2026-07-27 15:45:22	device_0001	temperature	22.4
2026-07-27 15:45:21	device_0001	humidity	45.2
2026-07-27 15:45:23	device_0002	temperature	24.1
2026-07-27 15:45:20	device_0002	humidity	42.8

Rule of thumb. For any "current state per key" query in QuestDB, reach for LATEST ON — never write a DISTINCT ON or a window-function-plus-filter equivalent. The primitive is dramatically faster because the storage engine's per-partition sort makes it O(distinct keys) rather than O(rows).

Senior interview question on QuestDB SQL extensions

A senior interviewer might ask: "You need a Grafana dashboard showing per-minute VWAP per symbol for the trading day, joined against the current market spread from the quote stream. The result must be gap-filled so quiet minutes render as flat lines. Write the QuestDB SQL and explain how each clause maps to a specific engine feature."

Solution Using SAMPLE BY 1m + FILL(PREV) + ASOF JOIN quotes for spread alignment

-- 1. Per-minute VWAP per symbol with gap-fill
WITH vwap AS (
    SELECT ts,
           symbol,
           sum(price * volume) / sum(volume) AS vwap,
           sum(volume)                        AS minute_volume
    FROM   trades
    WHERE  ts IN '2026-07-27'
    SAMPLE BY 1m
    FILL(PREV)
),
-- 2. Latest quote per symbol at each minute boundary
minute_quotes AS (
    SELECT ts,
           symbol,
           bid,
           ask,
           (ask - bid) AS spread
    FROM   quotes
    WHERE  ts IN '2026-07-27'
    SAMPLE BY 1m
    FILL(PREV)
)
-- 3. Align VWAP against the latest quote per (symbol, minute)
SELECT v.ts,
       v.symbol,
       v.vwap,
       v.minute_volume,
       q.bid,
       q.ask,
       q.spread,
       (v.vwap - (q.bid + q.ask) / 2.0) AS vwap_vs_mid
FROM   vwap v
ASOF JOIN minute_quotes q ON v.symbol = q.symbol
ORDER  BY v.ts, v.symbol;

-- 4. Grafana query variant — parametrise the date via $__timeFrom / $__timeTo
WITH vwap AS (
    SELECT ts, symbol,
           sum(price * volume) / sum(volume) AS vwap,
           sum(volume) AS minute_volume
    FROM   trades
    WHERE  ts BETWEEN $__timeFrom() AND $__timeTo()
    SAMPLE BY 1m
    FILL(PREV)
)
SELECT ts, symbol, vwap, minute_volume
FROM   vwap
ORDER  BY ts, symbol;

Step-by-step trace.

Step	Clause	Engine feature
Partition prune	WHERE ts IN '2026-07-27'	one-day partition folder only
Time-bucket	SAMPLE BY 1m	vectorised aggregation per bucket
Gap-fill	FILL(PREV)	carry-forward for quiet minutes
VWAP formula	sum(price*volume) / sum(volume)	SIMD kernel over two columns
Alignment	ASOF JOIN	merge-walk on pre-sorted streams
Join key	ON v.symbol = q.symbol	SYMBOL dictionary-encoded compare
Projection	vwap_vs_mid	scalar arithmetic after join

After deployment, the Grafana dashboard renders per-minute VWAP + spread across the trading day in ~180ms wall-clock; missing minutes are gap-filled with the previous VWAP and previous quote; the query is memory-flat because both CTEs are streaming aggregations, not materialised sets.

Output:

ts	symbol	vwap	minute_volume	bid	ask	spread	vwap_vs_mid
2026-07-27 09:30	AAPL	180.55	15,200	180.48	180.52	0.04	0.05
2026-07-27 09:31	AAPL	180.61	22,100	180.58	180.62	0.04	0.01
2026-07-27 09:32	AAPL	180.61	22,100	180.60	180.64	0.04	-0.01
2026-07-27 09:30	GOOG	145.23	8,400	145.20	145.26	0.06	0.00
2026-07-27 09:31	GOOG	145.28	12,300	145.24	145.32	0.08	0.00

Why this works — concept by concept:

• SAMPLE BY 1m + FILL(PREV) — one primitive collapses the equivalent of date_trunc + GROUP BY + LEFT JOIN generate_series + LAG(). The engine allocates one output row per (bucket × symbol), streams the input in TIMESTAMP order, and emits gap-filled rows using the last observed value per key.
• ASOF JOIN on pre-sorted streams — both CTEs emit rows in (symbol, ts) order because SAMPLE BY guarantees the output order. The ASOF JOIN kernel is then a two-pointer merge walk — the fastest possible join for time-aligned streams. No hash-join memory; no sort step.
• Partition pruning via ts IN '2026-07-27' — the WHERE clause is understood by the query planner as a partition-prune directive. The engine opens only the 2026-07-27.0/ folder and skips the other 30 partitions. Storage bandwidth drops by the partition-count factor.
• SYMBOL dictionary-encoded join key — the ON v.symbol = q.symbol compare operates on 4-byte SYMBOL ids, not variable-length strings. Cache pressure drops; the compare is a single register-register instruction per row.
• Cost — one QuestDB node scan of trades and quotes for one day (~1M and ~500k rows respectively), one merge-walk join. Total wall-clock ~180ms for the Grafana refresh. The eliminated cost is the Postgres approach (VWAP as a windowed function + LATERAL subquery for the quote = ~30 seconds per refresh). O(rows) streaming; O(rows) memory-flat aggregation; O(N + M) join.

Joins
Topic — joins
Joins problems on time-aligned streams

Practice →

SQL Topic — sql SQL problems on time-bucket + gap-fill patterns

Practice →

---

5. When to pick QuestDB + interview signals

The decision matrix — QuestDB wins single-node ultra-high ingest + PG SQL; TimescaleDB, InfluxDB 3, ClickHouse win on different axes

The mental model in one line: the 2026 time-series database landscape has four canonical answers — QuestDB for single-node ultra-high ingest with Postgres SQL and time-series-native primitives, TimescaleDB for teams that already live in Postgres and want hypertables + continuous aggregates without adopting a new database, InfluxDB 3 for object-storage-native lakehouse workloads with Rust + Arrow + DataFusion under the hood, and ClickHouse for multi-node OLAP + high-cardinality analytics workloads that happen to include time-series — and the senior interview signal is being able to name all four and their winning axes in a single 30-second monologue. Every senior architect should be able to pick the engine, defend the choice against the other three, and enumerate the five interview probes that come next.

When QuestDB wins.

• Ultra-high single-node ingest with SQL. 500k-1.4M rows/sec sustained on a modest cloud box, with the query dialect being standard SQL plus time-series extensions. No competing engine gives you both.
• Postgres wire compatibility. Every psql, JDBC, Metabase, Grafana, Tableau, Django, SQLAlchemy just works. Onboarding is minutes not weeks.
• ASOF JOIN as a first-class primitive. For any workload that aligns two time-series (trades × quotes, orders × prices, events × device state), ASOF JOIN is 100-400× faster than the LATERAL-subquery equivalent.
• Operational simplicity. Single-node deployment plus QuestDB Cloud for HA. No ZooKeeper, no shard rebalance, no distributed transaction coordinator.

When TimescaleDB wins.

• Postgres-first teams. If the team already runs Postgres with Patroni HA, TimescaleDB is a CREATE EXTENSION away. Every Postgres feature (transactions, foreign keys, PL/pgSQL, other extensions like PostGIS) still works.
• Continuous aggregates. Materialised rollups that refresh incrementally as new data arrives. Great for dashboards where the aggregation is deterministic and the source is high-frequency.
• Rich Postgres ecosystem. pg_partman, pg_cron, PostGIS, pg_stat_statements — every Postgres tool works. TimescaleDB is actually Postgres, not a Postgres-compatible layer.

When InfluxDB 3 wins.

• Object-storage-native lakehouse. InfluxDB 3 stores data as Parquet on S3 (or Azure Blob, or GCS). Zero-copy interop with Iceberg-shaped lakehouse tools. Rust + Apache Arrow + DataFusion under the hood.
• Multi-tenant SaaS ingest. InfluxDB Cloud Serverless bills per-write and scales elastically. Good fit for platforms that ingest customer telemetry at variable rates.
• Line-protocol-native ingest. InfluxDB invented ILP; every ILP client in the wild works against InfluxDB with zero changes.

When ClickHouse wins.

• Multi-node OLAP. ClickHouse's shard + replica model scales linearly across dozens of nodes. QuestDB is intentionally single-node; ClickHouse is intentionally distributed.
• High-cardinality analytics. Columnar storage + vectorised execution + per-column codecs make ClickHouse very fast for queries with millions of distinct group-by keys.
• Materialized views + AggregatingMergeTree. ClickHouse's materialised view engine is production-hardened for rollup patterns.

The five interview probes on QuestDB (senior).

• SIMD engine. Explain the column-per-file layout, the vectorised aggregation kernels, and the AVX-512 dispatch. Name the ~1.4M rows/sec figure with the "modest cloud hardware, TSBS-verifiable" caveat.
• ILP protocol. Name the port (9009 TCP or UDP), the line format (table,tag=v field=v <ts_ns>), and the O3 commit strategy with commitLag and maxUncommittedRows tuning knobs.
• ASOF JOIN semantics. Nearest-earlier alignment; O(N + M) via merge-walk on pre-sorted streams; the 100-400× speedup vs the correlated LATERAL subquery in Postgres.
• O3 commit strategy. Buffer within the commitLag window; sort within batch; merge into partition; DEDUP UPSERT for idempotent retries; WAL for crash safety.
• When to pick QuestDB. The four-axis matrix answer above. Name three alternatives with their winning axes.

Common interview probes at the strategic level.

• "Why not just use Postgres + TimescaleDB?" — required answer: single-node ingest ceiling ~200k rows/sec; QuestDB is ~10× that.
• "Why not just use ClickHouse?" — required answer: ClickHouse is multi-node-first; QuestDB is single-node-first; for our workload (single region, <2M rows/sec) the operational cost is 10× lower.
• "Why not InfluxDB 3?" — required answer: InfluxDB 3 is object-storage-native (Parquet on S3); QuestDB is single-node with local NVMe. Pick per lakehouse-tie requirement.
• "Is QuestDB production-ready in 2026?" — required answer: yes; QuestDB Cloud has been GA since 2023; deployed at Airbus, Aquis, and others.

Worked example — the four-engine decision matrix for three workloads

Detailed explanation. Given three realistic time-series workloads, run the decision matrix and defend the pick. Every senior interview eventually reduces to some variant of this — the interviewer describes a workload and you name the engine. Practice the shape.

• Workload A. Fintech tick store, 500k rows/sec, single-region, Postgres BI tools, small team.
• Workload B. Cloud observability platform, multi-tenant, 5M rows/sec aggregate across tenants, Parquet-on-S3 storage tier.
• Workload C. Existing Postgres shop with Patroni HA, wants to add time-series superpowers without a new DB.

Question. Match each workload to the winning engine and explain the axis that decides it.

Input.

Workload	Ingest rate	SQL need	Storage tier	Team profile
A. Fintech ticks	500k/sec	PG BI tools	local NVMe	small; Postgres-experienced
B. Cloud observability	5M/sec aggregate	SQL + Parquet interop	S3	Rust-comfortable; multi-tenant
C. Postgres shop + TS	~50k/sec	full Postgres	Postgres storage	Postgres-only

Code.

# Simplified matcher for the three scenarios
def match_engine(workload):
    if workload["ingest"] > 2_000_000:
        return "ClickHouse (multi-node)" if workload["storage"] != "s3" else "InfluxDB 3"
    if workload["storage"] == "s3":
        return "InfluxDB 3 (object-storage native)"
    if workload["team"] == "postgres-only":
        return "TimescaleDB (zero migration)"
    return "QuestDB (single-node + PG wire)"


workloads = [
    {"name": "A. Fintech ticks",      "ingest": 500_000,   "storage": "nvme", "team": "postgres-experienced"},
    {"name": "B. Cloud observability","ingest": 5_000_000, "storage": "s3",   "team": "rust-comfortable"},
    {"name": "C. Postgres shop + TS", "ingest": 50_000,    "storage": "pg",   "team": "postgres-only"},
]

for w in workloads:
    print(f"{w['name']}: {match_engine(w)}")

# A. Fintech ticks: QuestDB (single-node + PG wire)
# B. Cloud observability: InfluxDB 3 (object-storage native)
# C. Postgres shop + TS: TimescaleDB (zero migration)

Step-by-step explanation.

1. Workload A (fintech ticks). 500k rows/sec is well within QuestDB's single-node ceiling (~1.4M). PG wire matches the BI-tool requirement. ASOF JOIN handles the trade-vs-quote alignment natively. Winner: QuestDB.
2. Workload B (cloud observability). 5M rows/sec aggregate across tenants — this is over QuestDB's single-node ceiling; you'd need to shard by tenant at the application layer. But the "Parquet on S3" storage tier is InfluxDB 3's native shape. Winner: InfluxDB 3.
3. Workload C (Postgres shop). 50k rows/sec is well within every engine's ceiling. But the deciding axis is "team runs Postgres and refuses to add a new DB" — TimescaleDB is a CREATE EXTENSION away. Winner: TimescaleDB.
4. Each pick is defended by one axis in the matrix — the axis that is a hard constraint for that workload. Never pick an engine on aggregate feature count; pick on the constraint that dominates.
5. The Python matcher is simplified but captures the decision-tree shape. In a real interview you walk the tree out loud with the interviewer's specific numbers rather than showing code.

Output.

Workload	Winner	Deciding axis
A. Fintech ticks	QuestDB	single-node ingest ceiling + PG wire
B. Cloud observability	InfluxDB 3	Parquet-on-S3 storage tier
C. Postgres shop + TS	TimescaleDB	zero-migration constraint

Rule of thumb. For any time-series interview question, ask three clarifying questions (ingest rate, storage tier, team constraint) before naming an engine. The clarifying questions are themselves a senior signal — they show you understand the trade-off matrix.

Worked example — the 5-probe interview grading rubric

Detailed explanation. After you name QuestDB, the interviewer will probe five specific areas to test depth. Every senior candidate should be able to score senior on each probe without preparation-specific priming. Walk through the rubric.

• Probe 1 — SIMD engine. Explain the column-per-file layout + vectorised aggregation + zero-GC hot path.
• Probe 2 — ILP protocol. Explain the port, format, and O3 commit strategy.
• Probe 3 — ASOF JOIN. Explain the semantics and the O(N + M) complexity.
• Probe 4 — O3 commit. Explain commit-lag, max-uncommitted-rows, DEDUP UPSERT.
• Probe 5 — When to pick QuestDB. The four-engine matrix answer.

Question. Score a senior candidate on each of the 5 probes and write out the model answer.

Input.

Probe	Weak answer	Senior answer
1. SIMD engine	"it's fast"	"column per file, AVX-512 kernels, off-heap zero-GC"
2. ILP protocol	"we use inserts"	"TCP 9009, line format, O3 commit with commit-lag"
3. ASOF JOIN	"a special join"	"nearest-earlier alignment; O(N+M) merge walk"
4. O3 commit	"it handles OOO"	"commit-lag window sort + partition merge + DEDUP"
5. When to pick	"faster than Postgres"	"single-node ingest + PG SQL; vs TS/InfluxDB 3/CH"

Code.

Senior QuestDB probe answers (30s each)
=======================================

Probe 1 — SIMD engine (30s)
  "Column-per-file storage partitioned by time; SIMD-vectorised
   aggregation kernels using AVX-512 / AVX2 / NEON depending on the
   host; zero-GC hot path with off-heap Unsafe allocation + mmap-
   backed page cache access. Together they hit ~1.4M rows/sec ingest
   and ~40ms SUM over 100M-row DOUBLE columns on modest cloud boxes."

Probe 2 — ILP protocol (30s)
  "InfluxDB Line Protocol on TCP 9009 (or UDP for constrained
   devices). Format is one line per row: 'table,tag=v field=v <ts_ns>'.
   Client libraries in Python, Java, Go, Rust batch client-side
   before flushing. Server-side, rows land in an O3 commit pipeline
   with commit-lag + max-uncommitted-rows tuning."

Probe 3 — ASOF JOIN (30s)
  "Nearest-earlier alignment of two time-series on a join key. For
   each row in the left table, the join returns the most-recent right
   row with ts <= left.ts matching the key. Because both tables are
   TIMESTAMP-sorted, the engine implements it as a two-pointer merge
   walk in O(N + M), vs O(N log M) for the LATERAL subquery in plain
   Postgres. ~100-400x speedup on real fintech data."

Probe 4 — O3 commit (30s)
  "Out-of-order commit strategy. Rows accumulate in an in-memory ring
   for up to commit-lag milliseconds or max-uncommitted-rows count,
   whichever fires first. On flush, the batch is sorted by TIMESTAMP
   and merged into the partition file. WAL guarantees crash safety.
   DEDUP UPSERT KEYS(ts, keys...) makes retries idempotent."

Probe 5 — When to pick QuestDB (30s)
  "Ultra-high single-node ingest with Postgres SQL and time-series-
   native ASOF/SAMPLE BY primitives. Not the answer for Postgres-only
   shops (pick TimescaleDB), object-storage-native lakehouse (pick
   InfluxDB 3), or multi-node OLAP (pick ClickHouse). Deploy on 8-16
   vCPU + fast NVMe; add QuestDB Cloud for managed HA."

Step-by-step explanation.

1. Each probe is 30 seconds max. Over-explaining is a weak signal; concise-with-technical-depth is the senior signal. Practice each answer until it fits in 30 seconds without cutting technical content.
2. Every probe answer includes a specific number, a specific term-of-art, and a specific alternative. Numbers (1.4M rows/sec, AVX-512, O(N+M), 100-400× speedup) prove depth. Terms of art (O3 commit, DEDUP UPSERT, TIMESTAMP designation) prove familiarity. Alternatives (TimescaleDB/InfluxDB 3/ClickHouse) prove breadth.
3. Probe 5 is the decision-matrix probe — the interviewer wants to see you understand the trade-off space, not just the QuestDB happy path. Naming the three alternatives with their winning axes is what elevates the answer from "advocate" to "architect."
4. In a real interview the probes come in different orders and often with follow-ups. Practice the answers as modular chunks that can be reassembled — not as a rigid script.
5. The 5-probe answer set totals ~2.5 minutes of speaking time. Combined with the 30-second matrix framing at the start, that's a 3-minute complete senior answer to "what do you know about QuestDB?" — enough to satisfy the depth check without over-explaining.

Output.

Probe	Weak candidate	Senior candidate
1. SIMD engine	~10%	~85%
2. ILP protocol	~5%	~80%
3. ASOF JOIN	~5%	~90%
4. O3 commit	rare	~70%
5. When to pick	~15%	~95%

Rule of thumb. Every senior QuestDB interview boils down to the 5 probes above. Rehearse each answer as a 30-second chunk with one number, one term of art, and one alternative. Deploy the same chunks regardless of the specific question — they compose.

Senior interview question on engine choice + probe survival

A senior interviewer might ask: "You have 90 seconds to convince the head of platform engineering that QuestDB is the right pick for a new fintech tick store, then handle the 5 follow-up probes about the SIMD engine, ILP, ASOF JOIN, O3 commit, and why not one of the alternatives. Walk me through the full 5-minute pitch + follow-up sequence."

Solution Using a 90-second pitch + 5x30-second probe answers with decision-matrix framing

1. THE 90-SECOND PITCH
=======================

"For the fintech tick store I'd deploy QuestDB. It's the open-source
Apache-2.0 single-node time-series DB written in Java + C++ that
combines a SIMD-vectorised columnar storage engine (~1.4M rows/sec
sustained on a modest 16-vCPU cloud box) with the Postgres wire
protocol on port 8812. Metabase, Grafana, DBeaver, every JDBC / psql
tool we own connects with no config change beyond the port. The
write path is ILP over TCP 9009 with client libraries in every
language — 500k rows/sec is 35% of engine ceiling, comfortable margin.
The query dialect is standard SQL plus four time-series primitives
(SAMPLE BY, LATEST ON, ASOF JOIN, FILL) so intraday VWAP + trade-vs-
quote alignment is a one-liner rather than a 20-line LATERAL query.
Operational shape: one node + QuestDB Cloud managed replica for HA;
no ZooKeeper, no shards, no rebalance. Alternatives considered:
TimescaleDB (ceiling ~200k rows/sec — 2.5x under our workload);
InfluxDB 3 (object-storage native, but we don't need lakehouse tie);
ClickHouse (multi-node, overkill for one region). QuestDB wins on
the (single-node ingest x PG wire x ASOF JOIN) axis specifically."

2. THE 5 PROBE ANSWERS (30s each)
==================================

Probe 1 (SIMD engine):
  "Column-per-file storage partitioned by day, SIMD-vectorised
   aggregation kernels via AVX-512, zero-GC hot path with off-heap
   Unsafe + mmap. SUM over 100M-row DOUBLE column ~40ms wall-clock."

Probe 2 (ILP protocol):
  "TCP port 9009, one-line-per-row format 'table,tag=v field=v <ts>'.
   Server-side O3 commit pipeline with commit-lag + max-uncommitted-
   rows tuning. Python questdb library batches client-side."

Probe 3 (ASOF JOIN):
  "Nearest-earlier alignment of two time-series. For each trade, the
   most-recent quote with quotes.ts <= trades.ts matching symbol.
   Two-pointer merge walk on pre-sorted streams — O(N+M). Replaces
   LATERAL subquery, ~100-400x speedup on 10M x 5M row joins."

Probe 4 (O3 commit):
  "Buffer commits for up to commit-lag ms or max-uncommitted-rows
   count. Sort within batch, merge into partition. WAL for crash
   safety. DEDUP UPSERT KEYS(ts, symbol) for idempotent retries.
   Tuned per table via ALTER TABLE SET PARAM."

Probe 5 (why not alternatives):
  "TimescaleDB ceiling ~200k rows/sec — too low; adopting a new DB
   is cheaper than scaling PG. InfluxDB 3 is object-storage native —
   great for lakehouse tie, we don't need it. ClickHouse is multi-
   node — operational overkill for one region. QuestDB is single-
   node + PG SQL — the two axes that actually matter for us."

# 3. Deployment artifact — the docker-compose the pitch implies
services:
  questdb:
    image: questdb/questdb:8.0.0
    ports:
      - "9000:9000"    # REST / web console
      - "8812:8812"    # PG wire
      - "9009:9009"    # ILP TCP + UDP
    volumes:
      - questdb-data:/var/lib/questdb
    environment:
      QDB_CAIRO_COMMIT_LAG: "500"
      QDB_CAIRO_MAX_UNCOMMITTED_ROWS: "500000"
      QDB_LINE_TCP_MSG_BUFFER_SIZE: "1048576"

volumes:
  questdb-data:

Step-by-step trace.

Time budget	Content	Purpose
0:00-1:30	90-second pitch	frame the pick + defend on axis
1:30-2:00	Probe 1 (SIMD)	engine internals depth
2:00-2:30	Probe 2 (ILP)	ingestion depth
2:30-3:00	Probe 3 (ASOF JOIN)	SQL primitive depth
3:00-3:30	Probe 4 (O3 commit)	operational depth
3:30-4:00	Probe 5 (alternatives)	decision-matrix breadth
4:00-5:00	Q&A buffer	interviewer follow-ups

After the pitch + probes, the interviewer has heard: (a) the engine choice and its defence, (b) the four-axis matrix, (c) five specific technical primitives, (d) the three alternative engines with their winning axes, (e) a concrete deployment artifact. That's a complete senior answer that covers every reasonable follow-up.

Output:

Signal	Weak candidate	Senior candidate
Names engine in first sentence	rare	mandatory
Names 4 SQL primitives	rare	required
Names 3 alternatives + axes	rare	senior signal
Names specific tuning knobs	rare	senior signal
Ships deployment artifact	never	senior signal

Why this works — concept by concept:

• 90-second decision framing — the pitch names QuestDB, names the four winning axes (SIMD engine, PG wire, ILP, ASOF JOIN), names the three alternatives with their losing-to-QuestDB axes. The interviewer knows within 90 seconds that you understand the whole trade-off space, not just the QuestDB happy path.
• Modular 30-second probe chunks — each probe answer stands alone and can be reordered freely. This matches how real interviews work — probes come in different orders and often with follow-ups. Modular chunks compose; a rigid script doesn't.
• Numbers + terms + alternatives per answer — every probe answer includes one specific number (~1.4M rows/sec, AVX-512, O(N+M), 100-400×), one term of art (O3 commit, DEDUP UPSERT, TIMESTAMP designation), and one alternative comparison. This triad is what earns "senior" on the rubric.
• Concrete deployment artifact — the docker-compose file demonstrates that you've actually run QuestDB, not just read the docs. Interviewers score this heavily because it separates candidates who could operate the system from candidates who could only describe it.
• Cost — 5 minutes of speaking time, no whiteboard needed (though the axis matrix draws well). Practice the pitch + probes end-to-end 3-5 times until the modular chunks flow. The eliminated cost is the "senior candidate who freezes when the interviewer asks 'why not TimescaleDB'" failure mode — the alternative-axis defence is prebuilt into the pitch. O(1) preparation for O(reps) interview performance.

SQL
Topic — sql
SQL time-series interview problems

Practice →

Joins
Topic — joins
Joins problems on ASOF-style alignment

Practice →

---

Cheat sheet — QuestDB recipes

• Which engine when. QuestDB is the 2026 default for single-node ultra-high-ingest time-series workloads with Postgres wire compatibility — ~1.4M rows/sec per instance, standard SQL plus SAMPLE BY / LATEST ON / ASOF JOIN / FILL primitives, ILP write path plus PG-wire read path. Pick TimescaleDB when the team is Postgres-first and refuses to adopt a new DB. Pick InfluxDB 3 when object-storage-native (Parquet on S3) matters. Pick ClickHouse when the workload is multi-node OLAP with high-cardinality analytics. Never rank on ingest rate alone; walk the four-axis matrix (ingest × SQL × ecosystem × distribution) with the actual workload constraints.
• CREATE TABLE template — the four load-bearing declarations. TIMESTAMP(ts) designates the time-axis column; PARTITION BY DAY (or MONTH, YEAR) sets the on-disk folder granularity; SYMBOL CAPACITY N CACHE for low-cardinality strings enables dictionary encoding; WAL enables the write-ahead log for crash safety and out-of-order commits. Full form: CREATE TABLE trades (ts TIMESTAMP, symbol SYMBOL CAPACITY 4096 CACHE, price DOUBLE, volume LONG) TIMESTAMP(ts) PARTITION BY DAY WAL DEDUP UPSERT KEYS(ts, symbol). Skipping any of these leaves throughput on the table.
• ILP producer template (Python). pip install questdb; use Sender.from_conf("tcp::addr=host:9009;auto_flush_rows=10000;auto_flush_interval=1000;"). Each row: sender.row("table", symbols={"tag": "v"}, columns={"field": v}, at=TimestampNanos(ts_ns)). Client-side batching amortises TCP overhead. Server-side batching handled by commit-lag. For 500k+ rows/sec use multiple sender threads pointing at the same server; ILP is thread-safe on the server side.
• PG-wire connection URL template. postgresql://admin:quest@questdb:8812/qdb for every psql / JDBC / SQLAlchemy / Django / Spring Data client. The engine responds with a server 11.3 (QuestDB compat) banner so tools that fingerprint by version get a real Postgres number. Default credentials are admin / quest (change these in production via server.conf).
• Commit tuning knobs — the two that matter. commitLag (milliseconds to buffer before flushing — larger absorbs more out-of-order at the cost of freshness) and maxUncommittedRows (row-count trigger — larger amortises WAL cost but delays visibility). Set per table via ALTER TABLE t SET PARAM commitLag = 1s and ALTER TABLE t SET PARAM maxUncommittedRows = 500000. Default is 300ms + 500k rows; tune upward for higher throughput, downward for tighter freshness.
• SAMPLE BY + FILL cheat sheet. SAMPLE BY 1s | 1m | 5m | 1h | 1d | 1M | 1y for time-bucket aggregation. Pair with FILL(NULL) for explicit gaps, FILL(PREV) for carry-forward (states), FILL(LINEAR) for interpolation (slowly-varying signals), FILL(0) for constant fill (counters). ALIGN TO CALENDAR aligns buckets to natural boundaries (midnight, top-of-hour). Combine with WHERE ts IN 'YYYY-MM-DD' for partition-pruned queries.
• LATEST ON template. SELECT ... FROM t WHERE <filter> LATEST ON ts PARTITION BY key_col returns the newest row per key. Replaces DISTINCT ON and windowed ROW_NUMBER() = 1 patterns from Postgres. O(distinct keys) instead of O(rows) thanks to the TIMESTAMP-ordered partition storage. Composes cleanly with WHERE filters — the predicate is pushed into the partition scan.
• ASOF JOIN template. SELECT l.*, r.* FROM left l ASOF JOIN right r ON l.key = r.key aligns two TIMESTAMP-designated tables on nearest-earlier ts per key. Right-side NULLs when no earlier row exists (LEFT JOIN semantics). O(N+M) via merge-walk. For fintech: trades ASOF JOIN quotes ON symbol; for IoT: events ASOF JOIN device_state ON device_id.
• DEDUP UPSERT for idempotent retries. Declare DEDUP UPSERT KEYS(ts, k1, k2) in the CREATE TABLE (or via ALTER TABLE). Available on WAL tables from QuestDB 7.3+. On insert of a row with a key collision, the engine replaces (upserts) rather than appending a duplicate. Removes the need for application-side dedup logic on retry-heavy pipelines.
• Retention via O(1) partition drop. ALTER TABLE t DROP PARTITION WHERE ts < dateadd('d', -30, now()) drops the on-disk partition folders older than 30 days. Runtime is O(partitions dropped), not O(rows). Ship as a nightly cron job; combines with monthly snapshot to S3 for archival.
• Decision matrix — QuestDB vs TimescaleDB vs InfluxDB 3 vs ClickHouse. Ingest ceiling: QuestDB ~1.4M rows/sec, InfluxDB 3 ~1M+, ClickHouse ~1M+, TimescaleDB ~200k. SQL dialect: QuestDB standard SQL + time-series extensions, TimescaleDB full Postgres, InfluxDB 3 SQL (was InfluxQL/Flux), ClickHouse SQL-adjacent. Ecosystem: QuestDB PG wire compat, TimescaleDB is Postgres, InfluxDB 3 vendor driver, ClickHouse vendor driver. Distribution: QuestDB single-node + replicas, TimescaleDB Postgres HA, InfluxDB 3 object-storage native, ClickHouse shards + replicas.
• QuestDB Cloud managed features. Automatic backups, replicas (async), monitoring dashboards, TLS, RBAC, auto-scaling for the ILP writer pool. Pricing per-instance-hour. Adopt when the team lacks stateful-service ops experience or when the operational overhead of self-managed QuestDB isn't worth the savings.
• Observability contract — the four metrics to alert on. (a) commitLag p99 wall-clock — alert if consistently above the configured commitLag (means the writer worker is backed up). (b) Partition-writer queue depth — alert if growing (means ingest > commit throughput). (c) WAL size — alert if growing (means partition writer is stuck). (d) ILP TCP connection count — alert on unexpected drops (means client pool is churning). Export via QuestDB's /metrics endpoint in Prometheus format.
• The senior interview 5-probe answer set. SIMD engine (column-per-file + AVX-512 + zero-GC), ILP protocol (TCP 9009 + line format + O3), ASOF JOIN (nearest-earlier + O(N+M)), O3 commit (commit-lag + max-uncommitted + WAL + DEDUP UPSERT), when-to-pick (four-axis matrix + three alternatives). 30 seconds each, ~2.5 minutes total plus a 90-second decision framing at the start. Rehearse until modular; deploy in any interview.

Frequently asked questions

What is QuestDB in one sentence?

QuestDB is the open-source, Apache-2.0-licensed, single-node time-series database written in Java plus C++ that combines a SIMD-vectorised columnar storage engine (column-per-file layout partitioned by time, AVX-512-dispatched aggregation kernels, zero-GC off-heap hot path with mmap-backed page cache access) with the InfluxDB Line Protocol on port 9009 for high-throughput writes (sustained ~1.4M rows/sec per instance on modest cloud hardware, TSBS-verifiable) and the Postgres wire protocol on port 8812 for SQL-shaped reads with time-series-native primitives (SAMPLE BY for time-bucket aggregation, LATEST ON for the newest snapshot per key, ASOF JOIN for nearest-earlier alignment of two time-series, FILL(NULL|PREV|LINEAR|N) for gap-filling), all without operating a distributed cluster and with drop-in compatibility for every psql / JDBC / DBeaver / Metabase / Grafana / Tableau tool the team already owns. Every senior data-engineering interview in 2026 that reaches "which time-series database would you pick" eventually gets to QuestDB because it collapses the (ultra-high ingest × standard SQL × single-node ops) trifecta that competing engines typically force you to trade off pairwise.

QuestDB vs InfluxDB — when do I pick each?

Default to QuestDB when the workload is single-node ultra-high ingest with Postgres wire compatibility and you want SQL (plus time-series extensions) as the query dialect rather than InfluxQL or Flux — QuestDB hits ~1.4M rows/sec per instance, speaks the Postgres wire protocol natively (every BI tool just works), and its ASOF JOIN is 100-400× faster than the LATERAL subquery equivalent in plain Postgres for aligning two time-series on nearest-earlier ts. Default to InfluxDB 3 (the 2024+ Rust + Arrow + DataFusion rewrite) when the workload demands object-storage-native architecture (Parquet on S3 / Azure Blob / GCS), when multi-tenant SaaS billing per-write matters, or when the team is already deeply invested in the InfluxDB ecosystem (Telegraf collectors, InfluxQL queries, existing InfluxDB Cloud accounts). Both speak ILP on the write path so migrating existing InfluxDB producers to QuestDB is a config change; the deciding axis is almost always the storage tier (local NVMe vs S3) and the query dialect preference (SQL vs InfluxQL/Flux). Feature-wise both offer time-series primitives (SAMPLE BY / LATEST ON / ASOF JOIN in QuestDB; time-bucketing operators in InfluxDB 3); the deciding question is "does object-storage-native architecture matter" — yes means InfluxDB 3, no means QuestDB.

What is the SIMD engine and why does it matter?

QuestDB's aggregation kernels (SUM, AVG, MIN, MAX, COUNT, and predicate filtering) are implemented in vectorised C++ that dispatches at query-compile time to the widest SIMD instruction set the host CPU supports — AVX-512 (16 float32 or 8 float64 per instruction) on 2020+ Xeon and EPYC, AVX2 (8 float32 or 4 float64) on older x86, NEON (4 float32 or 2 float64) on ARM64. A single AVX-512 SUM instruction accumulates 8 doubles at once; QuestDB's kernel loops over the column file (which is mmap-backed and dictionary-encoded for symbol columns) in 64-byte chunks and reduces the 8 partial sums into a single scalar at the end. Compared to a naive scalar for (i = 0; i < n; i++) sum += a[i] loop, this is a straight 4-16× multiplier depending on the CPU width. Combined with the columnar layout (aggregation queries touch only the requested column, not the full row), the partition pruning (queries with WHERE ts IN 'YYYY-MM-DD' skip other partitions entirely), and the zero-GC hot path (no JVM pauses under load), the SIMD kernels are the single largest reason QuestDB sustains ~1.4M rows/sec per instance and returns SUM over 100M-row DOUBLE columns in ~40ms wall-clock on modest cloud boxes. Interviewers probe SIMD because it is the concrete engineering detail that separates candidates who have read the QuestDB docs from candidates who have measured the engine.

Can QuestDB replace Postgres for OLTP?

No — QuestDB is an append-optimised, time-series-first columnar engine and Postgres is a row-store OLTP database, and each is dramatically better at its native shape. QuestDB's ingest path assumes append-only writes ordered (or nearly-ordered) by timestamp; UPDATE and DELETE work via UPDATE ... WHERE and ALTER TABLE ... DROP PARTITION but are not first-class OLTP operations. There are no foreign keys, no PL/pgSQL, no triggers, no MVCC row-versioning with per-row locks. Transactions exist but scope to one table at a time; cross-table atomic transactions are not the engine's design point. For any workload that needs referential integrity across normalised tables, per-row updates as the dominant access pattern, or full ACID transactions across multiple tables — Postgres (or a Postgres-superset like TimescaleDB) is the correct answer, not QuestDB. Where QuestDB shines is exactly where Postgres struggles: append-heavy writes at 100k-1M+ rows/sec, aggregation queries over time-bucketed data, ASOF JOIN for time-aligned analytics. A common architecture is Postgres for OLTP + QuestDB for the derived time-series analytics layer, with CDC (Debezium) or an application-side dual-write feeding the QuestDB side. The Postgres wire compatibility means BI tools can query both engines with the same client library.

What does ASOF JOIN do?

ASOF JOIN is QuestDB's native primitive for aligning two time-series on nearest-earlier timestamp per join key. Given a SELECT ... FROM trades t ASOF JOIN quotes q ON t.symbol = q.symbol query, for each row in trades the engine returns the most-recent row in quotes where quotes.ts <= trades.ts and the symbol matches — semantically equivalent to a LEFT JOIN with a correlated LIMIT 1 ORDER BY ts DESC subquery, but implemented as a two-pointer merge walk over pre-sorted streams in O(N + M) rather than the O(N × log M) of the correlated LATERAL subquery in plain Postgres. On real fintech data (10M trades × 5M quotes), the LATERAL subquery in Postgres takes ~30 seconds even with the ideal (symbol, ts DESC) index; the QuestDB ASOF JOIN returns in ~80ms — a 400× speedup on the primitive that dominates every trade-vs-quote reconciliation query, every event-vs-device-state analysis, and every order-vs-price backtest. Because both tables are stored in TIMESTAMP order (thanks to the TIMESTAMP(ts) designation in the DDL), the join kernel walks each table exactly once — no hash-join memory, no sort step. This is one of the two primitives (the other is SAMPLE BY) that makes QuestDB's dialect strictly more expressive than plain Postgres for time-series workloads, and it is the single most-cited answer in senior fintech interviews when asked "why not Postgres for the tick store."

Is QuestDB Cloud production-ready in 2026?

Yes for both the managed variant and the self-hosted OSS release. QuestDB Cloud (the managed offering, GA since 2023) is deployed at Airbus, Aquis Exchange, and other production customers for tick storage, IoT telemetry, and observability workloads; it provides automatic backups, async replicas for HA, monitoring dashboards, TLS, RBAC, auto-scaling for the ILP writer pool, and per-instance-hour billing. Self-hosted QuestDB OSS (Apache 2.0, single-binary Java + native libs) is at version 8.x in 2026 with the WAL + O3 commit + DEDUP UPSERT features that make high-ingest pipelines operationally sound; the remaining rough edges are around observability (invest in Prometheus scraping of /metrics), backup automation (implement snapshot-to-S3 nightly), and the standard "run a stateful service in production" concerns (PagerDuty for disk-full alerts, WAL-size alerting, ILP-connection-count monitoring). Neither the managed nor the self-hosted variant is a "production-ready?" question anymore — the question is "which do we want to run" — and for teams without deep stateful-service ops experience, QuestDB Cloud lifts the operational burden while keeping the engine's ingest and query throughput. Interviewers in 2026 no longer ask "is QuestDB mature" — they ask "why is QuestDB the right pick for this specific workload" — and the answer is the four-axis decision matrix from the earlier sections.

Practice on PipeCode

• Drill the SQL practice library → for the time-series SQL, SAMPLE BY, ASOF JOIN, and LATEST ON problems senior interviewers use to test QuestDB-shaped query patterns.
• Rehearse aggregation on the aggregation practice library → for the time-bucketed rollup, VWAP, and hourly-average shapes that dominate QuestDB dashboarding workloads.
• Sharpen the joins axis with the joins practice library → for the ASOF-style time-aligned join problems that show up in senior fintech + IoT interviews.
• Practise window function patterns with the window functions practice library → for the LAG / LEAD / running-aggregate shapes that complement QuestDB's SAMPLE BY primitives.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the QuestDB decision matrix, the four SQL primitives, and the five interview probes against real graded inputs.

Lock in questdb muscle memory

Docs explain what QuestDB is. PipeCode drills explain when it wins — when the SIMD engine buys you the throughput headroom, when the ILP protocol saturates your NIC before the engine, when ASOF JOIN turns a 30-second Postgres query into an 80ms one-liner, when the four-axis matrix picks QuestDB over TimescaleDB or InfluxDB 3 or ClickHouse. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior time-series engineers actually face.

Practice SQL problems →
Practice aggregation problems →