Re: Design proposal: grain, exact per-time-bucket aggregates from a summarizing index

Hi,

Attached is v1 of the amnotifydelete callback, patch 1 from the plan above.
Besides DELETE and UPDATE it covers MERGE, ON CONFLICT, logical replication
apply and REPACK (CONCURRENTLY). The limits are documented in indexam.sgml,
and dummy_index_am has tests.

--
BR,
Jan Bjørge (JB)
Norway

On Tue, Oct 20, 2026 at 12:54 PM Jan Bjørge Løvland <janbjorge(at)gmail(dot)com>
wrote:

> Hi!
>
> Thanks for taking the time. I used agents to help write this: I'm highly
> dyslexic, and without help I feel that my proposal doesn't come across.
>
> I'd like design feedback on a new index type that keeps exact count, sum,
> min and max per time bucket, and on a new index access method callback it
> needs. I have working code for the callback, with tests and benchmarks, but
> I haven't posted it. I'd like some blessing on the callback before going
> further. The index type itself isn't written yet.
>
> Problem
>
> SELECT date_bin('1 day', created_at, '2000-01-01 00:00+00'),
> count(*), sum(amount_cents)
> FROM orders
> WHERE status = 'paid' AND created_at >= now() - interval '365 days'
> GROUP BY 1 ORDER BY 1;
>
> In our runs, the time of this query grew with the number of rows in the
> window, with no index, a btree, a covering btree and BRIN alike.
> Materialized views go stale. Triggers that keep a rollup table are tricky
> to get right: the incremental view maintenance patches [1], built on
> triggers, lock the view on DELETE and UPDATE and raise errors under
> REPEATABLE READ. TimescaleDB works, but for one rollup on one table it
> feels like shooting sparrows with a cannon, as we say in Norway.
>
> Proposal
>
> CREATE INDEX ON orders USING grain (created_at) INCLUDE (amount_cents)
> WITH (buckets = '1 minute, 1 hour, 1 day') WHERE status = 'paid';
>
> The first version has a timestamptz time key and integer INCLUDE columns.
> Bucket widths are fixed, finest first, and each width divides the next.
> Partial indexes work. The index is never scanned. Reads go through
> pg_grain_buckets(index, from, to) first, later through a planner path for
> GROUP BY date_bin(...).
>
> The index keeps three things:
> - W, a timestamp in the index's metapage: buckets before W are summarized,
> rows after W are read from the table. W only moves forward.
> - A hidden summary table with one row per summarized bucket at each level,
> holding count, sum, min and max as plain values.
> - A hidden marker table listing buckets that changed after they were
> summarized.
> Both tables are ordinary tables owned by the index, the way a TOAST table
> is owned by its table, so they are transactional, crash safe and replicated
> like any other table.
>
> A write whose row's time key is before W inserts a marker in the same
> transaction. Rows stamped "now" cost one comparison. Inserts go through
> aminsert, and deletes and the old version of an update go through the new
> callback.
>
> The sealer advances W, then calls WaitForLockers(heap, ShareLock) as
> CREATE INDEX CONCURRENTLY does. Then, under one snapshot, it recomputes new
> and marked buckets, rolls them up into the coarser levels, and deletes the
> markers it handled.
>
> A reader uses a bucket's summary row when the row is visible, no marker is
> visible, and the bucket is wholly inside the window. Otherwise it
> range-scans the bucket's rows.
>
> Why we think it is exact: a write that a summary row misses comes from a
> transaction the sealer didn't wait for. The sealer waits for everyone
> already holding a write lock when it moves W, so that writer locked the
> table after W moved. It therefore saw the new W and wrote a marker in the
> same transaction. A reader sees both the write and the marker, or neither,
> and a marker makes it scan the bucket instead. This relies only on locks
> and MVCC visibility, not on the visibility map, VACUUM or clocks.
>
> The callback
> void amnotifydelete(Relation index, Datum *values, bool *isnull,
> ItemPointer heap_tid, Relation heap,
> IndexInfo *indexInfo);
>
> It is NULL for every existing index type. The executor computes the old
> row version's index values and calls it from ExecDeleteEpilogue,
> ExecUpdateEpilogue (not on TU_None) and the logical replication apply
> paths. The relcache tracks whether any index on a table opted in, and the
> executor does nothing extra when none did.
>
> The closest earlier proposals are amtargetdelete() [2], which was driven
> by VACUUM, and amupdate() [3]. The executor passing old values to the index
> follows Robert's suggestion in the WARM thread [4].
>
> Numbers
>
> Callback, on a 1 million row table, master against the patch, both -O2
> without asserts. Medians, in ms:
>
> master patch
> one-row DELETE + INSERT 0.106 0.105
> one-row UPDATE of an indexed column 0.072 0.070
> UPDATE of every row, rolled back 4808.265 4572.124
> DELETE of every row, fresh table 463.349 469.150
>
> With an index that asks for the callback (dummy_index_am), the DELETE of
> every row took 555.168 ms on the patch. The DELETE rows are rounds 4 to 7;
> master's first three rounds took 845.669, 1015.431 and 880.740 ms.
>
> Read path, simulated in SQL since the index type isn't written yet: 100
> million rows, warm cache, one client, against the fastest plain GROUP BY
> (no index, btree, covering btree, BRIN). Answers were identical, including
> after late writes.
>
> daily, 30 days: 1196.245 ms -> 0.375 ms
> total, 365 days: 909.363 ms -> 0.590 ms
>
> Open questions
>
> 1. Is an index the right object, or does this want its own DDL?
> 2. A delete callback, or a broader amupdate()?
> 3. Hidden heaps: which namespace, and how should pg_dump and pg_upgrade
> handle them?
> 4. Is a CREATE INDEX CONCURRENTLY style wait acceptable for a job that
> runs every minute?
> 5. Generic WAL for the metapage, or its own resource manager?
>
> Plan: patch 1 the callback, patch 2 the index type with docs and tests,
> patch 3 the planner path. Target is master.
>
> [1] https://commitfest.postgresql.org/patch/4337/
> [2]
> https://www.postgresql.org/message-id/flat/11d32604-c0b9-7c05-edee-76582374404c%40postgrespro.ru
> [3]
> https://www.postgresql.org/message-id/CAH2-WzmL%2Bp-F1JOb7b6gJRhV-qiO_%3DaNETG43%2BV%2B7EPKxSKP5g%40mail.gmail.com
> [4]
> https://www.postgresql.org/message-id/CA%2BTgmob4RAhFm_is%3DszyvXWGvtV%3D5-9gbr9Ni391NFWpce6tOg%40mail.gmail.com
>
> --
> BR,
> Jan Bjørge (JB)
> Norway
>
>