No problem. First, use CREATE INDEX CONCURRENTLY to create the index on each partition. Then use CREATE INDEX to create the index on the partitioned table. That will be fast, and the indexes on the partitions will become the partitions of the index.
No problem. First, use CREATE INDEX CONCURRENTLY to create the index on each partition. Then use CREATE INDEX to create the index on the partitioned table. That will be fast, and the indexes on the partitions will become the partitions of the index.
Step 1: Create an index on the partitioned (parent) table
CREATE INDEX foo_idx ON ONLY foo (col1, col2, col3);
This step creates an invalid index. That way, none of the table partitions will get the index applied automatically.
Step 2: Create the index for each partition using CONCURRENTLY and attach to the parent index
CREATE INDEX CONCURRENTLY foo_idx_1
ON foo_1 (col1, col2, col3);
ALTER INDEX foo_idx
ATTACH PARTITION foo_idx_1;
Repeat this step for every partition index.
Step 3: Verify that the parent index created at the beginning (Step 1) is valid. Once indexes for all partitions are attached to the parent index, the parent index is marked valid automatically.
SELECT * FROM pg_index WHERE pg_index.indisvalid = false;
The query should return zero results. If thats not the case then check your script for mistakes.
Hello experts,
We have a few tables having size ~5TB and are partitioned on a timestamp column. They have ~90 partitions in them and are storing 90 days of data. We want to create a couple of indexes on those tables. They are getting the incoming transactions(mainly inserts) 24/7 , which are mostly happening on the current day/live partition. Its RDS postgres version 15.4. So in this situation
Should we go with below i.e one time create index command on the table..
CREATE INDEX CONCURRENTLY idx1 ON tab(column_name);
Or
create index on individual partitions from different sessions, say for example create indexes on 30 partitions each from three different sessions so as to finish all the 90 partitions faster?
CREATE INDEX CONCURRENTLY idx1 ON tab_part1(column_name);
CREATE INDEX CONCURRENTLY idx1 ON tab_part2(column_name);..........
Basically I have three questions:
1)If we can do this index creation activity online without impacting the incoming transactions or do we have to take down time for this activity?
2)If we can't do it online then , what is the fastest method to do this index creation activity ?
3)Should we change the DB parameters in a certain way to make the process faster? We have currently set below parameters
max_parallel_workers-16
max_parallel_maintenance_workers-2
maintenance_work_mem- 4GB
Hey there, I'm still trying to find such an organized way to create indexes concurrently on a partitioned table.
A normal way to create a proper index without exclusively locking the whole table on a partitioned table is basically
-
Creating an invalid index on the `parent` table
-
Creating indexes concurrently for child tables
-
Attaching the indexes by altering the parent's index
This works fine, but it gets annoying the more partitions you have, because point two and three should be done for every child table.
We could create a stored procedure for it, which would be very easy later just giving some variables and call a procedure, but the problem is `concurrently` since stored procedures and functions are both transactional, there is no way to do that.
Is there an organized way to implement these logics as a procedure or something within the database?