Subject Re: [firebird-support] Re: Maximum number of rows in Firebird 2.1 and up (is it 1099511627776 records ?)
Author Ann Harrison
On Sun, Apr 17, 2011 at 6:39 AM, lmatusz <matuszewski.lukasz@...> wrote:
>  Ok i think i figured it out.
> Database in Firebird are made of pages. Their size depends on size of database page which is chosen during database creation (max is 16 384 bytes). There are (among other) 2 types of pages in interest:
> 1) Pointer Page - hold a list of all data pages that make up a single table (relation). There is in it a field called
> ...
> SLONG ppg_sequence;
> ...
> which is signed long. This field gives us a maximum table size in bytes which is:
> 2 ^ 31 * 16384 bytes = 2 ^ 45 = 32 TB per table (it is size of table and its structures, not only the data inside table - so it is less then that).

Good guess, but no. The ppg_sequence is an index into the array of
pointer pages for a table. Each
pointer page is an array of 32-bit data page numbers. There's some
page level overhead on pointer pages so
the the number of potential data pages is a bit less than 2**32 *
<page size> / 8
> 2) Data Page - belongs exclusively to a single table. There is in it a filed called
> ...
> USHORT dpg_count;
> ...
> which is unsigned short. The number of records on this page which is:
> 2 ^ 16 = 65535 records per page. Again this is theoretical limit - the actual limit is less then 16 384 (the page size limit).

Sorry, again good guess, but wrong. My recollection - I don't have
sources with me at the moment - is that
is the number of data pages that fit on a pointer page, given the page
and the page overhead.

> We could say it is 16 000 records limit per page, but that is not true. (if it would be 16 000 records then this record will contain one field of size one byte, and there would not be any space for 'table of contents' for records on the page). The safe number of records that will fit inside one Data Page is 2000.

Again, no. The minimum record size is the size of a record header
and a byte for null
bits - on the order of 14-16 bytes. Every record version has a header.

I'll look around tomorrow - I did a bunch of calculations when the
format of the record
number was changed for V2. Basically, the page size falls out of the
computation of
the maximum number of rows in a table because larger pages take more
of the record
number for the offset on the data page. Maybe someone who was involved in that
change will have the numbers at hand.