> ## 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.

# Faster queries via columnstore

> Shows how to migrate heavy queries from Postgres to DuckDB-backed columnstores for sub-second runtimes.

When `duckdb.force_execution` is enabled, pg\_duckdb handles query execution but still relies on PostgreSQL to scan row-store tables. For scans without selective filters, this hybrid flow can bottleneck on PostgreSQL’s table scan before DuckDB ever gets to apply its vectorized magic. Converting hot analytical tables into parquet-backed columnstores removes that bottleneck—the data is read natively by DuckDB, which keeps the entire pipeline columnar.

## Why columnstore tables help

* Row-store scans stay in PostgreSQL and are serialized tuple-by-tuple into DuckDB batches.
* Columnstore tables (backed by parquet files) are read directly by DuckDB, avoiding the conversion step and enabling parallel columnar IO.
* You can keep the original OLTP table for writes while exporting snapshots to parquet whenever you need faster analytics.

## Real example: clickstream engagement

### 1. Create the row-store fact table

```sql theme={null}
CREATE TABLE event_log (
    event_id BIGSERIAL PRIMARY KEY,
    event_ts TIMESTAMPTZ NOT NULL,
    user_id INT NOT NULL,
    region TEXT,
    device TEXT,
    session_minutes NUMERIC(10, 2),
    ad_impressions INT,
    revenue NUMERIC(10, 2),
    payload JSONB
);
```

### 2. Insert 3 million sample rows

```sql theme={null}
INSERT INTO event_log (
    event_ts, user_id, region, device,
    session_minutes, ad_impressions, revenue, payload
)
SELECT
    NOW() - (random() * INTERVAL '730 days'),
    (random() * 200000)::INT,
    CASE 
        WHEN random() > 0.66 THEN 'NA' 
        WHEN random() > 0.33 THEN 'EMEA' 
        ELSE 'APAC' 
    END,
    (ARRAY['ios','android','web'])[1 + (random()*2)::INT],
    ROUND((random() * 120)::numeric, 2),
    (random() * 40)::INT,
    ROUND((random() * 25)::numeric, 2),
    jsonb_build_object('campaign', md5(random()::text))
FROM generate_series(1, 3000000);

-- Refresh statistics
ANALYZE event_log;
```

### 3. Benchmark aggregates on the row-store table

Run the single-stage aggregation below first with PostgreSQL’s executor, then flip `SET duckdb.force_execution = true;`. DuckDB still trims latency (about 3.0s → 2.1s), but the scan stays in PostgreSQL so both paths remain in the same ballpark.

```sql theme={null}
EXPLAIN ANALYZE
SELECT
    date_trunc('hour', event_ts) AS hour_bucket,
    region,
    device,
    SUM(session_minutes) AS total_minutes,
    AVG(ad_impressions) AS avg_ads,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS events_seen
FROM event_log
GROUP BY 1,2,3
HAVING SUM(session_minutes) > 1000
ORDER BY total_minutes DESC
LIMIT 50;
```

| Engine     | Table scanned | Runtime observed | Notes                                                 |
| ---------- | ------------- | ---------------- | ----------------------------------------------------- |
| PostgreSQL | `event_log`   | \~3.0 seconds    | Row-store scan plus aggregate materialization         |
| DuckDB     | `event_log`   | \~2.1 seconds    | DuckDB executes, but scan still comes from PostgreSQL |

## Export to parquet for true columnar IO

### 4. Copy the dataset to parquet files

Prerequisites:

* Access to an S3-compatible object store that DuckDB can reach.

Exports to object storage always run with DuckDB automatically, so no GUC changes are needed. Relyt ONE’s built-in cache service further boosts bandwidth/latency for repeated parquet scans—nothing to configure on your end.

```sql theme={null}
COPY (
    SELECT * FROM event_log
) TO 's3://<endpoint>/<bucket>/event_log/data.parquet accessid=xxx secret=yyy';
```

### 5. Create an external table

```sql theme={null}
CREATE TABLE event_log_cstore () USING duckdb WITH (
    duckdb_external_location = 's3://<endpoint>/<bucket>/event_log/data.parquet accessid=xxx secret=yyy',
    duckdb_external_format = 'parquet'
);
```

For more details on DuckDB-backed external tables, see [external tables](../external_table).

### 6. Rerun the query against the columnstore

```sql theme={null}
EXPLAIN ANALYZE
SELECT
    date_trunc('hour', event_ts) AS hour_bucket,
    region,
    device,
    SUM(session_minutes) AS total_minutes,
    AVG(ad_impressions) AS avg_ads,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS events_seen
FROM event_log_cstore
GROUP BY 1,2,3
HAVING SUM(session_minutes) > 1000
ORDER BY total_minutes DESC
LIMIT 50;
```

| Engine | Table scanned      | Runtime observed | Notes                                       |
| ------ | ------------------ | ---------------- | ------------------------------------------- |
| DuckDB | `event_log_cstore` | \~0.8 seconds    | Fully columnar scan, vectorized aggregation |

Scanning parquet with DuckDB removes the PostgreSQL scan bottleneck entirely, dropping the aggregation from \~2 seconds to \~0.8 seconds. Refresh the parquet snapshot whenever you need fresher analytics, and continue to use the OLTP `event_log` table for day-to-day writes.