Partitioned Index - Alibaba Cloud RDS PostgreSQL Best Practices

Background

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.

Advantages of table partitioning:

1. Table partitioning allows tables to be stored in different table partitions that correlate to block devices. For example, historical data, which usually contains a huge amount of data with low page views can be stored in a table partition on your HDD. However, active data can be stored in table partitions on an SSD.
2. Tables are easier to maintain after partitioning. For example, you can just use Drop Table to delete historical data without using REDO.

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:

1. You do not need to create indexes for data that you do not search for. 1.Taking a user table as an example, we only search for active users and do not search for inactive users, so we can create indexes only for active users.
2. For data with different structures, you can use different index interfaces. 2.For example, when data distribution in a table is uneven, some values appear frequently, while other values appear less frequently. We can use bitmap or gin indexes for values that appear frequently, and use B-tree indexes for values that do not appear frequently.

Let’s move to details on how to implement index partitioning through PostgreSQL.

Global Index

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);  

Primary Partition Index

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';  

Multilayer Partition Index

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;  

Example of Partitioning Unevenly Distributed Data

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  

Summary

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.