Do nullable columns occupy additional space in PostgreSQL?

by

Basically, NULL values occupy 1 bit in the NULL bitmap. But it’s not that simple.

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. Conversely, removing the last NULL value from the row also removes the NULL bitmap.

Physically, the initial null bitmap occupies 1 byte between the HeapTupleHeader (23 bytes) and actual column data or the row OID (if you should still be using that) – which always start at a multiple of MAXALIGN (typically 8 bytes). This leaves 1 byte of padding that is utilized by the initial null bitmap.

In effect, NULL storage is absolutely free for tables of 8 columns or less (including dropped, but not yet purged columns).
After that, another MAXALIGN bytes (typically 8) are allocated for the next MAXALIGN * 8 columns (typically 64). Etc.

More details in the manual and under these related questions:

Once you understand alignment padding of data types, you can further optimize storage:

But the cases are rare where you can save substantial amounts of space. Normally it’s not worth the effort.

@Daniel already covers effects on index size.

Note that dropped columns (though now invisible) are kept in the system catalogs until the table is recreated. Those zombis can force the allocation of an (enlarged) NULL bitmap. See:

More Related Contents:

  1. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  2. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  3. Does a Postgres UNIQUE constraint imply an index?
  4. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  5. How to add a conditional unique index on PostgreSQL
  6. PostgreSQL LIKE query performance variations
  7. Calculating and saving space in PostgreSQL
  8. Create unique constraint with null columns
  9. What to do with null values when modeling and normalizing?
  10. Store common query as column?
  11. Making sense of Postgres row sizes
  12. Why does PostgreSQL perform sequential scan on indexed column?
  13. CONSTRAINT to check values from a remotely related table (via join etc.)
  14. Grant all on a specific schema in the db to a group role in PostgreSQL
  15. Difference between GiST and GIN index
  16. Test for null in function with varying parameters
  17. Postgres query optimization (forcing an index scan)
  18. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  19. Optimize Postgres query on timestamp range
  20. Multicolumn index on 3 fields with heterogenous data types
  21. PostgreSQL Index on JSON
  22. PostgreSQL – set a default cell value according to another cell value
  23. PostgreSQL composite primary key
  24. Sort by column ASC, but NULL values first?
  25. quick random row selection in Postgres
  26. How to persist data in a dockerized postgres database using volumes
  27. Strange behaviour in Postgresql
  28. Cursor based records in PostgreSQL
  29. PostgreSQL disable more output
  30. Syntax error at end of input in PostgreSQL

Leave a Comment