QuestDB Integration Guide

Connect to QuestDB databases to query and write time-series data in your pipelines. This guide walks you through everything from basic setup to advanced configurations.

Overview

The QuestDB connector is your gateway to high-performance time-series database operations in MaestroHub. It enables you to:

• Query data from tables using standard SQL with QuestDB extensions
• Write data by bulk-inserting pipeline rows into tables, or run INSERT, UPDATE, ALTER, and CREATE TABLE statements
• Use parameterized queries for dynamic, reusable data operations
• Leverage time-series features like SAMPLE BY, LATEST ON, and ASOF JOIN
• Secure connections with SSL/TLS encryption (QuestDB Enterprise)

PostgreSQL Wire Protocol

QuestDB exposes a PostgreSQL wire protocol (pgwire) endpoint on port 8812 by default. The MaestroHub connector uses the same pgx driver as PostgreSQL, ensuring full compatibility with QuestDB's SQL engine.

Connection Configuration

Creating a QuestDB Connection

Navigate to Connections → New Connection → QuestDB and configure the following:

QuestDB Connection Creation Fields

1. Profile Information

Field	Default	Description
Profile Name	-	A descriptive name for this connection profile (required, max 100 characters)
Description	-	Optional description for this QuestDB connection

2. Database Configuration

Field	Default	Description
Host	localhost	QuestDB server hostname or IP address - required
Port	8812	QuestDB PostgreSQL wire protocol port (1-65535) - required
Connect Timeout (sec)	30	Maximum time to wait for connection establishment (0-600 seconds) - required
Database	qdb	Database name to connect to - required

Default Port

QuestDB uses port 8812 for its pgwire endpoint, not the standard PostgreSQL port 5432. Make sure your firewall allows traffic on this port.

3. Basic Authentication

Field	Default	Description
Username	admin	QuestDB database user (required)
Password	-	QuestDB user password

4. SSL Settings

4a. SSL Configuration

Field	Default	Description
Enable SSL	false	Use encrypted connection to QuestDB server

4b. SSL Mode

(Only displayed when SSL is enabled)

When SSL is enabled, the connection uses sslmode=require which encrypts the connection without verifying the server certificate.

QuestDB SSL Support

• QuestDB Open Source does not support native TLS on the pgwire endpoint. Use a TLS-terminating proxy like PgBouncer for encrypted connections.
• QuestDB Enterprise supports native TLS on all protocols including pgwire.

5. Connection Pool Settings

Field	Default	Description
Max Open Connections	100	Maximum number of simultaneous database connections (0-1000). Higher values increase concurrency but add DB load
Max Idle Connections	25	Idle connections kept ready for reuse to reduce latency (0-1000, 0 = close idle connections immediately)
Connection Max Lifetime (sec)	900	Maximum age of a connection before it is recycled (0-86400 seconds, 0 = keep connections indefinitely). Helpful to avoid server-side timeouts
Connection Max Idle Time (sec)	300	How long an idle connection may remain unused before being closed (0-86400 seconds, 0 = disable idle timeout)

Connection pool settings help optimize database performance by balancing concurrency, resource usage, and latency across workloads.

6. Retry Configuration

Field	Default	Description
Retries	3	Retries after the first connection attempt fails (0-10, 0 = no retries). Each retry waits the Retry Delay first; the delay grows by the Backoff Multiplier after every retry
Retry Delay (ms)	100	Delay between retry attempts in milliseconds (0-3600000 ms)
Retry Backoff Multiplier	2	Exponential factor for retry delay growth (1-10, e.g., 2.0 means each retry waits twice as long)

Example Retry Behavior

• With Retry Delay = 100ms and Retry Backoff Multiplier = 2:
  • 1st retry: wait 100ms
  • 2nd retry: wait 200ms
  • 3rd retry: wait 400ms

7. Connection Labels

Field	Default	Description
Labels	-	Key-value pairs to categorize and organize this QuestDB connection (max 10 labels)

Example Labels

• env: prod - Environment
• team: data - Responsible team

Notes

• Required Fields: All fields described as "required" must be filled.
• SSL/TLS: When SSL is disabled, SSL Mode is automatically set to disable. Native TLS requires QuestDB Enterprise or a TLS proxy.
• Connection Pooling: Manages concurrency with Max Open Connections, reduces latency with Max Idle Connections, mitigates stale connections via Connection Max Lifetime, and frees resources using Connection Max Idle Time.
• Retry Logic: Implements exponential backoff; initial connection attempts use the base delay and each retry multiplies the delay by the backoff factor. Ideal for transient network issues or database restarts.
• Timeout Values: Connect Timeout applies to initial connection establishment; all timeout values are in seconds unless noted (Retry Delay uses milliseconds).

Function Builder

Creating QuestDB Functions

Once you have a connection established, you can create reusable functions for different operation types:

1. Open the connection and go to its Functions tab → New Function
2. Select one of the QuestDB function types: Query, Execute, or Write
3. Configure the function parameters

Zoom

QuestDB query function creation interface with SQL editor and parameter configuration

Query Function

Purpose: Execute SQL statements on QuestDB. Run standard SQL queries enhanced with QuestDB's time-series extensions to read data from your time-series database.

Configuration Fields

Field	Type	Required	Default	Description
SQL Query	String	Yes	-	SQL statement to execute (SELECT). Supports parameterized queries with QuestDB SQL extensions.
Timeout (seconds)	Number	No	1800	Per-execution timeout in seconds. Sets maximum time allowed for query execution.

Use Cases:

• SELECT sensor readings with time-based filtering
• Aggregate metrics using SAMPLE BY for downsampling
• Retrieve latest values per device using LATEST ON
• Perform time-series joins with ASOF JOIN

Using Parameters

The ((parameterName)) syntax creates dynamic, reusable queries. Parameters are automatically detected and can be configured with:

Configuration	Description	Example
Type	Data type validation	string, number, boolean, date, array
Required	Make parameters mandatory or optional	Required / Optional
Default Value	Fallback value if not provided	NOW(), 0, active
Description	Help text for users	"Start date for the report"

Zoom

Parameter configuration interface showing type validation, required flags, default values, and descriptions

Execute Function

Purpose: Execute DML/DDL statements (INSERT, UPDATE, ALTER, CREATE TABLE) on QuestDB. Returns rowsAffected instead of row data. Use this for single writes, schema changes, and table maintenance.

Configuration Fields

Field	Type	Required	Default	Description
SQL Statement	String	Yes	-	DML/DDL SQL statement to execute. Supports parameterized queries with ((param)) syntax.
Timeout	Duration	No	30m	Per-execution timeout (1s–1h).

Use Cases:

• INSERT single readings with now() timestamps and ((param)) values
• UPDATE rows to correct bad readings
• CREATE TABLE with a designated timestamp and PARTITION BY DAY
• ALTER TABLE to add columns or drop old partitions

Write Function

Purpose: Bulk-insert pipeline data into a QuestDB table. This is not a SQL editor — it loads structured data (rows/objects) from your pipeline into a target table.

Write reads the table's columns and maps the incoming data fields to them automatically. Supports batching for efficient bulk loading.

Configuration Fields

Field	Type	Required	Default	Description
Table Name	String	Yes	-	Target table to insert data into.
Data	JSON	Yes	-	Rows to write: an array of objects or a single object. Use ((data)) to take them from the pipeline, or enter static JSON.
Create Table If Not Exists	Boolean	No	false	Create the table if it does not exist, with column types from the Schema or inferred from the data.
Allow Schema Evolution	Boolean	No	false	Add a column to an existing table when the data carries a field the table has no column for. When off, such a batch fails instead of dropping the field. See step 3 below.
Schema	JSON	No	-	Column definitions plus QuestDB table options: designated timestamp, Partition By, WAL Mode, and SYMBOL capacity/cache. Set in the Visual Editor or as JSON. Shown when Create Table If Not Exists is on.
Batch Size	Number	No	100	Number of rows per INSERT batch (1–10,000).
Timeout	Duration	No	30m	Per-execution timeout (1s–1h).

Use Cases:

• Ingest sensor readings from MQTT, OPC UA, or Modbus collectors
• Create a partitioned time-series table on the first write
• Land the output of transform nodes in a QuestDB table

How Write Works Step by Step

1. Batching

Each batch generates a single INSERT INTO table (col1, col2) VALUES ($1, $2), ($3, $4) statement over the PostgreSQL wire protocol. If your pipeline sends 10 rows and Batch Size is 5, exactly 2 INSERT statements are executed. If it sends 13 rows, 3 INSERT statements are executed (5 + 5 + 3).

2. Column Matching

Write uses case-insensitive matching between incoming data field names and table column names. For example, a data field named sensorId will match a table column sensorid or SensorId. The column name from the database is used in the generated INSERT statement.

3. Table Exists — Normal Flow

1. The function queries tables() to check if the target table exists
2. If the table exists, it fetches the column names and types with table_columns()
3. If the data carries fields that have no column in the table, Allow Schema Evolution decides (see below)
4. It matches incoming data fields to table columns (case-insensitive)
5. Batch INSERT statements are generated and executed using the matched columns

Allow Schema Evolution on: each missing field gets an ALTER TABLE ... ADD COLUMN, typed from the Schema or inferred from the batch with the rules below. Column metadata is then re-fetched and all rows are inserted. Schema evolution only adds columns — it never changes or drops an existing column. Use the Execute function for those changes.

Allow Schema Evolution off: the write fails before anything is inserted, with an error that names the fields:

"write batch carries field(s) [pressure] with no matching column in table "sensors" and schema evolution is disabled — enable 'Allow schema evolution' on this function, or add the column(s) manually"

Turn the switch on, add the column with the Execute function, or remove the field upstream. The error is marked permanent because the same batch would fail again: with Store & Forward on, the batch is not retried and goes to Failed messages.

Functions saved before the switch existed have no Allow Schema Evolution value. For them, Create Table If Not Exists decides: when on, missing columns are added; when off, fields that have no column are skipped and listed in skippedFields. When you open such a function in the editor, the switch starts in the same position as Create Table If Not Exists, and saving stores it. Once it is saved as off, a batch with an unknown field fails instead of being skipped.

4. Table Does Not Exist

Create Table If Not Exists	Behavior
Disabled (default)	Returns an error immediately: "table 'X' does not exist. Enable 'Create table if not exists' or create it manually". No insert is attempted.
Enabled, no schema provided	Types are inferred from all rows in the batch using the rules below. A plain CREATE TABLE is executed (no designated timestamp, no partitioning), then all rows are inserted.
Enabled, with schema	The Schema is used for CREATE TABLE, including TIMESTAMP(col) for the designated timestamp, PARTITION BY, WAL / BYPASS WAL, and SYMBOL CAPACITY / CACHE. After creation, all rows are inserted.

Designated timestamp

QuestDB accepts partitioning only on a table with a designated timestamp, and WAL mode only on a partitioned table. The Visual Editor warns when these don't line up. Every row must carry a value for the designated timestamp column: if no row in the batch has that field, the write fails before the table is created.

Data Type Inference — When no schema is provided, the Write function infers each column's type from all rows in the batch. For each field it uses the first row where the field has a non-null value, except that a whole number gives way to a later number with a fractional part (38 then 38.5 gives DOUBLE). A field that is null in every row gets VARCHAR.

Data Value	Inferred Type	Notes
true / false	BOOLEAN	JSON boolean values
42, -7, 1000	LONG	Whole numbers (no fractional part)
3.14, 0.5	DOUBLE	Numbers with fractional part
"2024-01-15T10:30:00Z"	TIMESTAMP	Strings matching ISO 8601 / RFC 3339 and other common timestamp formats are automatically detected
"hello", "sensor-01"	VARCHAR	All other strings
null	VARCHAR	Null values default to text
{"key": "val"}, [1,2]	VARCHAR	Objects and arrays are stored as JSON text

5. Partial Failure

Each batch executes independently — there is no transaction wrapping all batches. If batch 3 of 5 fails, batches 1 and 2 have already been committed. The error response still includes rowsInserted showing how many rows succeeded before the failure.

Response Metadata

On success, the response includes:

Field	Description
rowsInserted	Total number of rows successfully inserted
matchedColumns	Column names that had matching data fields (when the table already existed)
skippedFields	Data fields that had no matching column and were skipped — only for functions saved before Allow Schema Evolution existed (if any)
schemaEvolution	Columns added by schema evolution, each with its type (if any)
tableCreated	true if the table was created during this call
columns	Columns written to (when the table was created during this call)
batchSize	Batch size used
totalRows	Total number of input rows

Pipeline Integration

Use the QuestDB functions you create here as nodes inside the Pipeline Designer to query and write time-series data alongside the rest of your operations stack. Drag the appropriate node type onto the canvas, bind its parameters to upstream outputs or constants, and configure connection-level options without leaving the designer.

Each function type maps to a dedicated pipeline node — Query, Execute, and Write.

For end-to-end orchestration ideas, such as combining database reads with MQTT, REST, or analytics steps, explore the Connector Nodes page to see how SQL nodes complement other automation patterns.

Zoom

QuestDB query node in the pipeline designer with connection, function, and parameter configuration

Common Use Cases

Real-Time Sensor Monitoring

Scenario: Query the latest sensor readings across all devices.

SELECT * FROM sensors
LATEST ON timestamp PARTITION BY sensor_id
WHERE timestamp > dateadd('h', -1, now());

Pipeline Integration: Use in a pipeline to feed real-time dashboards or trigger alerts based on threshold breaches.

---

Time-Series Downsampling

Scenario: Generate hourly averages of sensor data for reporting.

SELECT
    timestamp,
    sensor_id,
    avg(temperature) as avg_temp,
    avg(pressure) as avg_pressure,
    min(temperature) as min_temp,
    max(temperature) as max_temp
FROM sensors
WHERE timestamp BETWEEN ((startDate)) AND ((endDate))
SAMPLE BY 1h
ALIGN TO CALENDAR;

Pipeline Integration: Schedule this function to run periodically and push aggregated data to BI tools, dashboards, or downstream databases.

---

Cross-Stream Time-Series Joins

Scenario: Correlate temperature readings with production events using time-based joins.

SELECT
    t.timestamp,
    t.sensor_id,
    t.temperature,
    p.event_type,
    p.machine_id
FROM sensors t
ASOF JOIN production_events p ON (t.sensor_id = p.sensor_id)
WHERE t.timestamp > dateadd('d', -1, now());

Pipeline Integration: Combine with data transformation nodes to enrich sensor data with production context for analytics.

---

Data Retention Queries

Scenario: Query data within a retention window for archiving or cleanup workflows.

SELECT count(*) as record_count,
    min(timestamp) as oldest_record,
    max(timestamp) as newest_record
FROM sensors
WHERE timestamp < dateadd('d', -((retentionDays)), now());

Pipeline Integration: Schedule this function to run daily or weekly via pipeline triggers (cron jobs) to monitor data retention.

---

Write: Ingesting Sensor Readings

Scenario: Load real-time sensor readings from IoT devices into a partitioned QuestDB table.

Configure a Write function with:

• Table Name: sensors
• Create Table If Not Exists: enabled, with a Schema that marks timestamp as the designated timestamp and sets Partition By to DAY
• Allow Schema Evolution: enabled, so a new field from the devices becomes a new column instead of failing the batch

Connect it after data collection nodes (MQTT, OPC UA, Modbus, etc.) in your pipeline. Every row must carry the timestamp field. The Write function maps the incoming fields (timestamp, sensor_id, temperature, pressure) to table columns and inserts them in batches.