How Much Disk Space Does a NULL Value Actually Consume in PostgreSQL?

Understanding Null Value Storage in PostgreSQL Databases

When working with databases, it's essential to optimize storage space for efficient performance. One common question arises when dealing with NULL values: how much disk space is required to store a NULL value?

In PostgreSQL, the storage of NULL values differs depending on the context. Let's consider the example given where the column "MyColumn" is defined as "smallint NULL." In this case, storing a value like 0 or 1 would generally require 2 bytes.

Storing NULL Values

When "MyColumn" is set to NULL, the storage requirement becomes more nuanced. Contrary to intuition, NULL values do not occupy 0 bytes. Instead, PostgreSQL uses a technique called bitmap indexing to track nulls. For each row in a table, a bitmap is created to mark which columns contain NULL values.

Now, let's break down the storage implications of this approach:

• Tables with Less Than 8 Columns: For tables with up to 8 columns, the null bitmap is stored in the row's header, utilizing an otherwise unused byte of padding. Therefore, storing NULL values in such tables has virtually no additional space overhead.
• Tables with 8 or More Columns: For tables with 8 or more columns, the null bitmap requires additional space of MAXALIGN bytes (usually 8 bytes). This space is allocated for every row, regardless of whether any columns contain NULL values.

Additional Considerations

• Alignment: Row data must align with specific boundaries to optimize memory access. If the null bitmap doesn't fit into the remaining padding space, additional alignment bytes may be added, potentially increasing storage requirements.
• Dropped Columns: Even columns that have been dropped from a table's definition may still be reflected in the null bitmap, occupying a bit of space. VACUUM FULL cannot remove this space, requiring a dump/restore cycle to reclaim it.

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