# BRIN Maintenance: Correct Answers, Different Pruning Work

Hesela native controlled experiment, 18 November 2026.
Evidence: results.json. Replay: replay.py. Offline checks: test_replay.py.
Code, synthetic fixture and observations: CC0-1.0. PostgreSQL and cited sources
retain their own licenses; no upstream paper/code is redistributed here.

## Question and Method

Can deleting an extreme value leave a valid but unnecessarily broad BRIN summary?
Does summarizing an already summarized range tighten it?

Three fresh private PostgreSQL clusters each contain one 8192-byte heap page:
integers 1 through 100 and one extra value 1000000. The only index is BRIN with
int4_minmax_ops, pages_per_range=1, autosummarize off; autovacuum and parallel
query are disabled. Seven stages include a positive starting state, deletion,
summarize-existing, ordinary VACUUM, desummarization, rebuild and summarize-again.

Each stage compares exact results for v=1000000 and v=50 under forced BRIN bitmap
and sequential plans. The complete visible table is checked against a separately
constructed Python list. EXPLAIN ANALYZE records heap blocks and rejected tuples;
TIMING and SUMMARY are off. pageinspect follows the range-map TID to distinguish
the active summary from raw page contents. Raw plans, values, bounds, binary
digests, runtime versions and the executed script hash are in results.json.

## Observed Result

Tested server: PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1), not PostgreSQL 18.
All three repetitions agree, covering 42 stage/query observations.

After DELETE, summarize-existing returns 0 and ordinary VACUUM retains bounds
1..1000000. The absent-value query returns no rows correctly, but its bitmap
heap scan visits one lossy heap block and rejects 100 visible tuples.
Desummarizing also retains that block as a candidate. After rebuilding,
bounds are 1..100; the same absent-value query visits zero heap blocks and
performs zero tuple rechecks. The retained-value query still returns exactly 50.
The final summarize-again call returns 0.

A diagnostic pitfall appeared during author validation: scanning every regular
index page still exposed the former tuple after desummarization, while the
range-map entry was invalid, (4294967295,0). Those raw bytes did not constitute
an active summary. The final harness follows the range map. The initial
all-page observation failed its oracle and was not published as accepted evidence.

## Reproduction

Requires an unprivileged OS account, Python 3.12, PostgreSQL 16 server/client
binaries and the matching pageinspect extension. These version pins identify
the tested environment, not a recommendation to install an older security release.

    python3 replay.py --bindir /path/to/postgresql/16/bin --psql /path/to/psql --output replay.json
    python3 -B -m unittest discover -s . -p test_replay.py
    python3 -B -O -m unittest discover -s . -p test_replay.py

The replay does not accept a database URL. It creates private temporary clusters,
disables TCP listening, restricts the Unix socket directory and shuts each server
down in a finally block. Existing output paths are refused. The offline tests
check published results.json and reject deliberately damaged observations;
they are not a substitute for native replay.

## Interpretation and Limits

This is an executor/maintenance demonstration, not a speed benchmark, physical
storage read trace, corruption test, production incident or statistical sample.
The tiny fixture requires forced plans; it does not establish the optimizer's
preferred plan. There are no concurrent writers or old retained snapshots.
VACUUM precedes rebuilding. Do not generalize this schedule to every MVCC horizon,
operator class, version or large-table maintenance policy. Repeated deterministic
cases do not provide confidence intervals or an error rate. Author validation
is not independent scientific review.

Moerkotte's VLDB 1998 paper classifies physical buckets as qualifying, excluded
or requiring inspection, and discusses the bucket-size/summary-overhead tradeoff.
That motivates separating answer correctness from work avoided. This experiment
does not implement SMA aggregation or reproduce its historical benchmark.

## Primary Sources

- PostgreSQL 16 index maintenance functions:
  https://www.postgresql.org/docs/16/functions-admin.html#FUNCTIONS-ADMIN-INDEX
- PostgreSQL 16 pageinspect BRIN functions:
  https://www.postgresql.org/docs/16/pageinspect.html#PAGEINSPECT-BRIN-FUNCS
- Exact upstream REL_16_15 BRIN design, Range Map / Vacuuming:
  https://github.com/postgres/postgres/blob/REL_16_15/src/backend/access/brin/README
- Exact upstream REL_16_15 brinRevmapDesummarizeRange:
  https://github.com/postgres/postgres/blob/REL_16_15/src/backend/access/brin/brin_revmap.c
- Guido Moerkotte, Small Materialized Aggregates: A Light Weight Index Structure
  for Data Warehousing, VLDB 1998, pp. 476-487, sections 2-4:
  https://www.vldb.org/conf/1998/p476.pdf

Sources accessed 2026-11-18. The complete paper was read through the web PDF view.
No DOI was established. Documentation synthesizes PostgreSQL 16, while the
existing BRIN definition separately describes PostgreSQL 18 documentation.
