> ## Documentation Index
> Fetch the complete documentation index at: https://docs-relytone.data.cloud/llms.txt
> Use this file to discover all available pages before exploring further.

# pg_duckdb hands-on

> Accelerate analytical SQL by enabling pg_duckdb with a single session GUC—no query rewrites or data movement required.

Relyt ONE ships with [pg\_duckdb](https://github.com/duckdb/pg_duckdb), a DuckDB-based columnar execution engine. It accelerates analytical SQL workloads on your operational data with no schema changes or ETL.

This hands-on guide walks you through enabling pg\_duckdb, running starter queries, and stress-testing a larger dataset so you can feel the speedup directly in `psql` or your favorite client.

## Step 1. Create a sample table and seed data

The following SQL creates a transactional `orders` table and inserts a few rows for testing.

```sql theme={null}
-- Create a standard PostgreSQL table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_name TEXT,
    amount DOUBLE PRECISION,
    order_date DATE
);

-- Insert sample rows
INSERT INTO orders (product_name, amount, order_date)
VALUES 
    ('Laptop', 1200.00, '2024-07-01'),
    ('Keyboard', 75.50, '2024-07-01'),
    ('Mouse', 25.00, '2024-07-02');
```

## Step 2. Route queries through DuckDB

Enable the DuckDB execution engine for the current session. With this single GUC, all supported queries automatically run on the vectorized engine.

```sql theme={null}
SET duckdb.force_execution = true;
```

To make DuckDB the default executor for every new connection, set the GUC at the role or database level (changes apply only to sessions started after the ALTER runs):

```sql theme={null}
ALTER ROLE <role> SET duckdb.force_execution = true;

ALTER DATABASE <database> SET duckdb.force_execution = true;
```

No query rewrites are required—keep using PostgreSQL syntax.

## Step 3. Run analytical queries

### Aggregate revenue by product

```sql theme={null}
SELECT
    product_name,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count
FROM orders
GROUP BY product_name
ORDER BY total_sales DESC;
```

### Aggregate revenue by day

```sql theme={null}
SELECT
    order_date,
    SUM(amount) AS daily_sales,
    COUNT(*) AS order_count
FROM orders
GROUP BY order_date
ORDER BY order_date;
```

These queries now execute on DuckDB’s columnar engine, delivering lower latency and more predictable performance for aggregation-heavy workloads.

## Real analytical workload example

For a more realistic benchmark, seed a `sales_log` fact table with three million synthetic transactions and then route a complex window query through DuckDB.

### Create the transactional table

```sql theme={null}
CREATE TABLE sales_log (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2),
    user_id INT,
    region TEXT,
    category TEXT,
    comment TEXT
);
```

### Insert 3 million sample rows

```sql theme={null}
INSERT INTO sales_log (
    sale_date, amount, user_id, region, category, comment
)
SELECT 
    '2020-01-01'::DATE + (random() * 1500)::INT, 
    (random() * 1000)::NUMERIC(10, 2),
    (random() * 10000)::INT,
    CASE WHEN random() > 0.5 THEN 'North America' ELSE 'Asia Pacific' END,
    'Category-' || (random() * 50)::INT,
    md5(random()::TEXT) || md5(random()::TEXT)
FROM generate_series(1, 3000000);
```

Refresh planner statistics so both executors have accurate metadata:

```sql theme={null}
ANALYZE sales_log;
```

Optionally confirm the on-disk size:

```sql theme={null}
SELECT pg_size_pretty(pg_total_relation_size('sales_log')) AS table_size;
```

### Benchmark the window-heavy query

1. **Run with PostgreSQL’s executor (default)**\
   Leave `duckdb.force_execution` unset (or explicitly `SET duckdb.force_execution = false;`) and execute the analytical query below. Record the elapsed time from `EXPLAIN ANALYZE`.

2. **Enable DuckDB and rerun**\
   Turn on DuckDB for the current session and immediately run the same SQL again to compare results:

   ```sql theme={null}
   SET duckdb.force_execution = true;
   ```

The query operates directly on the `sales_log` row-store table, grouping three million rows into 10,000 user partitions, applying a moving average window, filtering anomalies, and sorting the top 100 largest deviations:

```sql theme={null}
EXPLAIN ANALYZE
WITH user_history AS (
    SELECT 
        sale_id,
        user_id,
        sale_date,
        amount,
        AVG(amount) OVER (
            PARTITION BY user_id 
            ORDER BY sale_date 
            ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING
        ) AS user_recent_avg
    FROM sales_log
)
SELECT *
FROM user_history
WHERE amount > (user_recent_avg * 5)
ORDER BY amount DESC
LIMIT 100;
```

| Executor      | `duckdb.force_execution` | Runtime observed |
| ------------- | ------------------------ | ---------------- |
| PostgreSQL    | `false` (default)        | \~5.8 seconds    |
| DuckDB engine | `true`                   | \~1.5 second     |

Your mileage may vary based on hardware and background workload, but the pattern—run once with PostgreSQL, flip the GUC, rerun with DuckDB—makes it easy to validate the speedup yourself.

## Key benefits

* **Seamless integration**: Works with existing PostgreSQL SQL and tooling.
* **High performance**: Vectorized execution with automatic parallelism for scans and aggregations.
* **Zero friction**: A single session parameter (`duckdb.force_execution`) controls the engine.

## Operational tips

### Automatic fallback

If a statement includes features not supported by DuckDB, Relyt automatically falls back to the default PostgreSQL executor. No manual intervention is required.

### Inspect the execution engine

Use `EXPLAIN` to verify whether DuckDB handled the query:

```sql theme={null}
EXPLAIN
SELECT
    product_name,
    SUM(amount) AS total_sales
FROM orders
GROUP BY product_name;
```

Look for DuckDB nodes in the plan to confirm the handoff. If you only see PostgreSQL plan nodes, the fallback path was used.