Limits of the Firebird Database

(Last updated Aug 29th 2018)

  • Object identifiers - Maximum length in all versions is 31 characters for almost all object types. In databases with ODS lower than 11,constraint names are restricted to 27 characters. Characters outside the range of US ASCII (ASCIIZ) cannot be used unless the identifier is double-quoted. Double-quoted identifiers cannot be used in dialect 1 databases. Also note that user names in Firebird 3 and above are object identifiers.

  • Dates - The earliest date in all versions is January 1, 100 A.D. The latest date is December 31, 9999 A.D., but note that it is believed that the engine is susceptible to problems if the system date of the server is set higher than the year 2039.

  • Server - Maximum connected clients: TCP/IP on all platforms 2,048 from Fb 2.5.3 forward. For earlier versions, the limit is 1,024 connections. For WNET (NetBEUI, Windows Networking) connections the limit for all versions remains at around 930. For Classic server, the numbers may be lower because each client consumes more resources. The same is true for SuperClassic if resources are limited.

  • Maximum number of databases open in one transaction - depends on the method used to start the transaction: The number of databases opened during a transaction started by isc_start_multiple() is limited only by available system resources. A transaction started by isc_start_transaction() limits concurrent database attachments to 16.

  • Maximum number of tables in a database - 32,767

  • Maximum database size - approx. 7TB, but it depends on the file system. On FAT32 and ext2, files are limited to 2GB. Older NTFS and some ext3 are usually 4GB. Many 64-bit file systems place no limit on the size of a shared-access file.

  • Maximum number of files per database - theoretically, 2^16 (65,536), including shadow files. The limitation is more likely to be imposed by the operating system’s limit on the number of files that can be opened simultaneously by one process. Some permit that limit to be raised.

  • Maximum page_size - (since Firebird 1.5, ODS 10+): 16,384 bytes. Prior versions: 8,192 bytes. Other sizes are 1,024, 2,048 and 4,096 (default).

  • Minimum page_size - Firebird 2.0 and higher will not create databases with page_size smaller than 4,096 bytes.

  • Maximum cache buffers (DefaultDbCachePages)- 128,000 pages for ODS 11+, 65,636 pages for databases with lower ODS. The practical limit depends on available RAM. The total size (cache pages * page_size on Superserver; cache pages * page_size * no. of concurrent users on Classic or SuperClassic servers) should never be more than half of the available RAM. Consider 10,000 pages as a practical limit and tweak backward or forward from there as performance dictates.

  • Maximum metadata versions per table - 255 for all versions. Firebird keeps account of up to 255 formats for each table. The format version steps up by 1 each time a metadata change is done. When any table reaches the limit, the whole database becomes unavailable. It must be backed up and then restored.

  • Maximum row size for user tables - 64KB.Count bytes. BLOB and ARRAY columns each cost 8 bytes to store the ID; VARCHARs, byte length + 2;CHARs, byte-length; SMALLINT, 2; INTEGER, FLOAT, DATE and TIME, 4; BIGINT, DOUBLE PRECISION and TIMESTAMP, 8; NUMERIC and DECIMAL, 4 or 8, depending on precision.

  • Maximum row size for system tables - 128KB.

  • Maximum number of rows per table - 2^63 - 1 for ODS 11 and higher; 2^32, more or less, to a maximum table size of 30GB. Rows are enumerated with a 32-bit unsigned integer per table and a 32-bit row slot index. A table with long rows, either a lot of fields or very long fields, will store fewer records than a table with very short rows. All rows, including deleted ones, use up numbers; BLOBs and BLOB fragments stored on table data pages use up numbers, too.

  • Maximum number of columns per table - depends on data types used (see Maximum row size).

  • Maximum indexes per table - 256 since Firebird 1.5 (ODS 10.1+); 64 prior to that.

  • Maximum size of external file - 4GB on Windows NTFS, 2GB on Windows FAT32, Linux ext2 and ext3, and Solaris.

  • Theoretical maximum size of a single-column index, where the character set is single-byte and uses the default (binary) collation sequence - for databases of ODS 11+: One quarter of the page_size, for databases of ODS < 11: 252 bytes. Count bytes, not characters. The practical maximum is reduced by compound indexing, multi-byte character sets, and complex collation sequences. A single-column index using 4-byte UTF-8 characters, for example, can have a maximum of (2048/4) = 512 characters on a database with a page_size of 8192. Some ISO8859 collation sequences consume up to 4 bytes per character just for the sorting attributes.

  • Maximum number of segments in an index - 16 for any version.

  • Maximum number of joined tables in a query - theoretically, 256. Other factors come to bear, such as the number of Boolean evaluations required by the joins. From the point of view of resources and performance, the largest practicable number of table references is probably around 16.

  • Maximum nesting level in a query - there is no theoretical limit but, since deep nesting of subqueries is sub-optimal for performance, it will be performance and resource consumption that determine your practical limit, query-by-query.

  • Maximum size of ORDER BY key-set data - 32KB

  • Maximum size of BLR for a PSQL module - 48KB, stored procedure and trigger sources are compiled into BLR bytecode, which is more dense than the PSQL source. Still, if you hit this limit, try to break up your monumental procedure into a “master” procedure with callable chunks.

  • Maximum number of events posted per PSQL module - no limit. The practical limit is related to the length limit for BLR byte code (above).

  • Maximum level of embedded calls from a PSQL module - 1,000 on POSIX, 750 on Windows.

  • Maximum BLOB size: depends on page_size -

    =========  ==============
    Page Size  Max. BLOB size
    =========  ==============
      2KB             512MB
      4KB             4GB
      8KB             32GB
      16KB            256GB
    
  • Max number of conjuncts is 255. A conjunct is a pair of conditions combined with an AND.

    WHERE F1 = 1 and F2 >= 2 and F2 = 5
    

    is 3 conjuctions, but:

    WHERE F1 = 1 or F2 >= 2 or F2 = 5
    

    is 1 conjunction

  • Maximum number of elements in an IN list is 1499.

    select name from phones where extension
        in (0000, 0001, 0002 ... 1499);