Giải quyết các vấn đề của Database Partitioning (Unmatched partitioning key problem)

GIẢI QUYẾT CÁC VẤN ĐỀ CỦA PARTITION

Khi ta thực hiện một truy vấn với điều kiện WHERE không sử dụng trường phân vùng (partition key) trong một bảng phân vùng, PostgreSQL sẽ phải quét tất cả các phân vùng để tìm kiếm kết quả. Điều này có thể làm giảm hiệu suất truy vấn, ví dụ như sau:

Tôi có bảng engineer chia partition theo started_date:

CREATE TABLE ENGINEER
(
id bigserial NOT NULL,
first_name character varying(255) NOT NULL,
last_name character varying(255) NOT NULL,
gender smallint NOT NULL,
country_id bigint NOT NULL,
title character varying(255) NOT NULL,
started_date date,
created timestamp without time zone NOT NULL
) PARTITION BY RANGE(started_date);
CREATE TABLE ENGINEER_Q1_2020 PARTITION OF ENGINEER FOR VALUES
FROM ('2020-01-01') TO ('2020-04-01');
CREATE TABLE ENGINEER_Q2_2020 PARTITION OF ENGINEER FOR VALUES
FROM ('2020-04-01') TO ('2020-07-01');
CREATE TABLE ENGINEER_Q3_2020 PARTITION OF ENGINEER FOR VALUES
FROM ('2020-07-01') TO ('2020-10-01');
CREATE TABLE ENGINEER_DEFAULT_PARTITION PARTITION OF ENGINEER DEFAULT;

Nhưng tôi lại query với where là field gender:

explain (ANALYZE, BUFFERS) SELECT \* FROM ENGINEER WHERE gender = 2;

Đây là kết quả:

Phải query hết cả 4 partition => quá chậm

Vậy trước sau gì cũng phải quét hết cả 4 partition, vậy nếu ngay từ đầu ta không chia partition, thì hiệu suất sẽ như thế nào?

Kết quả nếu không chia partition mà thực hiện query trên:

Có thể thấy được rằng nếu không match đúng partition key, hiệu suất của query thậm chí còn tệ hơn bình thường. Điều này có thể do nhiều thứ:

+ Khi query với partition, postgres phải tốn thời gian lập query plan để quyết định xem nên query ở partition nào, nên tất nhiên nó sẽ chậm hơn bình thường

+ Phải query lần lượt ở nhiều partition, tức là chuyển qua nhiều partition khác nhau cũng sẽ gây tốn thời gian

Ngoài ra còn nhiều yếu tố khác ảnh hưởng.

Vậy có cách nào giải quyết vấn đề này không?

1. Sử dụng Indexes

Tạo chỉ mục (index) trên trường gender trong từng phân vùng của bảng ENGINEER. Điều này cho phép PostgreSQL tìm kiếm nhanh hơn trong mỗi phân vùng mà không cần phải quét toàn bộ bảng.

CREATE INDEX idx_gender_q1_2020 ON ENGINEER_Q1_2020 (gender); CREATE INDEX idx_gender_q2_2020 ON ENGINEER_Q2_2020 (gender); CREATE INDEX idx_gender_q3_2020 ON ENGINEER_Q3_2020 (gender);

Khi thực hiện một truy vấn với điều kiện WHERE không sử dụng trường phân vùng (partition key) trong một bảng phân vùng, PostgreSQL sẽ phải quét tất cả các phân vùng để tìm kiếm kết quả. Điều này có thể làm giảm hiệu suất truy vấn.

2. Query Optimization

Nếu ta biết rằng dữ liệu sẽ được phân phối theo started_date, ta có thể thêm một điều kiện vào truy vấn để giới hạn phạm vi dữ liệu cần quét. Ví dụ:

EXPLAIN (ANALYZE, BUFFERS) SELECT \* FROM ENGINEER WHERE gender = 2 AND started_date >= '2020-01-01' AND started_date < '2020-02-01';

Như hình thì nhờ ràng buộc thêm started_date vào where mà ta có thể thu hẹp phạm vi tìm kiếm trong 1 partition.

3. Chuyển qua dùng hash partition và hash trên nhiều cột

Ta có thể chuyển qua hash trên cả 2 cột gender và started_date như sau

CREATE TABLE ENGINEER (

id bigserial NOT NULL,

first_name character varying(255) NOT NULL,

last_name character varying(255) NOT NULL,

gender smallint NOT NULL,

country_id bigint NOT NULL,

title character varying(255) NOT NULL,

started_date date,

created timestamp without time zone NOT NULL

) PARTITION BY HASH ((gender, country_id));

CREATE TABLE ENGINEER_PARTITION_1 PARTITION OF ENGINEER FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE ENGINEER_PARTITION_2 PARTITION OF ENGINEER FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE ENGINEER_PARTITION_3 PARTITION OF ENGINEER FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE ENGINEER_PARTITION_4 PARTITION OF ENGINEER FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Có thể thấy lúc này data được chia khá đều vào các partition:

Giờ ta sẽ thử thực hiện lại query ban nãy nếu có where là gender:

Nhưng thay đổi câu query một chút:

Lần này ta có where cả gender và started_date => thì mới hiệu quả

Kết luận, dù có hash nhiều colum cùng lúc, nhưng nó chỉ hiệu quả khi tất cả các partition key kia đều nằm trong query

explain (ANALYZE, BUFFERS) SELECT * FROM ENGINEER WHERE started_date = '2020-01-01';

4. Partition multi level

Một cách khác có thể nghĩ đến đó là chia partition theo gender, rồi trong gender đó lại chia theo started_date, nhưng điều đó sẽ rất phức tạp.

Kết luận, khi quyết định sử dụng partition, ta nên lựa các field thường được sử dụng trong điều kiện WHERE như date, status, hoặc region.... Ngoài ra, partition không thay thế được index, mà chúng nên kết hợp với nhau là tốt nhất.

5. Partition không phải là giải pháp tối ưu trong mọi trường hợp

Không phải trường hợp nào ta cũng có thể áp dụng Partition, trên thực tế Partition được xem như là một phương pháp thích hợp nếu như hiện tại database của bạn thỏa một trong các điều kiện sau:

+ Có những bảng với size rất lớn: chỉ những bảng với size cực kì lớn với hàng tỷ rows thì chúng ta mới thấy được ích lợi của partition

+ Hệ thống của bạn có mức tiêu thụ lớn: mặc dù có thể hiện tại hệ thống còn nhỏ, nhưng mức tiêu thụ lớn là điều dự đoán trong tương lai hệ thống của bạn sẽ ngày một lớn dần, do đó áp dụng partition từ sớm là giải pháp tránh được các trì trệ về performence về sau.

+ Phát hiện ra rằng query chỉ tập trung vào 1 vùng xác định nào đó trong bảng: thích hợp để áp dụng partitioning, ví dụ như time-series data, partition by date sẽ gia tăng hiệu suất đáng kể khi ta query trong một khoảng thời gian nào đó mà không cần scan cả table lớn.

+ Maintenance overhead: Đây là một trong những trường hợp nên sử dụng partition nhất, có ý kiến cho rằng, trên thực tế partitioning không nên được xem là một giải pháp giải quyết query performence mà nó nên được xem như là một giải pháp để tối ưu cho việc maintainance. Khi bảng ngày càng phình to, thì những maintenance operations như là VACUUM, ANALYZE và indexing sẽ rất chậm và ảnh hưởng đến hiệu suất hệ thống. Bằng cách áp dụng partitioning ta sẽ giải quyết được vấn đề này bởi vì chỉ cần tập trung maintainance một phần nhỏ trong cả table lớn.

+ Khi gặp trường hợp xóa data cũ định kỳ: nếu như dataset của ta yêu cầu thường xuyên phải xóa một lượng data lớn định kỳ, thì việc partitioning là vô cùng hiệu quả, bởi vì drop một partition sẽ nhanh hơn hẳn là dùng query DELETE

Khi nào thì không nên sử dụng partitioning:

+ Table nhỏ, và không có dấu hiệu sẽ phình to quá mất trong tương lai

+ Nếu ta phải thường xuyên query cả bảng mà hiếm khi có thể tập trung vào một vùng nào đó của bảng (ví dụ như dữ liệu mới đây, dữ liệu thuộc loại nào đó,...): nếu vẫn cố partitioning thì performance sẽ tệ đi