Skip to content

motherduck.md

Latest commit

 

History

History
193 lines (131 loc) · 9.13 KB

motherduck.md

File metadata and controls

193 lines (131 loc) · 9.13 KB

MotherDuck Integration

Connect with MotherDuck

pg_duckdb integrates natively with MotherDuck. To enable this support, you first need to generate an access token. Then, you can enable it using the duckdb.enable_motherduck convenience function:

-- If not provided, the token will be read from the `motherduck_token` environment variable
-- If not provided, the default MD database name is `my_db`
CALL duckdb.enable_motherduck('<optional token>', '<optional MD database name>');

This function creates a motherduck SERVER using the pg_duckdb Foreign Data Wrapper, which hosts the options for this integration. It also provides a USER MAPPING for the current user, which stores the provided MotherDuck token (if any).

Important: once MotherDuck is enabled, calling enable_motherduck again with a different database parameter will not change the default database. Instead, it will return a notice saying "MotherDuck is already enabled" and ignore the new database parameter. To change the default database, see Changing the Default Database below. Alternatively, you can access any MotherDuck database without changing the default using the ddb$<database> schema syntax described in Schema Mapping.

You can refer to the Advanced MotherDuck Configuration section below for more on the SERVER and USER MAPPING configuration.

Non-Superuser Configuration

If you want to use MotherDuck as a non-superuser, you also have to configure the duckdb.postgres_role setting:

# Changing this requires a Postgres restart
duckdb.postgres_role = 'your_role_name'  # e.g., duckdb or duckdb_group

You also need to ensure that this role has CREATE permissions on the public schema in Postgres, as this is where tables from the MotherDuck main schema are created. You can grant these permissions as follows:

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;

If you grant these permissions after starting Postgres, the initial sync of MotherDuck tables may have failed for the public schema. You can force a full resync of the tables by running:

SELECT * FROM pg_terminate_backend((
  SELECT pid FROM pg_stat_activity WHERE backend_type = 'pg_duckdb sync worker'
));

Using MotherDuck with pg_duckdb

After completing the configuration (and possibly restarting Postgres in case you changed duckdb.postgres_role), you can create tables in your MotherDuck database using the duckdb Table Access Method (TAM):

CREATE TABLE orders(id bigint, item text, price NUMERIC(10, 2)) USING duckdb;
CREATE TABLE users_md_copy USING duckdb AS SELECT * FROM users;

Any tables that you already had in MotherDuck are automatically available in Postgres. Since DuckDB and MotherDuck allow accessing multiple databases from a single connection and Postgres does not, we map database+schema in DuckDB to a schema name in Postgres.

The default MotherDuck database will be easiest to use (see below for details), by default this is my_db.

Advanced MotherDuck Configuration

If you want to specify which MotherDuck database is your default, you need to configure MotherDuck using a SERVER and a USER MAPPING:

CREATE SERVER motherduck
TYPE 'motherduck'
FOREIGN DATA WRAPPER duckdb
OPTIONS (default_database '<your database>');

-- You may use `::FROM_ENV::` to have the token be read from the environment variable
CREATE USER MAPPING FOR CURRENT_USER SERVER motherduck OPTIONS (token '<your token>')

Note: The duckdb.enable_motherduck function simplifies this process:

CALL duckdb.enable_motherduck('<token>', '<default database>');

Changing the Default Database

If you need to change the default MotherDuck database after initially enabling it, you must first drop the motherduck server and then 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>');

Important: 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 reconnecting.

Alternative: if you only need to query tables from a different database occasionally, you don't need to change the default database at all. You can use the ddb$<database> schema prefix to access any MotherDuck database simultaneously. See the Schema Mapping section below for details and examples.

Schema Mapping

DuckDB and Postgres have different schema and database conventions. While you specify a default MotherDuck database when connecting, you can access tables from any MotherDuck database in your account using the schema mapping described below.

The mapping from a DuckDB database.schema to a Postgres schema is done as follows:

  1. The main DuckDB schema in your default database is merged with the Postgres public schema.
  2. All other schemas in your default MotherDuck database is merged with the Postgres schema of the same name.
  3. The main schema in other databases can be accessed using the name ddb$<db_name> (including the literal $ character).
  4. All other schemas in non-default MotherDuck databases are accessible through ddb$<duckdb_db_name>$<duckdb_schema_name> (including the literal $ characters).

An example of each of these cases is shown below:

-- Assuming my_db is your default database:

-- Case 1: Default database main schema -> public
INSERT INTO my_table VALUES (1, 'abc'); -- inserts into my_db.main.my_table

-- Case 2: Default database other schemas -> same schema name
INSERT INTO your_schema.tab1 VALUES (1, 'abc'); -- inserts into my_db.your_schema.tab1

-- Case 3: Other database main schema -> ddb$<db_name>
SELECT COUNT(*) FROM ddb$my_shared_db.aggregated_order_data; -- reads from my_shared_db.main.aggregated_order_data

-- Case 4: Other database other schemas -> ddb$<db_name>$<schema_name>
SELECT COUNT(*) FROM ddb$sample_data$hn.hacker_news; -- reads from sample_data.hn.hacker_news

Example: Accessing multiple databases in a single query:

-- Join data from your default database (my_db) with sample_data
SELECT
    orders.order_id,
    orders.customer_name,
    hn.title as related_article
FROM my_table orders  -- my_db.main.my_table (default database)
JOIN ddb$sample_data$hn.hacker_news hn  -- sample_data.hn.hacker_news (different database)
    ON orders.topic = hn.type
WHERE orders.created_at > '2024-01-01';

Switching MotherDuck Tokens

To switch your MotherDuck Token, first call: DROP SERVER MOTHERDUCK CASCADE; and then reinitialize with a new token: CALL duckdb.enable_motherduck('new_token');

Simple Read-scaling Setup

pg_duckdb supports read-scaling tokens for MotherDuck, you can simply use a read-scaling token as the token when enabling MotherDuck:

CALL duckdb.enable_motherduck('<your_read_scaling_token>', '<database_name>');

You can create a read-scaling token in the MotherDuck UI by selecting "Read Scaling Token" as the token type.

Read-scaling setup with Read Write Setup

For applications that need both read and write access from the same PostgreSQL instance, you can configure different PostgreSQL users with different MotherDuck tokens. In this case the server owner should get a write token, so it can sync the latest versions of tables from MotherDuck. Any Postgres users that should use read-scaling should get a read-scaling token.

Step 1: Create the MotherDuck server with a write token

-- Create the server (no token here)
CALL duckdb.enable_motherduck('<your_write_token>', '<your_database_name>');

Step 2: Create a read-only user with a read-scaling token

-- Create a PostgreSQL user for read-only access
CREATE USER reader_user IN ROLE duckdb_group;

-- Give them a read-scaling token
CREATE USER MAPPING FOR reader_user
    SERVER motherduck
    OPTIONS (token '<your_read_scaling_token>');

Usage

-- As the server owner: full read-write access
INSERT INTO my_table VALUES (1, 'data');
SELECT * FROM my_table;

-- As reader_user: read-only access that scales across read-scaling ducklings
SELECT * FROM my_table;  -- works
INSERT INTO my_table VALUES (2, 'more');  -- fails with read-only error

Debugging

If some tables or schemas are not appearing as expected, check your Postgres log file. The background worker that automatically syncs tables may have encountered an error, which will be reported in the logs, often with information on how to resolve the issue.