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

Disk Space Required to Store NULL Values in PostgreSQL

When defining a column as a nullable smallint, the assumption is that storing values like 0 or 1 would require 2 bytes. However, the question arises: how much space is occupied when the column is set to NULL?

Storage Optimization

In PostgreSQL, NULL values do not require any dedicated storage space. Instead, a bitmap is used to indicate which columns in a row contain NULL values. For any row with one or more nulls, a bitmap of the appropriate size (rounded up to the nearest byte) is added to the row's header.

Data Alignment Considerations

Data alignment plays a crucial role in memory management. In PostgreSQL, the HeapTupleHeader (row header) occupies 23 bytes, and column data is always aligned to multiples of MAXALIGN (typically 8 bytes). Consequently, one byte of padding exists within the row header that can be utilized by the null bitmap.

Free Storage for Small Tables

Due to the aforementioned data alignment, tables with up to 8 columns can store NULL values absolutely free. Beyond this limit, additional MAXALIGN bytes are allocated for the next 64 columns, and so on. However, this additional space is only occupied if there are actual null values in the row.

Dropped Columns and Null Bitmaps

Even when columns are dropped from a table, they continue to occupy a bit in the null bitmap if they remain listed in the pg_attribute system catalog. As a result, dropped columns can contribute to the space needed for the null bitmap, which can persist even after VACUUM FULL operations.

Conclusion

NULL values in PostgreSQL are stored efficiently using a bitmap mechanism. However, data alignment and the number of columns in a table can influence the amount of disk space required to store these values. Understanding these optimization techniques can help optimize database performance and minimize storage requirements.

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