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

by

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.

More Related Contents:

  1. Calculating and saving space in PostgreSQL
  2. Making sense of Postgres row sizes
  3. Do nullable columns occupy additional space in PostgreSQL?
  4. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  5. Create unique constraint with null columns
  6. What to do with null values when modeling and normalizing?
  7. Store common query as column?
  8. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  9. CONSTRAINT to check values from a remotely related table (via join etc.)
  10. Grant all on a specific schema in the db to a group role in PostgreSQL
  11. Does a Postgres UNIQUE constraint imply an index?
  12. Test for null in function with varying parameters
  13. How to add a conditional unique index on PostgreSQL
  14. PostgreSQL – set a default cell value according to another cell value
  15. How to exit from PostgreSQL command line utility: psql
  16. How to perform update operations on columns of type JSONB in Postgres 9.4
  17. See and clear Postgres caches/buffers?
  18. How to close idle connections in PostgreSQL automatically?
  19. postgresql – replace all instances of a string within text field
  20. Is it possible to have autoincrement number without it being an ID?
  21. PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
  22. function returns multiple columns as a single column instead of multiple columns
  23. Sequences not affected by transactions?
  24. Postgres: check if array field contains value?
  25. Dynamic pivot query using PostgreSQL 9.3
  26. Find Parent Recursively using Query
  27. Error: Column does not exist in postgresql for update [duplicate]
  28. How can I ensure that a materialized view is always up to date?
  29. Convert escaped Unicode character back to actual character in PostgreSQL
  30. postgresql error PANIC: could not locate a valid checkpoint record

Leave a Comment