[Bug] Fixed-size metadata allocations in SingleFile header break at scaled PageSizes (FSM, BlockRegistry, FSM Extension)

[saltus7]

Describe the bug
When a SingleFile (.scdb) database grows beyond a specific size threshold, FreeSpaceManager.FlushAsync throws an InvalidOperationException because the serialized FSM bitmap exceeds its hardcoded 4-page allocation limit.

To Reproduce

1. Create a SingleFile database with the default PageSize = 4096.
2. Insert records continuously until the file size approaches ~350MB - 500MB.
3. Trigger a flush (e.g., via ExecuteBatchSQL transaction commit or explicit Flush()).
4. See error:

   System.InvalidOperationException: FSM too large: 22685 bytes exceeds limit 16384
      at SharpCoreDB.Storage.FreeSpaceManager.FlushAsync(CancellationToken cancellationToken)
   

Expected behavior
The database should support file sizes well beyond 512MB without crashing, or the FSM allocation should scale dynamically with the file size.

Root Cause Analysis
In SingleFileStorageProvider.InitializeNewFile, the FSM is allocated a fixed 4 pages regardless of the expected database size:

header.FsmLength = (ulong)options.PageSize * 4; // 4 pages for FSM

With a 4KB page size, 4 pages = 16,384 bytes.
The FSM L1 bitmap requires 1 bit per allocated page. 16,384 bytes provides exactly 131,072 bits.
131,072 pages × 4KB/page = 512 MB maximum theoretical file size.

In practice, the 64-byte FreeSpaceMapHeader, the L2 extent map, and padding reduce this usable limit. During a 10M record stress test (which peaked at ~338MB / ~86,500 pages), the L1 bitmap (~10.8KB) combined with the L2 extent map exceeded the 16KB limit, triggering the exception.

Current Workaround
During a 10M record stress test (which peaked at ~1.4GB uncompressed), I successfully bypassed this limitation by increasing PageSize from the default 4096 to 16384:

options.PageSize = 16384;

With PageSize = 16384, the fixed 4-page FSM allocation becomes 64KB. This increases the tracking capacity to 524,288 pages, supporting a maximum file size of ~8GB.

However, this is not an ideal permanent solution for downstream users because:

1. It significantly increases the WAL memory footprint (default 1024 pages × 16KB = 16MB of RAM just for the WAL).
2. It increases internal fragmentation for smaller data blocks.
3. It merely raises the hard ceiling rather than eliminating it.

Proposed Fixes

1. v1.x Mitigation: Increase the default FSM allocation (e.g., to 64 pages) or expose a DatabaseOptions.FsmSizePages property to allow users to configure the FSM footprint based on their expected maximum database size.
2. v2.0 Architectural Fix: Move the FSM out of the fixed-offset header layout and manage it as a standard dynamic block in the BlockRegistry (e.g., sys:fsm). This would allow the FSM to grow organically as the database expands, eliminating the hard ceiling entirely.

Desktop (please complete the following information):

• OS: Windows NT 10.0.26200.0
• Runtime: .NET 10.0.3
• Library Version: v1.9.8

Why I haven't submitted a PR for this yet
Unlike the block-level compression feature (PR #344), which was a purely additive extension, fixing this FSM limitation requires an architectural decision that impacts the .scdb file format:

• Option 1 (v1.x Mitigation) is a localized, backward-compatible fix.
• Option 2 (v2.0 Refactor) requires moving the FSM out of the fixed-offset header layout and managing it as a standard dynamic block in the BlockRegistry. This is a breaking change to the file format.

Given that v2.0 appears to be in development (with the block-registry encryption roadmap mentioned in #341), Option 2 might be the perfect fit for the upcoming release. However, I did not want to invest the effort into a large refactor PR without knowing your architectural preference. I am happy to submit a PR for either approach once you have decided on the direction.

Additional context
Discovered while running a 10M record stress test for the block-level compression PR (#344). The compression feature itself is orthogonal to this issue, but the stress test reliably triggers the FSM ceiling.

[saltus7]

Update: Additional Findings (Block Registry & FSM Extension)

During further stress testing (specifically a manual Disk Full POC using a mounted VHD), I discovered two additional bugs that share the exact same root cause as the FSM bitmap limitation: hardcoded page-count constants that fail to scale when PageSize is increased.

1. Block Registry Fixed Allocation Limit

Just like the FSM, the Block Registry is allocated a fixed 4 pages in InitializeNewFile:

header.BlockRegistryLength = (ulong)options.PageSize * 4;

Each BlockEntry is 96 bytes. With a standard 4KB page size, this limits the database to a maximum of ~170 blocks (16,320 usable bytes ÷ 96 bytes per entry) before throwing InvalidOperationException: Block registry too large.

2. MIN_EXTENSION_PAGES Scales Linearly with PageSize

In FreeSpaceManager.ExtendFile, the minimum file extension is defined as a page count, not a byte count:

private const int MIN_EXTENSION_PAGES = 2560;  // 10 MB @ 4KB pages

The comment reveals the intent (a 10MB minimum extension), but because it is a page count, the actual byte size scales linearly with PageSize:

PageSize	MIN_EXTENSION actual size
4 KB	10 MB (intended)
16 KB	40 MB
32 KB	80 MB

Impact: When testing with PageSize = 32768 on a 150MB VHD, the very first WriteBlockAsync triggered ExtendFile, which requested an 80MB allocation. Because the VHD had slightly less than 80MB of contiguous free space remaining, the OS immediately threw a hard IOException ("There is not enough space on the disk"), entirely bypassing the expected gradual disk-fill behavior.

The Shared Root Cause & v2.0 Solution

All three limitations (FSM capacity, Block Registry capacity, and FSM Extension thresholds) stem from treating critical metadata as fixed-size structures in the file header layout.

The v1.x mitigations (exposing FsmSizePages / RegistrySizePages in DatabaseOptions, or converting MIN_EXTENSION_PAGES to a byte-based constant) are localized patches.

The ideal v2.0 architectural fix is to move the FSM and Block Registry out of the fixed-offset header layout entirely, managing them instead as standard, dynamically-sized blocks within the BlockRegistry itself (e.g., sys:fsm and sys:registry). This would allow the metadata to grow organically as the database expands, permanently eliminating these hard ceilings.

[MPCoreDeveloper]

Thanks @saltus7 — excellent, precise root-cause analysis. Confirmed on all three counts, with a small addition:

• FSM: fixed FsmLength = PageSize * 4 in InitializeNewFile → ~512 MB ceiling at 4 KB pages (slightly lower once the 64-byte header + L2 extent map are accounted for, exactly as you measured).
• Block Registry: fixed BlockRegistryLength = PageSize * 4, and BlockEntry.SIZE is indeed 96 bytes (~170 entries at 4 KB pages). The "64B per entry" comment in BlockRegistry.cs is stale — good catch.
• MIN_EXTENSION_PAGES = 2560: it is a page count, so the actual allocation scales linearly with PageSize (40 MB @ 16 KB, 80 MB @ 32 KB) instead of staying a fixed ~10 MB. Confirmed.
• Fourth instance of the same pattern: TableDirLength = PageSize * 4 (table directory). Lower risk since schema metadata is small, but it is the same fixed-4-pages pattern.

Direction — we'll do this in two steps:

1. Now, on master (backward-compatible): a localized fix — make the file-extension threshold byte-based (MIN_EXTENSION_BYTES), and expose FsmSizePages / BlockRegistrySizePages / TableDirectorySizePages on DatabaseOptions (defaults stay at 4 pages, so existing files are unaffected and new files self-describe their region sizes in the header). This removes the practical ceiling for 1.9.x users without a format change.
2. For v2.0: the architectural fix — make the FSM and Block Registry growable (region relocation, or fully dynamic blocks) so the hard ceilings disappear entirely. Note: v2.0 is actively getting metadata encryption ([Bug] SingleFile (.scdb) mode ignores DatabaseOptions.EncryptionKey, writing data in plaintext #341) in exactly these regions, so I coordinate that as rework togther rather than have the two efforts collide.