Duckgres

Trino operators: see cell registration and placement for the optional registry, unchanged legacy defaults, and migration limitations.

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
• Trino API identity
• Metrics
• Runbooks

• Quick Start
• Configuration

• DuckDB Extensions
• DuckLake Integration

• COPY Protocol
• Graceful Shutdown
• Rate Limiting
• Usage Examples
• Architecture

• 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

Trino API identity

The optional rollout readiness endpoint observes registered backend pods, coordinator identity, workers, and a dedicated warehouse canary. It is disabled by default and does not provision or move tenants.

DUCKGRES_TRINO_HOGLAKE_URI defaults to empty (DuckLake catalog provisioning). The frozen perf deployments set it automatically to their namespace-local Hoglake service, replacing the Trino backend in the existing cached and uncached scenarios. See the scenario runbook.

The existing Trino deployment appears as legacy in the Trino console API. This name does not change its stored org assignments or catalog-store key. DUCKGRES_TRINO_CELL_ID remains the ownership setting, with the existing default cell-001; do not change it to legacy to match the API display name. Connection details remain readiness-gated and use the existing endpoint. Trino readiness requires a reconciled catalog and the current tenant password file on every active coordinator and worker of every running backend. Secret projection lag remains provisioning; catalog creation alone does not make a tenant ready. Checks use namespace-scoped pod read/exec access, with batches of 128 files, up to four concurrent observations and a five-second timeout per observation within the existing 30-second backend budget. These limits are fixed defaults. See the readiness runbook for deployment requirements, local verification, and recovery. See the Trino admin API documentation for local verification, compatibility details, and recovery instructions.

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.

| Metric | Type | Description | |--------|------|-------------| | duckgres_connections_open | Gauge | Process-wide number of currently open client connections, including native metadata-proxy sockets | | duckgres_connection_duration_seconds{org} | Histogram | Worker-backed Duckgres connection lifetime, accept→disconnect (includes _count, _sum, _bucket); excludes native metadata-proxy connections, which use their dedicated duration family | | duckgres_metadata_proxy_connections_open{org} | Gauge | Current admitted native metadata Postgres proxy connections; process-local, so sum across control-plane replicas | | duckgres_metadata_proxy_connection_attempts_total{org,outcome} | Counter | Metadata proxy attempts by bounded terminal outcome | | duckgres_metadata_proxy_connection_duration_seconds{org} | Histogram | Lifetime of admitted metadata proxy connections, including upstream bootstrap | | duckgres_metadata_proxy_upstream_connect_duration_seconds{org,outcome} | Histogram | Internal metadata Postgres connect/auth latency; outcome is success or error | | duckgres_metadata_proxy_bytes_total{org,direction} | Counter | Post-authentication pgwire bytes relayed in client_to_upstream or upstream_to_client direction | | duckgres_metadata_proxy_cancel_requests_total{outcome} | Counter | Raw metadata-proxy CancelRequests handled as session_terminated on the owning control-plane replica or not_local on another replica | | duckgres_query_total{org,status,reason} | Counter | Total non-empty query attempts. Valid status/reason pairs: success/none; failure/user, failure/canceled, failure/conflict; error/metadata_connection_lost, error/system. | | duckgres_query_duration_seconds{org} | Histogram | Simple/extended query execution latency (includes _count, _sum, _bucket); use duckgres_query_total for attempt totals | | duckgres_auth_failures_total | Counter | Process-wide authentication failures, including wrong-password metadata-proxy attempts; use duckgres_metadata_proxy_connection_attempts_total{outcome="auth_failed"} for the proxy-specific split | | duckgres_rate_limit_rejects_total | Counter | Process-wide pre-TLS connection rejections due to rate limiting; these cannot be attributed to the worker or metadata endpoint because SNI is not available yet | | duckgres_rate_limited_ips | Gauge | Number of currently rate-limited IP addresses | | duckgres_control_plane_workers_active | Gauge | Number of active control-plane worker processes | | duckgres_control_plane_worker_acquire_seconds | Histogram | Time spent acquiring a worker for a new session | | duckgres_control_plane_worker_queue_depth | Gauge | Approximate number of session requests waiting on worker acquisition | | duckgres_control_plane_worker_spawn_seconds | Histogram | Time spent spawning and health-checking a new worker | | duckgres_session_admission_evaluation_duration_seconds{decision,reason} | Histogram | Latency of one DB-backed admission poll for the polling request | | duckgres_session_admission_evaluations_total{decision,reason} | Counter | Admission request polls; repeated polls are distinct evaluations | | duckgres_session_admission_wait_seconds{org,outcome,reason} | Histogram | End-to-end wait for one successfully enqueued admission request | | duckgres_session_admission_requests_total{org,outcome,reason} | Counter | Exactly one terminal event per successfully enqueued admission request | | duckgres_session_admission_queue_depth{org} | Gauge | Local callers waiting after successful durable enqueue; sum across replicas | | duckgres_session_admission_active_vcpus{org} | Gauge | Requested vCPUs held by local live lease handles; cleanup-pending durable rows are excluded | | duckgres_session_admission_limit_vcpus{org} | Gauge | Config-reconciled effective org cap for active org stacks; zero means unlimited, max across replicas | | duckgres_session_admission_reclaim_pending | Gauge | Activated cleanup intents awaiting or executing exact database reclamation | | duckgres_session_admission_reclaim_attempts_total{outcome} | Counter | Exact cleanup attempts by success or error outcome | | duckgres_session_admission_reclaim_reservations_in_use | Gauge | Cleanup-ownership slots held before enqueue, while queued or live, and during pending cleanup | | duckgres_session_admission_reclaim_reservation_capacity | Gauge | Cleanup-ownership slot capacity for this control-plane process (4096 per reclaimer by default) | | duckgres_session_admission_reclaim_reservation_rejections_total{reason} | Counter | Reservations rejected because capacity was full, the reclaimer was closed, or the exact reference was a duplicate | | duckgres_session_start_duration_seconds{org,protocol,outcome} | Histogram | Authenticated PostgreSQL session bootstrap through flushed ReadyForQuery | | duckgres_postgres_session_start_total{org,outcome,reason} | Counter | Exactly one terminal result per authenticated PostgreSQL session start after server retries; outcome is success\|failure and bounded reasons distinguish operator-actionable failures from client/lifecycle noise |

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/
• 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

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

• all — every statement.
• off — no 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:

• query_metadata — JSON with the resolved detail: read_relations and

• metadata_complete — **false when extraction could not see the whole

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://…') reaches data without naming a relation, so a policy built on relation names alone would not see it at all. Reading an external location is supported usage — a tenant pointing read_parquet at their own bucket is a feature, and it is classified as a plain read. The cross-tenant question is about the target, not the function: an entry marked external records enough of the path (scheme, host, path — credentials in a presigned URL's query string are stripped) for a policy to decide whether it resolves inside managed DuckLake storage. Moving data the other way, COPY … TO 's3://…', keeps the admin class: egress is a different risk from reading a location in.

Extraction runs on the redacted statement text, so credential material never reaches the parser. It costs one parse per distinct statement, memoized per process; disable with query_log.metadata: false or DUCKGRES_QUERY_LOG_METADATA=false.

The column set has a single source of truth: queryLogColumns in server/querylog_schema.go. It generates the CREATE TABLE DDL, the ALTER TABLE ... ADD COLUMN IF NOT EXISTS migration that brings already provisioned tenants forward, the INSERT column list and argument order, the partition-repair copy list, and the ducklake.system.query_log view. Adding a column means appending one entry there; existing tenants pick it up on the next sink initialization, and a view whose columns have drifted is rebuilt with CREATE OR REPLACE VIEW. Append only — never reorder or remove an entry, and an appended column must be nullable or carry a DEFAULT (a bare NOT NULL column cannot be added to a populated table).

Runbooks

• Worker Upgrades & Canaries: Process for upgrading DuckDB/DuckLake versions, canarying builds for a subset of tenants, and global version management.
• Node-local Cache Proxy Bypass: Fail-open cache behavior, detection, and recovery.
• Performance Harness: Local smoke and nightly operations for performance testing.
• Dev Scenario Runner: Scheduled and manually dispatched scenario runs against the configured dev environment.
• Control Plane Rollout: Zero-downtime deployment process for the control plane itself.
• Org Connection Admission: Global vCPU admission, exact cleanup ownership, failure recovery, and operational metrics.
• Managed Warehouse Provisioning Recovery: Diagnose a failed warehouse whose Duckling dependencies were repaired and verify automatic convergence back to ready.
• Managed Warehouse Deprovision: Destructive teardown process for managed warehouse infrastructure and org cleanup.
• Resharding Operations: Runner recovery, durable respawn reset, safety checks, and local verification.

Quick Start

The project uses just as a command runner. Run just to see all available recipes.

Build & Run

just build    # Build the binary
just run      # Run in standalone mode

The server starts on port 5432 by default with TLS enabled. Database files are stored in ./data/. Self-signed certificates are auto-generated in ./certs/ if not present.

Connect

just psql     # Connect via psql (port 5432)
just psql 35437  # Connect on a different port

Docker

just docker   # Build image (tagged duckgres:dev)
docker run --rm -p 5432:5432 -p 9090:9090 duckgres:dev

Mount a config file and persist data:

docker run --rm \
  -p 5432:5432 -p 9090:9090 \
  -v ./duckgres.yaml:/app/duckgres.yaml \
  -v ./data:/app/data \
  duckgres:dev

Configuration

Duckgres supports three configuration methods (in order of precedence):

1. CLI flags (highest priority)
2. Environment variables
3. YAML config file
4. Built-in defaults (lowest priority)

Node-local cache proxy

Kubernetes workers can use the optional node-local NVMe cache proxy with DUCKGRES_CACHE_ENABLED=true. The worker waits at most DUCKGRES_CACHE_PROXY_CONNECT_TIMEOUT (default: 5s, maximum: 10s) for its initial health check. It then starts normally: a worker-local forward router bypasses an unhealthy proxy and fetches signed objects from the authoritative S3 source. The router probes for recovery with capped exponential backoff and jitter, and re-enables the local cache after a healthy probe. This setting is an environment variable only; it is injected into worker pods alongside NODE_IP.

Cache-proxy loss affects cache performance, not worker readiness or PostgreSQL session admission. A bypass does not hide HTTP/S3 responses from the proxy, and does not replay writes; only a GET/HEAD whose local-proxy connection failed before a response is received is retried against the authoritative source.

YAML Configuration

Create a duckgres.yaml file (see duckgres.example.yaml for a complete example):

host: "0.0.0.0"
port: 5432
data_dir: "./data"
session_init_timeout: "10s"
admission_reclaimer_max_reservations: 4096
tls:
  cert: "./certs/server.crt"
  key: "./certs/server.key"
users:
  postgres: "postgres"
  alice: "alice123"
extensions:
  - ducklake
  - httpfs
ducklake:
  metadata_store: "postgres:host=localhost user=ducklake password=secret dbname=ducklake"
  # Default: true. Disables postgres_scanner thread-local caching for the
  # hidden DuckLake metadata pool to reduce retained metadata connections.
  # Set to false to opt back into warm connection reuse.
  disable_metadata_thread_local_cache: true
  # Default: false. Also attach a Delta Lake catalog/table on worker boot.
  # Without delta_catalog_path, defaults to a sibling top-level delta/ prefix
  # beside the configured DuckLake object_store prefix.
  delta_catalog_enabled: false
  # delta_catalog_path: "s3://bucket/delta/"
process:
  min_workers: 0
  max_workers: 0
  retire_on_session_end: false
rate_limit:
  max_failed_attempts: 5
  failed_attempt_window: "5m"
  ban_duration: "15m"
  max_connections_per_ip: 100
query_log:
  enabled: true
  flush_interval: "5s"
  batch_size: 1000

Run with config file:

./duckgres --config duckgres.yaml

Environment Variables

| Variable | Description | Default | |----------|-------------|---------| | DUCKGRES_CONFIG | Path to YAML config file | - | | DUCKGRES_HOST | Host to bind to | 0.0.0.0 | | DUCKGRES_PORT | Port to listen on | 5432 | | DUCKGRES_DATA_DIR | Directory for DuckDB files | ./data | | DUCKGRES_CERT | TLS certificate file | ./certs/server.crt | | DUCKGRES_KEY | TLS private key file | ./certs/server.key | | DUCKGRES_MEMORY_LIMIT | DuckDB memory_limit per session (e.g., 4GB) | Auto-detected | | DUCKGRES_THREADS | DuckDB threads per session | 2.5 × runtime.NumCPU(), rounded up | | DUCKGRES_DISABLE_PARQUET_PREFETCHING | Disable DuckDB Parquet prefetching for standalone/process workers and control-plane-spawned K8s workers. Boolean values use Go's accepted forms (true, TRUE, 1, etc.). | false | | DUCKGRES_PROCESS_ISOLATION | Enable process isolation (1 or true) | false | | DUCKGRES_PROCESS_RETIRE_ON_SESSION_END | Retire a process worker immediately after its last session ends instead of keeping it warm for reuse | false | | DUCKGRES_IDLE_TIMEOUT | Connection idle timeout (e.g., 30m, 1h, -1 to disable) | 24h standalone, 5m control-plane | | DUCKGRES_CLIENT_IDLE_TIMEOUT_MAX | Maximum client-requested duckgres.idle_timeout; unset disables client overrides | disabled | | DUCKGRES_SESSION_INIT_TIMEOUT | Session startup metadata initialization and catalog probe timeout | 10s | | DUCKGRES_WORKER_QUEUE_TIMEOUT | Max time to wait for worker acquisition and per-org/per-user vCPU resource admission; the managed K8s queue TTL uses this value | 60s | | DUCKGRES_ADMISSION_RECLAIMER_MAX_RESERVATIONS | Max queued/live admission identities whose cleanup ownership one control plane may retain; new admissions are rejected before enqueue when full | 4096 | | DUCKGRES_HANDOVER_DRAIN_TIMEOUT | Max time to drain planned shutdowns and upgrades before forcing exit | 24h in process mode, 15m in remote K8s mode | | DUCKGRES_SNI_ROUTING_MODE | Multi-tenant managed-hostname routing: off, passthrough, or enforce. Postgres uses the requested dbname first; managed SNI must resolve to the same org, and SNI supplies the database only when dbname is empty. | off | | DUCKGRES_MANAGED_HOSTNAME_SUFFIXES | Comma-separated managed hostname suffixes such as .dw.us.postwh.com | - | | DUCKGRES_METADATA_HOSTNAME_SUFFIXES | Comma-separated SNI suffixes for the explicitly enabled native metadata Postgres proxy, such as .md.dev.postwh.com, .md.us.postwh.com, or .md.eu.postwh.com | - | | DUCKGRES_METADATA_PROXY_MAX_CONNECTIONS_PER_ORG | Maximum admitted metadata proxy sessions per org on each control-plane replica | 20 | | DUCKGRES_DUCKLAKE_METADATA_STORE | DuckLake metadata connection string | - | | DUCKGRES_DUCKLAKE_DELTA_CATALOG_ENABLED | Attach a Delta Lake catalog/table during worker boot/activation | false | | DUCKGRES_DUCKLAKE_DELTA_CATALOG_PATH | Delta Lake catalog/table path; defaults to sibling delta/ prefix at the DuckLake object-store root when enabled | Derived | | DUCKGRES_QUERY_LOG_ENABLED | Enable per-query logging | true | | DUCKGRES_QUERY_LOG_FLUSH_INTERVAL | Query-log flush interval for native Postgres writes | 5s | | DUCKGRES_QUERY_LOG_BATCH_SIZE | Query-log batch size for native Postgres inserts | 1000 | | DUCKGRES_STORAGE_SAMPLE_INTERVAL | Storage-billing sampling cadence (Go duration): how often the leader CP reads each warehouse's tracked DuckLake footprint and credits byte-seconds. Env-only. | 30m | | DUCKGRES_EXPLORATORY_TIER_ENABLED | Exploratory worker tier (small-first routing, remote/K8s backend only): a connection that sends no duckgres.worker_* sizing options acquires NO worker at connect, and its first engine-touching statement lands on the small shape below; state-mutating statements and engine OOMs escalate it to the shape it would otherwise have started on. Env-only. | false | | DUCKGRES_EXPLORATORY_WORKER_CPU | CPU request/limit of the exploratory worker pod (e.g. 1, 500m). Required (with the memory knob) for the tier to activate; a missing or invalid value logs a warning and leaves the tier OFF. Env-only. | - | | DUCKGRES_EXPLORATORY_WORKER_MEMORY | Memory request/limit of the exploratory worker pod (e.g. 2Gi). Same requirement as the CPU knob. Env-only. | - | | DUCKGRES_EXPLORATORY_WORKER_TTL | Hot-idle TTL of exploratory worker pods (Go duration) — how long one stays parked for the org's next connection after its last one ends. Env-only. | 48h | | POSTHOG_API_KEY | PostHog project API key (phc_...); enables log export and product-analytics events. Exported WARN/ERROR logs carry RedactForLog+4096 SQL (secret DDL is a placeholder). To get events without exporting SQL, leave this unset and use POSTHOG_ANALYTICS_API_KEY | - | | POSTHOG_ANALYTICS_API_KEY | PostHog project API key for product-analytics events only, leaving log export off. Takes precedence over POSTHOG_API_KEY for analytics. Never copied to worker pods | - | | POSTHOG_HOST | PostHog ingest host (shared by both exporters) | us.i.posthog.com | | ADDITIONAL_POSTHOG_API_KEYS | (Experimental) Comma-separated extra PostHog API keys for log export. Requires POSTHOG_API_KEY. CP-only; not forwarded to workers | - | | DUCKGRES_POSTHOG_LOG_LEVEL | Minimum level exported to PostHog Logs (debug/info/warn/error). Stderr stays at DUCKGRES_LOG_LEVEL. User-class Query execution failed. is Info and does not export at the default | warn | | DUCKGRES_POSTHOG_LOG_INFO_SAMPLE | Fraction of INFO records to keep on the PostHog branch (0–1). WARN/ERROR are never sampled | 0 | | DUCKGRES_POSTHOG_LOG_QUERY_TEXT | How query attrs are exported: off (drop), redacted (RedactForLog+4096; ordinary SELECT text still leaves), on (stderr-equivalent) | redacted | | DUCKGRES_IDENTIFIER | Resource attr duckgres.deployment (and deployment.environment when the value is exactly dev/staging/production). Does not suffix service.name. Shared by logs and traces | - |

Client-requested idle timeout

The control plane closes inactive client sessions after its configured DUCKGRES_IDLE_TIMEOUT (5 minutes by default on the control plane). Before closing, it sends a FATAL ErrorResponse with SQLSTATE 57P05 (idle_session_timeout) naming the effective timeout — the same protocol sequence PostgreSQL uses for idle_session_timeout — so a client's next use of the reaped connection reports "terminating connection due to idle timeout" instead of a generic transport failure. To let clients request a longer, bounded timeout, set a positive DUCKGRES_CLIENT_IDLE_TIMEOUT_MAX on the control plane. For example, with DUCKGRES_CLIENT_IDLE_TIMEOUT_MAX=15m:

PGOPTIONS='-c duckgres.idle_timeout=15m' psql "host=<host> dbname=ducklake sslmode=require"

Requests must be positive and no greater than the configured maximum. Leaving the maximum unset disables client overrides, and clients cannot request an unlimited timeout because idle sessions retain worker capacity.

Per-session worker TTL

On the remote/K8s backend, a worker whose last session ends is parked hot_idle (warm, quickly reusable by the same org) and retired once its TTL expires — 1 minute by default. Clients can override that TTL per connection, either at connect time or mid-session:

PGOPTIONS='-c duckgres.worker_ttl=20m' psql "host=<host> dbname=ducklake sslmode=require"

SET duckgres.worker_ttl = '20m';   -- Go duration, whole minutes, minimum 1m
SHOW duckgres.worker_ttl;          -- the TTL this session's worker will park with
RESET duckgres.worker_ttl;         -- back to the connect-time value

The mid-session form exists for clients that cannot set startup options; it takes effect on the bound worker immediately and governs the park when the session ends. Both forms are gated on DUCKGRES_K8S_ALLOW_CLIENT_WORKER_PROFILE (a mid-session SET is rejected with 22023 when the gate is off) and clamped to DUCKGRES_K8S_WORKER_MAX_TTL. The TTL is stamped with whole-minute precision (ttl_minutes, where 0 means "deployment default"), so a mid-session SET rejects zero and sub-minute values with 22023 rather than parking the worker for a TTL SHOW would misreport. (A sub-minute startup option still truncates to whole minutes at park — pre-existing.) On the standalone/process backends there is no hot-idle TTL to override; SET/SHOW are accepted as session state only.

PostHog Logging

Duckgres can optionally export structured logs to PostHog Logs via the OpenTelemetry Protocol (OTLP). Logs are always written to stderr regardless of this setting. PostHog export defaults to WARN+ERROR; stderr stays at DUCKGRES_LOG_LEVEL.

service.name is the process role (duckgres-control-plane, duckgres-worker, duckgres-reshard, or duckgres for standalone). Traces share that resource — existing VictoriaTraces / dashboards that filtered service.name=duckgres or duckgres- need to follow the new names. DUCKGRES_IDENTIFIER is now duckgres.deployment, not a service-name suffix.

Exported WARN/ERROR records keep query text after usersecrets.RedactForLog + a 4096-byte cap (DUCKGRES_POSTHOG_LOG_QUERY_TEXT=redacted). Secret DDL becomes a placeholder; ordinary SELECT text and its literals do leave the cluster. Anyone who can read the destination PostHog project can see org, user (including svc_ service credentials), client IPs, and that redacted SQL.

To enable, set your PostHog project API key (same project as product-analytics events in managed-warehouse):

export POSTHOG_API_KEY=phc_your_project_api_key
./duckgres

For EU Cloud or self-hosted PostHog instances, override the ingest host:

export POSTHOG_API_KEY=phc_your_project_api_key
export POSTHOG_HOST=eu.i.posthog.com
./duckgres

Remote worker pods do not inherit the CP process env. Spawn copies a closed allowlist of named env: entries from the CP pod spec (Get(namespace, POD_NAME) once at pool start):

• POSTHOG_API_KEY is copied only as valueFrom.secretKeyRef. A literal value: is refused. envFrom is insufficient (those keys do not appear on a Pod GET) and is not invented-around as os.Getenv → value:.
• POSTHOG_HOST, DUCKGRES_POSTHOG_LOG_LEVEL, DUCKGRES_POSTHOG_LOG_INFO_SAMPLE, DUCKGRES_POSTHOG_LOG_QUERY_TEXT, and DUCKGRES_IDENTIFIER may be a value or a valueFrom.
• ADDITIONAL_POSTHOG_API_KEYS and POSTHOG_ANALYTICS_API_KEY stay CP-only and are never forwarded.

The first operational slice is CP-only export. Worker records in PostHog (service.name=duckgres-worker) appear only after the charts Secret exists and the worker can reach *.i.posthog.com:443. In-repo NetworkPolicy 443 is not proof of production Cilium egress. The first mw-dev duckgres-worker line in the analytics project is the egress proof.

PostHog Product-Analytics Events

POSTHOG_API_KEY (and POSTHOG_HOST) also enables product-analytics event capture via the PostHog capture API. This is separate from log export: logs go to PostHog Logs, these are discrete events you can build insights and dashboards on.

The two exporters can be enabled independently, and the distinction matters because they carry different data. These events are metadata only. Application logs are not: OTLP keeps RedactForLog+4096 SQL only on exported WARN/ERROR. User-class Query execution failed. stays Info and does not export at the default WARN — those statements stay on stderr/query_log unless the PostHog level is raised. logQuery / logQueryError attach the statement, and usersecrets.RedactForLog only rewrites secret DDL, so ordinary SQL and its literals reach PostHog Logs only when that record is actually exported.

| Set | Analytics events | Log export | | --- | --- | --- | | POSTHOG_ANALYTICS_API_KEY | ✅ | ❌ | | POSTHOG_API_KEY | ✅ | ✅ | | both | ✅ (analytics key) | ✅ (POSTHOG_API_KEY) | | neither | ❌ | ❌ |

So a deployment serving customer data — where SQL must not be exported — sets only POSTHOG_ANALYTICS_API_KEY:

export POSTHOG_ANALYTICS_API_KEY=phc_your_project_api_key
./duckgres

Existing single-key deployments are unaffected: POSTHOG_API_KEY keeps both exporters on, exactly as before.

Events are attributed to an org using PostHog group analytics: the distinct_id is the org name and each event carries a group of type organization, so dashboards can break down and aggregate by org. In single-tenant standalone mode (no org) the distinct_id is standalone and no group is attached.

The org name is duckgres-internal, so the query events additionally carry a team_id property — the PostHog Team.id for the connection (the connecting user's team, else the org's oldest team; 0 when unknown or standalone). This is the PostHog-native key that joins duckgres usage to the rest of PostHog (e.g. product-intent cohorts for managed-warehouse activation). It is a config-snapshot read stamped once per connection, and mirrors the informational team id the compute-usage meter records.

Events never include SQL text, credentials, or secret values — only metadata.

Provisioning and deprovisioning are asynchronous: the admin API returns 202 Accepted and the per-org provisioner controller drives the warehouse to its terminal state. The lifecycle is therefore split into a _begin event (the admin API accepted the request) and a terminal _success / _failed event (the controller observed the warehouse reach Ready / Failed, or finish / fail teardown), so you can build a provisioning funnel and alert on failures.

| Event | Fires when | Properties | | --- | --- | --- | | warehouse_provision_begin | Provisioning accepted by the admin API (warehouse not usable yet) | database_name, metadata_store, ducklake_enabled | | warehouse_provision_success | Warehouse reaches Ready and is usable (provisioner controller) | metadata_store, ducklake_enabled | | warehouse_provision_failed | Warehouse reaches Failed (provisioner controller) | metadata_store, ducklake_enabled, reason (provisioning_timeout/crossplane_sync_failure) | | warehouse_deprovision_begin | Deprovisioning accepted by the admin API (teardown not finished yet) | — | | warehouse_deprovision_success | All underlying resources deleted (provisioner controller) | — | | warehouse_deprovision_failed | A teardown attempt failed (provisioner controller) | reason (duckling_delete_failed) | | warehouse_password_reset | An org's root password is reset (admin API) | username | | query_initiated | An accepted, non-empty client query is received | user, team_id, trace_id, application_name | | query_completed | A statement finishes executing successfully | user, team_id, trace_id, protocol, query_kind, duration_ms, cpu_seconds (DuckDB CPU/thread-time), result_rows, application_name; Trino adds execution_engine=trino, query_id, queue/input/memory/spill/driver resource fields, source, and resource_group | | query_failed | A query errors | user, team_id, trace_id, error_code (SQLSTATE), error_category (user/system/conflict/metadata_connection_lost), application_name; Trino adds execution_engine=trino, query_id, resource fields, error_type, and Trino error_code |

Note: warehouse_provision_success / _failed and warehouse_deprovision_success

are terminal and fire exactly once per warehouse (guarded on the state

transition). Deletion has no terminal Failed state — the controller retries

indefinitely — so warehouse_deprovision_failed represents a failed teardown

attempt and may fire once per reconcile pass until teardown succeeds.

The _success / _failed events are emitted by the Kubernetes provisioner

controller, so they only fire in the remote/multitenant backend (built with

-tags kubernetes). The _begin events fire wherever the admin provisioning

API runs.

Note: query_initiated fires once per accepted, non-empty simple-protocol

Query or extended-protocol Execute. Retries, rewrites, cursor helpers, and

generated COPY batches do not emit additional events. Capture is asynchronous

and batched, so it stays off the query latency path.

Note: query_completed fires on the terminal event of each successfully

executed statement, carrying that statement's resource cost (duration_ms,

cpu_seconds). Failures are covered by query_failed instead. It is emitted

at statement granularity, so a single logical client request can produce more

than one query_completed (e.g. cursor FETCHes or COPY batches) — unlike

query_initiated. Filter by query_kind to isolate real data queries from

utility statements. Emitted independently of the query-log configuration;

capture is asynchronous and batched, so it stays off the query latency path.

Note: application_name is the client-supplied application_name startup

parameter (also shown in the admin live view), letting you distinguish

PostHog-side callers (e.g. the register workflow, Dagster, the SQL editor)

from customer psql connections. It is an empty string when the client

didn't set one.

Trino terminal queries are collected by the janitor leader every 10 seconds

from the coordinator and use the same query_completed / query_failed

events with execution_engine=trino. They never include SQL text. The

coordinator retains completed queries only temporarily, so this is

best-effort product telemetry rather than the billing source of truth;

consumers should deduplicate by query_id during a leader failover.

Query Logs

Structured logs separate the SQL received from a client from the statements executed by a worker:

| Event | Scope | Meaning | | --- | --- | --- | | Client query received. | client | Emitted once with the bounded/redacted client SQL and protocol=simple or protocol=extended. | | Worker statement started. | worker | A physical statement is about to run for the client operation. | | Worker statement finished. | worker | The physical statement completed, with duration, affected rows, and SQLSTATE when applicable. |

Client-derived worker statements carry bounded/redacted executed SQL. Generated rewrite and COPY work instead carries a typed origin, stable operation, and compact metadata; generated SQL, placeholders, arguments, and values are not logged. Worker statements do not create additional durable query-log records.

CLI Flags

./duckgres --help
Options:
  -config string           Path to YAML config file
  -host string             Host to bind to
  -port int                Port to listen on
  -data-dir string         Directory for DuckDB files
  -cert string             TLS certificate file
  -key string              TLS private key file
  -memory-limit string     DuckDB memory_limit per session (e.g., '4GB')
  -threads int             DuckDB threads per session
  -process-isolation       Enable process isolation (spawn child process per connection)
  -idle-timeout string     Connection idle timeout (e.g., '30m', '1h', '-1' to disable)
  -mode string             Run mode: standalone (default), control-plane, duckdb-service, or reshard-runner
  -process-min-workers int Pre-warm process worker count at startup (control-plane mode, default 0)
  -process-max-workers int Max process workers, 0=auto-derived (control-plane mode)
  -process-retire-on-session-end
                          Retire a process worker immediately after its last session ends instead of keeping it warm for reuse (control-plane mode)
  -memory-budget string    Total memory for all DuckDB sessions (e.g., '24GB')
  -socket-dir string       Unix socket directory (control-plane mode)
  -handover-socket string  Handover socket for graceful deployment (control-plane mode)
  -sni-routing-mode string Hostname routing: off, passthrough, or enforce
  -managed-hostname-suffixes string
                          Comma-separated managed tenant hostname suffixes

DuckDB Extensions

Extensions are automatically installed and loaded when a user's database is first opened. The ducklake extension is enabled by default.

extensions:
  - ducklake    # Default - DuckLake lakehouse format
  - httpfs      # HTTP/S3 file system access
  - parquet     # Parquet file support (built-in)
  - json        # JSON support (built-in)
  - postgres    # PostgreSQL scanner

DuckLake Integration

DuckLake provides a SQL-based lakehouse format. When configured, the DuckLake catalog is automatically attached on connection:

ducklake:
  # Full connection string for the DuckLake metadata database
  metadata_store: "postgres:host=ducklake.example.com user=ducklake password=secret dbname=ducklake"
# Default: true. Disables postgres_scanner thread-local caching for the
  # hidden DuckLake metadata pool before ATTACH creates it.
  # Set to false to opt back into warm connection reuse.
  disable_metadata_thread_local_cache: true
# Also attach a Delta Lake catalog/table as catalog "delta" during worker
  # boot/activation. If delta_catalog_path is omitted, Duckgres derives
  # s3://<bucket>/delta/ from ducklake.object_store. Prefer that isolated
  # prefix over the bucket root so DuckLake and Delta files do not collide.
  delta_catalog_enabled: false
  # delta_catalog_path: "s3://my-bucket/delta/"

This runs the equivalent of:

ATTACH 'ducklake:postgres:host=ducklake.example.com user=ducklake password=secret dbname=ducklake' AS ducklake;
-- when delta_catalog_enabled=true:
ATTACH 's3://my-bucket/delta/' AS delta (TYPE delta);

See DuckLake documentation for more details.

ducklake.disable_metadata_thread_local_cache defaults to true. This applies a pre-attach workaround for the hidden DuckLake metadata postgres pool so idle worker threads do not retain metadata connections indefinitely. Set it to false only if you explicitly want the older warm-reuse behavior and accept the larger steady-state metadata connection footprint.

Quick Start with Docker

The easiest way to get started with DuckLake is using the included Docker Compose setup:

# Start PostgreSQL (metadata) and PGSTY Silo (object storage)
docker compose up -d
Wait for services to be ready
docker compose logs -f  # Look for "Bucket ducklake created successfully"
Start Duckgres with DuckLake configured
./duckgres --config duckgres.yaml
Connect and start using DuckLake
PGPASSWORD=postgres psql "host=localhost port=5432 user=postgres sslmode=require"

The docker-compose.yaml creates:

PostgreSQL (metadata catalog):

• Host: localhost
• Port: 5433 (mapped to avoid conflicts)
• Database: ducklake
• User/Password: ducklake / ducklake

• S3 API: localhost:9000
• Web Console: http://localhost:9001
• Access Key: minioadmin
• Secret Key: minioadmin
• Bucket: ducklake (auto-created on startup)

Local and CI object storage use a pinned PGSTY Silo release with its bundled mcli admin client. The Compose service names minio and minio-init, MINIO_* settings, and existing endpoints are retained for compatibility. See the Silo local storage runbook for exact image pins, migration, validation, and rollback instructions.

Object Storage Configuration

DuckLake can store data files in S3-compatible object storage (AWS S3, MinIO, etc.). Two credential providers are supported:

Option 1: Explicit Credentials (MinIO / Access Keys)

ducklake:
  metadata_store: "postgres:host=localhost port=5433 user=ducklake password=ducklake dbname=ducklake"
  object_store: "s3://ducklake/data/"
  delta_catalog_enabled: true       # attaches s3://ducklake/delta/ by default
  s3_provider: "config"            # Explicit credentials (default if s3_access_key is set)
  s3_endpoint: "localhost:9000"    # MinIO or custom S3 endpoint
  s3_access_key: "minioadmin"
  s3_secret_key: "minioadmin"
  s3_region: "us-east-1"
  s3_use_ssl: false
  s3_url_style: "path"             # "path" for MinIO, "vhost" for AWS S3

Option 2: AWS Credential Chain (IAM Roles / Environment)

For AWS S3 with IAM roles, environment variables, or config files:

ducklake:
  metadata_store: "postgres:host=localhost user=ducklake password=ducklake dbname=ducklake"
  object_store: "s3://my-bucket/ducklake/"
  s3_provider: "credential_chain"  # AWS SDK credential chain
  s3_chain: "env;config"           # Which sources to check (optional)
  s3_profile: "my-profile"         # AWS profile name (optional)
  s3_region: "us-west-2"           # Override auto-detected region (optional)

The credential chain checks these sources in order:

• env - Environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY)
• config - AWS config files (~/.aws/credentials, ~/.aws/config)
• sts - AWS STS assume role
• sso - AWS Single Sign-On
• instance - EC2 instance metadata (IAM roles)
• process - External process credentials

Environment Variables

All S3 settings can be configured via environmen

... (README truncated for length)