QuestDB on AWS User Guide

Overview

This image runs QuestDB, the fast, open source, columnar time series database built for high throughput ingestion and low latency SQL analytics. QuestDB speaks SQL with time series extensions, ingests data over the InfluxDB line protocol and the PostgreSQL wire protocol, and answers queries through a REST API and an interactive web Console.

The QuestDB open source core is installed under /opt/questdb with its own bundled Java runtime, so the image needs no separate JRE, and it runs as a dedicated unprivileged questdb system account under a systemd service that starts it on boot and restarts it on failure. The database root lives at /var/lib/questdb, which is a dedicated, independently resizable EBS data volume holding the configuration, table data, write ahead log, logs and the Console assets.

QuestDB's open source web Console and REST API ship with no built in authentication, so the database binds every listener to the loopback interface only and is never exposed directly. An nginx reverse proxy publishes the web Console and the REST API on port 80 behind HTTP Basic authentication. The admin password is generated on the first boot of every deployed instance, so two instances launched from the same Amazon Machine Image never share a password. It is written to /root/questdb-credentials.txt with mode 0600 so that only the root user can read it.

Prerequisites

Before you deploy this image you need:

• An Amazon Web Services account where you can launch EC2 instances
• IAM permissions to launch instances, create security groups, and subscribe to AWS Marketplace products
• An EC2 key pair in the target Region for SSH access to the instance
• A VPC and subnet in the target Region, with a security group allowing inbound port 22 from your management network and port 80 for the web Console and the REST API
• The AWS CLI (version 2) installed locally if you plan to deploy from the command line

Step 1: Launch the Instance from the AWS Marketplace

Sign in to the AWS Management Console, open the EC2 service, and select Launch instance. Under Application and OS Images choose AWS Marketplace AMIs and search for QuestDB. Select the cloudimg listing and choose Select, then Continue on the subscription summary.

Pick an instance type of m6i.large or larger. Choose your EC2 key pair under Key pair (login). Under Network settings select your VPC and subnet, and either create or select a security group that opens port 22 from your management network and port 80 for the web Console and the REST API. Leave the root volume at the default size or larger.

Select Launch instance. First boot initialisation takes a few seconds after the instance state becomes Running and the status checks pass; this is when the per instance admin password is generated.

Step 2: Launch the Instance from the AWS CLI

The following block launches an instance from the cloudimg QuestDB Marketplace AMI into an existing subnet and security group. Replace <ami-id> with the AMI ID shown on the Marketplace listing, <key-name> with your EC2 key pair name, <subnet-id> with your subnet ID, and <security-group-id> with a security group that opens ports 22 and 80 as described above.

aws ec2 run-instances \
  --image-id <ami-id> \
  --instance-type m6i.large \
  --key-name <key-name> \
  --subnet-id <subnet-id> \
  --security-group-ids <security-group-id> \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=questdb}]'

When the instance reaches the Running state and its status checks pass, note its public IP address or DNS name from the EC2 console or with aws ec2 describe-instances.

Step 3: Connect to Your Instance

Connect over SSH using your key pair and the login user for your operating system variant.

ssh -i <key-name>.pem ubuntu@<public-ip>

Step 4: Retrieve the Admin Password

The web Console admin password is unique to your instance and was generated on first boot. Read the credentials file as root:

sudo cat /root/questdb-credentials.txt

The file lists the Console URL, the admin user (admin) and the generated password, along with REST API examples and notes on the PostgreSQL wire and InfluxDB line protocol ports. Keep this password somewhere safe.

Step 5: Sign In to the Web Console

The QuestDB web Console is served on port 80 by nginx, which proxies to the database on 127.0.0.1:9000 behind HTTP Basic authentication. In a browser, go to:

http://<instance-public-ip>/

You are prompted for credentials by the nginx proxy. Sign in as admin with the password from the credentials file. The Console then loads, with the SQL editor in the centre, the tables and schema explorer on the left, and the results pane below.

Type a SQL query into the editor and choose Run query (or press Ctrl+Enter). The results render in a sortable grid, and the Console reports the row count and timing for the query.

QuestDB's SQL has time series extensions such as SAMPLE BY, which downsamples rows into fixed time buckets. Run an aggregation query, switch the results pane to the chart view, and the Console plots the result so you can visualise a series over time.

The tables and schema explorer on the left lists every table in the database. Expand a table to inspect its columns and their types, along with its storage details, without writing any SQL.

Step 6: Confirm QuestDB Is Running

Over SSH, confirm the database and the nginx proxy are active and that the health endpoint answers:

sudo systemctl is-active questdb nginx

curl -s http://127.0.0.1:9003/status

You should see both services reported as active, and the health server on 127.0.0.1:9003 reports Status: Healthy. The database binds the web Console and REST API to 127.0.0.1:9000, the health and metrics server to 127.0.0.1:9003, the PostgreSQL wire protocol to 127.0.0.1:8812 and the InfluxDB line protocol to 127.0.0.1:9009, all loopback only; nginx publishes the Console and REST API on port 80.

Confirm the bundled Java runtime that ships with QuestDB:

/opt/questdb/bin/java -version

Step 7: Run SQL Over the REST API

QuestDB exposes its REST API on the same port 80 as the Console, behind the same HTTP Basic credentials. The /exec endpoint runs SQL and returns JSON. On the instance you can call the loopback database directly without credentials:

curl -s -G 'http://127.0.0.1:9000/exec' --data-urlencode 'query=SELECT 1'

From outside the instance, send the same request through the proxy on port 80 with basic auth user admin and the generated password, for example curl -G -u admin:<password> 'http://<instance-public-ip>/exec' --data-urlencode 'query=SELECT now()'. The /imp endpoint bulk imports CSV and the /exp endpoint exports query results as CSV, so the REST API doubles as an ingestion and extraction interface over your time series data.

Step 8: Ingest Data

QuestDB accepts high throughput writes over two protocols, both of which bind to loopback by default so they are not exposed without a deliberate change:

• InfluxDB line protocol (ILP) — the recommended high throughput ingestion path, on TCP port 9009. Point an InfluxDB line protocol client or a Telegraf agent at the instance to stream metrics, events or tick data.
• PostgreSQL wire protocol — on TCP port 8812. Connect with any PostgreSQL client or driver (for example psql, JDBC or psycopg) using the admin user and the generated password to run inserts and queries.

To expose either protocol beyond the instance, edit the bind addresses in /var/lib/questdb/conf/server.conf, restart the service, and open the corresponding port in the security group, restricting the source to the networks that ingest data. For production you should front the exposed port with TLS as described in Step 11. The web Console also has a built in Import CSV panel for loading a file directly through the browser.

Step 9: Query with SQL and Time Series Extensions

Query QuestDB through the web Console, the REST API, or any PostgreSQL client. QuestDB uses a columnar storage engine partitioned by time, with SQL extensions for downsampling (SAMPLE BY), interpolation (FILL) and time aligned joins (ASOF JOIN and LT JOIN), so large time series datasets stay fast on modest hardware. On the instance you can run any query over loopback without credentials:

curl -s -G 'http://127.0.0.1:9000/exec' --data-urlencode "query=SELECT now()"

From outside the instance, send the same request to http://<instance-public-ip>/exec with basic auth user admin and the generated password.

Step 10: The Data Volume

The QuestDB database root lives on a dedicated EBS volume mounted at /var/lib/questdb. This keeps the time series store off the operating system disk and lets you resize or snapshot it independently. Confirm the mount with:

df -h /var/lib/questdb

The volume holds the conf/ configuration, the db/ table data, write ahead log and sequencer, and the log/ and public/ directories. To grow the store, expand the EBS volume in the AWS console, then grow the filesystem on the instance with sudo resize2fs on the underlying device. Snapshotting this volume captures the complete time series database.

Step 11: Enable HTTPS

The web Console and REST API are served over plain HTTP on port 80 by nginx. For production use, place them behind TLS. Obtain a certificate for your domain (for example with a managed certificate on an Application Load Balancer in front of the instance, or with Certbot installed on the instance), then configure nginx to listen on 443 with your certificate and proxy to 127.0.0.1:9000 exactly as the bundled site does for port 80, keeping the HTTP Basic authentication in place. Restrict the security group so ports 80 and 443 are reachable only from the networks that ingest and query data.

Step 12: Backup and Maintenance

Back up the database by snapshotting the /var/lib/questdb EBS volume, which captures the entire time series store, or by using the QuestDB SNAPSHOT SQL command to take a consistent filesystem snapshot before backing up. Apply operating system security updates with sudo apt-get update && sudo apt-get upgrade and reboot when a new kernel is installed; QuestDB and nginx start automatically on boot.

Support

This image is published and supported by cloudimg. Support covers deployment, ingestion protocol configuration, schema and partitioning design, SQL queries, PostgreSQL and InfluxDB integration, the authenticating proxy, TLS and storage tuning. Contact cloudimg through the support channel listed on the AWS Marketplace listing.

All product and company names are trademarks or registered trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.