How much disk-space is needed to store a NULL value using postgresql DB?

sql postgresql types nullable
Chris picture Chris · Nov 19, 2010 · Viewed 12.1k times · Source

let's say I have a column on my table defined the following:

"MyColumn" smallint NULL

Storing a value like 0, 1 or something else should need 2 bytes (1). But how much space is needed if I set "MyColumn" to NULL? Will it need 0 bytes?

Are there some additional needed bytes for administration purpose or such things for every column/row?

(1) http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html

Answer

Erwin Brandstetter picture Erwin Brandstetter · Oct 4, 2011

Laramie is right about the bitmap and he links to the right place in the manual. Yet, this is almost, but not quite correct:

So for any given row with one or more nulls, the size added to it would be that of the bitmap(N bits for an N-column table, rounded up).

One has to factor in data alignment. The HeapTupleHeader (per row) is 23 bytes long, actual column data always starts at a multiple of MAXALIGN (typically 8 bytes). That leaves one byte of padding that can be utilized by the null bitmap. In effect NULL storage is absolutely free for tables up to 8 columns.

After that, another MAXALIGN (typically 8) bytes are allocated for the next MAXALIGN * 8(typically 64) columns. Etc. Always for the total number of user columns (all or nothing). But only if there is at least one actual NULL value in the row.

I ran extensive tests to verify all of that. More details:

Related questions

How to concatenate columns in a Postgres SELECT?

I have two string columns a and b in a table foo. select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried : select a || b from foo and select …

Read More
Return multiple fields as a record in PostgreSQL with PL/pgSQL

I am writing a SP, using PL/pgSQL. I want to return a record, comprised of fields from several different tables. Could look something like this: CREATE OR REPLACE FUNCTION get_object_fields(name text) RETURNS RECORD AS $$ BEGIN -- …

Read More
Auto increment table column

Using Postgres, I'm trying to use AUTO_INCREMENT to number my primary key automatically in SQL. However, it gives me an error. CREATE TABLE Staff ( ID INTEGER NOT NULL AUTO_INCREMENT, Name VARCHAR(40) NOT NULL, PRIMARY KEY (ID) ); The error: ********** …

Read More