WW warin.me

POSTGRESQL → DUCKDB · ANALYTICAL BRIDGE

Keep transactions governed. Move bounded snapshots to analytics.

This lab uses the existing Data Systems runtime. PostgreSQL remains the operational source; DuckDB receives monthly analytical snapshots and a deliberately duplicated teaching event for comparison.

Checking existing PostgreSQL and DuckDB…
NO NEW ENGINE2 pathsmonthly export · dual write

WHY DUCKDB

An analytical database can live beside the files—not behind another service.

Embedded

DuckDB runs inside the application process and opens a local database or Parquet file.

Columnar analytics

Vectorized execution and column-oriented files suit scans, joins, windows and aggregations.

Clear boundary

PostgreSQL owns transactional truth; DuckDB serves a bounded analytical copy.

ANALYTICAL SQL DIRECTLY ON FILES

Take SQL to the files—without importing them into another server first.

DuckDB can query CSV and Parquet as if they were tables. The file remains the source while DuckDB supplies filters, joins, grouping and window functions.

CSV / Parquetpipeline files
→
DuckDB SQLscan · filter · join · aggregate
→
Analytical resulttable · report · Parquet

1 · Query CSV directly

No CREATE TABLE or INSERT is required before asking the question.

SELECT category, count(*) AS orders, sum(revenue) AS revenue
FROM read_csv_auto('sales.csv')
GROUP BY category;

2 · Query many monthly Parquet files

A filename pattern becomes one logical relation.

SELECT date_trunc('month', sale_timestamp) AS month,
       region, sum(revenue) AS revenue
FROM read_parquet('monthly/sales-2024-*.parquet')
GROUP BY month, region
ORDER BY month, region;

3 · Join files with SQL

The files do not need to become permanent DuckDB tables first.

SELECT s.category, c.segment, sum(s.revenue) AS revenue
FROM read_parquet('sales/*.parquet') AS s
JOIN read_parquet('customers.parquet') AS c
  ON s.customer_id = c.customer_id
GROUP BY s.category, c.segment;

4 · Window function on a file scan

Analytical SQL remains available when the source is Parquet.

SELECT sale_day, revenue,
       sum(revenue) OVER (
         ORDER BY sale_day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7_days
FROM read_parquet('daily-sales.parquet');

CSV

Values arrive as text. DuckDB must parse delimiters, records and types before analysis.

2024-01-01,Central,Electronics,1500.00

Parquet

Columns, types, compressed pages, row groups and statistics travel with the file.

sale_day │ region │ category │ revenue

Projection pushdown

SELECT region, sum(revenue)

DuckDB can read only region and revenue rather than every column.

Filter pushdown

WHERE sale_day >= DATE '2024-03-01'

Parquet statistics may let DuckDB skip row groups that cannot contain March data.

PostgreSQL vs DuckDB

PostgreSQL normally imports data into a governed table before querying it. DuckDB commonly queries Parquet in place. PostgreSQL can use extensions or foreign data wrappers, but direct file analytics is a central DuckDB workflow.

Use the file produced by this lab

After Lab 01 exports January, query the resulting Parquet directly rather than loading another table.

SELECT region, category, count(*) AS orders,
       sum(revenue) AS revenue
FROM read_parquet(
  '/var/lib/data-systems-lab/data/pg-2024-01.parquet'
)
GROUP BY region, category
ORDER BY revenue DESC;

Core idea: DuckDB does not need to own the data as a permanent table before SQL can analyze it.

PostgreSQLoperational rows
→
Monthly boundary[start, next month)
→
CSV → Parquetportable snapshot
→
DuckDBanalytical SQL

LAB 01 · MONTHLY EXPORT

Export one closed month, then analyze the snapshot.

  1. Filter by a half-open month range: start inclusive, next month exclusive.
  2. Stream PostgreSQL rows to a bounded CSV file.
  3. Load the CSV in DuckDB and persist a compressed Parquet snapshot.
  4. Aggregate by region and category.
WHERE sale_timestamp >= DATE '2024-01-01'
  AND sale_timestamp < DATE '2024-02-01'
COPY pg_monthly_sales
TO 'pg-2024-01.parquet'
(FORMAT PARQUET);
No export yet.

LAB 02 · DUAL WRITE + SAME QUERY

Write one event twice, then compare equivalent work.

The lab inserts the same synthetic sale into PostgreSQL and DuckDB, then runs the same monthly aggregation independently. Compare result equality, write cost and query time—not only the smallest millisecond value.

Important boundary

This demonstration is not an atomic distributed transaction. A production dual-write design needs an outbox, CDC or another recovery strategy for partial failure.

SELECT region, category, count(*) AS orders,
       round(sum(revenue), 2) AS revenue
FROM sales
WHERE sale_timestamp >= TIMESTAMP '2024-01-01'
  AND sale_timestamp < TIMESTAMP '2024-02-01'
GROUP BY region, category
ORDER BY revenue DESC;
No comparison yet.

WHAT TO OBSERVE

The useful conclusion is architectural—not a race result.

Freshness

A monthly snapshot is intentionally behind PostgreSQL. Record its cutoff and rebuild policy.

Consistency

Dual write creates two success/failure points. Verify counts or use a recoverable delivery pattern.

Fair timing

DuckDB timing includes process startup here; cache and data layout also affect both measurements.

LAB 03 · FIND THE WINNING WORKLOAD

Columnar is an advantage only when the workload can use it.

This separates engine-reported query execution from process startup and repeats each query three times. Two controls scan one million rows; three dashboard scenarios reuse a compact DuckDB summary.

Do not hide losing cases

The advantage should come from appropriate analytical layout and reuse—not from the word columnar alone.

1,000,000 rows · 3 repetitions

No benchmark yet.