Library

Maximum table size in InterBase (pre 2009)

Dmitry Kuzmenko, 20-Oct-2009
Working at interesting technical support incindent with one of our customers, we found what the actual maximum number of rows differs from declared limit.

In the documentation set [Operations Guide for InterBase 6, page 27, InterBase Specification] we can read the following:

"Maximum number of rows and columns per table: By design, 2^32 rows, because rows are enumerated with a 32-bit unsigned integer per table."

Of course, rows enumerated by 32-bit integer. Sadly, but actually no table ever can reach 2 billion records limit - even the table with only the one column.

The reason of such behaviour is in the algorithm of calculation of free space for new (inserted) record - the integer overflow may occur and you will see the following error message in interbase.log:

pointer page vanished from DPM_next (249)

And, the reality is, that database page size or row size doesn't affect this limit. The limit is a magic table size which is always the same (~36.6 gigabytes) and can be calculated in pages for any database page size as:

Maximum page count for one table can be calculated as
MaxDataPageCount = (MaxInt / PageSize) * 17.476

MaxInt, of course, = 2147483647. Place your database page size instead of PageSize and the result will show how much pages can be allocated for any table

For example, table can't have more than ~9 million data pages in the database with 4K page size.

From the point of view of record count, table with 2 integer columns can't grow larger than 600 million (!) records (don't forget that every record have 14 bytes header).

Confused? Multiply MaxDataPageCount by PageSize, and divide result with your biggest table average record size - you'll know when this table will exceed the limit and your database will stop working .

This limit was fixed only in Firebird 2. It still exists in InterBase 2007.

If you have questions or suggestions about information on this page, feel free to contact our research team via research@ib-aid.com. We are also welcome any information about interesting and/or strange things related with InterBase or Firebird.