Getting Started with QuestDB

Every database can store rows with a timestamp column. PostgreSQL, MySQL, SQLite, they all let you INSERT a row with created_at. But once you have millions of timestamped rows and need to answer "what was the average temperature per 15-minute bucket across 3 sensors for the last 24 hours," the cracks show.

In PostgreSQL, that query needs date_trunc, GROUP BY, and careful index tuning. Getting the latest reading per sensor is worse: you need a window function or a correlated subquery. These queries work, but they're verbose, slow at scale, and awkward to write.

QuestDB is built for exactly this. Time-partitioned columnar storage, aggressive compression, and SQL extensions like SAMPLE BY (time-bucketed aggregation in one clause) and LATEST ON (most recent row per group, one line). It speaks the PostgreSQL wire protocol, so you connect with any PG client library.

We'll build a sensor data pipeline in one TypeScript file that exercises both features. Run it locally or point it at a managed instance on Layerbase Cloud.

Contents

• Create a QuestDB Instance
  • Local with SpinDB
  • Layerbase Cloud
• Set Up the Project
• Generate Sensor Data
• Create the Table
• Insert the Data
• Time-Bucketed Aggregation with SAMPLE BY
• Latest Reading per Sensor with LATEST ON
• What This Looks Like in PostgreSQL
• When to Reach for a Time-Series Database
• Wrapping Up

Create a QuestDB Instance

Local with SpinDB

SpinDB is the simplest local setup. It downloads and runs database binaries directly, no Docker needed. (What is SpinDB?)

Install SpinDB globally:

npm i -g spindb    # npm
pnpm add -g spindb # pnpm

Or run it directly without installing:

npx spindb create quest1 -e questdb --start  # npm
pnpx spindb create quest1 -e questdb --start # pnpm

If you installed globally, create and start a QuestDB instance:

spindb create quest1 -e questdb --start

SpinDB downloads the QuestDB binary for your platform, configures it, and starts the server. Verify it's running:

spindb url quest1

postgresql://localhost:8812/qdb

QuestDB exposes two interfaces: a PostgreSQL wire protocol on port 8812 (what we'll use for queries) and an HTTP REST API on port 9000 (useful for health checks and the built-in web console at http://localhost:9000).

Leave the server running.

Layerbase Cloud

Rather host it? Layerbase Cloud has you covered. Pick QuestDB on the create page and grab the PostgreSQL connection string from Quick Connect.

Cloud instances use TLS, so the connection code looks a bit different:

const client = new pg.Client({
  connectionString: 'postgresql://user:pass@cloud.layerbase.dev:12345/qdb',
  ssl: true,
})

Everything else in this guide works identically whether you're running locally or on Layerbase Cloud. Just swap in your connection details.

Set Up the Project

mkdir questdb-sensors && cd questdb-sensors
pnpm init
pnpm add pg
pnpm add -D tsx typescript @types/pg

Create a file called sensors.ts. All the code in this post goes into that one file.

Start with the import and connection:

import pg from 'pg'

const client = new pg.Client({
  host: 'localhost',
  port: 8812,
  database: 'qdb',
})

await client.connect()
console.log('Connected to QuestDB')

Generate Sensor Data

We'll simulate 24 hours of readings from three sensors. Each sensor reports temperature and humidity every ~15 minutes, giving us roughly 100 rows per sensor:

type Reading = {
  sensorId: string
  temperature: number
  humidity: number
  ts: Date
}

function generateReadings(): Reading[] {
  const sensors = ['sensor_a', 'sensor_b', 'sensor_c']
  const readings: Reading[] = []
  const now = new Date()
  const twentyFourHoursAgo = new Date(now.getTime() - 24 * 60 * 60 * 1000)

  for (const sensorId of sensors) {
    let baseTemp = 20 + Math.random() * 10
    let baseHumidity = 40 + Math.random() * 20

    for (let i = 0; i < 96; i++) {
      const ts = new Date(
        twentyFourHoursAgo.getTime() +
          i * 15 * 60 * 1000 +
          Math.random() * 60 * 1000,
      )

      baseTemp += (Math.random() - 0.5) * 2
      baseHumidity += (Math.random() - 0.5) * 3

      readings.push({
        sensorId,
        temperature: Math.round(baseTemp * 100) / 100,
        humidity: Math.round(Math.max(0, Math.min(100, baseHumidity)) * 100) / 100,
        ts,
      })
    }
  }

  return readings.sort((a, b) => a.ts.getTime() - b.ts.getTime())
}

const readings = generateReadings()
console.log(`Generated ${readings.length} sensor readings`)

Each sensor drifts slightly from its starting values to simulate realistic environmental data. The readings are sorted by timestamp because QuestDB performs best with time-ordered inserts.

Create the Table

QuestDB extends standard SQL with time-series-specific syntax:

await client.query(`
  CREATE TABLE IF NOT EXISTS sensors (
    sensor_id SYMBOL,
    temperature DOUBLE,
    humidity DOUBLE,
    ts TIMESTAMP
  ) timestamp(ts) PARTITION BY HOUR WAL;
`)

console.log('Created sensors table')

Three things here are QuestDB-specific:

• SYMBOL: an interned string type. QuestDB stores each unique value once and references it by index, which makes filtering and grouping on sensor_id much faster than scanning raw strings. Use it for columns with a bounded set of repeating values (sensor names, device IDs, region codes).
• timestamp(ts): designates ts as the table's primary timestamp. QuestDB uses this for time-partitioned storage and to power SAMPLE BY and LATEST ON.
• PARTITION BY HOUR WAL: data is physically partitioned by hour, so time-range queries only scan relevant partitions. WAL (Write-Ahead Log) enables concurrent reads and writes.

Insert the Data

Insert all readings via parameterized queries:

console.log('Inserting readings...')

for (const r of readings) {
  await client.query(
    'INSERT INTO sensors (sensor_id, temperature, humidity, ts) VALUES ($1, $2, $3, $4)',
    [r.sensorId, r.temperature, r.humidity, r.ts],
  )
}

console.log(`Inserted ${readings.length} rows`)

For production workloads with millions of rows per second, QuestDB's HTTP endpoint supports the InfluxDB Line Protocol for high-throughput ingestion. The PG wire protocol is better suited for queries and moderate insert rates, which is fine for what we're doing here.

Let's verify the data:

const countResult = await client.query('SELECT count() FROM sensors')
console.log(`Total rows: ${countResult.rows[0].count}`)

const sensorResult = await client.query(
  'SELECT sensor_id, count() as readings FROM sensors GROUP BY sensor_id ORDER BY sensor_id',
)
console.log('\nReadings per sensor:')
for (const row of sensorResult.rows) {
  console.log(`  ${row.sensor_id}: ${row.readings}`)
}

Total rows: 288
Readings per sensor:
  sensor_a: 96
  sensor_b: 96
  sensor_c: 96

Time-Bucketed Aggregation with SAMPLE BY

This is QuestDB's signature feature. Get the average, min, and max temperature per 15-minute window across all sensors:

const sampled = await client.query(`
  SELECT
    sensor_id,
    avg(temperature) as avg_temp,
    min(temperature) as min_temp,
    max(temperature) as max_temp,
    avg(humidity) as avg_humidity
  FROM sensors
  SAMPLE BY 15m
  ALIGN TO CALENDAR
`)

console.log('\n15-minute averages (first 10 buckets):')
console.log('sensor_id  | avg_temp | min_temp | max_temp | avg_humidity')
console.log('-----------|----------|----------|----------|------------')
for (const row of sampled.rows.slice(0, 10)) {
  console.log(
    `${row.sensor_id.padEnd(10)} | ${Number(row.avg_temp).toFixed(1).padStart(8)} | ${Number(row.min_temp).toFixed(1).padStart(8)} | ${Number(row.max_temp).toFixed(1).padStart(8)} | ${Number(row.avg_humidity).toFixed(1).padStart(11)}`,
  )
}
console.log(`... (${sampled.rows.length} total buckets)`)

15-minute averages (first 10 buckets):
sensor_id  | avg_temp | min_temp | max_temp | avg_humidity
-----------|----------|----------|----------|------------
sensor_a   |     23.4 |     23.4 |     23.4 |        52.1
sensor_b   |     27.1 |     27.1 |     27.1 |        48.3
sensor_c   |     21.8 |     21.8 |     21.8 |        55.7
sensor_a   |     24.1 |     24.1 |     24.1 |        51.4
sensor_b   |     26.5 |     26.5 |     26.5 |        49.8
sensor_c   |     22.3 |     22.3 |     22.3 |        54.2
sensor_a   |     23.8 |     23.8 |     23.8 |        50.9
sensor_b   |     27.4 |     27.4 |     27.4 |        47.6
sensor_c   |     22.1 |     22.1 |     22.1 |        56.1
sensor_a   |     24.6 |     24.6 |     24.6 |        52.8
... (288 total buckets)

That's it. SAMPLE BY 15m. Change it to 1h for hourly, 1d for daily. QuestDB handles the bucketing, alignment, and grouping internally.

Latest Reading per Sensor with LATEST ON

Get the most recent reading from each sensor:

const latest = await client.query(`
  SELECT * FROM sensors
  LATEST ON ts PARTITION BY sensor_id
`)

console.log('\nLatest reading per sensor:')
for (const row of latest.rows) {
  console.log(
    `  ${row.sensor_id}: ${Number(row.temperature).toFixed(1)}°C, ${Number(row.humidity).toFixed(1)}% humidity (${new Date(row.ts).toISOString()})`,
  )
}

Latest reading per sensor:
  sensor_a: 25.2°C, 49.3% humidity (2026-03-14T12:47:22.000Z)
  sensor_b: 28.1°C, 45.8% humidity (2026-03-14T12:52:11.000Z)
  sensor_c: 19.7°C, 58.4% humidity (2026-03-14T12:44:55.000Z)

One line. LATEST ON ts PARTITION BY sensor_id returns exactly one row per sensor: the one with the most recent timestamp. No subqueries, no window functions, no self-joins.

What This Looks Like in PostgreSQL

To appreciate what SAMPLE BY and LATEST ON save you, here are the PostgreSQL equivalents.

15-minute bucketed averages (QuestDB: SAMPLE BY 15m):

-- PostgreSQL equivalent
SELECT
  sensor_id,
  date_trunc('hour', ts)
    + (EXTRACT(minute FROM ts)::int / 15) * interval '15 minutes'
    AS bucket,
  avg(temperature) AS avg_temp,
  min(temperature) AS min_temp,
  max(temperature) AS max_temp,
  avg(humidity) AS avg_humidity
FROM sensors
GROUP BY sensor_id, bucket
ORDER BY bucket, sensor_id;

Latest reading per sensor (QuestDB: LATEST ON ts PARTITION BY sensor_id):

-- PostgreSQL equivalent
SELECT DISTINCT ON (sensor_id)
  sensor_id, temperature, humidity, ts
FROM sensors
ORDER BY sensor_id, ts DESC;

-- Or with a window function:
SELECT sensor_id, temperature, humidity, ts
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY ts DESC) AS rn
  FROM sensors
) sub
WHERE rn = 1;

Both PostgreSQL approaches work, but they need more SQL knowledge and perform poorly at scale without careful indexing. QuestDB's storage engine is optimized for these exact access patterns, so the dedicated syntax is simpler to write and faster to execute.

When to Reach for a Time-Series Database

QuestDB makes the most sense when your workload looks like:

• IoT sensor data: temperature, pressure, GPS coordinates, device health metrics. Any fleet of devices reporting values over time
• Application metrics and monitoring: request latency, error rates, queue depths. QuestDB ingests millions of data points per second per server
• Financial market data: tick data, OHLCV candles, order book snapshots. SAMPLE BY makes aggregating ticks into candles trivial
• Event logging: user activity streams, audit logs, system events. Time-partitioned storage means old partitions can be detached or archived without touching query performance
• Anything with date_trunc fatigue: if you keep writing date_trunc and GROUP BY on timestamp columns, QuestDB replaces that entire pattern with one clause

The common thread: timestamped data, frequent "aggregate by time window" queries, and "give me the latest value per group" lookups. If that's your workload, a purpose-built time-series database saves both query time and SQL verbosity.

Wrapping Up

Close the connection at the end of the script:

await client.end()
console.log('\nDone.')

Run the full script:

npx tsx sensors.ts

The full script is under 100 lines of meaningful code. You generated sensor data, stored it in QuestDB, ran time-bucketed aggregations with SAMPLE BY, and retrieved the latest readings with LATEST ON. Those two features alone eliminate the biggest pain points of time-series work in a general-purpose database.

The QuestDB documentation covers the InfluxDB Line Protocol for high-throughput ingestion, ASOF JOIN for correlating time-series from different sources, WHERE IN with timestamp ranges, and FILL strategies for handling gaps.

To manage your local QuestDB instance:

spindb stop quest1    # Stop the server
spindb start quest1   # Start it again
spindb list           # See all your database instances

SpinDB manages 20+ engines. Run QuestDB for time-series data alongside PostgreSQL for your app and Redis for caching, all from one tool. Layerbase Desktop is there too if you prefer a macOS GUI over the CLI.