Partitioned Index — Alibaba Cloud RDS PostgreSQL Best Practices

Image for post
Image for post

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:

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.

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.

Reference:

https://www.alibabacloud.com/blog/Partitioned-Index---Alibaba-Cloud-RDS-PostgreSQL-Best-Practices_p341223?spm=a2c41.11152904.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store