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
• Perf Runbook
• 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: Full compatibility with PostgreSQL clients
• 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.

Metric	Type	Description
duckgres_connections_open	Gauge	Number of currently open client connections
duckgres_query_duration_seconds	Histogram	Query execution duration (includes _count, _sum, _bucket)
duckgres_query_errors_total	Counter	Total number of failed queries
duckgres_auth_failures_total	Counter	Total number of authentication failures
duckgres_rate_limit_rejects_total	Counter	Total number of connections rejected due to rate limiting
duckgres_rate_limited_ips	Gauge	Number of currently rate-limited IP addresses
duckgres_flight_auth_sessions_active	Gauge	Number of active Flight auth sessions on the control plane
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_flight_rpc_duration_seconds{method}	Histogram	Flight ingress RPC duration by method
duckgres_flight_ingress_sessions_total{outcome}	Counter	Flight ingress session outcomes (`created
duckgres_flight_sessions_reaped_total{trigger}	Counter	Number of Flight auth sessions reaped (`trigger=periodic
duckgres_flight_max_workers_retry_total{outcome}	Counter	Max-worker retry outcomes for Flight session creation (`outcome=attempted

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)

Perf Runbook

See docs/perf-harness-runbook.md and tests/perf/README.md for local smoke and nightly operations, including:

• Prod-us perf runner and Duckling topology
• duckgres_perf.query_results publish path in duckling-posthog
• Perf CSV artifact schema contract (query_results.csv)

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"

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"

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

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_FLIGHT_PORT	Control-plane Flight SQL ingress port (0 disables)	0
DUCKGRES_FLIGHT_SESSION_IDLE_TTL	Flight auth session idle TTL	10m
DUCKGRES_FLIGHT_SESSION_REAP_INTERVAL	Flight auth session reap interval	1m
DUCKGRES_FLIGHT_HANDLE_IDLE_TTL	Flight prepared/query handle idle TTL	15m
DUCKGRES_FLIGHT_SESSION_TOKEN_TTL	Flight issued session token absolute TTL	1h
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	runtime.NumCPU()
DUCKGRES_PROCESS_ISOLATION	Enable process isolation (1 or true)	false
DUCKGRES_IDLE_TIMEOUT	Connection idle timeout (e.g., 30m, 1h, -1 to disable)	24h
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_K8S_SHARED_WARM_TARGET	Neutral shared warm-worker target for K8s multi-tenant mode (0 disables prewarm)	0
DUCKGRES_DUCKLAKE_METADATA_STORE	DuckLake metadata connection string	-
POSTHOG_API_KEY	PostHog project API key (phc_...); enables log export	-
POSTHOG_HOST	PostHog ingest host	us.i.posthog.com
ADDITIONAL_POSTHOG_API_KEYS	(Experimental) Comma-separated list of additional PostHog API keys to publish logs to. Requires POSTHOG_API_KEY to be set.	-
DUCKGRES_IDENTIFIER	Suffix appended to the OTel service.name in PostHog logs (e.g., duckgres-acme); only used when POSTHOG_API_KEY is set	-

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)
  -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)

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"

This runs the equivalent of:

ATTACH 'ducklake:postgres:host=ducklake.example.com user=ducklake password=secret dbname=ducklake' AS ducklake;

See DuckLake documentation for more details.

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

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.

When a shared warm-worker target is configured (--k8s-shared-warm-target), the pool keeps workers neutral at startup, reserves them per org, activates tenant runtime over the activation RPC, and retires them after use. The full lifecycle is: idle → reserved → activating → hot → draining → retired.

# 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 keeps workers neutral at startup, reserves them per org, activates tenant runtime over the control-plane RPC channel, and retires them after use.

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.
• 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 warm 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
• MD5 password authentication
• 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:

Behavior	PostgreSQL (default)	Duckgres (DuckDB)
Default isolation level	Read Committed	Snapshot (≈ Serializable)
Non-repeatable reads	Possible	Not possible
Phantom reads	Possible	Not possible
Write conflicts	Last writer wins	Second writer gets a conflict error

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. The tables below document current coverage.

pg_catalog Views

View	Status	Notes
pg_class	Implemented	pg_class_full wrapper adding relforcerowsecurity; DuckLake variant sources from duckdb_tables()/duckdb_views()
pg_namespace	Implemented	Maps main → public; DuckLake variant derives from duckdb_tables()/duckdb_views()
pg_attribute	Implemented	Maps DuckDB internal type OIDs to PG OIDs via duckdb_columns() JOIN; fixes atttypmod for NUMERIC
pg_type	Implemented	Fixes NULLs + adds synthetic entries for missing OIDs (json, jsonb, bpchar, text, record, array types)
pg_database	Implemented	Hardcoded: postgres, template0, template1, testdb
pg_stat_user_tables	Implemented	Uses reltuples from pg_class; zeros for scan/tuple stats
pg_roles	Stub (empty)	Single hardcoded duckdb superuser
pg_constraint	Stub (empty)
pg_enum	Stub (empty)
pg_collation	Stub (empty)
pg_policy	Stub (empty)
pg_inherits	Stub (empty)
pg_statistic_ext	Stub (empty)
pg_publication	Stub (empty)
pg_publication_rel	Stub (empty)
pg_publication_tables	Stub (empty)
pg_rules	Stub (empty)
pg_matviews	Stub (empty)
pg_partitioned_table	Stub (empty)
pg_stat_activity	Stub (empty)	Intercepted at query time for live data
pg_statio_user_tables	Stub (empty)
pg_stat_statements	Stub (empty)
pg_indexes	Stub (empty)
pg_settings	Missing	current_setting() macro handles server_version and server_encoding only
pg_proc	Missing	DuckDB has native pg_catalog.pg_proc but no wrapper
pg_description	Missing	Handled via obj_description()/col_description() macros returning NULL
pg_depend	Missing
pg_am	Missing
pg_attrdef	Missing
pg_tablespace	Missing

information_schema Views

View	Status	Notes
tables	Implemented	Filters internal views, normalizes main → public
columns	Implemented	DuckDB → PG type name normalization, optional metadata overlay
schemata	Implemented	Adds synthetic entries for pg_catalog, information_schema, pg_toast
views	Implemented	Filters internal views
key_column_usage	Missing	Used by ORMs for relationship discovery
table_constraints	Missing	Used by ORMs for relationship discovery
referential_constraints	Missing	Used by ORMs for FK introspection

Functions & Macros

Function	Status	Notes
format_type(oid, int)	Implemented	Comprehensive OID → name mapping
pg_get_expr(text, oid)	Implemented	Returns NULL
pg_get_indexdef(oid)	Implemented	Returns empty string
pg_get_constraintdef(oid)	Implemented	Returns empty string
pg_get_serial_sequence(text, text)	Implemented	Returns NULL
pg_table_is_visible(oid)	Implemented	Always true
pg_get_userbyid(oid)	Implemented	Maps OID 10 → postgres, 6171 → pg_database_owner
obj_description(oid, text)	Implemented	Returns NULL
col_description(oid, int)	Implemented	Returns NULL
shobj_description(oid, text)	Implemented	Returns NULL
has_table_privilege(text, text)	Implemented	Always true
has_schema_privilege(text, text)	Implemented	Always true
pg_encoding_to_char(int)	Implemented	Always UTF8
version()	Implemented	Returns PG 15.0 compatible string
current_setting(text)	Implemented	Handles server_version and server_encoding
pg_is_in_recovery()	Implemented	Always false
pg_backend_pid()	Implemented	Returns 0
pg_size_pretty(bigint)	Implemented	Full human-readable formatting
pg_total_relation_size(oid)	Implemented	Returns 0
pg_relation_size(oid)	Implemented	Returns 0
pg_table_size(oid)	Implemented	Returns 0
pg_indexes_size(oid)	Implemented	Returns 0
pg_database_size(text)	Implemented	Returns 0
quote_ident(text)	Implemented
quote_literal(text)	Implemented
quote_nullable(text)	Implemented
txid_current()	Implemented	Epoch-based pseudo ID
current_schema()	Missing
current_schemas(bool)	Missing

Startup Parameters

Parameter	Value
server_version	15.0 (Duckgres)
server_encoding	UTF8
client_encoding	UTF8
DateStyle	ISO, MDY
TimeZone	UTC
integer_datetimes	on
standard_conforming_strings	on
IntervalStyle	Missing

Dependencies

• DuckDB Go Driver - DuckDB database engine

License

MIT