DOCSEARCHbyAlgolia
Submit Docs

pg_duckdb Documentation

repository·main·Indexed 25 days ago

https://github.com/duckdb/pg_duckdb

An official PostgreSQL extension that integrates the DuckDB columnar-vectorized analytics engine into PostgreSQL. It enables high-performance analytical queries on PostgreSQL data and external data lakes (Parquet, Iceberg, Delta Lake) without changing SQL syntax. Features include MotherDuck cloud analytics integration, Docker deployment options, and support for querying S3, CSV, and other external formats.

Tokens
19.1K
Snippets
74
Records
112
Agent score
85%

What's inside pg_duckdb

  1. Configure MotherDuck for non-superuser access

    main

    To use MotherDuck as a non-superuser, you must perform two steps:

    1. Set the Postgres role: Update the duckdb.postgres_role setting in your configuration. This requires a Postgres restart.
    2. Grant permissions: Ensure the configured role has CREATE permissions on the public schema in Postgres, as this is where tables from the MotherDuck main schema are created.

    If you grant permissions after Postgres has already started, you may need to force a full resync of the tables by terminating the sync worker process.

    # Changing this requires a Postgres restart
duckdb.postgres_role = 'your_role_name'  # e.g., duckdb or duckdb_group
    GRANT CREATE ON SCHEMA public TO {your_role_name};
-- So if you've configured the duckdb role above
GRANT CREATE ON SCHEMA public TO duckdb;
    -- Force a full resync if initial sync failed due to permission timing
SELECT * FROM pg_terminate_backend((
  SELECT pid FROM pg_stat_activity WHERE backend_type = 'pg_duckdb sync worker'
));

  2. Build and install pg_duckdb on macOS

    main

    Follow these steps to build the extension on macOS using Homebrew and Xcode Command Line Tools.

    1. Prerequisites

    Install Xcode Command Line Tools and Homebrew, then install PostgreSQL:

    xcode-select --install
brew install postgresql@18
brew services start postgresql@18
export PATH="/opt/homebrew/opt/postgresql@18/bin:$PATH"

    2. Install Build Dependencies

    brew install cmake ninja pkg-config lz4

    3. Build and Install

    make -j$(sysctl -n hw.ncpu)
sudo make install

    4. Configure and Restart

    Append pg_duckdb to your postgresql.conf (verify path with postgres --help-config):

    echo "shared_preload_libraries = 'pg_duckdb'" >> /opt/homebrew/var/postgresql@18/postgresql.conf
brew services restart postgresql@18

    5. Activate the extension

    psql -d postgres -c "CREATE EXTENSION pg_duckdb;"

    Troubleshooting macOS

    • Permission issues: Use sudo for make install.
    • Multiple PostgreSQL versions: Set PG_CONFIG to the correct path: export PG_CONFIG=/opt/homebrew/opt/postgresql@18/bin/pg_config

  3. Install DuckDB extensions

    main

    You can install DuckDB extensions manually using the duckdb.install_extension function. This is useful if automatic installation is disabled or if DuckDB does not automatically detect the need for an extension. Note that installing extensions requires superuser privileges.

    By default, known extensions can be automatically installed and loaded when a query depends on them, controlled by the duckdb.autoinstall_known_extensions and duckdb.autoload_known_extensions settings.

    -- Install the 'iceberg' extension
SELECT duckdb.install_extension('iceberg');

  4. Install pg_duckdb via Docker

    main

    You can run PostgreSQL with pg_duckdb pre-installed using the official Docker image.

    To run a standard instance:

    docker run -d -e POSTGRES_PASSWORD=duckdb pgduckdb/pgduckdb:18-v1.1.1

    To run with MotherDuck integration, provide your MotherDuck token via the MOTHERDUCK_TOKEN environment variable:

    export MOTHERDUCK_TOKEN=<your_token>
docker run -d -e POSTGRES_PASSWORD=duckdb -e MOTHERDUCK_TOKEN pgduckdb/pgduckdb:18-v1.1.1
    docker run -d -e POSTGRES_PASSWORD=duckdb pgduckdb/pgduckdb:18-v1.1.1

  5. Run pg_duckdb using Docker

    main

    You can run the pg_duckdb image using standard PostgreSQL Docker patterns. Use the -e POSTGRES_PASSWORD environment variable to set the database password.

    docker run -d -e POSTGRES_PASSWORD=duckdb pgduckdb/pgduckdb:18-main

  7. Run TPC-H benchmarks with pg_duckdb

    main

    To run TPC-H benchmarks, you must have a PostgreSQL server running with pg_duckdb installed. The easiest method is using Docker.

    1. Start PostgreSQL with pg_duckdb via Docker

    Basic setup (enables MotherDuck):

    docker run --rm -e POSTGRES_HOST_AUTH_METHOD=trust --network=host -d --name pgduck -e MOTHERDUCK_TOKEN \
    pgduckdb/pgduckdb:18-main

    Optimized setup for benchmarks: For real benchmarks, it is recommended to tune Docker's --shm-size and PostgreSQL settings to match your machine. For an AWS c6a.8xlarge (32 vCPU, 64GB RAM), use the following configuration:

    docker run --rm -e POSTGRES_HOST_AUTH_METHOD=trust --network=host -d --name pgduck -e MOTHERDUCK_TOKEN \
    --shm-size=64g \
    pgduckdb/pgduckdb:18-main \
    -c shared_buffers=32GB \
    -c max_parallel_workers=32 \
    -c max_parallel_workers_per_gather=8 \
    -c duckdb.max_workers_per_postgres_scan=8 \
    -c duckdb.threads_for_postgres_scan=8

    2. Install Dependencies

    Clone the repository and install dependencies using uv (recommended) or pip:

    git clone https://github.com/duckdb/pg_duckdb
cd pg_duckdb/scripts/tpch

# Using uv (recommended)
curl -LsSf https://astral.sh/uv/install.sh | sh

# OR using pip
pip install pandas matplotlib psycopg[binary]

    3. Execute Benchmarks

    Use the ./run.py script to execute the benchmark. For real performance comparisons, use scale factors of 1 or higher.

  8. Change the default MotherDuck database

    main

    If you need to change the default MotherDuck database after it has already been enabled, you must drop the existing motherduck server and then re-run the enablement function.

    Warning: Using DROP SERVER motherduck CASCADE will remove all schemas and tables that were synced from MotherDuck. They will be automatically recreated by the background worker after you reconnect.

    -- Drop the existing connection (this will CASCADE drop all synced tables/schemas)
DROP SERVER motherduck CASCADE;

-- Reconnect with a different default database
CALL duckdb.enable_motherduck('<token>', '<new_default_database>');

  9. Run pg_duckdb with MotherDuck integration

    main

    To use MotherDuck with the Docker image, export your personal MotherDuck token as an environment variable and pass it to the container using the -e MOTHERDUCK_TOKEN flag.

    $ export MOTHERDUCK_TOKEN=<your personal MD token>
$ docker run -d -e POSTGRES_PASSWORD=duckdb -e MOTHERDUCK_TOKEN pgduckdb/pgduckdb:18-main

  10. Build and install pg_duckdb on Ubuntu 24.04

    main

    Follow these steps to set up PostgreSQL, install dependencies, and build the extension on Ubuntu 24.04 (example uses PostgreSQL 18).

    1. Set up Postgres

    Install PostgreSQL 18 using PGDG:

    sudo apt install postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install postgresql-18 postgresql-server-dev-18

    Note: You must have the server-dev package installed to compile extensions.

    2. Install Build Dependencies

    sudo apt install \
    build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev \
    libxslt-dev libssl-dev libxml2-utils xsltproc pkg-config libc++-dev \
    libc++abi-dev libglib2.0-dev libtinfo6 cmake libstdc++-12-dev \
    liblz4-dev libcurl4-openssl-dev ninja-build

    3. Build and Install

    make -j$(nproc)
sudo make install

    4. Configure and Restart

    Add pg_duckdb to shared_preload_libraries. You can create a config file in conf.d:

    echo "shared_preload_libraries = 'pg_duckdb'" | sudo tee /etc/postgresql/18/main/conf.d/pg_duckdb.conf

    Then restart the service:

    sudo service postgresql restart

    5. Activate the extension

    Connect via psql as a superuser and run:

    CREATE EXTENSION pg_duckdb;

  11. Integrate with MotherDuck cloud analytics

    main

    To use MotherDuck as a compute provider, connect using the duckdb.enable_motherduck() function with your MotherDuck token. This allows you to run queries on MotherDuck's infrastructure and create cloud tables that sync across teams using the USING duckdb clause.

    -- Connect to MotherDuck
CALL duckdb.enable_motherduck('<your_motherduck_token>');

-- Create cloud tables that sync across teams
CREATE TABLE real_time_kpis USING duckdb AS
SELECT
    date_trunc('day', created_at) as date,
    COUNT(*) as daily_signups,
    SUM(revenue) as daily_revenue
FROM user_events
GROUP BY date;

  12. Configure simple secrets using utility functions

    main

    The simplest way to configure credentials for cloud storage is by using DuckDB utility functions. This is ideal for common use cases like AWS S3, GCS, or R2.

    AWS S3 / Compatible Storage

    To configure basic S3 credentials, use duckdb.create_simple_secret with the S3 type.

    Azure Storage

    For Azure, use duckdb.create_azure_secret with your connection string.

    -- Basic S3 secret (most common)
SELECT duckdb.create_simple_secret(
    type := 'S3',
    key_id := 'your_access_key_id',
    secret := 'your_secret_access_key',
    region := 'us-east-1'
);

-- Azure secret
SELECT duckdb.create_azure_secret(
    '< connection string >',
    scope := 'xxx'          -- (optional)
);