Data/ML Engineer Blog

QuestDB for Data Engineers: Fast Ingest, Time-Series SQL, and Pragmatic Ops

QuestDB is an advanced relational database that focuses on column-oriented storage optimized for time series and event-driven data. It incorporates SQL with additional features tailored for time-based analytics to facilitate real-time data processing. This documentation encompasses essential aspects of QuestDB, including initial setup instructions, comprehensive usage manuals, and reference materials for syntax, APIs, and configuration settings. Furthermore, it elaborates on the underlying architecture of QuestDB, outlining its methods for storing and querying data, while also highlighting unique functionalities and advantages offered by the platform. A key feature is the designated timestamp, which empowers time-focused queries and efficient data partitioning. Additionally, the symbol type enhances the efficiency of managing and retrieving frequently used strings. The storage model explains how QuestDB organizes records and partitions within its tables, and the use of indexes can significantly accelerate read access for specific columns. Moreover, partitions provide substantial performance improvements for both calculations and queries. With its SQL extensions, users can achieve high-performance time series analysis using a streamlined syntax that simplifies complex operations. Overall, QuestDB stands out as a powerful tool for handling time-oriented data effectively.

Meta description (156 chars):
QuestDB explained for data engineers: how to model time-series, ingest at scale, query with SQL (SAMPLE BY, LATEST ON, ASOF JOIN), and run with confidence.

Why QuestDB matters (the quick story)

You’re shipping millions of sensor ticks, trades, or metrics per minute. Most “general” databases bottleneck on writes or force an exotic query DSL. QuestDB gives you very high-throughput ingest and fast SQL for time-series, with an architecture you can actually operate. (QuestDB)

---

What QuestDB is (and isn’t)

• Purpose-built time-series database with SQL and a Postgres wire for interoperability. Use it when you need sustained, blazing ingest and sub-second analytics over recent data. (QuestDB)
• Multiple ways to ingest: first-party ILP clients (HTTP/TCP), message brokers (Kafka/Redpanda/Flink/Telegraf), CSV import, and PGWire. Prefer ILP for max throughput. (QuestDB)
• Architecture highlights: write-ahead logging → columnar storage; Enterprise ships WAL segments to object storage for HA/replication. (QuestDB)
• Query engine: custom SQL parser + JIT compilation + vectorized execution to chew through columnar frames efficiently. (QuestDB)

---

Architecture in one glance

[ ILP Clients / Kafka / CSV / PGWire ]
                │
        (WAL - durable write)
                │
   [ Columnar, time-partitioned tables ]
                │
  SQL engine (JIT + vectorized operators)
                │
       Analytics: SAMPLE BY, LATEST ON,
           ASOF/LT/SPLICE JOIN, etc.
                │
     (Enterprise) WAL → Object Storage
        for replication & recovery

Sources: ingestion overview, storage & query engine, replication docs. (QuestDB)

---

Data modeling that scales

1) Always set a designated timestamp and time partitions

Designate your event time and pick a partition granularity (DAY/HOUR for high-rate streams). It enables time-series SQL, efficient pruning, and retention. (QuestDB)

CREATE TABLE ticks (
  ts      TIMESTAMP,
  symbol  SYMBOL CAPACITY 2048 CACHE, -- dictionary-encoded
  price   DOUBLE,
  qty     LONG
) TIMESTAMP(ts) PARTITION BY DAY WAL;

This applies WAL durability and daily partitions up front. (QuestDB)

2) Use SYMBOL for identifiers + selective indexing

SYMBOL stores repeated strings dictionary-encoded. Add an index on the hot filter keys (e.g., symbol). Don’t over-index; only index columns you filter on. (QuestDB)

ALTER TABLE ticks ALTER COLUMN symbol ADD INDEX;

3) Built-in TTL and painless retention

Use TTL to keep only recent data; QuestDB drops whole expired partitions automatically. Great for observability/market data where “hot” windows matter most. (QuestDB)

ALTER TABLE ticks SET TTL 14 DAYS;

---

Ingestion: do it the fast way

Prefer ILP (InfluxDB Line Protocol) via QuestDB’s first-party clients. It bypasses SQL INSERT and handles batching, retries, and auto table/column creation if you allow it. (QuestDB)

Example ILP lines (HTTP/TCP):

ticks,symbol=ETH-USD price=2615.54,qty=2i 1646762637609765000
ticks,symbol=BTC-USD price=39269.98,qty=1i 1646762637710419000

Default ILP ports: 9000 (HTTP), 9009 (TCP). PGWire: 8812. (QuestDB)

If you need durable, exactly-once semantics across nodes, QuestDB Enterprise ships WAL segments to S3/Azure/NFS and supports primary-replica and multi-primary ingestion. (QuestDB)

---

Querying time-series with SQL (that you already know)

Rollups over time windows

SELECT ts, symbol, sum(qty) AS volume
FROM ticks
SAMPLE BY 1m FILL(NULL)      -- PREV/LINEAR/NULL/constant
ALIGN TO CALENDAR
WHERE ts in '2025-11-20';    -- prune by time

Use SAMPLE BY for calendar-aligned aggregations; control gaps with FILL. (QuestDB)

“Last known good” per key (point-in-time facts)

SELECT *
FROM ticks
LATEST ON ts PARTITION BY symbol;

LATEST ON returns the most recent row per series. Handy for device last-seen, portfolio latest positions, etc. (QuestDB)

Correlate streams by nearest timestamp

SELECT a.ts, a.symbol, a.price, q.qty
FROM ticks a
ASOF JOIN (SELECT ts, symbol, qty FROM ticks) q
ON (a.symbol);

ASOF JOIN pairs each row with the closest earlier/equal timestamp from the other stream—perfect for market data “join quotes to trades”. (QuestDB)

See what the engine plans to do

EXPLAIN
SELECT * FROM ticks WHERE symbol = 'BTC-USD' AND ts in last_day();

Use EXPLAIN to confirm partition pruning, index usage, and JIT’ed filters. (QuestDB)

---

Ops playbook (mid-level, production-ready)

Concept	Why it matters	QuestDB knob / tip
WAL tables	Crash-safe ingestion; parallel writes	Default for modern versions; keep WAL enabled for durability. (QuestDB)
Partitions	Massive pruning and cheap retention	Choose DAY/HOUR based on event rate; don’t forget TIMESTAMP(...). (QuestDB)
Symbols & indexes	Faster filters on categorical keys	Index hot filters only; adjust symbol capacity if very high cardinality. (QuestDB)
TTL	Auto-expire cold data	ALTER TABLE ... SET TTL n DAYS/WEEKS/.... Think “rolling window”. (QuestDB)
Out-of-order (O3) writes	Can increase write amplification	Partitioning + engine heuristics mitigate; monitor write amp metrics. (QuestDB)
Networking	Know your ports & protocols	ILP HTTP 9000, ILP TCP 9009, PGWire 8812, REST 9000. (QuestDB)
Replication	HA and PITR	Enterprise replicates WAL to object storage; primary/replica & multi-primary. (QuestDB)

Common pitfalls to avoid

• Forgetting the designated timestamp → many time-series features won’t work or will be slower. (QuestDB)
• Over-indexing SYMBOL columns → hurts ingest; add indexes only where filters justify it. (QuestDB)
• Expecting PGWire ingestion to match ILP speed → use ILP clients for high-rate streaming. (QuestDB)

---

Real example: trades table end-to-end

Create & index:

CREATE TABLE trades (
  ts TIMESTAMP,
  symbol SYMBOL CAPACITY 4096 CACHE,
  side   SYMBOL CAPACITY 8 NOCACHE,
  price  DOUBLE,
  qty    LONG
) TIMESTAMP(ts) PARTITION BY DAY WAL;

ALTER TABLE trades ALTER COLUMN symbol ADD INDEX;

(QuestDB)

Ingest lines:

trades,symbol=MSFT,side=buy price=414.72,qty=100i 1732089600000000000
trades,symbol=MSFT,side=sell price=415.01,qty=50i 1732089660000000000

(QuestDB)

Queries that matter:

-- 1) Volume per minute with gap handling
SELECT ts, sum(qty) AS vol
FROM trades
SAMPLE BY 1m FILL(0)
WHERE ts in '2025-11-20';

-- 2) Latest best known price per symbol
SELECT symbol, price, ts
FROM trades
LATEST ON ts PARTITION BY symbol;

-- 3) Trades correlated with prior quote (nearest)
SELECT t.ts, t.symbol, t.price, q.price AS quote_px
FROM trades t
ASOF JOIN quotes q ON (symbol)
WHERE t.ts in last_hour();

(QuestDB)

---

Performance & capacity notes

• Monitor write amplification and disk IO; heavy O3 patterns create extra merges. Partitioning and reasonable commit/merge behavior keep it in check. (QuestDB)
• Use EXPLAIN to validate pruning and JIT filters; don’t assume. (QuestDB)
• Start with DAY partitions for most workloads; drop to HOUR for very high ingest or strict retention windows. (QuestDB)

---

Conclusion & takeaways

• Model for time: designated timestamp + partitions unlock performance and retention. (QuestDB)
• Ingest smart: ILP clients are the happy path; PGWire and REST exist for compatibility. (QuestDB)
• Query naturally: SAMPLE BY, FILL, LATEST ON, ASOF give you time-series superpowers without a new DSL. (QuestDB)
• Operate pragmatically: WAL by default, TTL for auto-cleanup, and Enterprise replication when you need HA + PITR. (QuestDB)

Call to action: spin up the official demo and run the queries above, or start with ILP client quickstarts and wire in your stream. (QuestDB)

---

Internal link ideas (official pages)

• Introduction / Why QuestDB
• Ingestion overview (first-party clients, Kafka/Redpanda/Flink/Telegraf)
• ILP overview (HTTP/TCP, auth, health check)
• Networking layer (default ports)
• CREATE TABLE reference (WAL, partitions, TTL)
• Concepts: Partitions, Designated timestamp, Symbol, Indexes, TTL, Deduplication
• SQL: SAMPLE BY, FILL, LATEST ON, ASOF JOIN, EXPLAIN
• Operations: Data retention, Capacity planning, Design for performance
• Enterprise Ops: Replication & Multi-primary ingestion
  (All above are on questdb.com/docs or questdb.com/blog.) (QuestDB)

---

Image prompt (for Midjourney/DALL·E)

“A clean, modern data architecture diagram of QuestDB: ILP clients and Kafka ingest into a WAL layer, then columnar time-partitioned tables, SQL engine (JIT + vectorized), and Enterprise replication shipping WAL to object storage. Minimalistic, high contrast, 3D isometric style.”

---

Tags

#NoSQL #QuestDB #TimeSeries #SQL #DataEngineering #Scalability #StreamingData #Architecture #HighThroughput #Observability

---

Bonus: more articles to understand

1. “QuestDB Schema Design Playbook” — symbols vs strings, index selection, partition sizing, TTL strategies, and dedup keys (with checklists and gotchas). (QuestDB)
2. “From Kafka to QuestDB: Exactly-Once-ish Ingest Patterns” — ILP clients, backpressure, idempotency with UPSERT KEYS + dedup, and end-to-end tests. (QuestDB)
3. “Time-Series SQL You’ll Actually Use” — real scenarios with SAMPLE BY + FILL, LATEST ON, ASOF/LT/SPLICE JOIN, and performance profiling with EXPLAIN. (QuestDB)
4. “Operating QuestDB in Production” — capacity planning, write amplification monitoring, storage sizing, and retention/HA with Enterprise replication. (QuestDB)
5. “QuestDB vs. General-Purpose Databases for Metrics” — benchmark methodology and TCO framing (keep claims conservative; focus on architecture trade-offs). (QuestDB)