Firebird Internals: Inside a Firebird Database

The purpose of this document is to try to explain what goes on inside a Firebird database. Much of the information in this manual has been extracted from the Firebird source code — mainly on the ODS related code and headers — and from some (partially out of date) documents on the Research part of the IBPhoenix website (https://www.ibphoenix.com/). Other questions and queries that I had were very patiently answered on the Firebird Support forums where the developers hang out.

Much hex dumping of database files was undertaken in the creation of this document, but no Firebird databases were harmed during this process.

All databases mentioned or described within this document are those with an ODS of 11.1 — in other words, Firebird 2.1 — and a page size of 4,096 bytes. There may be differences between this ODS version and previous ones and wherever possible, this has been documented.

Unless otherwise noted, the test database used for this document is empty of all user tables, indices, generators etc. It was created on a 32-bit Linux system running on Intel hardware. It is therefore little endian.

Thanks to everyone who has contributed to the manual.

When you create a new database, be it single or multiple file, a number of things happen:

The various page types are described elsewhere in this document.

A database is created and the DBA can specify the page size, or leave it to default. This action creates a database file, or files, with enough space allocated to create all the system tables and indices. New pages will be added to the end of the database file(s) as and when the user creates new tables and/or indices. For example, a brand-new database, created on a 32-bit Linux system, with a 4Kb page size allocates a total of 0xa1 pages (161 pages) for the system tables, indices and the various database overhead pages.

Every page in a database has a 16-byte standard page header. Various page types have an additional header that follows on from the standard one. The C code representation of the standard header is:

The first page of the first file of a Firebird database is a very important page. It holds data that describes the database, where its other files are to be found, shadow file names, database page size, ODS version and so on. On startup, the Firebird engine reads the first part (1,024 bytes) of the first page in the first file of the database and runs a number of checks to ensure that the file is actually a database and so on. If the database is multi-file, then each file will have a header page of its own.

The C code representation of the database header page is:

The following is an example of a header page from a multi-file database on a little-endian system:

The miscellaneous data stored in the header from the above database, at hdr_data, is shown below.

From the above we can see that in our multi-file database:

Every database has at least one Page Inventory Page (PIP) with the first one always being page 1, just after the database header page. If more are required, the current PIP points to the next PIP by way of the very last bit on the page itself. The C code representation of the PIP page is:

If the database is large, and requires another PIP elsewhere in the database, then the last bit on this PIP represents the page number for the next PIP. For example, on a 4,096 byte page we have a total of 4,076 bytes to represent different pages in the database. As each byte has 8 bits, we have a total of 32,608 pages before we need a new PIP.

In a brand new database, a hex dump of the first few bytes of page 1, the first PIP, looks like the following:

In the above, we see that pip_min has the value 0x000000a1 and the following 20 bytes, the first part of the pip_bits array, are all zero. From this, it would appear that page 0xa1 is the first available page in the database for user tables etc and that all the pages up to that one have already been used for the system tables and indices etc.

Looking at the bitmap again, page 0xa1 will be represented by byte 0x14, bit 0x01 of the bitmap. This is byte 0x00001028 bit 1. We can see that this byte currently has the value 0xfe and bit 0x00 is already in use. So, our array is correct and so is our pip_min value — the next available page is indeed 0xa1.

If we look at the hexdump of that particular page, at address 0x000a1000, we see that it is actually the first byte past the current end of file, so our brand new blank database has been created with just enough space to hold all the system tables and indexes and nothing else.

Every database has at least one Transaction Inventory Page (TIP).

The highest possible transaction number is 2,147,483,647 or 0x7fffffff in a 32-bit system. Once you hit this transaction, no more can be created, and the database needs to be shutdown, backed up and then restored to reset the transaction numbers back to zero. The reason it has this maximum value is simply because the code for allocating transaction numbers uses a signed value.

The C code representation of the TIP page is:

Looking at a hex dump of the first few bytes of a new database, which has had a few transactions run against it, we see the following:

Now, if a new transaction starts we won’t see any changes because a live transaction and one that has not started yet, shows up as two zero bits in the tip_transactions array. However, if it commits, limbo’s or rolls back, we should see a change. The following is the above database after a session connected using isql and immediately exited without doing anything:

You can see that it looks remarkably like loading up a connection to isql and then exiting actually executes 4 separate transactions. We can see at the end of the last line that one byte has changed from 0x00 to 0xff and with 2 bits per transaction, that equates to 4 separate transactions, all of which committed.

Other tools may run fewer or indeed, more, transactions just to connect to a database and do whatever it is that they have to do to initialise themselves.

A pointer page is used internally to hold a list of all — or as may will fit on one pointer page — data pages (see below) that make up a single table. Large tables may have more than one pointer page but every table, system or user, will have a minimum of one pointer page. The RDB$PAGES table is where the Firebird engine looks to find out where a table is located within the physical database, however, RDB$PAGES is itself a table, and when the database is running, how exactly can it find the start page for RDB$PAGES in order to look it up?

The database header page contains the page number for RDB$PAGES at bytes 0x14 - 0x17 on the page. From experimentation, it appears as if this is always page 0x03, however, this cannot be relied upon and if you need to do this, you should always check the database header page to determine where RDB$PAGES is to be found.

The C code representation of a pointer page is:

You can find the pointer page for any table by running something like the following query in isql:

The page number which has RDB$PAGE_SEQUENCE holding the value zero is the top level pointer page for this table. In the above example, there is only one pointer page for the EMPLOYEE table. If we now hexdump the pointer page for the employee table, we see the following:

Looking at the above, we can see at address 0x0b4f10 on the page, that the byte there has the value of 0x01. This is an indicator that the page in ppg_page[0] — page 0xca — is full to capacity (bit 0 set) and does not have any large objects on the page (bit 1 unset). The page at ppg_page[1] — page 0xcb — is, on the other hand, not full up yet (bit 2 is unset) and doesn’t have a large object on the page either. This means that this page is available for us.

This is confirmed by checking the value in ppg_min_space which has the value 0x0001 and does indeed correspond to the first page with free space. The value in ppg_min_space is the index into the ppg_array and not the page number itself.

A data page belongs exclusively to a single table. The page starts off, as usual, with the standard page header and is followed by an array of pairs of unsigned two byte values representing the 'table of contents' for this page. This array fills from the top of the page (lowest address, increasing) while the actual data it points to is stored on the page and fills from the bottom of the page (highest address, descending).

The C code representation of a data page is:

The raw record data is structured into a header and the data.

Every table in the database has an Index Root Page which holds data that describes the indexes for that table. Even tables that have no indices defined have an index root page.

The C code representation of an index root page is:

Each descriptor entry in the array holds an offset to a list of key descriptors. These start at the highest address on the page and extend towards the lowest address. (The array of index descriptors (irt_rpt) starts at a low address on the page and increases upwards. At some point, they will meet, and the page will be full.

The index field descriptors are defined as follows:

You may note from the above that an irtd_itype with value 2 is not permitted.

The following commands have been executed to create a parent child set of two tables and a selection of indices:

The Following command was then executed to extract the index root pages for both of these tables:

Now that the root pages are known, we can take a look at the layout of these two pages and see how the details of the various indices are stored internally:

We can see that the PARENT table (relation 139) has two defined indices while the CHILD table (relation 140) has one.

If we examine the above output we can see that the indices do match up to those that were created above. We can also see that in the event of an index being created without a sort order (ascending or descending) that the default is ascending.

As described above for the Index Root Page (type 0x06) each index defined for a table has a root page from which the index data can be read etc. The Index Root Page field irt_root points to the first page (the root page — just to confuse matters slightly) in the index. That page will be a type 0x07 Index B-Tree Page, as will all the other pages that make up this index.

Indices do not share pages. Each index has its own range of dedicated pages in the database. Pages are linked to the previous and next pages making up this index.

The C code representation of a blob data page is:

If the flag byte in the standard page header (pag_flags) is set to 1, this blob page contains no data but acts as a pointer page to all the other blob pages for this particular blob.

Every database has at least one Generator Page, even if no generators (also known as sequences in Firebird 2.x) have been defined by the user. A blank database consisting only of system tables and indices already has a number of generators created for use in naming constraints, indices, etc.

The C code representation of the generator page is:

If we use isql to create a new blank database, we can dump out the generator page as follows:

We need to find the generator page next:

Now we can dump out the generator page:

The system table RDB$GENERATORS holds the defined sequence details but no values for each one. It does have an RDB$GENERATOR_ID column and this starts from 1, not zero. And increments by 1 for each new sequence. Where does this number come from?

Looking in the blank database we created, we can see that there are 9 sequences created for system use:

This is a clue, take a look at Sequence[00000], above, and see that it contains the value 9. I suspect therefore, that the very first sequence is used to generate the RDB$GENERATOR_ID value when a new sequence is created. One way to find out is to create a new sequence.

So far, so good, we see a new sequence. Time to hexdump the database file’s generator page again:

We can see that Sequence[00010], that a new sequence has been created. The value in this sequence is 666 in decimal. In addition, we can see that Sequence[00000] has increased to 10. So it looks remarkably like the RDB$GENERATOR_ID is itself obtained from a sequence that never appears in RDB$GENERATORS.

The value, stored in Sequence[n], appears to be the last value that was used and not the next value to be issued. It is also the total number of sequences that have been created thus far in the database, provided, that the value in gpg_sequence is zero.

I wonder what happens when we drop a sequence?

We can see that the sequence is dropped from the RDB$GENERATORS table, what about in the generator page in the database?

The generator page has not changed. Sequence[00010] still remains at its previous value — 666 — but this 64 bits of database page representing our recently dropped sequence can never be used again. It has ceased to be a sequence and has become wasted space.

Given that RDB$GENERATOR_ID is itself generated from Sequence[00000] and cannot therefore reuse any allocated RDB$GENERATOR_ID, it is not surprising that the simplest way of handling a dropped sequence is simply to ignore it.

If you are creating and dropping sequences frequently, you may end up with a lot of unused sequences. You can restore these to a usable state by dumping and restoring the database:

If we now dump the generator page as before, we see the following:

We now see that the deleted sequence has gone, and the value in Sequence[00000] has reduced by one (the number of deleted sequences) to suit. If we now create a brand new sequence, it will reuse the slot previously occupied by our deleted sequence.

Dumping the generator page again, we see:

Bearing in mind that in ODS 11 onwards, a sequence is a 64 bit value, how many sequences can we store on a page? The answer will be (page size - 32 bytes)/8 and we are allowed a maximum of 32,767 sequences in any one database. With a 4K page size this would mean sequence 508 would be the first on the next page.

Because there is no apparent next and previous page numbers on a generator page, how does the database know where to find the actual page that the generator values are stored on? RDB$PAGES is a system table that the main database header page holds the page number for. This allows the system, on startup, to determine where its internal data can be found. For because sequences live, as it were, in RDB$GENERATORS we can look in RDB$PAGES as follows, to find the actual page number(s):

The RDB$RELATION_ID is zero because this is not actually the location of a relation (table) in the database itself, but the location of a specific page that we are after. Given that RDB$PAGE_SEQUENCE = 0 and RDB$PAGE_TYPE = 9 we see that the first generator page is located on page 148 of the database.

If there are more than one page, then the page that has gpg_sequence set to zero is the first one and the first sequence on that page is the count of all sequences created (and possibly deleted) within the database. If the gpg_sequence is non-zero, then there is no way to tell how many sequences on that page are actually valid and even when the gpg_sequence is zero, unless the database has been restored since any sequences were last deleted, it is not possible to determine which sequences on the page are still valid. (Unless you have access to the RDB$GENERATOR_ID in RDB$GENERATORS of course.)

Every database has one Write Ahead Log page (WAL) which is currently always located at page 2.

The C code representation of the WAL page is:

As this structure is no longer in use within the database, it is effectively, a wasted page. Looking at a hexdump of the WAL page in a new database, we see the following:

The remainder of the page is filled with binary zeros.

Because the WAL is no longer in use, and may even be dropped completely from Firebird 3.0 onwards, it will not be discussed further.

Throughout some of this document you may have noticed that I’ve been using a tool named fbdump to display internal representations of Firebird Database pages. Maybe some of you are wondering where to find it in the Firebird installation directory.

Fbdump is a utility that I had to write myself while writing this document. I’m (almost) happy to let it loose into the wild, but it’s probably the worst code you will ever have the misfortune to see. It wasn’t written to a plan, I simply added bits here and there as I needed them. It’s not nice.

Firebird itself comes with a page dumping mechanism, but you need to be running a debug version of Firebird in order to use it. The good news about doing it this way, rather than using fbdump is that the official way will keep up with ODS changes. There is no guarantee that fbdump will.

Sorry.

The exact file history is recorded in our git repository; see https://github.com/FirebirdSQL/firebird-documentation

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the “License”); you may only use this Documentation if you comply with the terms of this License. Copies of the License are available at https://www.firebirdsql.org/pdfmanual/pdl.pdf (PDF) and https://www.firebirdsql.org/manual/pdl.html (HTML).

The Original Documentation is titled Firebird Internals.

The Initial Writer of the Original Documentation is: Norman Dunbar.

Copyright © 2009. All Rights Reserved. Initial Writer contact: NormanDunbar at users dot sourceforge dot net.