Checkpoints

Not Supported

Firebird does not support checkpoints as it does not include any transaction logging facilities.

Compression

Run-Length Encoding Prefix Compression

Firebird uses prefix and suffix compression for index keys. Record data is compressed using run-length encoding.

Concurrency Control

Multi-version Concurrency Control (MVCC)

Firebird uses a multi-generational architecture, also known as multi-version concurrency control, which uses record versions instead of read/write locks and transaction logs to provide external concurrency and consistency. Firebird keeps the preceding version of every modified record is kept as long as it is needed by at least one transaction. Each record version is signed by the transaction which created it, and each transaction knows what other transactions are currently active. Transactions cannot modify records whose most recent version is uncommitted, and transactions cannot read record versions created by active transactions. This ensures that active transactions can always see a consistent view of the database at any moment. Since readers typically do not block writers when accessing the same data, OLTP and OLAP operations can occur simultaneously in Firebird.

Limited explicit pessimistic locking is available through the WITH LOCK feature. However, Firebird warns users that explicit locking is rarely needed and intended for experts.

Firebird uses both its own lock manager and operating system locking for consistency control of the on-disk structure.

Data Model

Relational

Foreign Keys

Supported

Firebird supports both single-column and multi-column foreign keys and enforces foreign key constraints.

Indexes

B+Tree

Firebird uses B-trees for indexes. Each index has dedicated index pages in the database and contains record locators in the leaf nodes.

When using an index to access a table, Firebird retrieves the necessary index information in one pass and creates a sparse bitmap, in which the true bits correspond to the page address and record offsets of records in the query. Multiple indexes can be used on a table by performing Boolean AND and OR operations on the bitmaps created from the indexes.

Isolation Levels

Read Committed Snapshot Isolation

Firebird supports snapshot isolation, snapshot table stability isolation, and read committed isolation. Snapshot isolation is the default isolation level and allows the transaction to see only changes committed before it started. Snapshot table stability isolation is similar to snapshot isolation but more restrictive. Once this level has started, other transactions cannot modify any tables that have changes pending.

Read committed isolation allows the current transaction to see all data changes by transactions which have committed since the current transaction started. There are two kinds of conflict resolution used in read committed isolation: one that reads the last committed version of a row and thus is non-blocking, and one that makes the transaction unable to modify any row that has pending updates from another transaction.

Joins

Nested Loop Join Hash Join Sort-Merge Join

The query optimizer picks the join order with the longest path and lowest estimated cost in terms of I/O operations and processing, in which a join path is defined as a sequence of joins. The cost of the join method is considered in the computation of the join cost.

Firebird uses nested loop join, sort-merge join, and hash join. The join algorithm is chosen as follows. If the join is an outer join, the join conditions are indexed, or the join conditions are inequalities, the nested loop join is used. If the join is an inner join and the join conditions are both equalities and not indexed, the sort-merge or hash join is used.

Logging

Not Supported

Firebird does not include transaction logging facilities, but logging software is available from third-party vendors.

Query Compilation

Code Generation Stored Procedure Compilation

Firebird compiles SQL statements to Binary Language Representation (BLR), its internal language representation, before execution. Stored procedures, triggers, views, and computed columns are also compiled to BLR and stored along with the original SQL. When Firebird executes a stored procedure, the binary code is executed.

Query Interface

Custom API SQL Stored Procedures Command-line / Shell

The Firebird SQL language adheres to international SQL standards. Procedural extensions are supported through the procedural subset of the Firebird SQL language called Procedural SQL (PSQL). Users can use PSQL to write stored procedures, stored scalar functions, and triggers.

Firebird databases can be accessed interactively through the command-line interface provided by the isql utility. Applications and middleware can use the public Firebird API to connect with the database. The API can also be used to write user-defined routines in an external programming language.

Storage Architecture

Disk-oriented

Storage Model

N-ary Storage Model (Row/Record)

Firebird uses row storage with a maximum row size of 64 KB.

Storage Organization

Heaps

Firebird uses a heap file organization with a linked list structure. Database pages called pointer pages contain a list of data pages that are part of a single table. Each pointer page has a pointer to the next pointer page for the table.

Stored Procedures

Supported

Firebird supports two types of stored procedures: executable and selectable. Executable procedures typically modify data in the database, and selectable procedures typically retrieve data and return an arbitrary number of rows to the caller.

System Architecture

Shared-Everything

Firebird is a shared-everything DBMS and consists of two parts: the database server and a client, which connects to the server through the Firebird client library. Clients can connect to the server remotely or locally, and many clients can connect to the same database. The Firebird server process and databases must be hosted on the same machine, and only the server has direct, filesystem-level access to the database.

Views

Virtual Views

Firebird supports three types of virtual views: naturally updatable views, which can be updated automatically without needing to write triggers; updatable views, which are updated through underlying triggers and can accept UPDATE, INSERT, and DELETE statements; and non-updatable views.