5

I want to change a column to NOT NULL:

ALTER TABLE "foos" ALTER "bar_id" SET NOT NULL

The "foos" table has almost 1 000 000 records. It does fairly low volumes of writes, but quite constantly. There are a lot of reads.

In my experience, changing a column in a big table to NOT NULL like this can cause downtime in the app, presumably because it leads to (b)locks.

I've yet to find a good explanation corroborating this, though.

And if it is true, what can I do to avoid it?

EDIT: The docs (via this comment) say:

Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten.

I'm not sure if changing NULL counts as "changing the type of an existing column", but I believe I did have an index on the column the last time I saw this issue.

Perhaps removing the index, making the column NOT NULL, and then adding the index back would improve things?

Community
  • 1
  • 1
Henrik N
  • 15,786
  • 5
  • 82
  • 131
  • Found this on DBA Stack Exchange: http://dba.stackexchange.com/questions/66840/add-not-null-constraint-to-large-table-without-table-scan At the time of my writing this, they're basically saying there isn't a way, though you can do other things like use CONSTRAINTs (also suggested by @a_horse_with_no_name below). – Henrik N Feb 06 '17 at 15:20
  • It looks like one of the answers was removed for some reason. It pointed out how [the docs](https://www.postgresql.org/docs/current/static/sql-altertable.html) say that an "ACCESS EXCLUSIVE lock is held unless explicitly noted." (And nothing else is explicitly noted.) This is a full-table lock of both reads and writes, as I understand it. That answer also said that there will be a sequence scan of the entire table to ensure all columns are NULL. I guess these two things together (slow scan, full-table lock) explain the issues. – Henrik N Feb 06 '17 at 16:15
  • In postgres 11 this cnstraint has been removed. – dland Nov 14 '18 at 14:46
  • @dland Thanks! Do you mean that the CONSTRAINT solution is no longer possible, or that changing to NOT NULL is no longer dangerous? EDIT: Possibly the latter? "… PostgreSQL would rewrite the whole table, which on larger tables in active systems could cause a cascade of problems. PostgreSQL 11 removes the need to rewrite the table in most cases, and as such running ALTER TABLE .. ADD COLUMN .. DEFAULT .. will execute extremely quickly." https://www.postgresql.org/about/news/1855/ – Henrik N Nov 14 '18 at 19:08
  • 1
    Yes, in Pg 11, `SET NOT NULL` no longer requires a table rewrite. If you query a "empty" column when NOT NULL is specified, the engine will pull the missing default value from a new system catalog, thereby honoring the not null constraint. Before, the row had to be filled in with the default value on the new column, because there was no fallback in place to look elsewhere. They have basically solved a problem with another level of indirection. – dland Nov 22 '18 at 13:59

1 Answers1

5

I think you can do that using a check constraint rather then set not null. 

ALTER TABLE foos 
     add constraint id_not_null check (bar_id is not null) not valid;

This will still require an ACCESS EXCLUSIVE lock on the table, but it is very quick because Postgres doesn't validate the constraint (so it doesn't have to scan the entire table). This will already make sure that new rows (or changed rows) can not put a null value into that column

Then (after committing the alter table!) you can do:

alter table foos validate constraint id_not_null;

Which does not require an ACCESS EXCLUSIVE lock and still allows access to the table.

  • Thank you. I just stumbled across a sister question on another Stack Exchange where they also [mention this](http://dba.stackexchange.com/a/159499) – seems like a reasonable workaround, if one is fine with mixing NOT NULL and CONSTRAINTs. – Henrik N Feb 06 '17 at 15:23