When you have a very large table, you may want to partition it. For example, a user table can be split into many tables by user ID (hash) or by range.
In another example, a behavior data table can be partitioned by time, and be split into multiple tables.
In fact, indexes can also be partitioned, e.g. partitioning by user ID hash or by time. Aside from having the same advantages as table partitions, index partitions also feature the following advantages:
Let’s move to details on how to implement index partitioning through PostgreSQL.
We usually create a global index. This implementation is relatively easy, but it can make our database less efficient if we do not use partitions.
create table test(id int, crt_time timestamp, info text);
create index idx_test_id on test(id);
We can add primary partition indexes to split our table into multiple parts. In this example, we split the table based on crt_time.
create table test(id int, crt_time timestamp, info text);
Partitioned indexes are as follows
create index idx_test_id_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01';
create index idx_test_id_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01';
...
create index idx_test_id_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01';
We can further divide the partitioned tables into smaller ones by adding another layer of index. In this example, we add the province_code index to the crt_time index to create a multilayer partition index. Now we have created 6 partitions from the original table.
create table test(id int, crt_time timestamp, province_code int, info text);
Partitioned indexes are as follows
create index idx_test_id_1_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01' and province_code=1;
create index idx_test_id_1_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01' and province_code=1;
...
create index idx_test_id_1_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01' and province_code=1;
....
create index idx_test_id_2_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01' and province_code=2;
create index idx_test_id_2_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01' and province_code=2;
...
create index idx_test_id_2_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01' and province_code=2;
We can also apply gin and B-tree indexes to speed up the operation of our table partitions.
create table test(uid int, crt_time timestamp, province_code int, info text);
create index idx_test_1 on test using gin(uid) where uid<1000; -- This section contains a large number of repeated values (high-frequency values), so we can use gin index to accelerate the operation
create index idx_test_1 on test using btree(uid) where uid>=1000; -- This section contains low-frequency values, so we can use btree index to accelerate the operation
1.When searching for data, you can use index partitioning conditions, index fields and the corresponding operators to search with partitioned indexes.
2.Partitioned indexes are generally used in searches with multiple conditions, and uses the partitioning condition as one of the search conditions. Of course, it can also be used when searching a single column
3.PostgreSQL supports not only partitioned indexes, but also expression indexes and functional indexes.
Welcome to Alibaba Cloud RDS PostgreSQL to learn more.
An Insight into MongoDB Sharding Chunk Splitting and Migration
2,599 posts | 762 followers
FollowApsaraDB - October 20, 2020
ApsaraDB - January 13, 2022
digoal - May 16, 2019
digoal - September 27, 2022
Alibaba Clouder - January 9, 2018
digoal - May 16, 2019
2,599 posts | 762 followers
FollowAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreAlibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAn on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by Alibaba Clouder