What is the maximum number of rows per table in Firebird 2.1 and up?

Lukasz Matuszewski wrote:

We all know that documentation states that record enumeration is expanded to 40-bits (64-bits internally).

If these statements above are true then maximum number of records (rows) are: 2^40 which is 1099511627776 records (above 1 Tera records). Is it true? Because the docs states (or faq) it is above 16 G records (and not measured beyond) (2^34).

Ann W. Harrison answers:

The record number space is not dense. There are holes - numbers which are never used. The record number decomposes into the sequence number of a pointer page for the table, the offset of a data pages number on that page, and the offset of a pointer/length pair on the data page. To make that work, there have to be enough bits reserved for the offset on the data page to allow as many records as can possibly fit on a page. Since records can be very short, that's quite a few bits. But most records are bigger than the minimum size, so some of the bits are wasted.

Lukasz Matuszewski continues:

The 32 TB limit for one table (32 TB = data + housing structures of data pages) is stated in Firebird FAQ. Is it true or not?

What really bothers me is that in docs they state that they increased record enumeration limit to 40-bits (64 bits internally) and i do not see any 64 bits field or fields that will make up this enumeration (40-bit size is not mentioned in fb-internals.

I think it would be nice to have same max number of records, whatever i will use 4k or 16k database page size.

Ann W. Harrison answers:

The documentation is correct that you can't have more than 32TB of records in any table. But that's a negative statement. It's not possible for a person to jump more than three miles horizontally. That doesn't mean that a person can jump 2.8 miles horizontally. Nor can you store 32TB -1 records in a table, unless each record has a length of zero.

<dpg> is a data page. <ppg> is a pointer page. The record number space is limited this way:

<max records on dpg> * <max dpg per ppg> * <max ppg> < 2**40

The middle factor, data pages per pointer page, is determined by the page size, so the highest possible number of pointer pages per table depends on how many records you assume you could possibly have on a data page. In Firebird, the assumption is based on storing zero length records. As I said before, a zero length record uses 17 bytes of storage, of which 13 are the record header and 2 are the stored length and 2 are the offset on the data page.

Here's a matrix of page sizes, the number of zero byte records that fit on a page, the maximum number of pointer pages that can exist, given that the number of records per page times a fixed number of data pages per pointer page, times the number of pointer pages must not exceed 40 bits.

Yes, if you had zero length records, you could store 962 of them on a 16K page and get 1,099,511,627,520 records in a table. But if you have 10 byte records, you only get 692,285,098,809 records because you use only 37 of the 59 record number that are allocated for each 1K page. This is true regardless of the page size. With 10 byte records on a 2K page, you use 75 of the 119 record numbers. With a 4K page you use 151 of the 239 allocated, 8K pages of 10 byte records use 302 of the 480 numbers allocated. 16K pages use 606 of 962.

The net result is that the number of records that can be stored in a table depends on the record size, not the page size. The larger the records, the smaller the number that can be stored in a table.

Regardless of page size, you can store 692,285,098,809 10 byte records in a table, or 278,980,562,207 50 byte records, or 159,758,099,725 100 byte records. Record size is the compressed data length, including null bytes.

If Firebird assumed that records were actually 10 bytes of data on top of the 17 overhead bytes, then you could store over 1 trillion 10 byte records in a table, but if you had shorter records you would not be able to fill pages. Since the major time sink in a database is reading and writing pages, having them full is a good thing.

The number of records in a table depends on the size of the records. The larger the records, the more record numbers are wasted. Here's some of the relevant code from pag.cpp

/* Compute the number of data pages per pointer page.  Each data page
   requires a 32 bit pointer and a 2 bit control field. */

     dbb->dbb_dp_per_pp = (dbb->dbb_page_size - OFFSETA(pointer_page*, ppg_page))
                   * 8 / (BITS_PER_LONG + 2);

/* Compute the number of records that can fit on a page using the
   size of the record index (dpb_repeat) and a record header.  This
   gives an artificially high number, reducing the density of db_keys. */

     dbb->dbb_max_records = (dbb->dbb_page_size - sizeof(data_page)) /
             (sizeof(data_page::dpg_repeat) + OFFSETA(RHD, rhd_data));