Creating very quickly an composite partial index on 2 new columns with null values

Question

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?

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
– Florent2, Commented Jul 6, 2018 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 — user1822
– user1822, Commented Jul 6, 2018 at 5:45
"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
– Florent2, Commented Jul 6, 2018 at 8:01

jjanes · Accepted Answer · 2018-07-07 16:48:15Z

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.

answered Jul 7, 2018 at 16:48

jjanes

42.6k3 gold badges44 silver badges54 bronze badges

Add a comment |

Stack Exchange Network

Creating very quickly an composite partial index on 2 new columns with null values

1 Answer 1

Your Answer

Hot Network Questions

Creating very quickly an composite partial index on 2 new columns with null values

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions