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

Disk Space Requirements for NULL Values in PostgreSQL

When creating a database table, it's important to consider the storage requirements of its columns, especially when dealing with nullable values. Understanding how NULL values are stored can optimize disk space usage.

Storage of NULL Values

In PostgreSQL, NULL values are represented using a bitmask, known as a null bitmap. Each bit in the bitmap corresponds to a column in the table. If a bit is set to 1, it indicates that the corresponding column is NULL.

Disk Space Occupied by NULL Bitmaps

The size of the null bitmap depends on the number of columns in the table. For tables with 8 or less columns, the bitmap occupies 1 byte. For tables with 9 to 64 columns, it occupies 2 bytes, and so on. However, due to data alignment, the minimum storage requirement for a row containing at least one NULL value is 1 byte, regardless of the number of columns.

Overhead for NULL Columns

Besides the null bitmap, there are no additional bytes required for managing NULL columns. However, dropped columns marked as "dropped" in the system catalog pg_attribute still occupy one bit in the null bitmap. This overhead can persist until a dump/restore cycle is performed.

Example and Test Results

Consider a table with the following column definition:

"MyColumn" smallint NULL

Storing a value of 0 or 1 in this column would require 2 bytes. For NULL values, the following storage requirements apply:

• For tables with 8 or less columns:

  • No additional disk space

• For tables with 9 to 64 columns:

  • 1 byte for the null bitmap

• For tables with more than 64 columns:

  • Multiple bytes for additional MAXALIGNs

Extensive testing shows that these storage requirements are consistent for all tables. Optimizing disk space usage by storing NULL values in small tables (less than 9 columns) is beneficial, while it becomes less significant in larger tables.

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