CODEWITHSUNDEEP
sqlpostgresqltypesnullable
Chris
Chris

Reputation: 4505

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

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

Upvotes: 71

Views: 23238

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658202

Laramie is right about the bitmap and 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) occupies 23 bytes, 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.

Notably, dropped columns still listed in the system catalog pg_attribute (marked as dropped) also occupy one bit in the null bit map. Not even VACUUM FULL can get rid of this residue, only a dump/restore cycle does. (So beware of anti-patterns that would add and drop table columns routinely.)

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

Upvotes: 97

Laramie
Laramie

Reputation: 5587

Null columns are not stored. The row has a bitmap at the start and one bit per column that indicates which ones are null or non-null. The bitmap could be omitted if all columns are non-null in a row. 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).

More in depth discussion from the docs here

Upvotes: 84

J V
J V

Reputation: 11936

It should need 1 byte (0x00) however it's the structure of the table that makes up most of the space, adding this one value might change something (Like adding a row) which needs more space than the sum of the data in it.

Edit: Laramie seems to know more about null than me :)

Upvotes: -1

Related Questions