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

by

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) 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:

More Related Contents:

  1. Auto increment table column
  2. Any downsides of using data type “text” for storing strings?
  3. How to concatenate columns in a Postgres SELECT?
  4. Time zone storage in data type “timestamp with time zone”
  5. Change PostgreSQL columns used in views
  6. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  7. Casting NULL type when updating multiple rows
  8. PostgreSQL ORDER BY issue – natural sort
  9. How to store array or multiple values in one column
  10. Fetch records that are non zero after the decimal point in PostgreSQL
  11. Does PostgreSQL support “accent insensitive” collations?
  12. PostgreSQL DISTINCT ON with different ORDER BY
  13. PostgreSQL: running count of rows for a query ‘by minute’
  14. Split comma separated column data into additional columns
  15. IN vs ANY operator in PostgreSQL
  16. How to delete duplicate entries?
  17. Transpose latest rows per user to columns
  18. subquery in FROM must have an alias
  19. Compute percents from SUM() in the same SELECT sql query
  20. SQL Between clause with strings columns
  21. How to find a table having a specific column in postgresql
  22. postgresql: INSERT INTO … (SELECT * …)
  23. PostgreSQL JOIN with array type with array elements order, how to implement?
  24. Trim trailing spaces with PostgreSQL
  25. Return multiple columns and rows from a function PostgreSQL instead of record
  26. Postgres error updating column data
  27. Check whether string is a date Postgresql
  28. SQL LIKE condition to check for integer?
  29. How to detect query which holds the lock in Postgres?
  30. PostgreSQL: top n entries per item in same table

Leave a Comment