PostgreSQL – Partition trên môi trường production

Trong thực tế, thường khi hệ thống có vấn đề về hiệu năng, và bạn cần cải thiện nó, thì trong cơ sở dữ liệu đã có các bảng lớn phục vụ ứng dụng của bạn. Việc chỉnh sửa cơ sở dữ liệu khi ứng dụng đã chạy live là rất khó khăn.

Việc partition các bảng là một ví dụ của thách thức này, yêu cầu xây dựng lại các bảng từ đầu.

Bài viết này đưa ra một giải pháp mà bạn có thể tham khảo để thực hiện partition các bảng đã tồn tại trong cơ sở dữ liệu PostgreSQL mà không gây ra thời gian dừng ứng dụng (downtime) quá lớn trên môi trường production.

Partition table trong PostgreSQL

Khái niệm partition

PostgreSQL hỗ trợ việc partition bảng, chia một bảng logic lớn thành các bảng thực nhỏ hơn.

Ví dụ: Bạn có bảng system_logs có chứa các bản ghi log hệ thống trong tháng. Một thiết kế partition cho bảng này sẽ chia nó thành 30 bảng con, trong đó mỗi bảng chứa log của một ngày.

Việc partition bảng có thể mang lại một số lợi ích:

• Tăng đáng kể hiệu năng của các câu query trong một số trường hợp, đặc biệt là khi có các bản ghi thường được truy cập nằm chung trong một hoặc một số ít các partition.
• Khi thực hiện query hoặc update các bản ghi trong cùng một partition, hiệu năng sẽ được cải thiện khi sử dụng sequential scan trong partition đó thay vì dùng index – yêu cầu truy xuất ngẫu nhiên trong toàn bảng.
• Việc thêm hoặc xóa số lượng bản ghi lớn (bulk) có thể được thực hiện bằng việc thêm hoặc xóa partition, nếu việc này được lưu ý trong khâu thiết kế partition. Xóa một partition riêng lẻ bằng cách sử dụng lệnh DROP TABLE, hoặc ALTER TABLE DETACH PARTITION, sẽ nhanh hơn nhiều so với bulk operation.

Các loại partition

PostgreSQL hỗ trợ các loại partition sau

• Range partitioning: Bảng được chia thành các “khoảng” được định nghĩa bởi một hoặc một tập các cột, không có giá trị trùng lặp trong các khoảng giá trị được gán cho các partition khác nhau. Ví dụ: chia bảng theo khoảng thời gian. Giới hạn của mỗi phạm vi được hiểu là bao gồm giá trị biên dưới và không bao gồm giá trị biên trên. Ví dụ: Nếu một partition có phạm vi từ 1 đến 10, partition tiếp theo có phạm vi từ 10 đến 20, thì giá trị 10 sẽ thuộc partition sau.
• List partitioning: Chia bảng thành nhiều partition dựa trên một list các giá trị có trước. Ví dụ: Partition bảng order theo cột status có tập giá trị là “pending”, “shipped”, “delivered”, như vậy sẽ cần tạo 3 partition cho chứa các order với status tương ứng.
• Hash partitioning: Bảng được chia thành các partition dựa trên giá trị băm của một cột chỉ định.

Giải pháp partition trên production không có downtime

Việc thực hiện partition một bảng lớn có thể tốn nhiều thời gian và nhiều hệ thống quan trọng sẽ không chấp nhận việc dừng lại trong một khoảng thời gian như vậy.

Giải pháp sau có thể giúp bạn thực hiện partition một bảng lớn mà không gây ra (hoặc gây ra rất ít) thời gian dừng hệ thống:

• Tạo một bảng mới, được định nghĩa là bảng được partition, là bản sao của bảng gốc cần được partition trong Postgres.
• Tạo một VIEW trong Postgres kết hợp giữa hai bảng cũ và mới với logic sau: các lệnh INSERT sẽ thêm bản ghi vào bảng mới, các lệnh UPDATE và DELETE sẽ vào cả hai bảng, lệnh SELECT sẽ query theo union của cả hai bảng.
• Copy dữ liệu giữa hai bảng, đảm bảo mỗi dòng chỉ tồn tại ở một bảng.
• Sau khi đã copy dữ liệu xong, thay thế VIEW được tạo ở trên bằng bảng mới đã được partition.

Giải pháp này cho phép chúng ta cung cấp dữ liệu từ cả bảng cũ và bảng mới tới ứng dụng một cách liền mạch, đảm bảo tính toàn vẹn dữ liệu mà không làm ảnh hưởng đến ứng dụng. Các bước đều được thực hiện theo cách tốn ít thời gian nhất và chỉ trỏ ứng dụng đến bảng mới khi quá trình partition đã được hoàn tất.

Hướng dẫn chi tiết

Trước khi thực hiện các thay đổi về schema, bạn nên:

• Review lại các câu query trong code ứng dụng để đảm bảo việc query dữ liệu không trải dài qua nhiều partition.
• Xóa các khóa ngoại đến bảng gốc (bảng cần được partition). Việc này còn có thể ảnh hưởng đến việc update code ứng dụng.
• Đảm bảo plan_cache_mode được set thành force_custom_plan.

Demo cách live partition bảng large_table.

1. Tạo bảng được partition new_large_table có cấu trúc giống bảng large_table và tất cả các partition của nó. Tạo các index giống bảng large_table cho bảng new_large_table.
   
-- Step 1: Create the new partitioned table
CREATE TABLE new_large_table
// TODO - example columns
PARTITION BY RANGE (workspace_id);
-- Create partitions of new_large_table
-- Create indexes on new_large_table

2. Đổi tên bảng gốc
   
ALTER TABLE large_table RENAME TO old_large_table;

3. Tạo VIEW ẩn cả bảng cũ và mới đối với ứng dụng, đồng thời chuyển tiếp các hoạt động tới hai bảng.
   
CREATE OR REPLACE VIEW large_table AS (
SELECT * FROM new_large_table
UNION ALL
SELECT * FROM old_large_table
);

   Các lệnh INSERT sẽ được chuyển tiếp vào bảng new_large_table.
   
CREATE OR REPLACE RULE insert_rule_large_table AS ON INSERT TO large_table
DO INSTEAD (
INSERT INTO new_large_table (id, workspace_id, data)
VALUES (NEW.id, NEW.workspace_id, NEW.data)
RETURNING new_large_table.*;
);

   Các lệnh DELETE và UPDATE truyền đến cả hai bảng bằng cách sử dụng RULE và TRIGGER. Các thao tác này vẫn cần trả về số lượng bản ghi bị ảnh hưởng một cách chính xác.
   
-- Step 3c: UPDATE and DELETE triggers and rules.
-- Note: Any operations on new_large_table MUST contain a partition key to prevent performance issues.
CREATE OR REPLACE FUNCTION large_table_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM old_large_table WHERE id = OLD.id AND workspace_id = OLD.workspace_id;
IF NOT FOUND THEN
DELETE FROM new_large_table WHERE id = OLD.id AND workspace_id = OLD.workspace_id;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER delete_large_table_trigger
INSTEAD OF DELETE ON large_table
FOR EACH ROW EXECUTE PROCEDURE large_table_delete_trigger();

CREATE OR REPLACE FUNCTION large_table_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
UPDATE old_large_table SET
id = NEW.id,
workspace_id = NEW.workspace_id,
data = NEW.data
WHERE id = OLD.id AND workspace_id = OLD.workspace_id;
IF NOT FOUND THEN
UPDATE new_large_table SET
id = NEW.id,
workspace_id = NEW.workspace_id,
data = NEW.data
WHERE id = OLD.id AND workspace_id = OLD.workspace_id;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_large_table_trigger
INSTEAD OF UPDATE ON large_table
FOR EACH ROW EXECUTE PROCEDURE large_table_update_trigger();
   
4. Migrate dữ liệu và cleanup.
   Chuyển dữ liệu theo batch bằng cách run script sau trong vòng lặp đến khi bảng cũ không còn dữ liệu:
   
BEGIN;
WITH batch AS (
DELETE from old_large_table
WHERE id IN (
SELECT id FROM old_large_table
LIMIT 1000
)
RETURNING *
)
INSERT INTO new_large_table SELECT * from batch;
COMMIT;

   Xóa VIEW đã tạo và rename bảng mới, đồng thời xóa bảng cũ.
   
DROP VIEW IF EXISTS large_table;
ALTER TABLE new_large_table RENAME TO large_table;
DROP TABLE IF EXISTS old_large_table;


Với giải pháp nêu trên, chúng ta có thể partition một bảng trong cơ sở dữ liệu PostgreSQL mà vẫn đảm bảo ứng dụng chạy tương đối liên mạch mà không ảnh hưởng đến người dùng.

Nguồn tham khảo

https://www.postgresql.org/docs/current/ddl-partitioning.html
https://www.prefect.io/blog/database-partitioning-prod-postgres-without-downtime