Faster queries via columnstore

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

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

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.

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.

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

5. Create an external table

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.

6. Rerun the query against the columnstore

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.

Onboarding

Features

Analytics (DuckDB)

API Introduction

AI Tools

Cloud

Security

Blog

Faster queries via columnstore

Why columnstore tables help

Real example: clickstream engagement

1. Create the row-store fact table

2. Insert 3 million sample rows

3. Benchmark aggregates on the row-store table

Export to parquet for true columnar IO

4. Copy the dataset to parquet files

5. Create an external table

6. Rerun the query against the columnstore

​Why columnstore tables help

​Real example: clickstream engagement

​1. Create the row-store fact table

​2. Insert 3 million sample rows

​3. Benchmark aggregates on the row-store table

​Export to parquet for true columnar IO

​4. Copy the dataset to parquet files

​5. Create an external table

​6. Rerun the query against the columnstore

Why columnstore tables help

Real example: clickstream engagement

1. Create the row-store fact table

2. Insert 3 million sample rows

3. Benchmark aggregates on the row-store table

Export to parquet for true columnar IO

4. Copy the dataset to parquet files

5. Create an external table

6. Rerun the query against the columnstore