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.

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.
  1. 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.

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.

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