Build a Database Index Extension That Keeps Tests Green and Survives Crashes

## Restart the Server After Reinstalling and Read the Storage Spec First You contribute to a database extension that implements an index access method backed by layered on-disk storage inside the database's own relation files. The work sits close to durability, recovery, and concurrency, so correctness outranks speed of delivery in every judgment call. Operating assumptions to hold at all times: - The extension is loaded at server start. Adding it to the server's preload configuration and restarting is required before the extension can be created, and a further restart is required after reinstalling the binary during development. A stale loaded binary produces misleading test results; when behavior contradicts the source you just built, suspect a missing restart first. - Install the extension's objects into a dedicated schema rather than the default public namespace whenever the project permits it, to keep the namespace clean. - Prefer the smallest change that fully solves the problem. Do not refactor adjacent code that the task did not require. - Design specifications in the repository are binding. Read the storage specification before touching the write, read, or spill path, and update the specification in the same change when the design itself moves. ## Investigate Every Test Failure Instead of Calling It Unrelated The main branch stays continuously green. Every test failure matters. - Never dismiss a failure as pre-existing or unrelated. In practice it almost never is. - If you genuinely believe a failure is unrelated to your change, verify it: check out the main branch and run the same test there. - Reproducing on main does not excuse the failure. It still needs to be investigated and fixed, not filed away or annotated as known. - Never mark a test as skipped, relax an assertion, or edit an expected-output file merely to make a red run turn green. Change an expectation only after you can state precisely why the new output is correct. - Report failures honestly and early, including failures you caused. Concealing a red run is worse than the bug itself. ## Run Regression Tests and Read the Diff File Before Every Commit Complete every step below before committing a change. Do not reorder or omit steps because a change "looks trivial". 1. Compile the extension. 2. Run the declarative regression suite against an installed build. 3. Inspect the regression difference file produced by the run, not just the pass or fail summary. A suite can report success while masking a difference you introduced. 4. If you modified any user-visible message, update the corresponding expected-output files to match, and state in the commit why the message changed. 5. Run the formatting check. 6. Re-run the regression suite after updating any expectation file, so the final recorded run is the one that reflects the committed tree. Beyond the routine suite, run the concurrency, crash recovery, multi-backend, and stress tiers whenever a change touches locking, the write path, recovery, or segment lifecycle. Say plainly which tiers you ran and which you did not. ## Put Each Change in the Lowest Code Layer That Fixes It Source is organized in layers, and every change belongs at the lowest layer that fully solves it. Do not leak storage details upward into the database interface layer, and do not reach downward past an abstraction to shortcut a fix. - Layer one, database interface: the access method handler, index build, scan, and vacuum entry points; custom data types; planner hooks and cost estimation. - Layer two, index coordination: ranking and scoring, top-k optimization, index state lifecycle, registry, metapage handling, and the abstraction over posting sources. - Layer three, storage: the level-zero write buffer, immutable segments, merge, and compression. - Cross-cutting: debug and dump utilities, and module initialization. When a change spans layers, describe the boundary crossing explicitly in the change description. A fix that quietly widens an interface is a design change and must be reviewed as one. ## Understand the Write Buffer and Immutable Segments Before Changing Storage The index follows a log-structured, layered storage approach. Understand the model before altering any part of it. - Level zero is a write buffer: an append-oriented chain of pages stored inside the index relation itself and anchored at a metapage. Writes append records that carry the row identifier, a length, and a packed payload to the tail page. All mutations happen under standard buffer locks and are write-ahead logged. - Segments are immutable block-structured on-disk structures that carry skip entries so a scan can seek to a block without walking every posting. - Spill from the write buffer to a new segment is threshold-driven, and segments compact by level once a level accumulates enough of them. - Top-k queries use block-level upper bounds so blocks that cannot contribute to the result are skipped entirely, with early termination once the remaining bound cannot beat the current result set. An in-memory cache may serve query reads for speed, but the durable on-disk chain remains the single source of truth, and read-only standbys always read the durable chain rather than a cache. ## Log Every Page Change Through the Standard Write-Ahead Log, Not a Custom One Log every page mutation through the database's generic write-ahead logging facility instead of registering a custom resource manager for the extension. Why this constraint exists: with generic logging, the stock database replay machinery reconstructs every page during crash recovery and on a streaming standby, including the write buffer chain pages, the segment pages, and the metapage. That is precisely what allows single-page redo helpers, and any other replay context that cannot load the extension's shared library, to work correctly. Consequences to respect: - Introducing a custom resource manager is a major design decision, not an implementation detail. It breaks every replay context that cannot load the extension. Never introduce one implicitly as part of an unrelated change; raise it as a separate proposal with its own review. - Any new on-disk structure must be reconstructible by stock replay from the logged records alone. If a structure can only be rebuilt by extension code, the design is wrong. - Never bypass the buffer manager or write pages outside a logged mutation, even for a structure you consider disposable. ## Hold Pages Freed by a Merge Until Standby Queries Stop Reading Them A merge must not return the displaced source pages to the free space map immediately. Doing so is safe on the primary but unsafe for in-flight read queries on a hot standby, because without a custom resource manager there is no mechanism to resolve recovery conflicts during replay: the standby would reuse pages a running query is still reading. The required pattern: - Park displaced pages in a logged tombstone chain anchored off the metapage, stamped with the merge's transaction identifier. - Return parked pages to the free space map only once a later vacuum, or the next merge, observes that the stamp precedes the oldest non-removable transaction horizon. That horizon is the standby-safe reclaim boundary. - Require feedback from hot standbys serving queries to be enabled in the deployment. Their oldest snapshot then holds the primary's horizon back until they finish reading the parked pages. - Expose a diagnostic function that reports how many pages are currently parked, so operators can observe backlog growth instead of guessing. Generalize the principle: when a component frees a shared resource that a remote or delayed reader may still hold, defer the release behind an observable horizon and give operators a way to measure the deferred set. ## Sort by Ranking Score Instead of Filtering on It Ranking scores exist for ordering, not for filtering. Their numeric values are opaque and carry no meaningful threshold, so comparing a score against a constant is an anti-pattern whenever it is used as a convenience filter in tests, examples, or documentation. Rules: - Do not write a filter condition that compares a ranking score against a number in order to select "matching" rows. - A filter condition containing the ranking operator triggers standalone scoring through a sequential scan rather than an index scan. Index scans require an ordering clause. - The correct pattern is to order by the ranking expression and apply a row limit. - To count matching documents, wrap an ordered subquery and count its rows, rather than filtering on the score. - Exception: a test whose explicit target is the standalone scoring path, for example one that compares index scan results against standalone results, may use the operator in a filter condition. Such tests must state that intent in a comment so a later reader does not copy the pattern by mistake. The general lesson: when an API has one intended shape, make the wrong shape visibly wrong in every example the team ships, and carve out exceptions only with documented intent. ## Document Every Setting in One Table With a Safe Default Every runtime tunable is documented in one table with its name, description, and default. An undocumented tunable is treated as a defect. - Choose defaults that are safe and behavior-neutral for an ordinary deployment. Use zero to mean "disabled" for threshold-style settings and "unlimited" for budget-style settings, and say so in the description. - Settings that inject faults or force failures exist for testing only, must be restricted to privileged users, and must be labeled as testing-only in their description. - Logging switches for scoring detail, optimization statistics, and cache outcomes default to off; they are diagnostic aids, not normal operation. - Memory budgets are tiered rather than a single hard wall: a per-index soft cap degrades that index to the durable fallback path, a global soft cap evicts the largest consumer other than the caller, and a global hard cap refuses new allocations outright. Each tier must be observable in the logs. - Index-level options, such as the text analysis configuration and the ranking model's saturation and length-normalization parameters, are documented with the same rigor, and required options are marked required rather than given a silent default. ## Mark Dead Documents and Write Down the Stale Statistics Until the Next Merge Vacuum marks dead documents in a per-segment alive bitset, one bit per document, instead of rebuilding the segment. The cost is proportional to the number of dead documents rather than to the whole corpus. Dead documents are filtered during scoring and physically removed at the next segment merge. The known trade-off must stay documented rather than hidden: marking documents dead does not update the segment's total document count, total length statistics, or per-term document frequency. Ranking therefore uses slightly stale corpus statistics until the next merge or compaction corrects them. For small numbers of deletes the effect is negligible; a mass deletion can visibly affect ranking quality until the next merge. Treat this as a template. When an optimization trades accuracy or freshness for cost, write the trade-off down where the maintainer will find it, state the conditions under which it becomes significant, and name the event that resolves it. An undocumented approximation becomes an unexplained bug report later. ## Keep Separate Test Levels and Built-In Diagnostic Functions Maintain distinct test tiers and know what each one protects: - Declarative regression tests for core functionality, scoring correctness, storage and segments, query optimization paths, table variants such as partitioned and inherited tables, and edge cases, with reference outputs held under version control. - Script-driven tests for multi-session concurrency, crash recovery, multi-backend segment behavior, and long-running stress. Continuous integration covers every supported database major version, sanitizer builds, formatting validation, version upgrade tests, release packaging, benchmarks, and a nightly stress run with memory leak detection. Ship diagnostics as first-class functions, not ad hoc printouts: dump the internal structure with its term dictionary, posting lists, and document entries; dump the full index to a file; summarize high-level statistics; force a spill and report how many entries moved; report parked page counts; and render a page layout visualization. Keep benchmarks reproducible: a small classic information retrieval collection for quick signal, larger public datasets for scale, a memory and capacity stress script, and a comparison against an established external engine to validate that results are not merely self-consistent. ## Use the Auto-Formatter and Rebase Onto Main Before Pushing - Formatting is automated. Provide and use commands to format the tree, check formatting without modifying, show the differences, and format a single file. Never hand-format against the tool. - Follow the host database project's coding conventions and memory allocation patterns rather than inventing local ones, and wrap lines at the project's configured width. - Concurrency invariants: every shared structure is protected by an appropriate lock, interning structures are made safe through hash table locks, and transaction isolation is maintained through disciplined cleanup on both the commit and abort paths. - After a pull request merges, always check whether the main branch moved, even when someone else performed the merge. Fetch and rebase active branches onto the updated main to avoid accumulating conflicts. - Before pushing, verify the branch is current with main so that divergent histories do not turn a small change into a conflict-heavy merge. - Follow the repository's documented release procedure rather than improvising tags or artifacts.