Thứ Năm, 17 tháng 11, 2022

Bài 06 - Partitioning data với PostgreSQL P1

Một hướng tiếp cận khác có thể tăng query performance là áp dụng partitioning: thay đổi data model implementation. Let’s begin.

1) Horizontal & Vertical partitioning

Bản chất của việc thực hiện các câu query đều là scan table. Do đó, table càng lớn thì càng ảnh hưởng đến performance của query cho dù có được index hay không.

1.1) Horizontal partitioning

Vậy có cách nào làm cho query hiệu quả hơn ngoài việc index? Ý tưởng khá đơn giản dựa trên nguyên tắc chia để trị. Hãy tưởng tượng lớp học có 40 học sinh trong đó 12 nam và 8 nữ. Nếu cần tìm một bạn nữ tên Jane, thay vì scan từng học sinh, ta sẽ chia sẵn lớp học thành 2 dãy nam và nữ sao đó thực hiện scan trên dãy học sinh nữ.

Nếu có thêm học sinh vào lớp, chỉ cần sắp xếp bạn đó vào đúng nhóm là ok. Trên thực tế, việc thay đổi giới tính gần như không xảy ra :joy:, tuy nhiên nếu xảy ra thì việc cần làm là sắp xếp bạn đó về đúng nhóm của mình là xong.

Partitioning table chính là ví dụ phía trên. Chúng ta chia table lớn thành nhiều table nhỏ hơn, các table nhỏ hơn gọi là partition table, kế thừa toàn bộ cấu trúc của parent table, từ column cho đến kiểu dữ liệu. Việc chia nhỏ này được gọi là horizontal partitioning.

Horizontal partitioning có những ưu điểm sau:

  • Giới hạn vùng dữ liệu phải scan trên table trong một vài trường hợp. Nếu ta cần tìm một học sinh tên John Doe không phân biệt giới tính thì việc partition như ví dụ trên không đem lại hiểu quả.
  • Partition table cũng giống như một table thường nên ta có thể thực hiện index cho nó. Dẫn đến việc tốn ít cost hơn để maintain index table, do số lượng record ít hơn.
  • Ngoài ra, việc xóa các dữ liệu trên partition table sẽ nhanh hơn và không ảnh hưởng đến các partition khác.

Với những ưu điểm trên, ta cần lưu ý khi thực hiện horizontal partitioning để đạt hiệu quả tối đa:

  • Áp dụng với các table rất lớn. Thường là quá size của memory.
  • Việc partition trên điều kiện nào phải dựa vào tính chất và tần suất của các query.

1.2) Vertical partitioning

Vertical partitioning cũng là việc chia một table ra thành nhiều partition table nhưng theo chiều… dọc. Ví dụ một table 100 columns được partition thành 4 table mỗi table 25 columns. Về cơ bản sẽ không có một tiêu chuẩn hay công thức cụ thể nào cho việc vertical partitioning. Ta chỉ cần chú ý đến việc nhóm các columns có tần suất query cùng nhau thành một partition.

Ngoài ra, với vertical partitioning, best practice là sử dụng chung một PK cho toàn bộ các partition table.

Vậy lợi ích của vertical partitioning là gì?

Trước tiên cần hiểu về cách data được lưu trữ xuống disk (HDD/SSD). Về cơ bản, các records được lưu thành một khối dữ liệu có độ lớn gần tương tự như nhau được gọi là block. Do đó, nếu một table chứa số lượng column ít đồng nghĩa với việc tăng đương số lượng records lưu trữ trên một block. Như vậy nếu query các column trong cùng block, các xử lý tính toán I/O sẽ giảm đi phần nào dẫn tới việc tăng performance.

Trong thực tế, vertical partitioning nên được chú ý ngay từ khi design database vì việc này ảnh hưởng trực tiếp đến cách query và vận hành hệ thống do phải chia thành các table thực. Horizontal partitioning đơn giản hơn một chút tuy nhiên vẫn cần dừng hệ thống trong 1 khoảng thời gian để làm các thao tác sau:

  • Back-up data cũ.
  • Tạo partition table.
  • Insert data vào table mới.

2) Horizontal partitioning by range

Bản chất của partitioning là phân chia ra các partition table dựa trên điều kiện phân vùng partition key. Với partition by range, điều kiện partition có thể là:

  • Phân chia theo thời gian.
  • Phân chia theo numeric từ 1 đến 5, 6 đến 10…
  • Phân chia theo bảng chứ cái A, B, C; D, E, F…

Khi thực hiện range partition ta quan tâm đến giá trị min và max của mỗi partition để thực hiện việc phân chia. Ví dụ table ENGINEER có thông tin về ngày bắt đầu làm việc (start_date), ta có thể dựa trên column này để phân chia partition theo từng quý. Như đã nói ở trên, ta cần back-up data và tạo lại table mới nhé:

























DROP TABLE ENGINEER;
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_Q4_2020 PARTITION OF ENGINEER FOR VALUES
    FROM ('2020-10-01') TO ('2020-12-31');

Thay vì back-up, mình sẽ insert data mới luôn.

Explain query một engineer với id = 10:

EXPLAIN SELECT * FROM ENGINEER WHERE id = 10;

Sau khi thực hiện partition, một table ENGINEER sẽ bao gồm 4 table partition. Lúc này, query plan phải sequence scan trên toàn bộ partition để tìm ra record thỏa mãn điều kiện id = 10.

Chưa thấy có gì đặc biệt, thử tìm kiếm các engineer bắt đầu làm việc từ Apr 01, 2020:

EXPLAIN SELECT * FROM ENGINEER WHERE started_date = '2020-04-01';

Lúc này, query plan chỉ scan trên partition engineer_q2_2020. Đã thấy sự lợi hại của partition. Mặc dù ta không specific chỉ định partition engineer_q2_2020 nhưng PostgreSQL biết điều đó và tối ưu luôn cho chúng ta. Câu query trên tương tự với:

EXPLAIN SELECT * FROM ENGINEER_Q2_2020 WHERE started_date = '2020-04-01';

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.

Quick question, bạn có nhận ra điều gì đặc biệt khi tạo partition table không? Đó là nó không có PK, hay nói cách khác, không thể tạo constraint PK hay unique trên các column của table. Thử nhé:

























CREATE TABLE TEST_PK
(
    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,
    PRIMARY KEY (id)
) PARTITION BY RANGE(started_date);

CREATE TABLE TEST_UNQ
(
    id bigserial UNIQUE 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);

Bạn có biết vì sao không? Nếu chưa thì chờ câu trả lời ở cuối bài nhé.

Với ví dụ trên, ta có tổng cộng 4 partitions cho 4 quý của năm 2020. Nếu insert hoặc update một record không thuộc năm 2020 thì sao nhỉ:





UPDATE ENGINEER SET started_date = '2021-01-01';

INSERT INTO ENGINEER(first_name, last_name, gender, country_id, title, started_date, created) 
    VALUES('Hermina', 'Kuhlman', 3, 229, 'Backend Engineer', '2021-09-23', current_timestamp);

Cả 2 query đều báo lỗi vì… không biết nhét chúng vào partition table nào. Do đó, nếu không chắc chắn về tập data của mình, ta cần tạo một table default partition để chứa các record không biết phân loại vào đâu :joy:.

CREATE TABLE ENGINEER_DEFAULT_PARTITION PARTITION OF ENGINEER DEFAULT;

PostgreSQL version 11 trở xuống không support default partition, các vesion từ 11 trở lên mới thực hiện được nhé. Bây giờ chạy lại query insert trên sẽ ngon ngay.

Vài câu hỏi khác được đặt ra:

  • Có thể tạo một partition mới trong quá trình runtime không? Có thể có nhiều hơn 1 partition chứa cùng một khoảng thời gian không? Ví dụ partition: 2020-01-01:2020-04-01 và partition: 2020-02-01:2020-05-01.
  • Có thể drop một partition trong quá trình runtime mà giữ nguyên data của partition đó không?

Với câu đầu tiên, ta hoàn toàn có thể tạo một partition mới, ví dụ là default partition ở trên. Với ý thứ hai, bản chất của partition là phân chia tập data ra thành các partition độc lập với nhau dựa trên các điều kiện cho trước. Vậy nên để không vi phạm quy tắc đó, ta không thể tạo 2 partition với overlapping key. Nôm na là một record không thể thuộc nhiều hơn một partition, các partition key không được trùng nhau hoặc chứa một phần trùng nhau.

Câu thứ hai, có hai trường hợp:

  • Không có default partition: tất nhiên rồi, muốn giữ lại các record ta cần một partition phù hợp để chứa record đó. Trong trường hợp không có default partition, không có cách nào có thể giữ lại các record sau khi drop partition table.
  • Có default partition: thực ra đây là trick question, nếu không hiểu bản chất rất dễ bị… ăn cú lừa thế kỉ :joy:. Không cần quan tâm đến partition hay không partition, có default partition table hay không có default partition table, DROP table vẫn là DROP table, xóa toàn bộ tất cả các data, index, trigger, constraint… và định nghĩa của table.

Bài tiếp theo cùng tìm hiểu về 2 loại partition còn lại là:

  • Partition by list.
  • Partition by hash.

After credit

Vì sao không thể tạo constraint PK hoặc unique với parent partition table? Cần tìm hiểu kĩ lại thực chất partition table là gì?

Bản chất của partition là chia một logical table ra thành nhiều small physical pieces. Lúc này table ENGINEER ban đầu mà ta định nghĩa chỉ là abstract table, định nghĩa ra các partition bao gồm những column nào, trường dữ liệu gì. Việc quyết định constraint unique sẽ apply cho toàn bộ các partition, điều đó khiến cho các partition phải liên hệ với nhau để check một value có là unique hay không, nó làm phá vỡ đi ý nghĩa partition ban đầu.
=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels: ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master