[magnat]

If your data/leaf/heap pages hold indexed cells PostgreSQL-style [1], 16-bit in-page offset limits your page size to 64KB. On the other hand, physical memory frame size usually is 4KB or larger, so going below that is not really productive.

Having said that, both PostgreSQL and Microsoft SQL has 8KB page size, while original SQLite had 1KB pages, before switching to 4KB, while still supporting 64KB pages. On top of that, Microsoft SQL operates internally on extents of 8 pages (64 KB) instead of a single page, but still pretends to use 4 KB pages [2].

In other words - no idea.

[1] https://www.postgresql.org/docs/current/storage-page-layout....

[2] https://learn.microsoft.com/en-us/sql/relational-databases/p...

[msanlop]

I tried looking for papers on the subject but couldn't find much. There recent research about in-memory B-tree, and there is has been shown that smaller pages (sizes as low as 256/512B) are more performant due to better cache behavior[0]. The general wisdom seems to be that disk-based databases' IO performance is the main bottleneck—but again I couldn't find any concrete data/benchmarks about why those higher sizes were chosen.

[0] There's even some cool research about B-trees that have the layout depend on the probabilistic distribution of the lookups: https://dl.acm.org/doi/10.1145/3592980.3595316

[sroussey]

The default macOS page size is currently 16KB, and various (Apple) stuff is optimized for it, and I think SQLite is among them (not 100%). I think their server optimized builds for internal use are 64.

[o11c]

16-bit offsets doesn't limit you to 64K unless you require byte-alignment. 128K or 256K is usually trivial and even 512K or 1M is often viable.