QuestDB on a VPS: a SQL time-series database

Why a time-series database

A time series is a sequence of dated values: a probe's temperature every ten seconds, the number of orders per minute. It is written a lot, rarely modified, and almost always queried by time range.

QuestDB is built around that use. Each table has a designated time column and is split into partitions (by day or by hour), which keeps queries over a period fast and makes deleting old data instantaneous. Its SQL is still SQL, enriched with a few time-specific keywords.

What QuestDB brings

Time-series SQL — SAMPLE BY, LATEST ON and ASOF JOIN on top of standard SQL.
Continuous ingestion — InfluxDB Line Protocol over HTTP, with official clients for Python, Go, Java, Node.js, Rust and .NET.
Web console — SQL editor, drag-and-drop CSV import, charts of the results.
REST API — queries, CSV import and export over HTTP.
PostgreSQL protocol — your usual PostgreSQL drivers and tools.
Time partitions — fast queries over a period, old data deleted in one command.

QuestDB versus a regular PostgreSQL table

Scroll the table

	QuestDB	PostgreSQL alone
Built for	Continuous timestamped measurements	General relational data
Aggregate by interval	SAMPLE BY 1h	date_trunc and GROUP BY
Latest value per series	LATEST ON	Subquery or DISTINCT ON
Align two streams in time	ASOF JOIN	Lateral join to write
Delete old history	DROP PARTITION	DELETE then VACUUM
Ingestion	Line Protocol, CSV, SQL	SQL and COPY

Prerequisites

An Ubuntu 24.04 VPS with at least 2 GB of RAM and 20 GB of disk, Docker and the Compose plugin installed, and a name (subdomain) pointing to the VPS for HTTPS. QuestDB runs on the JVM: plan 4 GB of RAM if you ingest a lot of data or run heavy queries.

Install QuestDB with Docker Compose

01
Prepare the directory and the password
Create the application directory and a .env file holding the admin user's password.
```
mkdir -p /opt/questdb && cd /opt/questdb
echo "ADMIN_PASSWORD=$(openssl rand -hex 16)" > .env
chmod 600 .env
```

Write the docker-compose.yml

Ports are only published on the loopback interface: only nginx reaches the console and the API. QDB_HTTP_USER and QDB_HTTP_PASSWORD protect the whole HTTP API, QDB_PG_PASSWORD replaces the PostgreSQL protocol's factory password. TCP ingestion, unauthenticated in the open-source edition, is turned off, and QDB_TELEMETRY_ENABLED disables anonymous telemetry.

services:
  questdb:
    image: questdb/questdb:10.1.0
    restart: unless-stopped
    ports:
      - "127.0.0.1:9000:9000"
      - "127.0.0.1:8812:8812"
    environment:
      QDB_HTTP_USER: "admin"
      QDB_HTTP_PASSWORD: "${ADMIN_PASSWORD}"
      QDB_PG_USER: "admin"
      QDB_PG_PASSWORD: "${ADMIN_PASSWORD}"
      QDB_LINE_TCP_ENABLED: "false"
      QDB_HTTP_SETTINGS_READONLY: "true"
      QDB_TELEMETRY_ENABLED: "false"
    volumes:
      - questdb-data:/var/lib/questdb

volumes:
  questdb-data: {}

Start and check

Startup takes a few seconds. Without credentials the API answers 401; with them it returns the installed version.

docker compose up -d
curl -s -o /dev/null -w '%{http_code}\n' 'http://127.0.0.1:9000/exec?query=SELECT%201'
curl -u admin:VOTRE_MOT_DE_PASSE 'http://127.0.0.1:9000/exec?query=SELECT%20build()'

Publish over HTTPS with nginx

In your domain's vhost (certificate obtained with certbot), proxy to the local port. Raise client_max_body_size for CSV imports.

location / {
    proxy_pass http://127.0.0.1:9000;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Proto $scheme;
    client_max_body_size 256m;
}

Without QDB_HTTP_USER, the API is open to everyone

With no declared user, QuestDB accepts every HTTP request: anyone who knows the address can read your data and create, modify or drop tables in SQL. Keep authentication on, change the PostgreSQL protocol's factory password, and never publish ports 9000, 8812 or 9009 directly on the public IP: always go through the HTTPS reverse proxy.

Send data

The simplest way is the InfluxDB Line Protocol over HTTP: one line per measurement, with the table name, tags and values. The table and its columns are created on the first write.

curl -u admin:VOTRE_MOT_DE_PASSE -X POST \
  --data-binary 'capteurs,sonde=s1 temperature=21.5' \
  https://votre-domaine/write

From your code, an official client batches the lines and handles retries. In Python, after pip install questdb:

from questdb.ingress import Sender, TimestampNanos

conf = "https::addr=votre-domaine:443;username=admin;password=VOTRE_MOT_DE_PASSE;"
with Sender.from_conf(conf) as sender:
    sender.row(
        "capteurs",
        symbols={"sonde": "s1"},
        columns={"temperature": 21.5},
        at=TimestampNanos.now(),
    )

Query your series in SQL

In the web console or through the REST API (/exec?query=…), standard SQL works, and the time extensions spare you complicated queries.

-- Moyenne horaire par sonde
SELECT timestamp, sonde, avg(temperature)
FROM capteurs
SAMPLE BY 1h;

-- Dernière valeur connue de chaque sonde
SELECT * FROM capteurs LATEST ON timestamp PARTITION BY sonde;

A program running on the VPS itself can also connect over the PostgreSQL protocol on 127.0.0.1:8812, database qdb, user admin and the same password.

Model a time-series table properly

Automatic creation on the first write is enough to get started. For a table meant to last, declare it yourself: you choose the types, the time column and the partitioning.

CREATE TABLE capteurs (
  timestamp TIMESTAMP,
  sonde SYMBOL,
  temperature DOUBLE
) TIMESTAMP(timestamp) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(timestamp, sonde);

Three choices matter. The designated time column (TIMESTAMP(...)) is the one QuestDB sorts and partitions on: every time extension relies on it. The SYMBOL type suits repeated labels (probe name, device code, ticker): the value is stored once and referenced, which saves disk and speeds up filters. Partitioning (PARTITION BY DAY or HOUR) depends on volume: a partition should stay a reasonable size, and it is the unit you will delete later.

The DEDUP UPSERT KEYS clause makes ingestion idempotent: if a client resends the same measurement after a network cut, the row is replaced instead of duplicated.

Import and export CSV files

The console accepts CSV files by drag and drop. From the command line, /imp imports a file into a table and /exp exports a query result.

curl -u admin:VOTRE_MOT_DE_PASSE -F [email protected] 'https://votre-domaine/imp?name=mesures'
curl -u admin:VOTRE_MOT_DE_PASSE -G --data-urlencode 'query=SELECT * FROM mesures' https://votre-domaine/exp > export.csv

Retention, disk and backup

Nothing expires on its own. Drop old partitions in SQL and schedule the command if needed. For a consistent backup, freeze the database state while you copy the questdb-data volume.

ALTER TABLE capteurs DROP PARTITION
WHERE timestamp < dateadd('d', -90, now());

CHECKPOINT CREATE;
-- copiez le volume questdb-data, puis :
CHECKPOINT RELEASE;

Update QuestDB

Read the release notes before moving to a new major version, then replace the image tag and restart: the data in the volume is kept.

cd /opt/questdb
# changez le tag de l'image dans docker-compose.yml, puis :
docker compose pull && docker compose up -d

Common errors and troubleshooting

401 on every request. The user or password does not match QDB_HTTP_USER and QDB_HTTP_PASSWORD. Check the .env file, then restart the container after any change: the variables are only read at startup.

413 during a CSV import. The file exceeds the nginx limit. Raise client_max_body_size in the vhost, reload nginx, or split the file.

The container keeps restarting. Read docker compose logs questdb. The most common cause is memory: QuestDB runs on the JVM and a 1 GB VPS is not enough. Move to at least 2 GB.

The disk fills up. Nothing expires by default: schedule the deletion of old partitions and watch the size of the questdb-data volume with docker system df -v.

An application on the VPS cannot connect over PostgreSQL. Use 127.0.0.1:8812, database qdb, user admin and the password from .env: the factory password quest is refused.

One-click install from the ServOrbit Marketplace

On a ServOrbit VPS, QuestDB installs from the Marketplace: the script deploys the official container, generates the admin user's password, enables authentication on the HTTP API and the PostgreSQL protocol, turns off telemetry and configures nginx over HTTPS, on a subdomain of your domain or on the VPS's free subdomain. The password is shown in your client area.