pkslow.com 南瓜慢说

  • AllArticles
  • Container
  • Spring
  • Life
  • Cloud
  • Collections
  • About
  • GitHub

  • Search
Terraform101 English Terraform Middleware config Go Private Kubernetes pkslow Test HTTPS Redis Docker Mac Plan Stream MongoDB Spring DevOps JVM String Map Set List Performance Email Springboot JavaCollections ArrayList Java

How to partition the big table in PostgreSQL - Range List Hash

Created on: 2021-06-27 | 0 | View: 930

1 Why we need partitioning

Table Partitioning can resolve some performance issue caused by big tables, such as query latency. If the size of one table is reaching to the memory of the machine, it suggests that you should partition the table. There are 3 forms for table partitioning:

  • Range Partitioning
  • List Partitioning
  • Hash Partitioning

We will cover all the 3 forms by examples.

2 There way to partition

To make our work easier, we started up the PostgreSQL with Docker. We used the latest version so it can support Hash Partitioning:

docker run -itd \
    --name pkslow-postgres \
    -e POSTGRES_DB=pkslow \
    -e POSTGRES_USER=pkslow \
    -e POSTGRES_PASSWORD=pkslow \
    -p 5432:5432 \
    postgres:13

2.1 Range Partitioning

We created the big table named pkslow_person_r, we would partition the table by age, the SQL as below:

CREATE TABLE pkslow_person_r (
    age int not null,
    city varchar not null
) PARTITION BY RANGE (age);

Next we created the tables to partition the big table:表:

create table pkslow_person_r1 partition of pkslow_person_r for values from (MINVALUE) to (10);
create table pkslow_person_r2 partition of pkslow_person_r for values from (11) to (20);
create table pkslow_person_r3 partition of pkslow_person_r for values from (21) to (30);
create table pkslow_person_r4 partition of pkslow_person_r for values from (31) to (MAXVALUE);

We created 4 tables:

  • pkslow_person_r1: age from 0 to 10;
  • pkslow_person_r2: age from 11 to 20;
  • pkslow_person_r3: age from 21 to 30;
  • pkslow_person_r4: age older than 30.

Let insert some test data:

insert into pkslow_person_r(age, city) VALUES (1, 'GZ');
insert into pkslow_person_r(age, city) VALUES (2, 'SZ');
insert into pkslow_person_r(age, city) VALUES (21, 'SZ');
insert into pkslow_person_r(age, city) VALUES (13, 'BJ');
insert into pkslow_person_r(age, city) VALUES (43, 'SH');
insert into pkslow_person_r(age, city) VALUES (28, 'HK');

As you can see, the table name in insert sql is still pkslow_person_r, we don't need to use the partitioning sub tablename.

So as for querying:

But the 4 sub tables did exist:

If we queryed the sub table, we can just get the specific result belong to it:

2.2 List Partitioning

Similarly, List Partitioning can route your data into sub tables by the column name:

-- create the big table
create table pkslow_person_l (
                          age int not null,
                          city varchar not null
) partition by list (city);

-- create sub tables
CREATE TABLE pkslow_person_l1 PARTITION OF pkslow_person_l FOR VALUES IN ('GZ');
CREATE TABLE pkslow_person_l2 PARTITION OF pkslow_person_l FOR VALUES IN ('BJ');
CREATE TABLE pkslow_person_l3 PARTITION OF pkslow_person_l DEFAULT;

-- insert test data
insert into pkslow_person_l(age, city) VALUES (1, 'GZ');
insert into pkslow_person_l(age, city) VALUES (2, 'SZ');
insert into pkslow_person_l(age, city) VALUES (21, 'SZ');
insert into pkslow_person_l(age, city) VALUES (13, 'BJ');
insert into pkslow_person_l(age, city) VALUES (43, 'SH');
insert into pkslow_person_l(age, city) VALUES (28, 'HK');
insert into pkslow_person_l(age, city) VALUES (28, 'GZ');

We queryed the first sub table, just data in city GZ:

2.3 Hash Partitioning

The Hash Partitioning can partition the big table by Hash value of column:

-- create the big table
create table pkslow_person_h (
                          age int not null,
                          city varchar not null
) partition by hash (city);

-- create the sub tables
create table pkslow_person_h1 partition of pkslow_person_h for values with (modulus 4, remainder 0);
create table pkslow_person_h2 partition of pkslow_person_h for values with (modulus 4, remainder 1);
create table pkslow_person_h3 partition of pkslow_person_h for values with (modulus 4, remainder 2);
create table pkslow_person_h4 partition of pkslow_person_h for values with (modulus 4, remainder 3);

-- insert the test data
insert into pkslow_person_h(age, city) VALUES (1, 'GZ');
insert into pkslow_person_h(age, city) VALUES (2, 'SZ');
insert into pkslow_person_h(age, city) VALUES (21, 'SZ');
insert into pkslow_person_h(age, city) VALUES (13, 'BJ');
insert into pkslow_person_h(age, city) VALUES (43, 'SH');
insert into pkslow_person_h(age, city) VALUES (28, 'HK');

Queryed the one sub table:

3 sum-up

The code can check: https://github.com/LarryDpk/pkslow-samples


Reference:

Postgresql Table Partitioning

How to use table partitioning to scale PostgreSQL


Code for all: GitHub

欢迎关注微信公众号<南瓜慢说>,将持续为你更新...

file

Recommendations:
Cloud Native
Terraform
Container: Docker/Kubernetes
Spring Boot / Spring Cloud
Https
如何制定切实可行的计划并好好执行

  • Author 作者: LarryDpk 南瓜慢说
  • Link 链接: https://www.pkslow.com/archives/postgresql-partitioning-en
  • 版权声明: 本博客所有文章除特别声明外,不可转载!
# Terraform101 # English # Terraform # Middleware # config # Go # Private # Kubernetes # pkslow # Test # HTTPS # Redis # Docker # Mac # Plan # Stream # MongoDB # Spring # DevOps # JVM # String # Map # Set # List # Performance # Email # Springboot # JavaCollections # ArrayList # Java
Terraform101 English Terraform Middleware config Go Private Kubernetes pkslow Test HTTPS Redis Docker Mac Plan Stream MongoDB Spring DevOps JVM String Map Set List Performance Email Springboot JavaCollections ArrayList Java
IDEA因Untrusted project无法导入jar包解决方法
Docker这些none:none的镜像,难道就不配拥有名字吗
  • Contents
  • Site Overview
南瓜慢说

南瓜慢说

多年Java开发,主要专注后端技术:Java/Spring/Springboot/微服务/大数据等。

多读书,多分享;多写作,多整理。

241 Posts
9 Categories
30 Tags
RSS
0%
© 2020 — 2022 南瓜慢说 pkslow The WebSite keeping alive:   粤ICP备20036375号