What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?

Question

I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this fake table:

CREATE TABLE "tbl" (
    "id" serial,
    "name" varchar(40),
    "num" int,
    "time" timestamp
    PRIMARY KEY ("id"),
    UNIQUE ("id")
);

Also name, num, time are not explicitly stated as NOT NULL, in reality they are, because the enforcement happens on the application side.

---

My feeling is that it should be changed, but the counterpoint is that the application level makes sure that null values can't appear here and no one else manually modifies the table.

My question is: What are the benefits (performance, storage, consistency, something else) and drawbacks (assuming that I already verified that there are no nulls present at the moment, and from the business logic there should be no nulls) by setting an explicit NOT NULL constraint?

We have a good code review process and a reasonably good documentation, so the possibility that some new person would commit something that breaks this constraint is not really enough to justify the change.

This is not my decision, so this is exactly why I am looking for other justifications. In my opinion, if something can't be null and a database allows you to specify that something is not null - then just do it. Especially if the change is super simple.

Answer 1

What happens when a new programmer arrives and has to write an app against that db? They don't know that field x has to be NOT NULL.

Another programme might assume that all field x's are NOT NULL for performing counts say, but some now are NULL because of the new programme, leading to inconsistent and difficult to trace errors.

IMHO it is always best to enforce data integrity rules as near to the data as possible, i.e. in the database. That way, new apps and/or programmers can't mess up your data.

Programmers, applications, languages and frameworks come and go. Data and databases tend to persist. The database is your last line of defence against inconsistent, potentially erroneous data.

Make maximum use of your database's integrity constraint enforcement mechanisms, even at the expense of performance. A slow system that produces correct results is infinitely superior to a fast one that gets things wrong!

Answer 2

As already cited by others in comments, adding NOT NULL to your table specification can improve in a significant way the performances of your queries (in addition to the very good methodological reasons stated in another answer).

The reason is that the query optimizer, knowing that a column cannot have a NULL value, can exclude special tests for such values, like in the NOT IN vs. NOT EXISTS case. You can see for instance this blog, where it is shown that not declaring a field NOT NULL (when the table contains always non null values) with a certain query increases the execution time of 500%. The result is shown for SQL Server, but a similar behaviour could be present in other relational DBMSs, like yours (not to mention the fact that your database could be ported to other systems). A general rule that you can assume is that when more information is available to the query optimizer, then more efficient access plans can be produced.

Answer 3

Space implications

The space implications are talked about in this post by @Erwin Brandstetter

In short, you will save one totalColumns - 8 bits rounded up to the nearest byte (or MAXALIGN), if your database has

1. More than 8 columns
2. ALL columns on the table are NOT NULL

Performance implications

However, in this post on SE by @Erwin Brandstetter, he says

1. "Setting NOT NULL has no effect per se on performance. A few cycles for the check - irrelevant."
2. "... 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."

@Renzo has an answer that talks about the performance implications -- I would assume none of that is applicable to PostgreSQL. I can't find anything that substantiates any of that as being relevant to PostgreSQL. Whatever cycles are saved can not be quantified in even the most rudimentary query.

CREATE TABLE foo (
  a int,
  b int NOT NULL,
  x float,
  y float NOT NULL
);

INSERT INTO foo ( a, b, x, y )
SELECT x, x, x, x
FROM generate_series(1,1E7) AS X(x);

EXPLAIN ANALYZE SELECT 1/a FROM foo;
EXPLAIN ANALYZE SELECT 1/b FROM foo;
EXPLAIN ANALYZE SELECT 1/x FROM foo;
EXPLAIN ANALYZE SELECT 1/y FROM foo;

In addition I ran some tests to see if NULL-indexes were ever faster, and I couldn't substantiate that. You can find this awesomely useful thread by Scott Marlowe on the mailing lists which talks about the query planner in 9.1 being able to use partial index on dissimilar WHERE clauses. I tested this by running the following

CREATE TABLE foo ( a int );
CREATE TABLE bar ( a int NOT NULL );
INSERT INTO foo
  SELECT null FROM generate_series(1,1e5) AS x
  UNION ALL
  SELECT 10
  UNION ALL
  SELECT null FROM generate_series(1,1e5) AS x
;
INSERT INTO bar
  SELECT 0 FROM generate_series(1,1e5) AS x
  UNION ALL
  SELECT 10
  UNION ALL
  SELECT 0 FROM generate_series(1,1e5) AS x
;

Now I created the indexes,

CREATE INDEX foobar ON foo(a) WHERE a IS NOT NULL;
CREATE INDEX barbar ON bar(a) WHERE a <> 0;

In both of these cases the planner was able to use the index when selecting for = 10 and used a seq scan when searching for NULL or 0 respectively. Both partial indexes were the same size. And, full indexes (not shown) were the same size. Following the same methodology I loaded up the table with one sequence of 1..1e5, and the one null/0 value, and another sequence of 1..1e5. Both methods were able to find the null/0 with an index covering the whole table.

TLDR; Summary

I can't substantiate anything one way or another on most of the performance concerns that I thought were worth testing for including planner inadequacies. The benefit to using null to save ram is real. The disk space saved by not using null is negligible, and that's an overstatement on tables with one NULLABLE column, or fewer than 8 columns. In those cases there is no disk space saved.