Page size

This is the specification of the database page size in bytes.

Firebird/InterBase® databases are saved in blocks. Each of these blocks is called a page. A database page is the smallest administrative unit in the database file. Database administration occurs basically by accessing the hard drive block by block. The more data per access fetched by a single database page, the less often it is necessary to load a new page, at least theoretically. Practically, depending upon the operating system and server hardware, access to larger database pages can even influence the performance negatively, as 1024 bytes can be loaded quicker than 8192 bytes.

Page sizes permitted are 1024, 2048, 4096, 8192 and 16384. Up to and including Firebird version 1.5 page sizes up to 8192 should be used. The page size 16384 should be reserved for Firebird 2.0 and higher. Firebird 4 introduced the new page size of 32768.

Since Firebird 2.1 page sizes of 1K and 2K are deprecated as inefficient. The small page restriction applies to new databases only; old ones can be attached to regardless of their page size.

A large page size has certain advantages in the following situations:

1. Many index-based operations (indices work quicker if the index depth is minimized).
2. Wide records, because with very wide data structures, i.e. with very many and/or very long columns, reading a data set is more efficient. With data sets that do not fit onto one page, several pages have to be read to fetch a single data set. The same applies to writing; ie. fetches across several pages are necessary.
3. Large blob fields, as data is stored and retrieved more efficiently if fewer pages need to be fetched. With larger blobs the writing and reading processes are also more effective, as, for example, 100 accesses are necessary for a 100K blob column with a 1K page size. However with an 8K page size only 13 accesses are required.

A small page size is sufficient if many transactions return only a small number of rows. Slim table structures with small database pages can be accessed more quickly for reading and writing as less memory is required, and more database pages can be held in the cache. However a database with a page size less than 4096 is not recommended on Windows, as this is the Windows block size. Therefore smaller page sizes do not bring any advantages, as Windows will still fetch 4K blocks.

The database page size has a direct influence on the amount of database cache, which influences all of the above points. If a 16 KB page size is specified and the Firebird server's database cache defined in the firebird.conf at its maximum of 128,000 pages, a total of 2 GB cache is made available for holding data pages. The same cache specification with a page size of 1 KB only provides 180 MB cache. Please refer to Memory configuration for details of cache specification for the Firebird Superserver and Classic server.

Although you may be wasting a certain amount of space with a large page size, at today's hardware prices this should not be a serious problem, and it can offer more performance advantages.

The only way to subsequently alter a database page size, is to perform a database backup followed by a restore (IBExpert menu item, Services / Restore Database) where the database page size can be redefined.