How Much Disk Space Do NULL Values in PostgreSQL Really Consume?

Impact of NULL Values on PostgreSQL Disk Space Consumption

In PostgreSQL, the storage requirements for columns that allow NULL values differ from those that enforce values. Consider the following table column definition:

"MyColumn" smallint NULL

Storing integers such as 0, 1, or any other value requires 2 bytes. However, the question arises: how much disk space is consumed when the "MyColumn" is set to NULL? Does it occupy 0 bytes?

Bitmap vs. NULL Storage

Contrary to the initial assumption of 0 byte consumption, NULL values do not take up no space. Instead, PostgreSQL employs a bitmap to track NULL status for each column in a table. Each bit in the bitmap represents the NULL/NOT NULL status for a specific row.

However, the bitmap is not allocated for every row. Instead, data alignment is a factor to consider. The HeapTupleHeader, which includes information about each row, occupies 23 bytes. Data is aligned to start at multiples of MAXALIGN (typically 8 bytes), leaving one byte of padding for the null bitmap.

Disk Space Requirements

As a result of these factors, null storage is effectively free for tables with up to 8 columns. However, for tables with more columns, additional disk space is required as follows:

• Additional MAXALIGN bytes (typically 8) are allocated for the next group of 64 columns.
• Space is allocated only if at least one actual NULL value is present in the row.
• Dropped columns marked as such in the system catalog still occupy a bit in the null bitmap.

Real-World Testing

Extensive testing has confirmed these observations. For further details, refer to the discussion at:

• https://dba.stackexchange.com/questions/4452/does-not-using-null-in-postgresql-still-use-a-null-bitmap-in-the-header

The above is the detailed content of How Much Disk Space Do NULL Values in PostgreSQL Really Consume?. For more information, please follow other related articles on the PHP Chinese website!