🔐 Using TLS with PgBouncer for QuestDB

javier ramírez — Mon, 09 Jun 2025 15:45:14 +0000

If you're putting PgBouncer in front of QuestDB to enable TLS, you might run into a confusing error:

closing because: server refused SSL

Here’s what’s going on — and how to fix it.

🧩 The Setup

You likely have a pgbouncer.ini that looks something like this:

[databases]
questdb = host=127.0.0.1 port=8812 dbname=questdb user=admin password=secret

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 5432
auth_type = trust

client_tls_sslmode = require
client_tls_key_file = /path/pgbouncer.key
client_tls_cert_file = /path/pgbouncer.crt
client_tls_ca_file = /etc/ssl/cert.pem

server_tls_sslmode = require

You then launch PgBouncer:

pgbouncer ./pgbouncer.ini

And try connecting like this:

psql "host=127.0.0.1 port=5432 dbname=questdb user=admin sslmode=require"

But the connection fails with:

closing because: server refused SSL

💡 What’s Going Wrong?

QuestDB (Open Source) does not support TLS over the PostgreSQL wire protocol.

So when PgBouncer tries to connect to QuestDB using server_tls_sslmode = require, the QuestDB server responds with “refused SSL.”

✅ The Fix: Disable TLS Between PgBouncer and QuestDB

Update your pgbouncer.ini config like so:

-server_tls_sslmode = require
+server_tls_sslmode = disable

This keeps TLS enabled from the client to PgBouncer, but disables it between PgBouncer and QuestDB, which is necessary for compatibility.

🔐 TLS Path Overview

Connection	TLS Enabled?	PgBouncer Config
Client → PgBouncer	✅ Yes	`client_tls_sslmode = require`
PgBouncer → QuestDB	❌ No	`server_tls_sslmode = disable`

This lets you offer encrypted connections to clients, even if QuestDB itself doesn’t handle TLS.

📝 Final Thoughts

QuestDB OSS doesn’t support TLS on the PostgreSQL wire protocol.
You can terminate TLS at PgBouncer.
If you need full end-to-end encryption, you’ll need QuestDB Enterprise.

This setup is ideal for local or internal deployments where PgBouncer acts as a secure gateway to QuestDB.

⏱️ Taming QuestDB Materialized View Refresh Frequency

javier ramírez — Mon, 02 Jun 2025 15:54:00 +0000

Ever implemented a materialized view in QuestDB only to find it refreshing constantly, even when you just want daily snapshots? You're not alone. By default, QuestDB materialized views use INCREMENTAL refresh mode — which means they update as soon as new data arrives in the base table.

This is great for real-time dashboards… but wasteful if you only need a daily roll-up for analysis.

🔧 The Problem: Too Many Refreshes

Let’s say you have a trades table with market data arriving every second. If you create a materialized view like this:

CREATE MATERIALIZED VIEW trades_latest_1d AS (
  SELECT
    timestamp,
    symbol,
    side,
    last(price) AS price,
    last(amount) AS amount,
    last(timestamp) AS latest
  FROM trades
  SAMPLE BY 1d
)
PARTITION BY DAY;

It will refresh every time new trades are inserted — hundreds or thousands of times per day. That's the default behavior of INCREMENTAL refresh mode.

✅ The Solution: Use `incremental_timer` Instead

You can control how often your materialized view refreshes by specifying a scheduled interval. Here's how:

CREATE MATERIALIZED VIEW trades_latest_1d
  REFRESH START '2025-05-30T00:00:00.000000Z'
  EVERY 1d
AS (
  SELECT
    timestamp,
    symbol,
    side,
    last(price) AS price,
    last(amount) AS amount,
    last(timestamp) AS latest
  FROM trades
  SAMPLE BY 1d
)
PARTITION BY DAY;

This switches the view to incremental_timer mode, which means it will only refresh once per day, at midnight UTC. You can adjust the EVERY clause to suit your needs (e.g. 1h, 5m, 7d, etc).

🔍 How to Check If It’s Working

After creating the view:

SELECT * FROM materialized_views();

Look for:

refresh_type = incremental_timer
last_refresh_start_timestamp = null (initially)

Then, after the first scheduled refresh (in this case, at midnight), the view will populate and the timestamp fields will be updated.

🧪 If You Want More Frequent Updates

If you still want intraday updates but less noise, you could do:

REFRESH START '2025-05-30T00:00:00.000000Z'
EVERY 5m

Now the view only refreshes every 5 minutes — much saner than every insert.

🧠 TL;DR

Use Case	Refresh Mode	Recommended Interval
Real-time dashboard	`INCREMENTAL`	N/A (default)
Daily OHLC summary	`incremental_timer`	`EVERY 1d`
Light intraday reporting	`incremental_timer`	`EVERY 5m` or `1h`
Weekly snapshot for archiving	`incremental_timer`	`EVERY 7d`

📝 Final Thoughts

QuestDB’s default behavior favors low-latency use cases. But if you're building a daily view of activity, using REFRESH EVERY gives you total control over how often data is materialized — saving compute, reducing WAL activity, and improving predictability.

Perfect for finance, monitoring, and anyone with large data volumes but low update frequency requirements.

Forem: javier ramírez

🔐 Using TLS with PgBouncer for QuestDB

🧩 The Setup

💡 What’s Going Wrong?

✅ The Fix: Disable TLS Between PgBouncer and QuestDB

🔐 TLS Path Overview

📝 Final Thoughts

⏱️ Taming QuestDB Materialized View Refresh Frequency

🔧 The Problem: Too Many Refreshes

✅ The Solution: Use incremental_timer Instead

🔍 How to Check If It’s Working

🧪 If You Want More Frequent Updates

🧠 TL;DR

📝 Final Thoughts

✅ The Solution: Use `incremental_timer` Instead