--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:

>
> On Sat, Apr 16, 2011 at 1:15 PM, lmatusz <matuszewski.lukasz@...> 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).
> >
> > I am asking this question because the docs states (or faq) it is above 16 G records (and not measured beyond) (2^34)
> >
>
> 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.
>
> Good luck,
>
> Ann
>

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).

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).

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.

So if we have table with one field on size 1 byte (up to 4 bytes the result will not change), the maximum number of record will be:

2 000 * 2 ^ 31 = 4 294 967 296 000 = 3,90625 Tera (Tera = 1024 * 1024 * 1024 * 1024) records.

We can say that for 8 bytes record length it would be an half of it (above 1000 records per page), but it a little more:

1 125 * 2 ^ 31 = 2 415 919 104 000 = 2,197265625 Tera (Tera = 1024 * 1024 * 1024 * 1024) records (it is estimated)

For bigger records we all know that record data is compressed with RLE (Run-Length Encoding).

Again for example: 1024 bytes record length it would be:

15 * 2 ^ 31 = 32 212 254 720 = 30 Giga records (Giga = 1024 * 1024 * 1024)

(for 2048 bytes record length it would be:
7 * 2 ^ 31 = 14 Giga records)