3

I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns.

CREATE UNIQUE INDEX CONCURRENTLY index_users_on_col1_and_col2 ON public.users USING btree (col1, col2) WHERE ((col1 IS NOT NULL) AND (col2 IS NOT NULL));

I need that to be done concurrently to avoid writes lock. The 2 new columns will start having value only a few days after I add them and I add the index.

So basically the index will be empty when creating.

Is there a way to create the index by "saying" to PostgreSQL it should create the index directly empty and not scan the table?

  • CREATE INDEX CONCURRENTLY? – a_horse_with_no_name Jul 6 '18 at 5:27
  • Yes I need to create the index concurrently to avoid write locks (I've updated my question), but that would still do a table scan. I want to avoid entirely the table scan. – Florent2 Jul 6 '18 at 5:39
  • You can not avoid the table scan. How should the index be populated if the data isn't scanned? What is the point of having an empty index? It can't be used for anything, so creating it doesn't make sense to begin with – a_horse_with_no_name Jul 6 '18 at 5:45
  • 1
    "How should the index be populated if the data isn't scanned" -> at the time I create the index I know the index will be empty as no records will satisfy the index clause. "What is the point of having an empty index? It can't be used for anything, so creating it doesn't make sense to begin with" -> it won't be empty once records start to be created with values that satisfy the index clause. – Florent2 Jul 6 '18 at 8:01
1

There is no built-in way to do this. If you are willing to compile your own PostgreSQL binaries it might be relatively easy to implement this. This part of the code gets whacked around a bit between versions to add new features and performance improvements, so you would have to target a specific version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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