Duckgres

A PostgreSQL wire protocol compatible server backed by DuckDB. Connect with any PostgreSQL client (psql, pgAdmin, lib/pq, psycopg2, etc.) and get DuckDB's analytical query performance.

Table of Contents

• Features
• Metrics
• Runbooks
• Perf Runbook
• Worker Upgrades & Canaries
• Dev Scenario Runner
• Quick Start
• Configuration
• YAML Configuration
• Environment Variables
• CLI Flags
• PostHog Logging
• PostHog Product-Analytics Events
• DuckDB Extensions
• DuckLake Integration
• Quick Start with Docker
• Object Storage Configuration
• Seeding Sample Data
• COPY Protocol
• Graceful Shutdown
• Rate Limiting
• Usage Examples
• Architecture
• Standalone Mode
• Control Plane Mode
• Remote Worker Backend
• Two-Tier Query Processing
• Supported Features
• Transaction Isolation
• Limitations
• SQL Client Compatibility
• Dependencies
• License

Features

• PostgreSQL Wire Protocol: Compatibility with PostgreSQL clients for analytical workloads
• Two-Tier Query Processing: Transparently handles both PostgreSQL and DuckDB-specific syntax
• TLS Encryption: Required TLS connections with auto-generated self-signed certificates
• Per-User Databases: Each authenticated user gets their own isolated DuckDB database file
• Password Authentication: Cleartext password authentication over TLS
• Extended Query Protocol: Support for prepared statements, binary format, and parameterized queries
• COPY Protocol: Bulk data import/export with COPY FROM STDIN and COPY TO STDOUT
• DuckDB Extensions: Configurable extension loading (ducklake enabled by default)
• DuckLake Integration: Auto-attach DuckLake catalogs for lakehouse workflows
• Rate Limiting: Built-in protection against brute-force attacks
• Graceful Shutdown: Waits for in-flight queries before exiting
• Control Plane Mode: Multi-process architecture with long-lived workers, zero-downtime deployments, and rolling updates
• Flexible Configuration: YAML config files, environment variables, and CLI flags
• Prometheus Metrics: Built-in metrics endpoint for monitoring

Metrics

Duckgres exposes Prometheus metrics on :9090/metrics. The metrics port is currently fixed at 9090 and cannot be changed via configuration.

See docs/metrics.md for exact request-path boundaries, labels, aggregation rules, PromQL examples, and admission metric migration.

Testing Metrics

• scripts/test_metrics.sh - Runs a quick sanity check (starts server, runs queries, verifies counts)
• scripts/load_generator.sh - Generates continuous query load until Ctrl-C
• scripts/perf_smoke.sh - Runs the golden-query perf harness and writes artifacts to artifacts/perf/<run_id>
• scripts/perf_nightly.sh - Nightly wrapper with lock/timeout guards and optional artifact publisher
• metrics-compose.yml - Starts Prometheus and Grafana locally for metrics (Prometheus at http://localhost:9091, Grafana at http://localhost:3000)

Query Log

When DuckLake uses a Postgres metadata store, Duckgres writes durable per-query history to the native Postgres table querylog.query_log_entries. The query log is queryable through ducklake.system.query_log, a live view over that native Postgres table. The view is not DuckLake snapshot data.

Rows record SQL user (user_name), org, query text, duration, row counts, errors, trace/span IDs, and profiling-derived resource usage. cpu_time_s is DuckDB cumulative CPU/thread time in seconds, and peak_buffer_memory_bytes is DuckDB's system_peak_buffer_memory in bytes, not process RSS.

query_id is a per-statement UUIDv7 minted when the query arrives. It is time-ordered, appears on the statement's OTEL span (duckgres.query_id) and its error logs, and is the key that correlates every query-log event for one statement. A batched simple query (SELECT 1; SELECT 2) runs each statement under its own query_id, with parent_query_id and statement_index identifying the Query message they arrived in.

Statements produce a pair of events, using ClickHouse's type vocabulary (QueryStart = 1, QueryFinish = 2, ExceptionBeforeStart = 3, ExceptionWhileProcessing = 4):

• QueryStart is emitted when the statement begins executing.
• One terminal event follows: QueryFinish, or ExceptionWhileProcessing if it failed after execution began, or ExceptionBeforeStart if it failed before execution began — auth or policy denial, a transpile error, a failure to obtain a worker, or an extended-protocol Describe whose prepare the engine rejected. ExceptionBeforeStart events have no QueryStart, by definition.

The boundary is execution began, not an engine saw it: Describe hands the statement to a worker to learn its result schema, so a binder error there is an ExceptionBeforeStart even though the engine did see the SQL. This is the same line ClickHouse draws — analysis-time failures are ExceptionBeforeStart. In practice this is the largest source of them, so when triaging, read ExceptionBeforeStart as "never ran", not as "never reached a worker".

A QueryStart with no terminal event is a query that never came back — a worker OOM-killed mid-statement, a pod evicted. That row is the only evidence such a query ever ran, so treat a sustained population of unpaired starts as an incident signal, allowing for queries still in flight.

The query_id travels to the worker on every statement RPC (x-duckgres-query-id), and the worker stamps it on its own logs — notably the "Query appears stuck" warning. That is what closes the loop on an unpaired QueryStart: the statement's own log row cannot exist, but the pod's last words about it carry the same ID.

event_time is the statement's start time on every event type, including terminal ones. This diverges from ClickHouse, where event_time is when the event was logged: pinning both rows of a pair to the same instant keeps them in one monthly partition and lets them join without a window function. A terminal row's finish time is event_time + query_duration_ms.

query_log.start_events selects which statements get a QueryStart:

• data (default) — statements that touch data or change schema. Transaction control, SET/RESET/SHOW, and catalog introspection are skipped: they never hang, and they are the noisiest statements a driver sends.
• all — every statement.
• off — no start events.

Terminal events are always logged regardless of this setting, so nothing disappears from the log; cheap statements simply have no paired start row. Also settable via DUCKGRES_QUERY_LOG_START_EVENTS.

Each event also records what the statement touches, extracted from its parse tree (server/querymeta):

• access_kinds — the access classes the statement needs, comma-separated: read, write, ddl, config, admin, transaction, metadata, unknown. A statement can be several at once: WITH x AS (INSERT …) SELECT is both a read and a write, which a classifier based on the command tag gets wrong.
• query_metadata — JSON with the resolved detail: read_relations and write_relations (split, because grants are directional), columns, functions, and table_functions.
• metadata_complete — false when extraction could not see the whole statement. DuckDB-native syntax (ATTACH, CREATE SECRET, PIVOT, SUMMARIZE) is not parseable as PostgreSQL and falls back to a coarse lexical classification.

That last column is load-bearing. These signals exist to let an authorization policy be evaluated against real traffic before it denies anything, so "referenced no relations" and "we could not tell what it referenced" must never be the same answer: a consumer that gates on query_metadata must treat metadata_complete = false as unknown, and deny.

table_functions is recorded alongside relations because `read_parquet('s3:/