Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does setting "NOT NULL" on a column in postgresql increase performance?

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

like image 765
ryeguy Avatar asked Aug 03 '09 13:08

ryeguy


People also ask

IS NOT NULL SQL performance?

NOT NULL vs NULL performance is negligible and as per this article from 2016 (SQL SERVER), performance shouldn't be a consideration when deciding NOT NULL vs NULL. Even though that field will default to 'N', a command could still set it to NULL if nulls were allowed.

Can NULLs improve your database queries performance?

that NULLs can potentially speed up your research because the index will have fewer rows. you can still index the NULL rows if you add another NOT NULL column to the index or even a constant.

What does the not null constraint do in PostgreSQL?

The not-null constraint in PostgreSQL ensures that a column can not contain any null value. This is a column constraint. No name can be defined to create a not-null constraint. This constraint is placed immediately after the data-type of a column.

When should a column be not null?

The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.


2 Answers

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.
like image 92
Erwin Brandstetter Avatar answered Oct 16 '22 13:10

Erwin Brandstetter


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.

like image 45
Greg Smith Avatar answered Oct 16 '22 11:10

Greg Smith