Does setting “NOT NULL” on a column in postgresql

Question

I know this is a good idea in MySQL. If I recall correctly, in MySQL it allows indexes to work more efficiently.

Answer 1

No, as long as you don't actually store NULLs in the table the indexes will look exactly the same (and equally efficient).

Setting the column to NOT NULL has a lot of other advantages though, so you should always set it to that when you don't plan to store NULLs in it :-)

Answer 2

It's always a good ideal to keep columns from being NULL if you can avoid it, because the semantics of using are so messy; see What is the deal with NULLs? for good a discussion of how those can get you into trouble.

In versions of PostgreSQL up to 8.2, the software didn't know how to do comparisons on the most common type index (the b-tree) in a way that would include finding NULL values in them. In the relevant bit of documentation on index types, you can see that described as "but note that IS NULL is not equivalent to = and is not indexable". The effective downside to this is that if you specify a query that requires including NULL values, the planner might not be able to satisfy it using the obvious index for that case. As a simple example, if you have an ORDER BY statement that could be accelerated with an index, but your query needs to return NULL values too, the optimizer can't use that index because the result will be missing any NULL data--and therefore be incomplete and useless. The optimizer knows this, and instead will do an unindexed scan of the table instead, which can be very expensive.

PostgreSQL improved this in 8.3, "an IS NULL condition on an index column can be used with a B-tree index". So the situations where you can be burned by trying to index something with NULL values have been reduced. But since NULL semantics are still really painful and you might run into a situation where even the 8.3 planner doesn't do what you expect because of them, you should still use NOT NULL whenever possible to lower your chances of running into a badly optimized query.

Answer 3

Setting NOT NULL has no effect per se on performance. A few cycles for the check - irrelevant.

But you can improve performance by actually using NULLs instead of dummy values. Depending on data types, you can save a lot of disk space and RAM, thereby speeding up .. everything.

The null bitmap is only allocated if there are any NULL values in the row. It's one bit for every column in the row (NULL or not). For tables up to 8 columns the null bitmap is effectively completely free, using a spare byte between tuple header and row data. After that, space is allocated in multiples of MAXALIGN (typically 8 bytes, covering 64 columns). The difference is lost to padding. So you pay the full (low!) price for the first NULL value in each row. Additional NULL values can only save space.

The minimum storage requirement for any non-null value is 1 byte (boolean, "char", ...) or typically much more, plus (possibly) padding for alignment. Read up on data types or check the gory details in the system table pg_type.

More about null storage:

• Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
• The manual.