I'm adding a new column (bigints, initial values all null), to a large (~5 billion rows) Postgres (9.6) table.

I want to add an index on the row (algorithm: concurrently), and I'm wondering if there's a way I can save it the time of scanning through all five billion null fields to add them to the index, since I'm adding the index when the column is brand new and has no data. Is there any kind of optimization for this, or should I just let it run? (Is it even theoretically possible for there to be an optimization, or does it need to add something to the index even for null fields?)

1 Answer 1


No, sorry.

PostgreSQL generally indexes NULL fields, so would it would have to add an entry for each row. You can make an index not index NULLs (using a partial index, or using a non-default type of index which doesn't index NULLs), but it is not implemented for such an index to observe an all-NULL constraint on the column and so take a shortcut of not scanning the table. It would uselessly scan the table looking for not-NULLs anyway. Interesting idea though.

  • 1
    Nice. A partial index with WHERE (new_column IS NOT NULL) will of course be created much faster than a full index, as it'll write almost nothing. Apr 11, 2019 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.