How It Works:

You load the postgres extension in your DuckDB environment, connect to your PostgreSQL database using a standard connection string, and then either attach the entire database or query specific tables. Behind the scenes, DuckDB uses PostgreSQL's efficient binary transfer protocol to read data with minimal overhead.

Copy code
-- Example using DuckDB CLI or a client library  
-- First, install and load the Postgres extension in DuckDB  
INSTALL postgres;  
LOAD postgres;

-- Option 1: Attach the entire database (exposes tables as views in DuckDB)  
ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE postgres, READ_ONLY) as pg;  
SELECT count(*) FROM pg.your_pg_table WHERE status = 'active';

-- Option 2: Query a single table directly  
-- Use postgres_scan_pushdown to attempt filter/projection pushdown  
SELECT count(*)  
FROM postgres_scan_pushdown('host=your_pg_host user=your_user dbname=your_db password=your_password', 'public', 'your_pg_table')  
WHERE status = 'active';

The Good Stuff:

• Simplicity: No changes needed on your PostgreSQL server. Just credentials and network access. Works perfectly with managed services like AWS RDS or Google Cloud SQL.
• Flexibility: Run DuckDB wherever you want—laptop, on-premise, or in the cloud. All the analytical heavy lifting happens on the DuckDB side.
• Isolation: Your production PostgreSQL server doesn't have to break a sweat handling complex analytical workloads.
• Consistent Reads: Uses transactional snapshots to ensure you're getting a consistent view of your data.
• Easy Exports: Quickly move data from PostgreSQL to other formats like Parquet, even writing directly to S3.

The Trade-offs:

• Network Bottleneck: Data travels over the network from PostgreSQL to DuckDB, which can slow things down for large tables.
• Limited Pushdown: While it supports some projection and filtering pushdown, complex operations can happen on the Postgres side, potentially requiring more data to be transferred than necessary.
• Performance Ceiling: It's often faster than native PostgreSQL for complex analytics, but slower than if that same data were in DuckDB's native storage format.

Ideal For:

This approach quacks just right in a few scenarios: (1) quick, ad-hoc analysis for exploring data and working with smaller tables, (2) for building a simple full-refresh data pipeline, and lastly (3) when you can’t (or don’t want to) install extension in your PostgreSQL server. It’s a low commitment entrypoint into DuckDB Analytics on Postgres data.

Local pg_duckdb (Without MotherDuck)

In this configuration, DuckDB instances run as part of your PostgreSQL server. You can query PostgreSQL tables through the DuckDB engine or access external data files that your PostgreSQL server can see.

Copy code
-- Example using psql connected to your PostgreSQL database with pg_duckdb available in your postgresql.conf
CREATE EXTENSION pg_duckdb;

-- Use DuckDB engine to query a Postgres table directly  
SELECT count(*) FROM your_pg_table WHERE status = 'active';

-- Use DuckDB engine to query an external Parquet file accessible from the PG server  
SELECT COUNT(*) FROM read_parquet('file.parquet');

-- Install and use a DuckDB extension within PG DuckDB (e.g., Iceberg)  
SELECT duckdb.install_extension('iceberg');  
SELECT COUNT(*) FROM iceberg_scan('data/iceberg/table');

pg_duckdb with MotherDuck Integration

This extends pg_duckdb by connecting it to your MotherDuck database. Now you can run hybrid queries that join data from your PostgreSQL tables with data stored in MotherDuck (which might include data in S3, GCS, or other cloud storage).

Copy code
-- Example using psql with pg_duckdb and MotherDuck configured  
-- Load the extension (assuming shared_preload_libraries is set)  
-- ... configure MotherDuck connection via postgresql.conf or env vars ...

-- Query combining data from a Postgres table and a MotherDuck table  
SELECT  
    c.customer_name,  
    sum(md_o.order_total) as total_spent  
FROM  
   ddb$my_db$main.customers c -- Accessing the DuckDB 'customers' table   
JOIN  
   ddb$my_db$main.orders md_o ON c.customer_id = md_o.customer_id -- Accessing the MotherDuck 'orders' table  
GROUP BY 1;

-- Example of creating tables  
CREATE TABLE my_pg_table AS SELECT ...; -- Creates a standard PostgreSQL table  
CREATE TABLE my_md_table USING duckdb AS SELECT ...; -- Creates a MotherDuck table via pg_duckdb

Pros (for either Local or MotherDuck Integrated PG DuckDB):

• Analytical Performance: DuckDB's engine can deliver impressive speedups for complex analytical workloads compared to native PostgreSQL.
• Data Locality (Local mode): No network overhead for data already in PostgreSQL.
• Hybrid Queries (MotherDuck mode): Seamlessly join operational PostgreSQL data with cloud data in a single query.
• Columnar Access: Easily query Parquet, Iceberg, and other analytics-friendly formats directly from PostgreSQL.

Cons:

• Resource Risk: Significant chance of impacting PostgreSQL server performance if not isolated on a dedicated replica.
• Extension Required: You'll need to install the pg_duckdb extension on your PostgreSQL servers, which might not be possible on all managed services.
• Operational Complexity: You'll need to manage the extension and monitor resource usage carefully.

How It Works:

This method taps into PostgreSQL's logical decoding feature. A process connects to PostgreSQL, reads the WAL, decodes the changes, and streams them to a downstream system.

Conceptual steps (actual implementation depends heavily on the CDC tool and destination)

1. Configure PostgreSQL for logical replication/decoding
2. Install and configure the CDC tool (e.g., PG Replicate, Debezium)
3. CDC tool reads WAL and streams changes
4. Downstream system (e.g., MotherDuck via a loading process) consumes changes

Technical Considerations:

• WAL Impact: CDC increases the WAL detail level, slightly increasing disk I/O and storage requirements.
• Processing Load: The CDC process adds some CPU load, and risks falling behind during high-volume write periods.
• Operational Complexity: You need to set up, monitor, and maintain a continuous pipeline, handling network issues, processing lag, and error conditions.
• Extension Requirements: Like pg_duckdb, CDC tools often require installing extensions on your PostgreSQL server.
• Managed Service Support: Support varies by cloud provider. AWS RDS supports logical replication with specific output plugins, while other providers may have different limitations.

Ideal For:

This approach shines when you need low-latency, near real-time data updates for dashboards or operational analytics. It completely separates the analytical workload from PostgreSQL once the data is moved.

Tool Comparison:

• Debezium: Mature, open-source platform supporting many databases, typically used with Kafka. Requires more infrastructure but is battle-tested.
• ETL: Newer, PostgreSQL-specific tool from Supabase. Potentially simpler setup than Debezium/Kafka but less mature.

Operational Best Practices:

Whatever path you choose (except perhaps the simplest extension use cases), careful resource planning and isolation are key:

• Use Replicas: For pg_duckdb especially, a dedicated read replica is highly recommended
• Monitor Resources: Keep a close eye on CPU, memory, I/O, and network usage
• Profile Your Queries: Understand where bottlenecks lie and leverage optimization capabilities where possible