duckgres

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
• Quick Start
• Configuration
  • YAML Configuration
  • Environment Variables
  • CLI Flags
  • PostHog Logging
• 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.

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)

Runbooks

• Worker Upgrades & Canaries: Process for upgrading DuckDB/DuckLake versions, canarying builds for a subset of tenants, and global version management.
• Performance Harness: Local smoke and nightly operations for performance testing.
• Control Plane Rollout: Zero-downtime deployment process for the control plane itself.
• Managed Warehouse Deprovision: Destructive teardown process for managed warehouse infrastructure and org cleanup.
• Lakekeeper Iceberg Catalog: Per-org Lakekeeper Iceberg REST catalog backend — architecture, the no-vending credential model, activation, and the ACCESS_DELEGATION_MODE 'none' gotcha.

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)

YAML Configuration

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

host: "0.0.0.0"
port: 5432
flight_port: 8815
flight_session_idle_ttl: "10m"
flight_session_reap_interval: "1m"
flight_handle_idle_ttl: "15m"
flight_session_token_ttl: "1h"
data_dir: "./data"
session_init_timeout: "10s"

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

Run with config file:

./duckgres --config duckgres.yaml

Environment Variables

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.

To enable, set your PostHog project API key:

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

CLI Flags

./duckgres --help

Options:
  -config string           Path to YAML config file
  -host string             Host to bind to
  -port int                Port to listen on
  -flight-port int         Control-plane Arrow Flight SQL ingress port, 0=disabled
  -flight-session-idle-ttl string      Flight auth session idle TTL (e.g., '10m')
  -flight-session-reap-interval string Flight auth session reap interval (e.g., '1m')
  -flight-handle-idle-ttl string       Flight prepared/query handle idle TTL (e.g., '15m')
  -flight-session-token-ttl string     Flight issued session token absolute TTL (e.g., '1h')
  -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, or duckdb-service
  -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
  -k8s-max-workers int    Max K8s workers in the shared pool, 0=unbounded

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 MinIO (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

MinIO (S3-compatible object storage):

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

The included duckgres.yaml is pre-configured to use both services.

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

See DuckDB S3 API docs for details.

Environment Variables

All S3 settings can be configured via environment variables:

• DUCKGRES_DUCKLAKE_OBJECT_STORE - S3 path (e.g., s3://bucket/path/)
• DUCKGRES_DUCKLAKE_DELTA_CATALOG_ENABLED - attach Delta catalog (true/false)
• DUCKGRES_DUCKLAKE_DELTA_CATALOG_PATH - Delta catalog/table path (e.g., s3://bucket/delta/)
• DUCKGRES_DUCKLAKE_S3_PROVIDER - config or credential_chain
• DUCKGRES_DUCKLAKE_S3_ENDPOINT - S3 endpoint (for MinIO)
• DUCKGRES_DUCKLAKE_S3_ACCESS_KEY - Access key ID
• DUCKGRES_DUCKLAKE_S3_SECRET_KEY - Secret access key
• DUCKGRES_DUCKLAKE_S3_REGION - AWS region
• DUCKGRES_DUCKLAKE_S3_USE_SSL - Use HTTPS (true/false)
• DUCKGRES_DUCKLAKE_S3_URL_STYLE - path or vhost
• DUCKGRES_DUCKLAKE_S3_CHAIN - Credential chain sources
• DUCKGRES_DUCKLAKE_S3_PROFILE - AWS profile name

Seeding Sample Data

A seed script is provided to populate DuckLake with sample e-commerce and analytics data:

# Seed with default connection (localhost:5432, postgres/postgres)
./scripts/seed_ducklake.sh

# Seed with custom connection
./scripts/seed_ducklake.sh --host 127.0.0.1 --port 5432 --user postgres --password postgres

# Clean existing tables and reseed
./scripts/seed_ducklake.sh --clean

The script creates the following tables:

• categories - Product categories (5 rows)
• products - E-commerce products (15 rows)
• customers - Customer records (10 rows)
• orders - Order headers (12 rows)
• order_items - Order line items (20 rows)
• events - Analytics events with JSON properties (15 rows)
• page_views - Web analytics data (15 rows)

Example queries after seeding:

-- Top products by price
SELECT name, price FROM products ORDER BY price DESC LIMIT 5;

-- Orders with customer info
SELECT o.id, c.first_name, c.last_name, o.total_amount, o.status
FROM orders o JOIN customers c ON o.customer_id = c.id;

-- Event funnel analysis
SELECT event_name, COUNT(*) FROM events GROUP BY event_name ORDER BY COUNT(*) DESC;

COPY Protocol

Duckgres supports PostgreSQL's COPY protocol for efficient bulk data import and export:

-- Export data to stdout (tab-separated)
COPY tablename TO STDOUT;

-- Export as CSV with headers
COPY tablename TO STDOUT WITH CSV HEADER;

-- Export query results
COPY (SELECT * FROM tablename WHERE id > 100) TO STDOUT WITH CSV;

-- Import data from stdin
COPY tablename FROM STDIN;

-- Import CSV with headers
COPY tablename FROM STDIN WITH CSV HEADER;

This works with psql's \copy command and programmatic COPY operations from PostgreSQL drivers.

Graceful Shutdown

Duckgres handles shutdown signals (SIGINT, SIGTERM) gracefully:

• Stops accepting new connections immediately
• Waits for in-flight queries to complete (default 30s timeout)
• Logs active connection count during shutdown
• Closes all database connections cleanly

The shutdown timeout can be configured:

cfg := server.Config{
    ShutdownTimeout: 60 * time.Second,
}

Rate Limiting

Built-in rate limiting protects against brute-force authentication attacks:

• Failed attempt tracking: Bans IPs after too many failed auth attempts
• Connection limits: Limits concurrent connections per IP and, when configured, total concurrent sessions. In K8s multi-tenant mode, org max_connections is enforced cluster-wide through runtime-store leases.
• Auto-cleanup: Expired records are automatically cleaned up

rate_limit:
  max_failed_attempts: 5        # Ban after 5 failures
  failed_attempt_window: "5m"   # Within 5 minutes
  ban_duration: "15m"           # Ban lasts 15 minutes
  max_connections_per_ip: 100   # Max concurrent connections
  max_connections: 16           # Max total concurrent sessions (0 = unlimited)

Usage Examples

-- Create a table
CREATE TABLE events (
    id INTEGER,
    name VARCHAR,
    timestamp TIMESTAMP,
    value DOUBLE
);

-- Insert data
INSERT INTO events VALUES
    (1, 'click', '2024-01-01 10:00:00', 1.5),
    (2, 'view', '2024-01-01 10:01:00', 2.0);

-- Query with DuckDB's analytical power
SELECT name, COUNT(*), AVG(value)
FROM events
GROUP BY name;

-- Use prepared statements (via client drivers)
-- Works with lib/pq, psycopg2, JDBC, etc.

Architecture

Duckgres supports three run modes: standalone (single process, default), control-plane (multi-process with worker pool), and duckdb-service (worker process mode used by the control plane).

Standalone Mode

The default mode runs everything in a single process:

┌─────────────────┐
│  PostgreSQL     │
│  Client (psql)  │
└────────┬────────┘
         │ PostgreSQL Wire Protocol (TLS)
         ▼
┌─────────────────┐
│    Duckgres     │
│    Server       │
└────────┬────────┘
         │ database/sql
         ▼
┌─────────────────┐
│    DuckDB       │
│  (per-user db)  │
│  + Extensions   │
│  + DuckLake     │
└─────────────────┘

Control Plane Mode

For production deployments, control-plane mode splits the server into a control plane and a pool of long-lived worker processes. The control plane owns client connections end-to-end (TLS, authentication, PostgreSQL wire protocol, SQL transpilation), while workers are thin DuckDB execution engines reachable via Arrow Flight SQL over Unix sockets. Optional control-plane Flight ingress (flight_port) also exposes Arrow Flight SQL directly with HTTP Basic auth (Authorization: Basic ...), compatible with Duckhog clients.

                    CONTROL PLANE (duckgres --mode control-plane)
                    ┌──────────────────────────────────────────────┐
  PG Client ──TLS──>│ PG TCP Listener                              │
 Flight SQL Client ─>│ Flight SQL TCP Listener (Basic Auth)         │
                    │ TLS Termination + Password Auth              │
                    │ PostgreSQL Wire Protocol                     │
                    │ SQL Transpilation (PG → DuckDB)              │
                    │ Rate Limiting                                │
                    │ Session Manager + Connection Router           │
                    │   │ Arrow Flight SQL (Unix socket)           │
                    │   ▼                                          │
                    └──────────────────────────────────────────────┘
                                                           │
                                                Flight SQL (UDS)
                                                           │
                    WORKER POOL                            ▼
                    ┌──────────────────────────────────────────────┐
                    │ Worker 1 (duckgres --mode duckdb-service)    │
                    │   Arrow Flight SQL Server (Unix socket)      │
                    │   Bearer Token Auth                          │
                    │   DuckDB Instance (long-lived)               │
                    │   ├── Session 1                               │
                    │   ├── Session 2                               │
                    │   └── Session N ...                           │
                    ├──────────────────────────────────────────────┤
                    │ Worker 2 ...                                  │
                    └──────────────────────────────────────────────┘

Start in control-plane mode:

# Start in control-plane mode (workers spawn on demand, 1 per connection)
./duckgres --mode control-plane --port 5432

# Enable Flight SQL ingress for Duckhog-compatible clients
./duckgres --mode control-plane --port 5432 --flight-port 8815

# Pre-warm 2 process workers and cap at 10
./duckgres --mode control-plane --port 5432 --process-min-workers 2 --process-max-workers 10

# Connect with psql (identical to standalone mode)
PGPASSWORD=postgres psql "host=localhost port=5432 user=postgres sslmode=require"

# Flight SQL clients use Basic auth headers (user/password)
# Example endpoint: grpc+tls://localhost:8815

Zero-downtime deployment using the handover protocol:

# Start the first control plane with a handover socket
./duckgres --mode control-plane --port 5432 --handover-socket /var/run/duckgres/handover.sock

# Deploy a new version - it takes over the listener and workers without dropping connections
./duckgres-v2 --mode control-plane --port 5432 --handover-socket /var/run/duckgres/handover.sock

When running under systemd with RuntimeDirectory, ensure RuntimeDirectoryPreserve=yes is set in your unit file. This prevents systemd from cleaning up or remounting the socket directory as read-only when the old process exits during a handover.

Rolling worker updates via signal:

# Replace workers one at a time (drains sessions before replacing each worker)
kill -USR2 <control-plane-pid>

Remote Worker Backend

In Kubernetes environments, --worker-backend remote is the multitenant path. It requires --config-store. Control-plane replicas coordinate through durable runtime rows in the config-store Postgres DB, spawn worker pods via the Kubernetes API, and communicate with them over gRPC (Arrow Flight SQL). Planned rolling deploys mark old replicas draining, fail readiness, and wait up to handover_drain_timeout before forcing shutdown. Unplanned control-plane failure still drops live pgwire connections; Flight may reconnect with a durable session token if the worker survives and the token is still valid.

Managed-hostname routing is controlled by --sni-routing-mode and --managed-hostname-suffixes. For Postgres, an explicit startup database/dbname takes priority, but when SNI matches a managed suffix the hostname prefix and requested database must resolve to the same org. If the startup database is empty, the managed SNI prefix is used as the database fallback. Unknown --sni-routing-mode values behave like off.

Workers are spawned on demand: when an org opens a session with no reusable worker, the control plane creates a worker pod (sized from the connection's duckgres.worker_cpu/worker_memory request, or a default), activates it over the worker control RPC, and it becomes hot for that org. When its last session ends, the worker moves to hot_idle instead of being retired immediately: it keeps the org assignment and DuckLake attachment so any control-plane replica can reclaim it for the same org (by exact worker shape) without full reactivation, until its duckgres.worker_ttl expires. Hot-idle reuse is image/version strict. The janitor retires hot-idle workers at their TTL, but default_worker_min_hot_idle lets an org retain a minimum number of compatible default-profile hot-idle workers by skipping TTL retirement when the count is already at or below the floor. The default is 0 (disabled). The main lifecycle is: idle → reserved → activating → hot → hot_idle → retired. Workers can also move through draining during shutdown, rollout, or cleanup. (Spawn latency is hidden by the node-headroom controller, which keeps placeholder pods ready for real workers to preempt.)

# Local multitenant K8s workflow
just run-multitenant-kind

See k8s/README.md for the full architecture, configuration reference, manifest details, and the default local kind workflow via just run-multitenant-kind. The older OrbStack path remains available through just run-multitenant-local for manual macOS iteration.

On the multi-tenant path, the config store now keeps per-team managed-warehouse metadata in addition to team/user auth and limits. That team-scoped contract is intended to become the source of truth for the tenant warehouse DB, the tenant DuckLake metadata store (which may live on shared Aurora or a dedicated RDS instance), object-store settings, worker identity, secret references, and provisioning state. The older config-store DuckLakeConfig singleton remains only as a legacy cluster-wide setting and should not be treated as authoritative for multi-tenant runtime wiring.

The shared K8s pool spawns workers on-demand, reserves them per org, activates tenant runtime over the control-plane RPC channel, and keeps idle activated workers briefly available for same-org hot-idle reuse before janitor retirement.

Managed-warehouse contract notes:

• At most one managed-warehouse row exists per team. The row may be absent before first provisioning or after cleanup, but there is never more than one active warehouse contract for a team.
• The admin API exposes that contract at GET /api/v1/teams/:name/warehouse and PUT /api/v1/teams/:name/warehouse. Team list/get responses also include a nested warehouse object when present.
• User rows support an optional default_catalog field on POST /api/v1/users and PUT /api/v1/orgs/:id/users/:username. The default is empty, which preserves the standard DuckLake-first session behavior. Set default_catalog to iceberg for users whose sessions should resolve schema-qualified names and compatibility metadata through the Iceberg catalog by default; a client-supplied startup search_path still takes precedence.
• The typed sections are warehouse_database, metadata_store, s3, worker_identity, and structured secret refs for warehouse_database_credentials, metadata_store_credentials, s3_credentials, and runtime_config. In shared worker mode, every non-empty secret ref must store an explicit namespace, and it must match worker_identity.namespace.
• Secret references only are stored in the config store. Secret material remains outside the database.
• The provisioning fields are stored directly on the warehouse row as overall state / status_message, per-resource *_state / *_status_message, plus ready_at and failed_at.
• Those state fields are open strings. Canonical values are pending, provisioning, ready, failed, deleting, and deleted, but callers may persist other values while workflows evolve.

Two-Tier Query Processing

Duckgres uses a two-tier approach to handle both PostgreSQL and DuckDB-specific SQL syntax transparently:

┌─────────────────────────────────────────────────────────────────┐
│                        Incoming Query                           │
└─────────────────────────────┬───────────────────────────────────┘
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                  Tier 1: PostgreSQL Parser                      │
│                   (pg_query_go / libpg_query)                   │
└──────────────┬─────────────────────────────────┬────────────────┘
               │                                 │
          Parse OK                          Parse Failed
               │                                 │
               ▼                                 ▼
┌──────────────────────────┐    ┌─────────────────────────────────┐
│   Transpile PG → DuckDB  │    │   Tier 2: DuckDB Validation     │
│   (type mappings, etc.)  │    │   (EXPLAIN or direct execute)   │
└──────────────┬───────────┘    └──────────────┬──────────────────┘
               │                               │
               ▼                               ▼
┌─────────────────────────────────────────────────────────────────┐
│                     Execute on DuckDB                           │
└─────────────────────────────────────────────────────────────────┘

How It Works

1. Tier 1 (PostgreSQL Parser): All queries first pass through the PostgreSQL parser. Valid PostgreSQL syntax is transpiled to DuckDB-compatible SQL (handling differences in types, functions, and system catalogs).

2. Tier 2 (DuckDB Fallback): If PostgreSQL parsing fails, the query is validated directly against DuckDB using EXPLAIN. If valid, it executes natively. This enables DuckDB-specific syntax that isn't valid PostgreSQL.

Supported DuckDB-Specific Syntax

The following DuckDB features work transparently through the fallback mechanism: FROM-first queries, SELECT * EXCLUDE/REPLACE, DESCRIBE, SUMMARIZE, QUALIFY clause, lambda functions, positional joins, ASOF joins, struct operations, COLUMNS expression, and SAMPLE.

Supported Features

SQL Commands

• SELECT - Full query support with binary result format
• INSERT - Single and multi-row inserts
• UPDATE - With WHERE clauses
• DELETE - With WHERE clauses
• CREATE TABLE/INDEX/VIEW
• DROP TABLE/INDEX/VIEW
• ALTER TABLE
• BEGIN/COMMIT/ROLLBACK (DuckDB transaction support)
• COPY - Bulk data loading and export (see below)

PostgreSQL Compatibility

• Extended query protocol (prepared statements)
• Binary and text result formats
• Cleartext password authentication over TLS
• Basic pg_catalog system tables for client compatibility
• \dt, \d, and other psql meta-commands

Transaction Isolation

DuckDB provides snapshot isolation (MVCC), which is stricter than PostgreSQL's default read committed. In practice this means:

Clients that issue SET transaction_isolation or SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ... will succeed silently — the setting is accepted but DuckDB always operates at snapshot isolation. SHOW transaction_isolation returns read committed for client compatibility.

Since DuckDB's isolation is strictly stronger than PostgreSQL's default, applications that work correctly under read committed will also work correctly here. The only observable difference is write-write conflicts: DuckDB will reject a concurrent write that PostgreSQL would silently accept under read committed.

Limitations

• Single Node: No built-in replication or clustering
• Limited System Catalog: Some pg_* system tables are stubs (return empty)
• Unmapped DuckDB types (MAP, STRUCT, UNION, ENUM, BIT) fall back to OidText

SQL Client Compatibility

Duckgres implements a subset of PostgreSQL's system catalog to satisfy introspection queries from common SQL clients, ORMs, and BI tools — enough for psql, pgAdmin, DBeaver, Metabase, Grafana, Superset, Tableau, Fivetran, Airbyte, dbt, and the standard drivers (psycopg, pgx, JDBC, node-postgres, tokio-postgres, SQLAlchemy) to connect and introspect.

The full, authoritative breakdown — every PostgreSQL feature with its support status and the specific test that proves it, plus the per-object pg_catalog/information_schema/function/startup-parameter reference — lives in docs/postgres-compatibility.md. That document is the single source of truth; update it in the same PR as any PostgreSQL-visible behavior change.

Dependencies

• DuckDB Go Driver - DuckDB database engine

License

MIT