Firebird – Handling Table and Null Data

firebirdnull

I'm working with Firebird database. I have a big table with many thousand of rows. Each line records the status of an object at regular intervals for many objects. I need to register new properties for one object only.
I think I have 2 possibilities:

  1. add the new columns to the table even if I have a lot of null values
  2. create a table with the only properties that interest me and bind it appropriately to the first

which solution is the best for not having a loss of performance?

Best Answer

Adding a new column to a table will not modify existing data. Records in the database are versioned with a so-called format version at the time the record was written. This format version describes the columns (and other column related metadata).

If you alter a table to add a column, then a new format version is added. When reading records, Firebird will read the row using the original format version, and then - in memory - 'upgrade' the row to the latest format version, eg adding new columns with a null value (or the default value), removing columns that have been dropped, or changing sizes, etc.

In other words, adding columns is cheap. There is one exception: since Firebird 3, adding a column with a NOT NULL-constraint if there are existing records will require a default value, otherwise the addition fails. In earlier versions this would silently add a NOT NULL column containing NULL for existing records, leaving fixing of the data for those rows to the database owner.

Regarding storage itself, Firebird uses a simple form of RLE compression to compress records on disk. Null values written using the new format version do take up some space because of this, but max 2 bytes for most types, and multiples of 2 bytes for column types longer than 127 bytes. As an extreme form of optimization, it might make sense to group columns that are usually NULL together (but the performance benefit is likely very small).

Regarding runtime performance, since Firebird 3 (and assuming a client that supports wire protocol 13 or higher), columns that are NULL are skipped in the wire protocol (meaning that no data is sent for that column), which reduce the number of bytes sent over the network.

Related Question