Quick Creation of a Large Number of Partition Indexes in PostgreSQL
By digoal
Background
Suppose I have a partition table with tens of thousands of partitions. If I need to create indexes on these partitions, what should I do?
- I am lazy.
- I'm afraid that index creation will affect my business. (DML blocking occurs if indexes are not created using the concurrently statement.)
- I don't want to write too many SQL statements.
- I want to do it quickly. As far as I know, concurrently has been improved after PostgreSQL 12. For earlier versions, if there are a large number of DML operations, indexes created using concurrently will be slow.
- There are many partitions. If only one
docommand is used, the relevant DML operations will be blocked from the beginning to the end. If it takes a long time, I am afraid of affecting my business. - Each partition is small. It takes a few seconds to add indexes to a partition (a few seconds have less impact on the business).
How can I quickly create indexes for all partition tables without blocking DML operations (or by temporarily blocking DML operations)?
The answer is procedure (PostgreSQL 11 is used as an example):
set lock_timeout=xx; -- 避免长时间等锁导致雪崩.
loop xx..xxxx
create index [if not exists] [concurrently] ?; -- 仅仅在这个过程中堵塞dml
commit or rollback; -- 每创建一个索引后都结束事务, 释放这个索引相关的锁.
end loop;
reset lock_timeout;
Example
If you want to create multiple indexes on one table, the steps are the same as creating indexes on multiple tables. You only need to modify the content of the stored procedure:
create table tab2(uid int, info text);
insert into tab2 select generate_series(1,100), random()::text;
Create a function to execute SQL statements and configure the lock timeout period:
create or replace function exec_sql(text,text) returns boolean as $$
declare
begin
execute format('set lock_timeout=%L', $1);
execute $2;
reset lock_timeout;
return true;
exception when others then
reset lock_timeout;
return false;
end;
$$ language plpgsql strict;
Create a stored procedure to create a bunch of indexes on tab2:
create or replace procedure do_sqls() as $$
declare
begin
for i in 1..65 loop
if exec_sql('1s', format('create index IF NOT EXISTS idx_%s on tab2 (uid)', i)) then
raise notice 'success index: %', 'idx_'||i;
commit; -- 每个分区索引创建后, 结束事务, 自动释放锁
else
rollback; -- 每个分区索引创建后, 结束事务, 自动释放锁
raise notice 'not success, lock_timeout index: %', 'idx_'||i;
end if;
-- perform pg_sleep(5); -- 加一个sleep可以拉长整个时间, 从而有时间窗口可以模拟dml不堵塞的情况.
end loop;
-- reset lock_timeout;
end;
$$ language plpgsql;
Check the notice. If there are partitions unsuccessfully added with indexes (for example, the operation may fail due to lock_timeout), repeat the process until all partitions are added with indexes.
Advantages:
- During operations, only the partition where indexes are being created will block DML operations related to this partition.
- Indexes are immediately visible after being created.
Note: If you are using versions earlier than PostgreSQL 12 and directly accessing the main table to write, there may still be blocking. Blocking will not occur if you directly access partitions where indexes are not under creation. You can also use versions later than PostgreSQL 12 to solve this problem.
Read previous post:
Read next post:
PostgreSQL Multi-Tenant Usage – Performance Impact Test of set search_path
Comments
Related Products
-
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More -
Vector Retrieval Service for Milvus
A cloud-native vector search engine that is 100% compatible with open-source Milvus, extensively optimized in performance, stability, availability, and management capabilities.
Learn More -
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More -
Storage Capacity Unit
Plan and optimize your storage budget with flexible storage services
Learn More
More Posts by digoal
- Tips for Executing SQL Statements in Batch: Which Is Better, gexec or Dynamic SQL?
- What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?
- How to Remove Duplicate and Similar Contents in Vector Databases
- How to Implement Parallelism and Concurrency Control (Queue) in Shell
- How to Determine Whether the Current PostgreSQL Database is in a Consistent State?
- PolarDB for PostgreSQL | Retrieval and Matching-Degree Sorting by Tags on Blogs and Websites
- Three Key Details and Principles of PostgreSQL Online Backup & Recovery
- PolarDB for PostgreSQL | Database Performance Optimization for High-Concurrency Queue Processing
- Implementing PostgreSQL Hook: Stats on Tables with Full Scans & Corresponding SQLs
- Optimization on Memory Usage During Rust Cargo Code Compiling