1.0: gap filling, LOCF and approximate aggregates: recipes, compatibility, or build

Part of the 1.0 time-series set the owner chose on 2026-10-12. All seven are listed on #1283.

What is missing

Three query features time-series users expect. None of them depends on the storage layer:

Gap filling: emit a row for each time bucket that has no data.
Last observation carried forward (LOCF) and interpolation: fill those empty buckets from their neighbours.
Approximate aggregates: percentiles (t-digest or similar) and distinct counts (HyperLogLog), which are far cheaper than exact ones on large tables.

Measure and decide first

Can core PostgreSQL already express these well enough? generate_series joined to the buckets gives gap filling, and window functions give LOCF. If the SQL is acceptable and fast on columnar, documented recipes may be the right answer.
Do existing extensions work with columnar tables? Two examples are postgresql-hll and tdigest. If they work, document and test them rather than reimplement.
What would columnar add? For example, a distinct-count sketch kept per chunk could answer approximate distinct counts from metadata. Only that kind of gain justifies building it here.

Done when

For each of the three: either documented SQL recipes with arms that check them against a reference answer, or a tested compatibility statement for an extension, or a built feature with a measured gain.

🤖 Generated with Claude Code

@OffgridwithJD

Plan for #1296

@OffgridwithJD, please attack this before I write arms. The issue asks to measure and decide before building, so phase 0 decides the shape of everything after it.

Goal

For each of the three (gap filling, LOCF/interpolation, approximate aggregates), deliver one of:

a tested SQL recipe,
a tested compatibility statement for an existing extension,
or a built feature with a measured gain.

Not in scope

Our own sketch types, unless phase 0 shows a gain that only metadata can give.
A time_bucket_gapfill-style function, unless the recipe is measured unacceptable.

Phases

0. Measure, then decide. On PG17, with 10M rows in a columnar table and its heap mirror:

(a) The gap-fill recipe, generate_series of buckets LEFT JOIN the bucketed aggregate: time, and whether the aggregate side still runs as the grouped vectorized node.
(b) LOCF with window functions (the count-based group trick, then first_value), and linear interpolation with lag/lead over the filled series.
(c) count(DISTINCT x) against hll_cardinality(hll_add_agg(hll_hash_bigint(x))), and percentile_cont against tdigest_percentile, on the PG18 packages available here (postgresql-18-hll, postgresql-18-tdigest).
(d) What a per-chunk sketch in metadata would save: the exact count(distinct)'s cost, which is the most a metadata answer could save.
Decision rule: a feature gets built only if (d) shows a gain no recipe or extension reaches. Otherwise it's a recipe or a compatibility statement.
Verified by: the numbers, posted here.

1. Recipes, red first. test/timeseries_recipes.sh and an independent pytest twin. Each recipe is checked against a reference computed a different way: a plpgsql loop over the buckets for gap fill and LOCF, and exact arithmetic for interpolation. Fixtures include:

an empty first bucket (LOCF has nothing to carry: NULL, not an error);
an empty last bucket;
a run of empty buckets;
a NULL value inside a filled bucket, which must not be carried as if it were missing.

2. Extension compatibility. test/approx_aggregates.sh and twin, using MISSING_DEPENDENCY when the package is absent, as partman_columnar does. CI installs the PGDG packages per major, as it does for pg_partman. The arms:

hll and tdigest aggregates over a columnar table equal the same aggregates over its heap mirror;
the hll error stays inside its documented bound against the exact count;
the plan's scan is columnar, so the arm exercises our reader.

3. Docs. A how-to section per recipe, and a compatibility note naming the tested versions.

Risks and open questions

Does the vectorized grouped node survive the LEFT JOIN? If the planner flattens the join so the aggregate loses its node, the recipe is slower than it needs to be. Phase 0(a) checks the plan.
Who owns the CI change that installs hll and tdigest? It's small. I'll do it in phase 2 unless you'd rather, since you set up partman's.
Assumed: both extensions use only standard aggregate paths, so they need nothing from us. Phase 2 proves it rather than assuming.

@OffgridwithJD

Phase 0 results: recipes and existing extensions cover all three. No build for 1.0.

Setup. PG18 18.4, on the system binaries, with pgColumnar from main (3deb6af). postgresql-18-hll 2.19 and postgresql-18-tdigest 1.4.3 are loaded from a private prefix through extension_control_path and dynamic_library_path, so nothing was installed system-wide. The fixture has 8,750,000 rows over 30 days, with every 7th hour left empty, in a columnar table and its heap mirror. max_parallel_workers_per_gather = 0; times are single runs and indicative only.

	columnar	heap	answer
gap fill: `generate_series` LEFT JOIN the hourly `avg`, group vectorization off	1.26 s	1.08 s	720 buckets, 630 with data
same, `enable_group_vectorization = on`	0.69 s		the same
LOCF over the filled series (`count() OVER` groups, then `first_value`)	0.69 s		720 of 720 carried
`count(DISTINCT dev)`	0.78 s	0.79 s	500
`hll_cardinality(hll_add_agg(hll_hash_integer(dev)))`	0.27 s	0.29 s	515 (+3%)
`percentile_cont(0.99)`	0.94 s	0.93 s	98.901
`tdigest_percentile(v, 100, 0.99)`	0.67 s		98.92

What the plans show

The vectorized grouped node survives the LEFT JOIN. Hash Left Join → Hash → Subquery Scan → Custom Scan (PgColumnarScan) with Columnar Vectorized Group Keys: 1. That answers my risk (a).
hll and tdigest read through our scan. Both plans are Aggregate → Custom Scan (PgColumnarScan) with Projected Columns: 1 of 3. They need nothing from us.

(d) What would a per-chunk sketch in metadata add? It's the one thing here a scan can't match. hll is a full scan (0.27 s here; linear, so about 30 s at a billion rows), and a metadata answer would be near-constant. But deletes make it unsound. HyperLogLog can't subtract, so a sketch over a chunk with deleted rows overcounts until the chunk is rewritten. That's a design question with a write-path cost, not a 1.0 item.

Decision for 1.0

Gap fill, LOCF and linear interpolation: documented recipes, with arms that check them against an independently computed reference. This is phase 1 as planned.
Approximate distinct counts and percentiles: a tested compatibility statement for hll and tdigest. This is phase 2 as planned. CI installs the PGDG packages per major, and MISSING_DEPENDENCY applies where they're absent.
Per-chunk distinct sketches: filed as a post-1.0 issue, with these numbers and the delete problem stated.

@OffgridwithJD, unless you see a reason to build (d) now, I'll start phase 1's red arms.

@OffgridwithJD

Phase 1 is up as #1311: the three recipes, read from how-to.md by both twins and held to independent references. There are six mutations on 15–19, and they found the date_trunc half-hour-zone trap described there.

A question for phase 2, @OffgridwithJD. The hll and tdigest arms need those extensions on every major we run. CI gets them the way it gets pg_partman, from the PGDG packages. Locally, though, /usr/local/pg15..pg19 here have no pg_partman either, and you reported partman_columnar on 15–19. How did you provide pg_partman to the source-built majors: built from source with PGXS into each prefix, or another way? I'd rather do the same thing than invent a second route.

For phase 0, I loaded the PG18 packages from a private prefix through extension_control_path and dynamic_library_path. That's PG18+ only, so it doesn't reach 15–17.

test: the time-series recipes in how-to.md answer what they claim (#1296

docs: recipes to fill gaps, carry values forward and interpolate (#1296)

test: the pytest twin of timeseries_recipes, and the suite registered (…

test: ledger rows for timeseries_recipes from five majors and six mut…

@OffgridwithJD

Phase 2 is up. For the record, and @OffgridwithJD in case the shared majors matter to you: I built hll 2.19 and tdigest 1.4.3 from their tagged sources into /usr/local/pg15..pg19 here, with PGXS. The change is additive, but it is a change to the shared build tree. hll 2.19 doesn't build against PG19 (FuncnameGetCandidates signature), so the suite records that as named skips on 19, and the docs say so.

Merge pull request #1315 from commandprompt/test/1296-approx-aggregates

test: the time-series recipes in how-to.md answer what they claim (#1296

docs: recipes to fill gaps, carry values forward and interpolate (#1296)

test: the pytest twin of timeseries_recipes, and the suite registered (…

test: ledger rows for timeseries_recipes from five majors and eight m…

test: hll and tdigest over columnar tables, the compatibility stateme…

docs: approximate distinct counts and percentiles with hll and tdigest (

test: the time-series recipes in how-to.md answer what they claim (#1296

docs: recipes to fill gaps, carry values forward and interpolate (#1296)

test: the pytest twin of timeseries_recipes, and the suite registered (…

test: ledger rows for timeseries_recipes from five majors and eight m…

test: the time-series recipes in how-to.md answer what they claim (#1296

docs: recipes to fill gaps, carry values forward and interpolate (#1296)

test: the pytest twin of timeseries_recipes, and the suite registered (…

test: ledger rows for timeseries_recipes from five majors and eight m…

test: the time-series recipes in how-to.md answer what they claim (#1296

docs: recipes to fill gaps, carry values forward and interpolate (#1296)

test: the pytest twin of timeseries_recipes, and the suite registered (…

test: ledger rows for timeseries_recipes from five majors and eight m…

Merge pull request #1311 from commandprompt/test/1296-timeseries-recipes

Done, by the issue's own done-when: each of the three is covered.

Gap filling, LOCF and linear interpolation: documented SQL recipes in docs/how-to.md, held to independent references by timeseries_recipes.sh and test_timeseries_recipes.py (docs, test: gap-fill, LOCF and interpolation recipes, held to a reference (#1296 phase 1) #1311). The recipes use date_bin with an explicit origin, because date_trunc emptied every bucket in a half-hour-offset session.
Approximate distinct counts and percentiles: a tested compatibility statement for hll 2.19 (PostgreSQL 15–18; it doesn't build against 19) and tdigest 1.4.3 (15–19), in approx_aggregates.sh and test_approx_aggregates.py (test, docs: hll and tdigest over columnar tables, the compatibility statement (#1296 phase 2) #1315). Over columnar, each gives exactly what it gives over heap, and CI installs both.
What columnar would add (per-chunk sketches): measured in phase 0 and deferred as Post-1.0: approximate distinct counts from per-chunk sketches in metadata #1309, because deletes make HLL sketches unsound without a rebuild.

Navigation Menu

Repository navigation

1.0: gap filling, LOCF and approximate aggregates: recipes, compatibility, or build #1296

What is missing

Measure and decide first

Done when

Plan for #1296

Goal

Not in scope

Phases

Risks and open questions

Phase 0 results: recipes and existing extensions cover all three. No build for 1.0.

Decision for 1.0

6 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

1.0: gap filling, LOCF and approximate aggregates: recipes, compatibility, or build #1296

Description

What is missing

Measure and decide first

Done when

Activity

jdatcmd commented on Oct 12, 2026

Plan for #1296

Goal

Not in scope

Phases

Risks and open questions

jdatcmd commented on Oct 12, 2026

Phase 0 results: recipes and existing extensions cover all three. No build for 1.0.

Decision for 1.0

jdatcmd commented on Oct 12, 2026

jdatcmd commented on Oct 13, 2026

6 remaining items

jdatcmd commented on Oct 13, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions