Skip to content

DuckDB

Install an embedded OLAP database as a managed host-binary addon.

DuckDB is an embedded analytical database — often called “SQLite for analytics”. It is a C++ columnar engine with vectorised execution that queries Parquet, CSV, JSON and Arrow files directly, without a server process. Think of it as a single-binary OLAP engine you point at your data: SELECT * FROM read_parquet('s3://bucket/data.parquet') works out of the box once the httpfs extension is loaded.

As a pgcli addon, DuckDB is a host-binary addon: there is no container and no daemon. pg addon install duckdb downloads the official CLI zip from GitHub releases (sha256-verified; a duckdb already on PATH is reused), installs the httpfs extension (parquet and json are bundled in the binary), and records the entry in pg.yaml.

Platform: Linux only, amd64 only (macOS and other architectures refuse with a clear error). DuckDB is a standalone CLI tool — it does not run as a background service, does not listen on a port, and does not propagate into running PG instance containers.

Why DuckDB alongside pgcli

DuckDB fills the analytical gap that PostgreSQL’s row-oriented engine does not target: ad-hoc queries over large Parquet/CSV datasets, data-lake exploration, ETL pre-processing before loading into PostgreSQL, or quick aggregations on files that already live in S3. pgcli already manages S3 stores (MinIO, silo, rustfs, garage, seaweedfs) — DuckDB queries their contents directly via httpfs.

Usage 

# Default: v1.5.6 LTS, install httpfs extension (parquet/json are bundled)
pg addon install duckdb

# Pin a specific version
pg addon install duckdb --version 2.0.0

# Custom name and data directory
pg addon install duckdb --name analytics --data-dir /srv/duckdb

# Skip extension installation
pg addon install duckdb --no-extensions

# Re-install with different flags (entry already exists)
pg addon install duckdb --version 2.0.0 --force

Once installed, launch the interactive shell or run one-shot queries:

# Interactive shell (data dir is set as the working directory)
pg addon duckdb shell

# One-shot query against a local Parquet file
pg addon duckdb shell -- -c "SELECT * FROM read_parquet('/tmp/data.parquet')"

# Query S3 directly (needs the httpfs extension, installed by default)
duckdb -c "SELECT count(*) FROM read_parquet('s3://bucket/data/*.parquet')"

# Open a persistent database file
duckdb ~/.pgcli/addon/duckdb/duckdb/analytics.duckdb

Parameters

Flag Default Description
--name duckdb Instance name (config map key)
--version 1.5.6 Release to download (its sha256 is pinned in pgcli; 1.5.6 LTS and 2.0.0 supported)
--data-dir <base>/addon/duckdb/<name> Default database directory for .duckdb files
--install-dir /usr/local/bin Where the host binary goes (falls back to ~/.local/bin when not writable — keep it on PATH)
--no-extensions off Skip installing the httpfs extension (parquet and json are bundled in the binary)
--force off Rebuild the config entry from the flags

Daily operations 

pg addon duckdb status --name duckdb        # binary version, path, data dir
pg addon duckdb shell --name duckdb         # interactive DuckDB session
pg addon duckdb shell -- -c "SELECT 1"      # one-shot query (args after --)
pg addon list                               # duckdb appears under "Analytics add-ons"
pg addon start duckdb                       # no-op (CLI tool, not a daemon)
pg addon stop  duckdb                       # no-op (CLI tool, not a daemon)
pg addon remove duckdb --name duckdb        # forget config; binary and data dir stay
pg addon remove duckdb --name duckdb --clean-data   # also deletes the data directory

remove never deletes the binary — it may be shared across multiple named instances. Pass --clean-data to delete the data directory (database files); without it, the directory is kept and reported.

Server/Client mode (v2.0.0+)

DuckDB v2.0.0 introduced the Quack protocol for native server/client operation. This allows DuckDB to serve its databases over HTTP, letting multiple clients query the same instance.

Starting a server 

# Interactive server (stdin must stay open)
duckdb /path/to/db.duckdb -serve

# Background server (redirect stdin to keep alive)
nohup duckdb /path/to/db.duckdb -serve < /dev/zero > /tmp/duckdb-serve.log 2>&1 &

# Or use quack_serve() from SQL
duckdb -c "INSTALL quack; LOAD quack; CALL quack_serve(token='mytoken')"

The server listens on localhost:9494 by default and prints an auto-generated auth token. By default, it only accepts local connections — for remote access, put it behind a TLS-terminating reverse proxy (see DuckDB docs).

Cross-host access (0.0.0.0 binding)

To accept connections from any network interface, use quack_serve() with explicit parameters instead of -serve:

-- Server: bind to all interfaces on port 9494
CALL quack_serve(
  listen_uri='quack:0.0.0.0:9494',
  allow_other_hostname=true,  -- required for non-localhost binding
  disable_ssl=true,            -- optional, default is HTTPS
  token='mytoken'
);

Three parameters are needed:

  • listen_uri='quack:0.0.0.0:9494' — bind to all interfaces (not just localhost)
  • allow_other_hostname=true — bypass the localhost-only restriction
  • disable_ssl=true — use plain HTTP (default is HTTPS with auto-generated cert)

Clients connect using the server’s IP address:

# Client connecting to remote server
duckdb -c "ATTACH 'quack:192.0.2.10:9494' AS qk (TOKEN 'mytoken'); CONNECT qk; SELECT * FROM t"

Security warning: Binding to 0.0.0.0 with disable_ssl=true exposes the database to the network without encryption. Use only on trusted networks or behind a firewall. For production, use a reverse proxy with TLS.

Connecting to a server 

# Using -connect flag (v2.0.0+)
duckdb -connect -c "SELECT * FROM t"

# Or via SQL (short form, localhost default)
duckdb -c "ATTACH 'quack:localhost' AS qk (TOKEN 'BAED...'); CONNECT qk; SELECT * FROM t"

# Explicit ip:port form (for remote or non-default port)
duckdb -c "ATTACH 'quack:127.0.0.1:9494' AS qk (TOKEN 'BAED...'); CONNECT qk; SELECT * FROM t"

The -connect flag auto-discovers the token from the server’s output. For manual connections, copy the token from the server’s startup message.

Key points

  • stdin must stay open: the server exits when stdin closes (use < /dev/zero or tail -f /dev/null | for background operation)
  • localhost only: remote access requires a reverse proxy with TLS termination
  • Auth token: auto-generated, printed at startup, required for client connections
  • Not managed by pgcli: pg addon start/stop duckdb remain no-ops — the Quack server is a standalone process you manage yourself

Extensions

DuckDB’s extension system installs shared libraries on first use. pgcli pre-installs httpfs at install time — it enables reading files over HTTP/S3. parquet and json are statically linked into the DuckDB CLI binary, so they are always available without a network download.

  • httpfs — read files over HTTP/S3 (read_parquet('s3://...'))
  • parquet — Parquet file reader/writer (bundled)
  • json — JSON file reader (read_json_auto(...)) (bundled)

Additional extensions can be installed later from the DuckDB shell:

INSTALL 'spatial';       -- geospatial functions
INSTALL 'sqlite';        -- read/write SQLite databases
INSTALL 'mysql';         -- query MySQL directly
LOAD 'httpfs';           -- load an installed extension in the current session

Pass --no-extensions at install time if you prefer to manage extensions manually, or if the host has no internet access at install time.

Accessing S3, Parquet, JSON and CSV data

DuckDB is built for analytical queries over files — no server, no COPY, no import step. Point it at your data.

Parquet files 

-- Single file
SELECT * FROM read_parquet('/tmp/events.parquet');

-- Wildcard: all parquet files in a directory
SELECT * FROM read_parquet('/data/logs/*.parquet');

-- Schema inference + count
SELECT count(*) FROM read_parquet('s3://bucket/year=2026/*.parquet');

-- Specific columns + filter
SELECT user_id, event, ts
FROM read_parquet('/data/events.parquet')
WHERE ts > '2026-01-01'
LIMIT 100;

JSON files 

-- Auto-detect schema (handles JSON lines and nested JSON)
SELECT * FROM read_json_auto('/tmp/users.json');

-- Explicit schema with read_json
SELECT * FROM read_json('/tmp/users.json',
    columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'email': 'VARCHAR'},
    format = 'newline_delimited');

-- Nested objects: extract fields with ->>
SELECT
    data->>'user_id' AS user_id,
    data->>'event'   AS event
FROM read_json_auto('/tmp/events.jsonl');

CSV files 

-- Auto-detect header and types
SELECT * FROM read_csv_auto('/tmp/sales.csv');

-- Explicit column types (when auto-detection guesses wrong)
SELECT * FROM read_csv('/tmp/sales.csv',
    columns = {'id': 'INTEGER', 'amount': 'DOUBLE', 'date': 'DATE'},
    header = true);

-- TSV or custom delimiter
SELECT * FROM read_csv_auto('/tmp/data.tsv', delim = '\t');

S3 access (httpfs extension)

The httpfs extension enables S3 access. Credentials and endpoint are configured via CREATE SECRET (v2.0.0+) or legacy SET commands (v1.5.x).

DuckDB v2.0.0+ — CREATE SECRET 

-- Configure S3 credentials (v2.0.0+ syntax)
CREATE SECRET (
  TYPE S3,
  ENDPOINT 'minio.example.com:9000',
  KEY_ID 'admin',
  SECRET 'your-secret-key',
  USE_SSL false,
  URL_STYLE 'path'   -- required for MinIO and compatible stores
);

-- Query Parquet directly from S3
SELECT count(*) FROM read_parquet('s3://my-bucket/events/2026/*.parquet');

-- Query CSV from S3
SELECT * FROM read_csv_auto('s3://my-bucket/users.csv') LIMIT 10;

-- Query JSON from S3
SELECT * FROM read_json_auto('s3://my-bucket/config.json');

-- Write Parquet back to S3 (needs write permissions on the bucket)
COPY (SELECT * FROM read_parquet('/tmp/*.parquet') WHERE year = 2026)
  TO 's3://output-bucket/filtered/2026.parquet' (FORMAT parquet);

DuckDB v1.5.x — legacy SET commands 

-- Configure S3 credentials in the DuckDB session (v1.5.x only)
SET s3_endpoint = 'minio.example.com:9000';
SET s3_access_key_id = 'admin';
SET s3_secret_access_key = 'your-secret-key';
SET s3_use_ssl = false;   -- true for https endpoints
SET s3_region = 'us-east-1';   -- 'us-east-1' works for MinIO (region is ignored)
SET s3_url_style = 'path';   -- MinIO and compatible stores use path-style

Connecting to pgcli-managed S3 stores

pgcli manages several S3-compatible stores (MinIO, silo, rustfs, garage, seaweedfs). DuckDB queries them directly — the s3_endpoint is the store’s HTTP address, and credentials come from pg addon password.

First, create an S3 store instance and a bucket. The setup is identical across MinIO, silo (Pigsty’s MinIO fork), and rustfs (Rust reimplementation) — they all share the same pg mc wrapper and the same DuckDB s3_url_style = 'path':

# MinIO
pg addon install minio --name ms3
S3_SECRET=$(pg addon password minio --name ms3)

# or silo (same API surface, shares the port pool with minio)
pg addon install silo --name ms3
S3_SECRET=$(pg addon password silo --name ms3)

# or rustfs (Rust S3-compatible store, Linux only)
pg addon install rustfs --name ms3
S3_SECRET=$(pg addon password rustfs --name ms3)

# Create a bucket via the pg mc wrapper
pg mc alias set ms3 http://127.0.0.1:9000 admin "${S3_SECRET}"
pg mc mb ms3/my-bucket

# Upload test data (optional)
pg mc cp /tmp/data.parquet ms3/my-bucket/data.parquet

Then query it from DuckDB — note URL_STYLE 'path' is required for MinIO, silo, rustfs and compatible stores (they do not support virtual-host style):

# DuckDB v2.0.0+
duckdb -c "
  CREATE SECRET (
    TYPE S3,
    ENDPOINT '127.0.0.1:9000',
    KEY_ID 'admin',
    SECRET '${S3_SECRET}',
    USE_SSL false,
    URL_STYLE 'path'
  );
  SELECT count(*) FROM read_parquet('s3://my-bucket/data/*.parquet');
"

# DuckDB v1.5.x (legacy SET syntax)
duckdb -c "
  SET s3_endpoint = '127.0.0.1:9000';
  SET s3_access_key_id = 'admin';
  SET s3_secret_access_key = '${S3_SECRET}';
  SET s3_use_ssl = false;
  SET s3_url_style = 'path';
  SELECT count(*) FROM read_parquet('s3://my-bucket/data/*.parquet');
"

For a more permanent setup, create a DuckDB startup file (~/.duckdbrc) with the CREATE SECRET (v2.0.0+) or SET (v1.5.x) commands — they run on every session start:

-- ~/.duckdbrc (DuckDB v2.0.0+)
CREATE SECRET (
  TYPE S3,
  ENDPOINT 'minio.example.com:9000',
  KEY_ID 'admin',
  SECRET 'your-secret-key',
  USE_SSL false,
  URL_STYLE 'path'
);

Environment variables for S3

DuckDB reads endpoint and SSL settings from DUCKDB_S3_* environment variables, but credentials must use standard AWS variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY) — DuckDB’s enable_global_s3_configuration auto-detects these.

v2.0.0 limitation: URL_STYLE cannot be set via environment variables. It defaults to virtual-host, which breaks MinIO/silo/rustfs (they require path-style). For these stores, use CREATE SECRET with URL_STYLE 'path' instead of env vars.

# Endpoint and SSL via DUCKDB_S3_*
export DUCKDB_S3_ENDPOINT='127.0.0.1:9000'
export DUCKDB_S3_USE_SSL='false'

# Credentials via AWS_* (standard variables, auto-detected by DuckDB)
export AWS_ACCESS_KEY_ID='admin'
export AWS_SECRET_ACCESS_KEY='your-secret-key'

# URL style must still be set in the session or startup file (v1.5.x)
duckdb -c "SET s3_url_style = 'path'; SELECT * FROM read_parquet('s3://my-bucket/data.parquet');"

# For v2.0.0, URL_STYLE must be set via CREATE SECRET — env vars alone are insufficient
# for MinIO/silo/rustfs (virtual-host style is not supported by these stores)
duckdb -c "
  CREATE SECRET (TYPE S3, ENDPOINT '127.0.0.1:9000', KEY_ID 'admin',
    SECRET 'your-secret-key', USE_SSL false, URL_STYLE 'path');
  SELECT * FROM read_parquet('s3://my-bucket/data.parquet');
"

For MinIO/silo/rustfs, you still need s3_url_style = 'path' because these stores don’t support virtual-host style URLs.

Last modified 2026-10-30 · feat(addon): add pg_exporter as a pgexporter addon (2ea7255)