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.
NOT NULL
constraints do not have any direct effect on storage size. Of course, with all columns being definedNOT NULL
, there cannot be a null bitmap to begin with. On the other hand: storage size is typically much smaller if you use NULL instead of "empty" or dummy values for columns without actual value, because the null bitmap is comparatively much smaller (except for rare edge cases).