Postgres Endpoint

Preview

This feature is in preview and is subject to change.

MotherDuck's Postgres endpoint lets you query your databases using any client that speaks the PostgreSQL wire protocol — without installing a DuckDB client library.

For a how-to guide on connecting, see Connect through the Postgres endpoint.

Connection parameters

Parameter	Value
Host	pg.<region>-aws.motherduck.com (for example, pg.us-east-1-aws.motherduck.com; find your region with md_user_info())
Port	5432
Database	md:, or a specific database name
User	postgres
Password	Your MotherDuck access token

Connection string formats

# psql
PGPASSWORD=$MOTHERDUCK_TOKEN psql -h pg.us-east-1-aws.motherduck.com -p 5432 -U postgres "dbname=md: sslmode=verify-full sslrootcert=system"

# libpq URI
postgresql://postgres:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=verify-full&sslrootcert=system

# DSN keyword/value
host=pg.us-east-1-aws.motherduck.com port=5432 dbname=md: user=postgres password=$MOTHERDUCK_TOKEN sslmode=verify-full sslrootcert=system

Use md: as the database name, or specify a database by name, for example sample_data.

SSL and certificate verification

The Postgres endpoint requires encrypted connections. For the best security, verify the server certificate.

SSL modes

Mode	Encryption	Server verification	Recommendation
verify-full	Yes	Yes	Recommended for production
require	Yes	No	Fallback if certificate verification is not possible

Use the system certificate store (recommended)

Set sslmode=verify-full with sslrootcert=system to use your operating system's trusted root certificates. This is supported in libpq 16 and later, and in libraries that wrap libpq (like psycopg v3).

sslmode=verify-full sslrootcert=system

Use a specific certificate file

If your client doesn't support sslrootcert=system, download the ISRG Root X1 certificate from Let's Encrypt and point your client to it:

sslmode=verify-full sslrootcert=/path/to/isrgrootx1.pem

Library-specific SSL handling

Some libraries have their own SSL implementations that don't use libpq directly:

Library	SSL behavior	Workaround
psycopg (v3)	Wraps libpq — sslrootcert=system works	None needed
psycopg2	Bundles its own OpenSSL — sslrootcert=system is not supported	Use sslrootcert=certifi.where() with the certifi package
PostgreSQL JDBC	Looks for ~/.postgresql/root.crt by default	Set sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory to use JVM trust store
node-postgres (pg)	Reads sslrootcert as a file path — system causes ENOENT	Use config object: ssl: { rejectUnauthorized: true }
Cloudflare Workers (pg-cloudflare)	TLS handled by the Workers runtime at the socket level — application-level verification settings are not exposed through the pg client	Use ?sslmode=require in the connection string

Session options

You can pass DuckDB session options using the PGOPTIONS environment variable:

PGOPTIONS="--attach_mode=single --session_name=pg-using-options" psql -h pg.us-east-1-aws.motherduck.com -p 5432 -U postgres md:

Option	Description
--attach_mode=single	Only attach the specified database. Recommended when connecting from IDEs or BI tools to avoid seeing objects from other databases. See Attach Modes.

Connection pooling and timeouts

For production applications, use a connection pool and set client-side timeouts. Recommended starting points:

Setting	Starting point	Why
Connection acquire timeout	30 seconds	Fails fast when no connection can be opened or checked out
Maximum pool size	5-10 connections per application instance	Caps how many queries the application runs concurrently
Idle connection lifetime	30-60 seconds	Recycles unused connections quickly
Maximum connection lifetime	10-30 minutes	Periodically replaces long-lived connections
Query timeout	60 seconds, or your application SLA	Cancels runaway queries before requests pile up

Always release checked-out connections back to the pool after use. If a connection sees a network, protocol, or server-termination error, discard it instead of returning it to the pool. SQL errors caused by a bad query do not always mean the underlying connection is unhealthy, but failed transactions should be rolled back before reuse.

statement_timeout is not supported through the Postgres endpoint today. Do not rely on SET statement_timeout, options=-c statement_timeout=..., or startup options for query cancellation. Use your client library's timeout or cancellation feature instead, such as node-postgres query_timeout, JDBC Statement.setQueryTimeout, or a client-side cancel call in psycopg.

Supported features and limitations

DuckDB SQL, not PostgreSQL

The Postgres endpoint is a PostgreSQL-wire interface to MotherDuck. You write DuckDB SQL, not PostgreSQL SQL.

Best suited for

• query execution against MotherDuck tables
• DDL and DML that run entirely inside MotherDuck
• metadata inspection
• server-side reads from remote storage

Use a DuckDB client path instead when you need

• local-file workflows such as local-file COPY, EXPORT DATABASE, or IMPORT DATABASE
• local or in-memory attachments such as ATTACH ':memory:' or ATTACH '/path/to/file.duckdb'
• local execution paths such as MD_RUN=LOCAL
• extension-based workflows such as INSTALL, LOAD, or cloud-storage CREATE SECRET
• DuckDB-client session features such as CREATE RESULT

Compatibility notes

• PostgreSQL-specific features such as pg_* functions, PostgreSQL indexes, sequences, and stored procedures are not supported.
• Transaction semantics follow the DuckDB model. Nested transactions are not supported.
• Some commands are further restricted in PG server mode. For example, SET threads and CREATE TEMP TABLE are not supported through the Postgres endpoint.

Operational limitations

• Configuration settings are restricted. The Postgres endpoint connects in SaaS mode, a MotherDuck mode that blocks most DuckDB configuration changes after connecting and disables installing or loading extensions. Avoid using SET statements in your client code.
• IDE schema browsers may show extra objects. Some IDEs display tables from all attached databases. Use attach_mode=single to scope the catalog to your target database.
• Use connection pooling in production. Each connection consumes server resources. For applications that need many connections, use a connection pooler (for example, Cloudflare Hyperdrive, PgBouncer, or a language-native pool) rather than rapidly opening and closing connections.
• Third-party tool support is in early stages. Check the Integrations page for tools that support the Postgres endpoint.