Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

Apparently PostgreSQL stores a couple of values in the header of each database row.

If I don't use NULL values in that table - is the null bitmap still there?
Does defining the columns with NOT NULL make any difference?

like image 224
Xeoncross Avatar asked Feb 15 '11 19:02

Xeoncross


People also ask

What does the not null constraint do in PostgreSQL?

The not-null constraint in PostgreSQL ensures that a column can not contain any null value. This is a column constraint. No name can be defined to create a not-null constraint. This constraint is placed immediately after the data-type of a column.

What is null bitmap?

The NULL bitmap mask encodes at the Storage Engine level which column stores a NULL value and which not. It's a simple bitmap mask, where a set bit (1) means that the column has a NULL value, and a not set bit (0) means that the column has an actual value (not a NULL value).

IS null is not null in Postgres?

In PostgreSQL, NULL means no value. In other words, the NULL column does not have any value. It does not equal 0, empty string, or spaces. The NULL value cannot be tested using any equality operator like “=” “!=

Does NULL value occupy space in Postgres?

The null bitmap (per row) is only allocated if at least one column in that row holds a NULL value. This can lead to a seemingly paradoxic effect in tables with 9 or more columns: assigning the first NULL value to a column can take up more space on disk than writing a value to it.


2 Answers

It's actually more complex than that.

The null bitmap needs one bit per column in the row, rounded up to full bytes. It is only there if the actual row includes at least one NULL value and is fully allocated in that case. NOT NULL constraints do not directly affect that. (Of course, if all fields of your table are NOT NULL, there can never be a null bitmap.)

The "heap tuple header" (per row) is 23 bytes long. Actual data starts at a multiple of MAXALIGN (Maximum data alignment) after that, which is typically 8 bytes on 64-bit OS (4 bytes on 32-bit OS). Run the following command from your PostgreSQL binary dir as root to get a definitive answer:

./pg_controldata /path/to/my/dbcluster

On a typical Debian-based installation of Postgres 12 that would be:

sudo /usr/lib/postgresql/12/bin/pg_controldata /var/lib/postgresql/12/main

Either way, there is one free byte between the header and the aligned start of the data, which the null bitmap can utilize. As long as your table has 8 columns or less, NULL storage is effectively absolutely free (as far as disk space is concerned).

After that, another MAXALIGN (typically 8 bytes) is allocated for the null bitmap to cover another (typically) 64 fields. Etc.

This is valid for at least versions 8.4 - 12 and most likely won't change.

like image 160
Erwin Brandstetter Avatar answered Oct 17 '22 15:10

Erwin Brandstetter


The null bitmap is only present if the HEAP_HASNULL bit is set in t_infomask. If it is present it begins just after the fixed header and occupies enough bytes to have one bit per data column (that is, t_natts bits altogether). In this list of bits, a 1 bit indicates not-null, a 0 bit is a null. When the bitmap is not present, all columns are assumed not-null.

http://www.postgresql.org/docs/9.0/static/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE

so for every 8 columns you use one byte of extra storage. Then for every about million rows that would take up one megabyte of storage. Does not really seem that important. I would define the tables how they needed to be defined and not worry about null headers.

like image 42
nate c Avatar answered Oct 17 '22 14:10

nate c