Partitioned Index — Alibaba Cloud RDS PostgreSQL Best Practices

Image for post
Image for post

Background

Advantages of table partitioning:

Global Index

create table test(id int, crt_time timestamp, info text);  

create index idx_test_id on test(id);

Primary Partition Index

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

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

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

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