v3.0.0 - WAL-Logged Storage and Durable Pattern Matching

[CrystallineCore]

Version 3.0.0

First release integrated with WAL logging. This is a breaking on-disk
format change: indexes built under 2.x must be REINDEXed.

The focus of this release is durability: index state now lives in the index
relation's own WAL-logged pages, participating in PostgreSQL's ordinary
recovery machinery.

New Features

• WAL-logged, crash-safe on-disk storage. Replaces the external-file
  snapshot mechanism from 2.5.0 with in-relation, GenericXLog-protected page
  storage covering per-character and length bitmaps, the TID array,
  tombstones, the free-slot list, and per-record string caches. Index state
  now survives a crash and replicates correctly.

• Pending-list write path with opportunistic draining. Steady-state
  INSERT, UPDATE and DELETE append a small delta record to a structure's
  own pending chain instead of rewriting a whole snapshot. Once a structure's
  pending chain passes a threshold (biscuit.delta_compaction_slots, a new
  GUC), it is re-serialized into a fresh compacted blob. VACUUM also
  performs a full drain pass and tracks lifetime drain counters.

• Read-time pending-list reconciliation. Queries transparently merge
  not-yet-drained pending records into the results they read, so a backend
  sees a consistent view regardless of whether another backend's writes have
  been drained yet.

• Cross-backend cache coherency. Each session's cached copy of the index
  carries the generation it was loaded at; every scan compares that
  generation against the metapage and reloads on mismatch, so a backend
  always sees other backends' committed writes.

• Candidate-mask threading across scan keys. Conjunctive queries evaluate
  their most selective key first and restrict later keys to the surviving
  rows, instead of computing each key independently and intersecting.
  Queries combining an anchored predicate with an unanchored one benefit
  substantially.

• Rewritten cost model. Costs are now derived from pattern shape, column
  statistics and relation size, letting the planner weigh Biscuit against
  gin_trgm_ops pg_trgm and a text_pattern_ops B-tree. Costing for unanchored
  patterns continues to be refined; verify with EXPLAIN where a specific plan
  matters.

• Length-predicate support. Patterns consisting only of _ wildcards
  ('______', '______%') are recognised as length predicates and answered
  from the length bitmaps in a single lookup, rather than being treated as
  unusable.

• New biscuit_like_ops / biscuit_ilike_ops operator classes. In addition
  to the default biscuit_ops, which builds both case-sensitive and
  case-insensitive structures, a column may be indexed with biscuit_like_ops
  (LIKE and NOT LIKE only) or biscuit_ilike_ops (ILIKE and NOT ILIKE only),
  skipping the build and maintenance cost of the structure set it will never be
  queried with. The mode is derived from the column's opfamily at build and load
  time and is never persisted, so it cannot go stale across a REINDEX under a
  different opclass.

Internal Changes

• Removed the background preload worker; index loading is now synchronous.
• Rewrote the on-disk persistence layer to use the new directory and
  blob/pending-chain storage; the 2.5.0 flat-file snapshot format is no
  longer read.
• Deletes and updates now remove index entries individually and durably,
  replacing the previous bulk in-memory sweep.

Known Limitations

These follow from the design and should be planned for. Figures observed during
testing will vary with hardware, data and workload.

• Write amplification. Because one indexed string touches many
  per-character structures, INSERT and UPDATE against a live index generate
  considerably more WAL than the corresponding heap writes alone. Substantial
  WAL is characteristic of maintaining any secondary text-search structure, and
  in testing Biscuit's WAL volume per row was comparable to a pg_trgm GIN
  index on the same data. WAL per row also grows as the index grows, so
  measurements taken on a small index will understate a large one. DELETE is
  much cheaper, recording a tombstone rather than rewriting structures.

  Size pg_wal accordingly, monitor free space, and where replication slots
  are in use consider setting max_slot_wal_keep_size. Allow for the
  corresponding effect on crash-recovery duration when planning restart
  windows.

• Bulk-load before indexing. Creating the index after a load is
  substantially faster, and generates far less WAL, than inserting the same
  rows into an already-indexed table.

• Per-connection memory. Each backend holds its own copy of the index in
  session-local memory for the life of the connection, loaded lazily as
  patterns are queried. Memory therefore scales with concurrent connections;
  biscuit_index_memory_size() reports the current session's copy.

• Cache reload on invalidation. A committed write by any backend
  invalidates cached copies, which are then reloaded in full rather than
  refreshed incrementally. Read latency rises for a period after each write, and
  the effect is more pronounced with many concurrent readers. Incremental
  refresh is planned.

• Build cost and index size. Biscuit indexes are larger and slower to build
  than comparable pg_trgm or B-tree indexes on the same column. VACUUM does
  not reduce index size; use REINDEX. Build memory scales with row count.

• Unanchored query cost grows with the square of string length, so a small
  number of unusually long values can affect query cost across the table.

• No ordered, backward, index-only or unique scans, and Biscuit indexes are
  not clusterable.

Upgrade Notes

This is a breaking on-disk format change. Indexes built under 2.x must be
REINDEXed after upgrading; there is no automatic migration and no dual-format
reader. Until an index is rebuilt, its first cold load under the new version
fails with an error referring to this note.

Plan a maintenance window sized for the rebuild: index build is slower than for
pg_trgm GIN on the same data.

---

This discussion was created from the release v3.0.0 - WAL-Logged Storage and Durable Pattern Matching.