Various InterBase Limits

From InterBase
Jump to: navigation, search

Go Up to InterBase Limits


InterBase Specifications
Characteristic Value

Maximum number of clients connected to one server

There is no single number for the maximum number of clients the InterBase server can serve – it depends on a combination of factors including capability of the operating system, limitations of the hardware, and the demands that each client puts on the server. Applications that engage in high levels of contention or that perform complex or high-volume operations could cause the practical number of clients to be fewer. In applications that do not generate much contention, InterBase can support a large number of users, where “large” is not well-defined.

Maximum database size

No limit is imposed by InterBase; maximum size is defined by the operating system.

Limit depends on database page size:

  • 1KB page size: largest DB is 2TB
  • 2KB page size: largest DB is 4TB
  • 4KB page size: largest DB is 8TB
  • 8KB page size: largest DB is 16TB
  • 16KB page size: largest DB is 32TB

Limits for a database with 254 secondary tablespaces.

  • 1KB page size: largest DB is 510TB
  • 2KB page size: largest DB is 1PB
  • 4KB page size: largest DB is 2PB
  • 8KB page size: largest DB is 4PB
  • 16KB page size: largest DB is 8PB


Maximum number of tablespaces

  • The maximum number of tablespaces is 254, not including the PRIMARY tablespace contained by the main database file(s).

Maximum number of files
per database

By design, 32,767 + 254(1 file per secondary tablespace) , because we use a signed 16-bit integer. Shadow files count toward this limit.

This is a design parameter of InterBase, but most operating systems have a much lower limit on the number of files that a single process can have open simultaneously. In some cases, the OS provides a means to raise this limit. Refer to your OS documentation for the default open files limit, and the means to raise this limit.

Maximum number of cache pages per database

750,000. Not all database page sizes will be able to accommodate this limit in a 32-bit address space. When applying a large cache, other considerations must be taken into account such as the number of connections, statements or other database using memory at the same time. A large cache size will depend on whether a 32-bit executable is running on a 64-bit OS or how a 32-bit OS has been configured.

This number depends on system memory, OS, InterBase version and DB page size:

  • 750,000 pages for 32-bit InterBase
  • 75,000,000 pages for 64-bit InterBase

Maximum number of databases open in one transaction

No restriction. The parameters in a transaction parameter buffer comprise a linked list, so there is no limit except that imposed by system resources.

Maximum number of tables
per database

32,640

Maximum table size

Limit depends on database page size. Note that the total size for all tables cannot exceed maximum database size.

  • 1KB page size: largest table is 2TB
  • 2KB page size: largest table is 4TB
  • 4KB page size: largest table is 8TB
  • 8KB page size: largest table is 16TB
  • 16KB page size: largest table is 32TB

Maximum versions per table

255; then no more metadata changes until the database has been backed up and restored.

Maximum row size

64KB. Each Blob and array contributes eight bytes to this limit in the form of their Blob handle.

Systems tables (tables maintained by the InterBase engine for system data) have a row size limit of 128KB.

Maximum number of rows and columns per table

By design, 264 rows, are enumerated with a 64-bit unsigned integer per table, current implementation limits the numbe rof rows to 240. Number of columns in a row depends on data types used. One row can be 64K. For example, you can define 16,384 columns of type INTEGER (four bytes each) in one table.

Depends on row characteristics and compression, but as many rows as can be stored in maximum table size. The highest row number is 2**38 - 1 (274,877,906,943).

Maximum number of indexes per table

It is possible to create 255 indexes on a single table in InterBase.

A larger DB page size always enables more index definitions than smaller DB page sizes. If your DB page size is not sufficient, you will receive the error “cannot add index, index root page is full.”

Maximum number of indexes per database

By design, 232, because you can create 216 tables per database, and each table can have 216 indexes.

Maximum number of tables x Maximum number of indexes per table
32,640 x 64 = 2,089,960

Maximum index key size

With the ODS 15 databases the maximum index key size limit is increased. Now larger column data can use this for both single-byte character sets and multi-byte (such as UTF8) columns.

Because InterBase supports UTF8 and multiple other multi-byte character sets, the limit has been increased. For example, a single-column key using 4-byte UTF8 character would calculate to 1020/4 = 254 UTF8 characters with a 4KB page size.

  • ODS 15 and above databases automatically allow index definitions where the underlying key size is now a factor of the database page size. An index key can now be up to 4 bytes less than 1/4th the page size.
  • By default, InterBase databases are created with a 4KB page size. This can be overridden up to 16KB page size by the database developer.
  • The 4KB page size database would allow indexes that can accommodate 1020 bytes per key.
  • A 16KB page size can accommodate a 4092 bytes per key and so on.

Caution: Databases created with engines enabled with this functionality cannot be moved back to older versions of InterBase.

Also a database restore to a smaller page size will fail if indexes with large key size cannot fit within the limit specified above.

No user interface or actions are required by the user to enable this functionality. Each time a database restore is performed, the indices are recreated.

Only ODS 15 and later databases have support for larger index keys. If you want to use this facility, restore your database to ODS 15. Other indices that use a smaller size than 252 bytes continue to have the same on-disk storage without any penalty.

Note that multibyte character sets must fit within the key by counting bytes, not by counting characters.

It is good practice to keep index keys as small as possible. This limits the depth of indexes and increases their efficiency.

Maximum number of events
per stored procedure

No restriction by design, but there is a practical limit, given that there is a limit on the length of code in a stored procedure or trigger (see below).

Maximum stored procedure
or trigger code size

48KB of BLR, the bytecode language compiled from stored procedure or trigger language.

Maximum Blob size

The size of the largest single Blob depends on the database page size:
1KB page size: largest Blob is 64MB
2KB page size: largest Blob is 512MB
4KB page size: largest Blob is 4GB
8KB page size: largest Blob is 32GB
16KB page size: largest Blob is 256GB

A Blob is a stream of many segments. The maximum Blob segment size is 64KB.

Maximum tables in a JOIN

No restriction by design, but the task of joining tables is exponential in relation to number of tables in the join.

The largest practical number of tables in a JOIN is about 16, but experiment with your application and a realistic volume of data to find the most complex join that has acceptable performance.

Maximum levels of nested queries

There is no restriction by design.

The practical limit depends on the type of queries you nest. Experiment with your application and a realistic volume of data to find the deepest nested query that has acceptable performance.

Maximum number of columns per one composite index

16

Levels of nesting
per stored procedure or trigger

  • 750 on Windows platforms
  • 1000 for UNIX platforms

Maximum size of key
in SORT clause

32 KB

Maximum size of external table file

64-bit file offset allows up to 16 Exabytes of information.

Range of date values

January 1, 100 a.d. to February 29, 32768 a.d.

Transaction Limits

  • Databases with ODS <=15 need to be backed up and restored before they hit the 2 Billion transaction ID limit.
  • Databases with ODS >=16 can continue to be online beyond 2 billion transactions since they support 64-bit Transaction ID. A benefit of this is you can have your databases online to service your applications closer to a 24/7 scenario without having to backup/restore due to this earlier 32-bit limit.

Maximum number of generators per database

  • The maximum number of generators in a database is 32,767. However, the number of user defined generators is lower because system tables use some generator IDs for internal use from the same ID namespace.

Back To: