i love this question.
what is null ?
something that is missing right ? haha.
but then the real question starts.
how do you store "nothing" inside a database ?
you cannot put "nothing" inside a 32 bit integer column by storing 0.
because 0 is still a value.
and storing another magic number also breaks logic because every possible integer might already be a valid value.
so how does postgres handle it ?
postgres does something smart.
it does not store a fake value for null.
instead it keeps a small null bitmap for every row.
basically a tiny map that says:
this column has value
this column has no value (null)
1 bit per column.
8 columns = 1 byte.
more than 8 columns ? another byte gets added.
so the actual integer space remains untouched.
the database simply knows whether data exists or not.
and funny thing...
many people avoid null thinking: "extra storage man "
but the storage cost is tiny compared to the flexibility and correctness it gives.
nulls are powerful.
but also naughty 😂
they break normal logic because null means "unknown".
so comparisons become weird.
5 = null → not true
5 != null → also not true
even this gets dangerous:
NOT IN with a single null
can suddenly return nothing.
that is why sql has three-valued logic:
true
false
unknown
and indexing nulls ?
postgres can even index rows containing nulls.
you can create partial indexes like:
"only index rows where deleted_at is null"
which becomes insanely useful for:
soft deletes
pending jobs
unprocessed events
active users
so yeah ! null is not an empty value ! it is the database saying: "i genuinely do not know what belongs here."