Partitioning table trong PostgreSQL

Partitioning table là một kỹ thuật nâng cao trong PostgreSQL giúp cải thiện hiệu suất truy vấn cho các bảng lớn bằng cách chia bảng thành các phần nhỏ hơn dựa trên một hoặc nhiều cột. Mỗi phần được gọi là một phân vùng và có thể được lưu trữ và quản lý riêng biệt.

Lợi ích của Phân vùng Bảng:

Cải thiện hiệu suất truy vấn: Phân vùng bảng có thể giúp tăng tốc độ truy vấn cho các bảng lớn bằng cách chỉ quét các phân vùng có liên quan đến truy vấn. Điều này đặc biệt hữu ích cho các truy vấn có điều kiện lọc trên các cột phân vùng.
Giảm thiểu I/O: Do chỉ cần truy cập các phân vùng liên quan, phân vùng bảng có thể giúp giảm thiểu I/O đĩa, dẫn đến hiệu suất truy vấn nhanh hơn.
Quản lý dữ liệu dễ dàng hơn: Phân vùng bảng có thể giúp quản lý dữ liệu dễ dàng hơn cho các bảng lớn bằng cách cho phép bạn chia nhỏ dữ liệu theo các tiêu chí khác nhau.

Các loại Phân vùng Bảng:

Phân vùng theo Cột (Range Partitioning): Chia bảng thành các phân vùng dựa trên giá trị của một cột. Ví dụ: bạn có thể phân vùng bảng orders theo cột order_date để tạo các phân vùng riêng biệt cho các đơn hàng theo năm, quý hoặc tháng.
Phân vùng theo Danh sách (List Partitioning): Chia bảng thành các phân vùng dựa trên giá trị cụ thể của một cột. Ví dụ: bạn có thể phân vùng bảng customers theo cột country để tạo các phân vùng riêng biệt cho khách hàng ở các quốc gia khác nhau.
Phân vùng theo Hash (Hash Partitioning): Thực hiện hash partition key ra hash value.

Khi nào thì nên thực hiện partition:

Đây là câu hỏi khó và rất rộng lớn, cơ bản là phụ thuộc vào các trường hợp thực tế chúng ta sẽ có phương án khác nhau. Nhưng theo thông thường các table có dung lượng tầm 2GB hoặc khoảng 50 triệu rows thì chúng ta nên thực hiện partition cho bảng để đảm bảo hiệu năng hệ thống.

Cách Tạo Bảng Phân vùng: (Partition Table) phổ biến.

RANGE partition:

Giả sử chúng ta có bảng orders có các thông tin sau: và chúng ta thực hiện partition RANGE trên cột order_date theo 4 quý của năm (01-03, 04-06, 07-09, 10-12)

CREATE TABLE orders (
order_id int,
customer_id int,
order_amount decimal(10,2),
order_date date) PARTITION BY RANGE(order_date);

create table order_q1_2024 partition of orders for values
from ('2024-01-01') to ('2024-04-01');

create table order_q2_2024 partition of orders for values
from ('2024-04-01') to ('2024-07-01');

create table order_q3_2024 partition of orders for values
from ('2024-07-01') to ('2024-10-01');

create table order_q4_2024 partition of orders for values
from ('2024-10-01') to ('2025-01-01');

create table order_default partition of orders default;

## nếu ngày tháng không thuộc range trên thì cho vào table default

kết quả:

mm=# dt

List of relations

Schema | Name | Type | Owner

——–+—————+——————-+———-

public | order_default | table | postgres

public | order_q1_2024 | table | postgres

public | order_q2_2024 | table | postgres

public | order_q3_2024 | table | postgres

public | order_q4_2024 | table | postgres

public | orders | partitioned table | postgres

public | tab2 | table | postgres

Ngoài ra chúng ta không thể lưu dữ liệu vào 2 partition khác nhau mà có chung khoảng thời gian được. => khác biệt về range trong partition

Test dữ liệu với range partition:

Thực hiện insert dữ liệu test vào bảng bên trên theo các partition

insert into orders
SELECT 11, 22, 33,'2024-02-05'
FROM generate_series(1, 1000);

insert into orders
SELECT 11, 22, 33,'2024-04-01'
FROM generate_series(1, 10000);

insert into orders
SELECT 11, 22, 33,'2024-09-30'
FROM generate_series(1, 3333);

insert into orders
SELECT 11, 22, 33,'2024-12-31'
FROM generate_series(1, 4444);

insert into orders
SELECT 11, 22, 33,'2023-04-01'
FROM generate_series(1, 23232);

Kiểm tra dữ liệu sau khi insert:

mm=# select count(*) from order_q1_2024;

count

——-

1000

(1 row)

mm=# select count(*) from order_q2_2024;

count

——-

20000

(1 row)

mm=# select count(*) from order_q3_2024;

count

——-

6666

(1 row)

mm=# select count(*) from order_q4_2024;

count

——-

4444

(1 row)

mm=# select count(*) from order_default;

count

——-

23232

(1 row)

mm=# select count(*) from orders;

count

——-

55342

(1 row)

LIST partition:

Chúng ta có bảng nhân viên như bên dưới đây, bây giờ chúng ta tạo partition cho bảng dựa trên cột giới tính có 2 thuộc tính là M, F

CREATE TABLE employees (
employee_id SERIAL,
last_name VARCHAR(50) NOT NULL,
gender CHAR(1) NOT NULL
) partition by list (gender);

create table employees_male partition of employees for values in ('M');

create table employees_female partition of employees for values in ('F');

create table employees_default partition of employees default;

List partition phân chia table dựa trên danh sách các giá trị cho trước, không theo khoảng giá trị như range partition. Do đó, nó phù hợp phân chia dữ liệu theo những giá trị cụ thể, giống bài toán phân chia nam, nữ ở trên.

Việc thực hiện các truy vấn DML, DDL trên table có partition chúng ta thực hiện như bình thường trên table gốc mà không cần quan tâm đến partition table.

Mỗi partition được coi là một table riêng biệt và kế thừa các đặc tính của table. Ta hoàn toàn có thể thêm index cho từng partition để tăng performance cho query, được gọi là local index. Hoặc thêm index cho parent table, được gọi là global index. Chúng ta nên thực hiện tạo local index sẽ tối ưu cho DB hơn là global, vì khi drop partition table sẽ không ảnh hưởng tới index chung của toàn bảng, nó chỉ ảnh hưởng index partition hiện tại mà thôi.

partition table là thành phần kế thừa của table gốc nên nó sẽ không có Primary key hay Unique… tất cả được kế thừa từ table gốc.

Insert và test dữ liệu:

insert into employees
SELECT 11, 'Duong', 'M'
FROM generate_series(1, 1000);

insert into employees
SELECT 11, 'Ha', 'F'
FROM generate_series(1, 1000);

insert into employees
SELECT 11, 'MAMA', 'A'
FROM generate_series(1, 1000);

mm=# dt

List of relations

Schema | Name | Type | Owner

——–+——————-+——————-+———-

public | employees | partitioned table | postgres

public | employees_default | table | postgres

public | employees_female | table | postgres

public | employees_male | table | postgres

public | order_default | table | postgres

public | order_q1_2024 | table | postgres

public | order_q2_2024 | table | postgres

public | order_q3_2024 | table | postgres

public | order_q4_2024 | table | postgres

public | orders | partitioned table | postgres

public | tab2 | table | postgres

(11 rows)

mm=# select count(*) from employees;

count

——-

3000

(1 row)

Time: 2.013 ms

mm=# select count(*) from employees_male;

count

——-

1000

(1 row)

Time: 0.500 ms

mm=# select count(*) from employees_female;

count

——-

1000

(1 row)

Time: 0.542 ms

mm=# select count(*) from employees_default;

count

——-

1000

(1 row)

Time: 0.440 ms

Partition Pruning: https://datalinks.vn/partition-pruning-postgresql/

Partition Pruning giúp cải thiện hiệu suất truy vấn bằng cách loại bỏ các phân vùng không liên quan khỏi kế hoạch thực thi truy vấn. Điều này giúp giảm lượng dữ liệu cần xử lý, từ đó tăng tốc độ truy vấn.

timing

SET enable_partition_pruning = off;

mm=# SET enable_partition_pruning = off;

SET

Time: 0.219 ms

mm=# explain SELECT count(*) FROM orders WHERE order_date >= DATE ‘2024-06-01’;

QUERY PLAN

—————————————————————————————

Aggregate (cost=1130.12..1130.13 rows=1 width=8)

-> Append (cost=0.00..1102.34 rows=11113 width=0)

-> Seq Scan on order_q1_2024 orders_1 (cost=0.00..19.50 rows=1 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

-> Seq Scan on order_q2_2024 orders_2 (cost=0.00..378.00 rows=1 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

-> Seq Scan on order_q3_2024 orders_3 (cost=0.00..126.33 rows=6666 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

-> Seq Scan on order_q4_2024 orders_4 (cost=0.00..84.55 rows=4444 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

-> Seq Scan on order_default orders_5 (cost=0.00..438.40 rows=1 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

(12 rows)

Time: 0.550 ms

=>Quét full bảng khi thực hiện câu query tìm data vào tháng 06/2024, Q2 của partition, cost = 1130.12

SET enable_partition_pruning = on;

explain SELECT count(*) FROM orders WHERE order_date >= DATE '2024-06-01';

mm=# SET enable_partition_pruning = on;

SET

Time: 0.335 ms

mm=# explain SELECT count(*) FROM orders WHERE order_date >= DATE ‘2024-06-01’;

QUERY PLAN

—————————————————————————————

Aggregate (cost=1110.62..1110.62 rows=1 width=8)

-> Append (cost=0.00..1082.84 rows=11112 width=0)

-> Seq Scan on order_q2_2024 orders_1 (cost=0.00..378.00 rows=1 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

-> Seq Scan on order_q3_2024 orders_2 (cost=0.00..126.33 rows=6666 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

-> Seq Scan on order_q4_2024 orders_3 (cost=0.00..84.55 rows=4444 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

-> Seq Scan on order_default orders_4 (cost=0.00..438.40 rows=1 width=0)

Filter: (order_date >= ‘2024-06-01’::date)

(10 rows)

Time: 0.677 ms

=>Quét q2 q3 q4 default và bỏ qua q1 (vì q1 ko nằm trong ngày cần tìm kiếm), cost 1110.62

=>Như vậy chúng ta thấy rằng khi bật tính năng Partition Pruning thì tốc độ được cải thiện.

Xem thêm:

Bài viết cùng chủ đề:

Setup PostgreSQL Cluster Highly Available with Patroni Etcd HAProxy

Tạo Index cho Partitioned Table trong PostgreSQL

Khắc phục lỗi column c.relhasoids does not exist Postgresql

Cài đặt và sử dụng EXTENSION trên Postgresql

Partition Pruning Postgresql

PostgreSQL Limitations – giới hạn của Postgresql

Tham số cấu hình WAL file Postgresql

Tham số cấu hình wal_level Postgresql

Postgresql Thu hồi dung lượng sau khi delete dữ liệu như thế nào?

Quản trị index trong postgresql

Quản trị Table Postgresql

Quản trị Tablespace Postgresql

PostgreSQL Row-Level Security – Kiểm soát truy cập dữ liệu row table

PostgreSQL superuser

Quản trị Postgresql GRANT

Quản trị Postgresql Roles

Phần mềm bản quyền

Update sản phẩm

Thêm mới sản phẩm

Đặt hàng, thanh toán