When Indexes Stop Helping: Columnar Engines

A systems-level look at why row-store indexes stop paying off for wide analytical scans, and where columnar execution changes the access pattern.

Indexing is usually the first serious answer to a slow SQL query. That instinct is correct when the query is selective.

But “slow query” is not one failure mode. A query can be slow because statistics are stale, predicates are non-sargable, joins are correlated accidentally, memory settings force spills, network round trips dominate, or the application is asking a row-oriented SQL database(PostgreSQL) to compute too much derived state on demand.

This article narrows in on that last case: the point where the query is no longer mostly a row-finding problem. At that boundary, the decision is not simply “add another index” versus “buy more compute.” The decision is whether the workload has become an analytical read-model problem, and whether a columnar engine should own that read path.

If the application asks for one shipment by ID, the last 50 events for one container, or all pending invoices for one customer, an index can change the query from:

read too much data

to:

seek directly to the rows that matter

But indexing has a ceiling.

An index is an access path over stored values. It helps when PostgreSQL can skip irrelevant rows. It does not make every expensive query cheap, because not every expensive query is expensive for the same reason.

The important distinction is:

Indexing problem:
  PostgreSQL already has the answer stored in rows.
  The bottleneck is finding the right rows without reading too many wrong ones.

Read-model problem:
  The answer does not exist as stored rows yet.
  The query must build it by reading many rows, joining them, aggregating them,
  then filtering and ranking the derived result.

Those are different problems.

The columnar DB migration (DuckDB in this example) is not primarily about “more compute.” It is about using a physical read model and execution engine built for the second shape: large analytical working sets, selected columns, vectorized aggregation, and derived result ranking. Indexes reduce lookup work over stored facts; they do not remove the need to build facts that are produced only during the query.

This article is about that boundary: when a PostgreSQL query should be fixed with better indexes or query shape, and when the workload has crossed into a shape better served by DuckDB over a derived columnar read model.

The Scenario

Imagine a logistics platform with a cargo lane screener.

A lane is an origin-destination pair:

Kathmandu -> Singapore
Shanghai  -> Rotterdam
Hamburg   -> Dubai

The product team wants a screen that finds risky cargo lanes and carriers.

The user does not ask:

Show me shipment SHP-100923.

The user asks:

For every carrier and cargo lane,
look at a custom date range,
compare current transit time against the lane baseline,
compute exception rate, late delivery rate, dwell time, cost volatility,
combine those into a risk score,
filter by the derived metrics,
sort by risk,
return the top 100.

That is not an OLTP lookup. It is an analytical screener.

OLTP means online transaction processing: create a shipment, update a status, enforce a constraint, fetch one customer, write one transaction.

OLAP means online analytical processing: scan many rows, read selected columns, aggregate, group, filter, rank.

PostgreSQL can do plenty of analytics. The question is not whether PostgreSQL is capable. The question is whether an interactive, scan-heavy analytical workload should share the same physical path and failure domain as the transactional source of truth.

A Simplified Schema

Use four tables:

CREATE TABLE shipments (
  shipment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  carrier_id bigint NOT NULL,
  customer_id bigint NOT NULL,
  origin_port text NOT NULL,
  destination_port text NOT NULL,
  cargo_type text NOT NULL,
  departed_at timestamptz NOT NULL,
  delivered_at timestamptz,
  quoted_cost numeric NOT NULL,
  actual_cost numeric,
  weight_kg numeric NOT NULL
);
CREATE TABLE shipment_events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shipment_id bigint NOT NULL REFERENCES shipments(shipment_id),
  event_type text NOT NULL,
  event_time timestamptz NOT NULL,
  location_code text NOT NULL
);
CREATE TABLE cargo_exceptions (
  exception_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shipment_id bigint NOT NULL REFERENCES shipments(shipment_id),
  exception_type text NOT NULL,
  severity text NOT NULL,
  created_at timestamptz NOT NULL
);
CREATE TABLE carrier_lane_targets (
  carrier_id bigint NOT NULL,
  origin_port text NOT NULL,
  destination_port text NOT NULL,
  target_transit_hours numeric NOT NULL,
  target_exception_rate numeric NOT NULL,
  PRIMARY KEY (carrier_id, origin_port, destination_port)
);

A normal application query might be:

SELECT *
FROM shipments
WHERE shipment_id = 100923;

That is selective. The primary key fits the query shape.

Now compare that with the cargo screener.

The CTE Query That Indexing Cannot Fix Alone

The screener query is not looking up stored values. It builds a temporary analytical relation out of several derived metrics.

The user chooses:

date range: 2026-01-01 through 2026-06-01
cargo type: electronics
minimum shipments per lane: 50
late rate > 8%
exception rate > 3%
cost volatility > 12%
sort by computed risk score

A simplified PostgreSQL query looks like this:

WITH scoped_shipments AS (
  SELECT
    s.shipment_id,
    s.carrier_id,
    s.origin_port,
    s.destination_port,
    s.cargo_type,
    s.departed_at,
    s.delivered_at,
    s.quoted_cost,
    s.actual_cost,
    EXTRACT(EPOCH FROM (s.delivered_at - s.departed_at)) / 3600.0 AS transit_hours,
    (s.actual_cost - s.quoted_cost) / NULLIF(s.quoted_cost, 0) AS cost_delta_ratio
  FROM shipments s
  WHERE s.departed_at >= TIMESTAMPTZ '2026-01-01'
    AND s.departed_at <  TIMESTAMPTZ '2026-06-01'
    AND s.delivered_at IS NOT NULL
    AND s.cargo_type = 'electronics'
),
lane_baselines AS (
  ...
),
shipment_exception_flags AS (
  ...
),
port_dwell AS (
  ...
),
carrier_lane_metrics AS (
  ...
),
scored_lanes AS (
  ...
)
SELECT *
FROM scored_lanes
WHERE shipment_count >= 50
  AND late_rate > 0.08
  AND serious_exception_rate > 0.03
  AND cost_volatility > 0.12
ORDER BY risk_score DESC
LIMIT 100;

This query has a fundamentally different shape from a lookup.

It says:

filter base shipments
derive per-shipment facts
derive per-lane baselines
derive per-carrier lane metrics
derive a risk score
filter and sort by those derived values

The expensive values are not stored in the base tables:

median_lane_hours
p90_lane_hours
dwell_hours
late_rate
serious_exception_rate
cost_volatility
risk_score

That is the core reason indexing alone cannot fix the query.

The obvious objection is:

Why not store those expensive values in a view, materialized view, or separate table?

That can absolutely help PostgreSQL.

If the product needs a fixed metric contract, store it:

carrier-lane risk for last 7 days
carrier-lane risk for last 30 days
carrier-lane risk for last 90 days
late rate by carrier-lane-cargo_type per day
exception count by severity per day

In that case, PostgreSQL can index the derived table:

CREATE TABLE carrier_lane_risk_30d (
  carrier_id bigint NOT NULL,
  origin_port text NOT NULL,
  destination_port text NOT NULL,
  cargo_type text NOT NULL,
  shipment_count integer NOT NULL,
  late_rate numeric NOT NULL,
  serious_exception_rate numeric NOT NULL,
  cost_volatility numeric NOT NULL,
  risk_score numeric NOT NULL,
  computed_at timestamptz NOT NULL,
  PRIMARY KEY (carrier_id, origin_port, destination_port, cargo_type)
);

At that point, indexes such as carrier_lane_risk_30d_score_idx and carrier_lane_risk_30d_filters_idx make sense because risk_score, late_rate, and serious_exception_rate are now stored columns.

Now the query is back in indexing territory:

SELECT *
FROM carrier_lane_risk_30d
WHERE cargo_type = 'electronics'
  AND shipment_count >= 50
  AND late_rate > 0.08
  AND serious_exception_rate > 0.03
  AND cost_volatility > 0.12
ORDER BY risk_score DESC
LIMIT 100;

That is fast because the answer now exists as stored rows. PostgreSQL is no longer building the screener result from raw shipments on every request. It is finding and sorting precomputed facts.

But this only works when the precomputed shape matches the product question.

It breaks down when users can change:

date range
cargo type grouping
exception severity rules
late threshold definition
dwell-time formula
risk-score weights
sort metric

Precomputing carrier_lane_risk_30d does not answer last 17 days. Precomputing daily rollups can help, but the query may still need to combine many daily rows, recompute percentiles, apply new weights, and rank the result. Precomputing every possible (start_date, end_date, cargo_type, severity_rule, score_formula) combination creates state explosion.

So the decision is not:

Materialization versus DuckDB

The decision is:

Is the derived metric contract stable enough to store, or is the product asking for ad hoc analytical exploration?

Stable derived facts belong in PostgreSQL tables or materialized views. Ad hoc analytical exploration is where a columnar read model starts to make sense.

The Indexes That Help

The obvious indexes are not wrong.

Indexes like shipments_cargo_departed_delivered_idx, shipments_lane_carrier_idx, cargo_exceptions_shipment_idx, and shipment_events_shipment_type_time_idx can help PostgreSQL find the base rows, join exceptions to shipments, and join events to shipments.

But they do not make the screener cheap.

Assume:

20 million shipments
80 million shipment events
3 million cargo exceptions
date range selects 2 million delivered electronics shipments
those shipments cover 40,000 carrier-lane groups

The index on (cargo_type, departed_at) WHERE delivered_at IS NOT NULL may reduce shipments from 20 million rows to 2 million rows. For append-heavy time-range data, partitioning by date or a BRIN index may also be a better first tool than piling on composite B-trees.

That is useful.

But the query still has to compute:

percentiles over each lane
exception flags per shipment
dwell time from event pairs
carrier-lane aggregates
cost volatility
risk score
top-N sort over derived rows

The plan smell is not “no index used.”

The plan smell is:

large index or bitmap scan
large hash joins
large hash aggregates
window or ordered-set aggregate work
sort over derived result
high buffer reads

A representative plan shape looks like:

Limit
  -> Sort
       Sort Key: risk_score DESC
       -> CTE Scan on scored_lanes
            Filter:
              shipment_count >= 50
              late_rate > 0.08
              serious_exception_rate > 0.03
              cost_volatility > 0.12
CTE scoped_shipments
  -> Bitmap Heap Scan on shipments
       Recheck Cond:
         departed_at >= '2026-01-01'
         departed_at < '2026-06-01'
       Filter:
         delivered_at IS NOT NULL
         cargo_type = 'electronics'
       Rows: 2000000
CTE lane_baselines
  -> GroupAggregate
       Group Key: origin_port, destination_port
       Ordered-set aggregates:
         percentile_cont(0.50)
         percentile_cont(0.90)
CTE shipment_exception_flags
  -> HashAggregate
       Group Key: shipment_id
       -> Hash Left Join
            -> CTE Scan on scoped_shipments
            -> Bitmap/Index Scan on cargo_exceptions_shipment_idx
CTE port_dwell
  -> HashAggregate
       Group Key: shipment_id
       -> Join shipment_events to shipment_events by shipment_id/location/time
CTE carrier_lane_metrics
  -> HashAggregate
       Group Key: carrier_id, origin_port, destination_port
       Aggregates:
         avg(transit_hours)
         avg(dwell_hours)
         stddev_samp(cost_delta_ratio)
         avg(case transit_hours > p90_lane_hours)

Indexes help the input side of this plan.

They do not remove the middle of the plan.

Why No Index Can Target The Final Filters

The final screener predicates are:

WHERE shipment_count >= 50
  AND late_rate > 0.08
  AND serious_exception_rate > 0.03
  AND cost_volatility > 0.12
ORDER BY risk_score DESC

None of those values exists in a base table.

late_rate depends on:

shipment transit hours
the lane's p90 transit baseline
the user's selected date range
the user's selected cargo type

serious_exception_rate depends on:

joined exception rows
severity classification
grouping by carrier and lane
the scoped shipment set

cost_volatility depends on:

actual cost
quoted cost
standard deviation over the scoped group

risk_score depends on:

all previous derived metrics
weights chosen by the product
clamping rules such as LEAST(...)

A B-tree index can be built over stored expressions in a table. It cannot be built over an arbitrary per-request CTE result whose meaning changes with date range, cargo type, grouping key, percentile baseline, and scoring formula.

That is the concrete indexing ceiling:

An index is a shortcut PostgreSQL prepares ahead of time. It can only point to values that already exist in a table.

For example, an index on shipments(departed_at, cargo_type) contains ordered entries that point to rows in shipments.

That lets PostgreSQL answer:

Find shipment rows where departed_at is in this range
and cargo_type = 'electronics'.

An index on carrier_lane_risk_30d(risk_score) would also work, because risk_score would be a stored column in a real table or materialized view.

That lets PostgreSQL answer:

Find precomputed carrier-lane rows ordered by risk_score.

But in the CTE query, risk_score is not a stored column. It is produced after PostgreSQL has already:

selected the shipment range
joined exception rows
joined event rows
computed dwell time
computed lane baselines
aggregated carrier-lane metrics
applied the score formula

There is nothing for a normal base-table index to point to before that work happens.

The database cannot jump directly to:

carrier-lane rows where risk_score > 0.75

because those carrier-lane rows do not exist yet. They are the output of the query.

That is what “not seekable” means here: PostgreSQL cannot use an index to seek into a result set that is created only after the query has already done the expensive joins and aggregates.

You could materialize one version:

CREATE MATERIALIZED VIEW carrier_lane_risk_30d AS
SELECT ...

That may work for a fixed 30-day dashboard.

It does not solve:

custom date ranges
custom cargo types
new score formulas
ad hoc metric combinations
sorting by any derived metric

For those, the problem is not missing indexes. The problem is that the query is rebuilding an analytical read model at request time.

Why More Indexes Still Do Not Solve The Workload

At this point, the tempting response is:

“Add more indexes.”

That may help one query variant. It does not solve the general workload. A cargo screener usually has dynamic filters:

origin_port = ?
destination_port = ?
cargo_type = ?
carrier_id IN (...)
average dwell time > ?
late rate over custom dates > ?
cost volatility over custom dates > ?
exception rate for severity class > ?
sort by any visible derived metric

If users can combine filters freely, composite indexes explode:

You start considering indexes keyed by cargo_type, departed_at, origin_port, destination_port, carrier_id, and different permutations of those fields. That path does not end.

It gets worse when the product adds a new metric.

Suppose the screener adds:

cold_chain_breach_rate

This metric means:

For refrigerated cargo,
what fraction of shipments had a temperature sensor reading
outside the allowed range for more than 30 minutes?

Now the query needs another source table:

CREATE TABLE temperature_readings (
  reading_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shipment_id bigint NOT NULL REFERENCES shipments(shipment_id),
  recorded_at timestamptz NOT NULL,
  temperature_c numeric NOT NULL
);

The metric is not a simple column on shipments. It depends on:

temperature readings
allowed temperature range for the cargo type
duration outside the allowed range
grouping by carrier and lane
the user's selected date range

So adding this one visible screener column may require:

a join to temperature_readings
a per-shipment breach calculation
a carrier-lane aggregate
a new filter: cold_chain_breach_rate > ?
a new sort option: sort by cold_chain_breach_rate
possibly a new materialized table or rollup
possibly new indexes for the rollup

The index set is now chasing the product surface. Every new derived metric becomes another question:

Do we compute it live?
Do we store it for fixed windows?
Do we index the stored version?
Do we backfill it?
What happens when the formula changes?

That is why dynamic screeners often stop being an indexing problem. They become a read-model design problem.

The Financial Screener Boundary

I hit this same boundary in a financial screener.

The static metrics were easy to think about:

market cap
sector
latest close price
gross margin
free cash flow margin
return on equity
valuation multiples

Those could live in a company metrics snapshot and be filtered like normal stored columns.

The dynamic metrics were different:

purchase count over a selected date range
unique purchasing insiders over a selected date range
average purchase value = average(shares * price) over selected transactions
latest purchase date within the selected window
CEO/CFO/director trade flags from officer-title text matching
multi-insider same-day signal:
  group by company and transaction date,
  then check whether more than one insider traded that day
multi-insider same-week signal:
  group by company and transaction week,
  then check whether more than one insider traded that week

The important split was:

standard windows:
  precompute period-specific columns
custom windows:
  aggregate raw transaction history for the exact request

Before that split, custom-range queries fell back to PostgreSQL and took 8–22 seconds.

After the split, the same class of queries used a company metrics snapshot plus a transaction history snapshot in DuckDB/Parquet and returned in under 600 ms.

The accomplishment was not “adding DuckDB.” It was separating stable metrics from dynamic metrics and giving each one the right read path.

Each index has write cost, storage cost, cache cost, and planner-estimation cost. Worse, many composite indexes only help when the query uses the same leading column order.

PostgreSQL can combine multiple indexes with bitmap plans, but bitmap plans have their own ceiling. They build candidate row-location sets, combine them, then visit table rows. If the query needs a large fraction of the scoped dataset anyway, the planner may prefer a sequential scan because walking the table in physical order can be cheaper than bouncing through indexes.

The failure mode is not:

PostgreSQL is slow.

The failure mode is:

the query no longer has a small search space to narrow

Once most query variants must inspect many candidate rows and derive metrics over them, indexing stops being the main bottleneck. The remaining cost is scan bandwidth, aggregation, tuple materialization, joins, sorting, and repeated derivation.

The Better Shape: Aggregate Once, Then Join

The first improvement is not DuckDB.

The first improvement is query shape.

Do not write the screener as:

for each carrier-lane:
  run subqueries to calculate shipment count, late rate, exception rate, dwell time, cost volatility

That creates correlated execution.

The named principle is decorrelation. Decorrelation means moving work out of per-row dependent subqueries and turning it into relations that can be joined.

The CTE version above is already closer to the right relational shape:

scope shipments once
calculate baselines once
calculate exception flags once
calculate dwell once
aggregate by carrier-lane once
score once

This can help PostgreSQL substantially.

But if the workload is still:

scan millions of shipments
read selected metric columns
join millions of events and exceptions
aggregate into tens of thousands of groups
rank derived results

then a columnar read model becomes a natural next step.

Why PostgreSQL Pays Extra Cost Here

PostgreSQL is not slow because it is badly designed.

It is doing a different job.

In this architecture, PostgreSQL is the source-of-truth row store. Its first responsibility is correctness for operational data. That means it protects:

transactions
constraints
concurrent reads and writes
rollback
row-level visibility
indexes for stored facts

Those features are exactly what you want when the application is creating shipments, updating delivery status, recording exceptions, saving user-facing state, or charging customers.

But the cargo screener is a different shape. It asks PostgreSQL to read a large historical working set and build a temporary analytical result.

While doing that, PostgreSQL still has to work through its OLTP storage model.

The named mechanism is MVCC: multi-version concurrency control. MVCC lets readers and writers use the database at the same time without corrupting each other. A reader sees the version of each row that is valid for its transaction.

That is valuable.

It also means a large analytical query is not just reading clean metric arrays. It may have to inspect row versions, check visibility, fetch heap tuples, follow indexes back to table pages, and assemble full row-shaped data before computing the derived metrics.

For a lookup, that overhead is fine:

find one shipment
check that row is visible
return it

For the screener, the same model becomes expensive:

read millions of shipment rows
check row visibility
fetch related event and exception rows
build joined intermediate data
group by carrier-lane
compute derived metrics
sort the derived result

PostgreSQL can do this. The point is that it is doing analytical work through a storage and execution model built primarily for correct transactional rows.

That is why this is not only a “compute” problem.

It is a physical data-access problem:

PostgreSQL row-store:
  optimized for correct reads and writes of stored rows
Columnar read model:
  optimized for reading selected columns across many rows
  and aggregating them in batches

The difference starts with how the data is physically arranged.

Row Stores And Columnar Engines Optimize Different Shapes

A row store physically groups values by row.

Conceptually:

shipment 1: carrier_id, origin_port, destination_port, cargo_type, departed_at, delivered_at, costs...
shipment 2: carrier_id, origin_port, destination_port, cargo_type, departed_at, delivered_at, costs...
shipment 3: carrier_id, origin_port, destination_port, cargo_type, departed_at, delivered_at, costs...

That is excellent when the query needs many columns for a small number of rows.

The earlier shipment_id = 100923 lookup is that shape: one row, many columns, direct access.

A columnar layout physically groups values by column.

Conceptually:

carrier_id:        row 1, row 2, row 3, ...
origin_port:       row 1, row 2, row 3, ...
destination_port:  row 1, row 2, row 3, ...
departed_at:       row 1, row 2, row 3, ...
actual_cost:       row 1, row 2, row 3, ...

That is excellent when the query needs a few columns for many rows.

The cargo screener does not need every column from every shipment. It needs selected columns across many shipments:

carrier_id
origin_port
destination_port
cargo_type
departed_at
delivered_at
quoted_cost
actual_cost

This is where a columnar engine helps:

read only needed columns
scan them in batches
apply filters in vectors
aggregate without repeatedly fetching whole rows

The solution is a columnar analytical read model.

DuckDB is one executor for that model. It fits here because it is embedded, supports vectorized analytical execution, and can query files directly without a separate database server.

Parquet is one storage format for that model.

It fits here for two reasons.

First, it stores data by column. If the screener needs only 8 columns out of 80, the engine can read those 8 columns instead of reading every full shipment row.

Second, Parquet stores small summaries for groups of rows. Those summaries can help the engine skip entire row groups that cannot match the query.

The query result may be the same.

The physical work is not.

This also adds a new failure domain: snapshot freshness, schema drift, file sizing, partition skew, object-store latency, memory pressure, manifest promotion, and fallback overload. DuckDB/Parquet is not magic compute. It is a different read path with different operational responsibilities.

The DuckDB Transition

Do not replace PostgreSQL. That is the wrong lesson. PostgreSQL should remain the source of truth:

canonical writes
constraints
transactions
identity
application state
fresh operational reads

DuckDB should serve the derived analytical read path:

wide scans
selected columns
custom aggregations
ranking
interactive screener reads

This is CQRS: Command Query Responsibility Segregation. The write model and read model are separated because they optimize different jobs.

The write model answers:

What happened?

The read model answers:

How should we search, filter, and rank it?

For the cargo screener, the read model can be Parquet files:

shipments.parquet
shipment_events.parquet
cargo_exceptions.parquet
carrier_lane_targets.parquet

These files contain a snapshot copied out of PostgreSQL or produced by an export job.

For example:

shipments.parquet:
  shipment_id, carrier_id, origin_port, destination_port, cargo_type,
  departed_at, delivered_at, quoted_cost, actual_cost
shipment_events.parquet:
  shipment_id, event_type, event_time, location_code
cargo_exceptions.parquet:
  shipment_id, exception_type, severity, created_at

The files do not replace the PostgreSQL tables. They are a read-optimized copy for analytical queries.

The Parquet files should contain the facts needed to build many screener metrics, not every possible final metric.

Store stable analytical inputs:

shipment identity and grouping keys:
  shipment_id, carrier_id, origin_port, destination_port, cargo_type
time fields:
  departed_at, delivered_at, event_time
numeric facts:
  quoted_cost, actual_cost, weight_kg, temperature_c
classification fields:
  event_type, exception_type, severity

Then compute flexible screener metrics from those facts:

late_rate
cost_volatility
dwell_hours
cold_chain_breach_rate
risk_score

If a metric is stable and used constantly, it can also be stored as a rollup:

carrier_lane_daily_rollups.parquet

But do not try to store every possible final screener result. With 200+ metrics, custom date ranges, and changing formulas, that becomes the same state-explosion problem as materializing everything in PostgreSQL.

The files also do not need to be one giant file:

shipments/year=2026/month=01/part-000.parquet
shipments/year=2026/month=02/part-000.parquet
shipment_events/year=2026/month=01/part-000.parquet
cargo_exceptions/year=2026/month=01/part-000.parquet

This lets the query skip files outside the requested time range before it even starts reading row groups inside a file.

DuckDB also does not need to load the whole Parquet file into memory first. If shipments.parquet is 50 MB, DuckDB can read the needed columns and row groups as it executes the query. Memory is used for the active scan, joins, aggregates, and sort state, not simply "the whole file must fit in RAM."

It could also be a DuckDB native database file.

That is a reasonable choice when the snapshot is local to one service and mostly queried by DuckDB.

Parquet is the better fit when the snapshot should live as portable files in blob storage such as S3. DuckDB can read Parquet from object storage, and other tools can read the same files later. The read model is not locked to one DuckDB database file.

DuckDB can run the same analytical shape against the derived snapshot:

WITH scoped_shipments AS (
  SELECT
    shipment_id,
    carrier_id,
    origin_port,
    destination_port,
    cargo_type,
    EXTRACT(EPOCH FROM (delivered_at - departed_at)) / 3600.0 AS transit_hours,
    (actual_cost - quoted_cost) / NULLIF(quoted_cost, 0) AS cost_delta_ratio
  FROM read_parquet('shipments.parquet')
  WHERE departed_at >= TIMESTAMPTZ '2026-01-01'
    AND departed_at <  TIMESTAMPTZ '2026-06-01'
    AND delivered_at IS NOT NULL
    AND cargo_type = 'electronics'
),
carrier_lane_metrics AS (
  ...
)
SELECT *
FROM carrier_lane_metrics
WHERE shipment_count >= 50
  AND cost_volatility > 0.12
ORDER BY cost_volatility DESC
LIMIT 100;

The SQL looks similar, but it is not identical. PostgreSQL reads tables such as shipments; DuckDB reads files here through functions such as read_parquet('shipments.parquet').

The important change is not that the SQL text changed a little.

The important change is physical shape:

PostgreSQL row-store path:
  indexed access into base rows, tuple materialization, large joins, large aggregates
DuckDB + Parquet path:
  scan selected columns, aggregate in vectors, join derived facts

That is why the solution is not:

DuckDB is faster.

The solution is:

the read model now matches the query shape

Why Parquet Is Part Of The Design

DuckDB can query many sources, but Parquet is a good fit for this read model.

Parquet is columnar storage.

That gives the engine two important opportunities:

projection pushdown
filter pushdown

Projection pushdown means:

read only the columns the query needs

Filter pushdown means:

use file or row-group metadata to skip irrelevant data where possible

If the shipment files are partitioned or organized by departure date, a custom date range can avoid reading unrelated chunks.

This is not an OLTP index. It is still access-path thinking, but the physical mechanism is different:

B-tree index:
  navigate ordered keys to find rows
Parquet columnar scan:
  read selected columns and skip irrelevant chunks

Both optimize access.

They optimize different access patterns.

Why Not A Materialized View?

A PostgreSQL materialized view can be the right answer.

For fixed windows, it often is:

carrier_lane_risk_7d
carrier_lane_risk_30d
carrier_lane_risk_90d

If the product only needs fixed periods, precompute those metrics inside PostgreSQL or in a derived table.

But custom screener windows are different.

Users can choose:

last 17 days
2026-01-03 to 2026-04-19
previous quarter
electronics cargo only
high-severity exceptions only
new risk-score weights

You cannot precompute every possible combination without exploding state.

A daily rollup table can reduce raw scan volume:

CREATE TABLE carrier_lane_daily_rollups (
  rollup_date date NOT NULL,
  carrier_id bigint NOT NULL,
  origin_port text NOT NULL,
  destination_port text NOT NULL,
  cargo_type text NOT NULL,
  shipment_count integer NOT NULL,
  late_count integer NOT NULL,
  serious_exception_count integer NOT NULL,
  total_dwell_hours numeric NOT NULL,
  total_abs_cost_delta numeric NOT NULL,
  PRIMARY KEY (rollup_date, carrier_id, origin_port, destination_port, cargo_type)
);

That can be a valid design.

But it still keeps analytical scan pressure inside PostgreSQL. If the screener is interactive and high-volume, the source-of-truth database becomes the blast radius for analytical reads.

The DuckDB/Parquet design isolates the workload:

PostgreSQL:
  source of truth
Parquet:
  replayable analytical snapshot
DuckDB:
  embedded OLAP executor over the snapshot

The named principle is workload isolation. Do not make the transactional database pay for every analytical exploration if a derived read model can answer it safely.

The trade-off is operational complexity. A materialized view keeps the system closer to PostgreSQL and simpler to reason about. A DuckDB/Parquet read model adds export jobs, manifests, validation, snapshot promotion, and routing policy.

That extra machinery is not justified for five fixed dashboard metrics.

It becomes justified when the read model is large and dynamic:

200+ screener metrics
millions of shipments
10 years of history
custom date ranges
filters and sorts over many derived metrics

At that point, PostgreSQL is no longer just answering a few reports. It is being asked to rebuild a large analytical workspace on demand. Moving that workspace into a columnar read model removes meaningful load from the source of truth and makes the query shape viable.

The Hard Part Is Correctness, Not Speed

Once DuckDB enters the design, the hard question changes.

It is no longer only:

How fast is the query?

It becomes:

Can the derived result be trusted?

A derived read model has failure modes:

stale snapshot
partial export
schema mismatch
missing Parquet file
small-file explosion
partition skew
object-store latency
memory pressure during large joins or sorts
result drift from PostgreSQL semantics
non-atomic snapshot promotion
fallback overload

That means the export pipeline needs a manifest.

A manifest is a small metadata file that says:

snapshot_id
schema_version
source database watermark
files included
row counts
created_at
validation status

Readers should not discover Parquet files by scanning a directory and hoping the files belong together.

They should read through the promoted manifest.

A safe export flow looks like:

1. export data into a versioned staging path
2. validate row counts, required columns, and date ranges
3. run a semantic smoke test against PostgreSQL
4. write a manifest for the snapshot
5. promote the manifest pointer
6. make readers use only the promoted manifest

The invariant:

A reader can see the old valid snapshot or the new valid snapshot,
but never a half-written snapshot.

There is also a semantic invariant:

For the same snapshot watermark and filter set,
the DuckDB path and PostgreSQL reference path must agree within the domain's tolerance.

For counts and IDs, tolerance should usually be exact. For decimal aggregates, the tolerance should be explicit and caused only by known numeric representation differences. Silent drift is poison because it makes the read model untrustworthy without making the system obviously unavailable.

Fallback Is Not Free

The natural fallback is:

If DuckDB cannot serve the query, run the PostgreSQL path.

That preserves correctness.

It can also destroy the source database.

If DuckDB fails and every custom screener request falls back to the old expensive PostgreSQL path, the system has converted one read-model outage into source-of-truth overload.

Fallback needs back-pressure:

max_concurrent_duckdb_queries = N
max_concurrent_postgres_fallback_queries = M

When the limits are hit, the system should queue for a bounded time, return degraded results, or reject expensive custom-range queries temporarily.

The named principle is back-pressure. When a downstream executor is saturated, callers must wait, shed, or degrade. They must not amplify load.

The router should also emit structured signals:

duckdb_query_count
duckdb_query_duration
fallback_count_by_reason
snapshot_age_seconds
snapshot_schema_version
postgres_fallback_concurrency
result_validation_failures

If the system cannot explain why it used DuckDB or PostgreSQL for a request, the routing policy is not production-grade yet.

How To Decide What To Do

When indexing cannot make a query fast, do not jump directly to DuckDB.

Use this sequence.

First, inspect the plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Look for:

sequential scans over large tables
index scans with huge loop counts
nested loops over large outer relations
sorts over large derived result sets
hash aggregates over many rows
ordered-set aggregates over large groups
row estimates far from actual rows
high shared buffer reads

Second, ask whether the query is selective.

If the query needs a tiny fraction of rows, fix the index.

If the query needs a large fraction of rows, an index may not be the right access path.

Third, identify which predicates are over stored values and which predicates are over derived values.

Stored-value predicates can often use indexes:

departed_at >= ?
cargo_type = ?
shipment_id = ?
carrier_id = ?

Derived-value predicates cannot be fixed by base-table indexes alone:

late_rate > ?
cost_volatility > ?
serious_exception_rate > ?
risk_score > ?

Fourth, remove accidental correlation.

Rewrite:

for each outer row, run an aggregate

into:

aggregate once, then join

Fifth, precompute stable facts.

Fixed windows, daily rollups, and common dashboard metrics should not be recalculated from raw facts on every request.

Sixth, split the read model when the workload is truly analytical.

Use a derived OLAP path when the query is:

wide
scan-heavy
column-selective
aggregation-heavy
high-concurrency
not source-of-truth mutation

Seventh, define the consistency contract.

Write down:

maximum allowed snapshot age
snapshot promotion invariant
schema versioning rule
PostgreSQL reference-query sample
fallback concurrency limit

Without those, the system has a faster query engine but not a production read path.

The Mental Model

• Indexing solves selective access.
• Columnar execution solves scan-heavy analytical access.
• Materialization solves repeated derivation.
• Workload isolation protects the system of record.
• Back-pressure prevents fallback from becoming an outage amplifier.
• Those are different tools.
• Use the smallest one that matches the failure mode.
• If the query is slow because PostgreSQL cannot find rows, design the right index.
• If the query is slow because every request rebuilds analytical state, design the right read model.

That is the transition:

indexing is access-path design
DuckDB is read-model design

Do not reach for DuckDB because indexing failed once.

Reach for a derived columnar read model when the workload itself has stopped being an indexed lookup and has become an analytical scan.