Cú pháp so sánh trong postgresql năm 2024

Bài viết này giới thiệu về chức năng partitioning bảng dữ liệu theo kiến trúc mới, chức năng Declarative Partitioning được đưa vào từ phiên bản PostgreSQL 10.

Scale out cho hệ thống PostgreSQL

Trước khi đi vào giới thiệu cụ thể, xin được tóm tắt các kỹ thuật scale out hệ thống của PostgreSQL hiện tại. Đối với các hệ thống lớn hay các hệ thống dữ liệu có độ lớn tăng dần theo thời gian, việc thiết kế scale out (chia nhỏ dữ liệu thành nhiều phần để dễ quản lý) là một việc quan trọng nếu không muốn xử lý các vấn đề về dung lượng sau vận hành. PostgeSQL cung cấp các chức năng như bên dưới để hỗ trợ scale out hệ thống.

• Sử dụng TABLESPACE Từ các phiên bản cũ TABLESPACE đã được sử dụng như công cụ để phân tán dữ liệu qua nhiều đĩa cứng giảm disk I/O. Nhưng gần đây gần đây nhiều hệ thống chuyển qua sử dụng RAID, nên chức năng này cũng không được sử dụng nhiều
• Sử dụng các software liên quan Sử dụng một số cluster software như PostgreSQL-XL, pacemaker, pgpool-II sử dụng slave node để tham chiếu giảm tải cho master. Hay dùng chức năng load balancer (của pgpool-II),... cũng có thể được coi là một trong các giải pháp scale out. Giải pháp này hơi khó khăn về mặt bảo trì về cấu trúc hệ thống.
• Cascade Replication Là chức năng đưa vào từ phiên bản 9.2 của PostgreSQL, slave node có thể chuyển tiếp đồng bộ sang các node slave mới.Ta có thể sử dụng chức năng này để scale out cho hệ thống. Hạn chế của cấu trúc này là cascade replication chỉ support phi đồng bộ, thời gian phản ánh WAL (transaction log) lên các slave node có thể trễ, nên dữ liệu trên slave không phải lúc nào cũng là mới nhất.
• Sử dụng postgres_fwd Contrib postgres_fwd cho phép PostgreSQL kết nối tới server bên ngoài. PostgreSQL 9.6 hỗ trợ push down, kết quả được sử lý (sort, join, ...) ở remote server trước khi gửi về local. Việc này làm giảm tải rất nhiều cho local server trong các hệ thống lớn. Chức năng này trong tương lai được kỳ vọng là tạo thành nền tảng cho chức năng Sharding giống như các RDBMS như mongodb.
• Partitioning Table Chức năng phân tán dữ liệu từ một bảng sang nhiều bảng con để tăng khả năng scale out cho hệ thống. Từ Application chỉ cần chú ý tới bảng dữ liệu cha. Dữ liệu khi INSERT/UPDATE vào bảng cha sẽ được phân tán tới các bảng con. Khi SELECT dữ liệu bảng cha, nhờ chức năng Partitioning dữ liệu các bảng con được tập hợp lại và gửi lại cho bảng cha. Các hệ thống cũ thường sử dụng chức năng này kết hợp với TABLESPACE để scale out hệ thống.

Chức năng Partitioning của PostgreSQL

Chức năng này cũng là một chức năng được được PostgreSQL đưa vào sớm từ phiên bản 8.1. Mặc dù có một số hạn chế overhead do phải thiết dựa trên trigger, nhưng chức năng này cũng được sử dụng rộng dãi cho tới nay. Từ phiên bản 10 PostgreSQL hỗ trợ phương thức partitioning mới, không dựa vào trigger nên có performance tốt và cách sử dụng đơn giản.

Chức năng Partitioning sử dụng kế thừa

Ở các phiên bản trước của PostgreSQL 10, để sử dụng chức năng partitioning. Ta cần các bước tổng quan như sau.

1. Tạo bảng master
2. Tạo các bảng con
3. Tạo TRIGGER cho bảng master

Tạo bảng master

Ta tạo bảng master với các trường cần thiết. Ở đây ta sử dụng cột range cho chức năng partitioning.

10000 postgres@postgres=# CREATE TABLE parent(id integer, childname text, range bigint); CREATE TABLE

Tạo các bảng con

Bạn có thể định nghĩa số lượng bảng con tuỳ ý theo thiết kế hệ thống của bạn. Nhưng community khuyến cáo không nên sử dụng quá 100 bảng con. Ví dụ bên dưới mình tạo 3 bảng con kế thừa bảng master. Ở đây mình sử dụng ràng buộc CHECK dữ liệu, để bảng con tương ứng chỉ chứa những giá trị cho phép. Khi tham chiếu dữ liệu bảng cha, PostgreSQL có thể dựa vào ràng buộc CHECK của bảng con để bỏ qua tham chiếu tới các bảng con không cần thiết.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

Tạo TRIGGER cho bảng master

Mặc định khi tạo table con ta đã có thể sử dụng chức năng partitioning để truy suất dữ liệu các bảng con thông qua bảng master. Nhưng để insert dữ liệu tới các bảng con thông qua bảng master ta phải sử dụng chức năng TRIGGER của PostgreSQL. Ví dụ bên dưới thực hiện:

• Tạo hàm TRIGGER insert_to_child cho phép kiểm tra dữ liệu INSERT và, thực hiện INSERT vào các bảng con tương ứng mà không thực hiện INSERT trên bảng master (

  10000 postgres@postgres=# \d+ parent

                                  Table "public.parent"  
  

  Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
  ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:

  insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()  
  

  Child tables: child1,

            child2,  
            child3  
  

  1).
• Sử dụng hàm insert_to_child đã định nghĩa để tạo TRIGGER cho bảng master trước khi INSERT dữ liệu.

  Ta có thể sử dụng chức năng RULE của PostgreSQL thay vì tạo TRIGGER. RULE có overhead nhiều hơn TRIGGER khi INSERT 1 hàng, nhưng ngược lại khi INSERT một khối lượng lớn dữ liệu (ví dụ như COPY) thì overhead nhỏ hơn TRIGGER.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

Sau khi tạo được bảng con và TRIGGER cho bảng master. Bảng master sẽ có cấu trúc như bên dưới.

10000 postgres@postgres=# \d+ parent

                                Table "public.parent"

insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()

          child2,
          child3

Thực hiện truy vấn

INSERT & SELECT dữ liệu

Ví dụ bên dưới INSERT 3 dòng dữ liệu thông qua bảng master và xác nhận dữ liệu đã được ghi vào các bảng con nhờ TRIGGER đã định nghĩa.

10000 postgres@postgres=# INSERT INTO parent VALUES (1,'a',1), (2,'b',199999), (3,'c',200000); INSERT 0 0 10000 postgres@postgres=# SELECT FROM parent ; id | childname | range +---+ 1 | a | 1 2 | b | 199999 3 | c | 200000 (3 rows) 10000 postgres@postgres=# SELECT FROM parent ; id | childname | range +---+ 1 | a | 1 2 | b | 199999 3 | c | 200000 (3 rows) 10000 postgres@postgres=# SELECT FROM child1; id | childname | range +---+--- 1 | a | 1 (1 row) 10000 postgres@postgres=# SELECT FROM child2; id | childname | range +---+ 2 | b | 199999 (1 row) 10000 postgres@postgres=# SELECT * FROM child3; id | childname | range +---+ 3 | c | 200000 (1 row)

Như kết quả EXPLAIN của câu lệnh SELECT bảng master bên dưới. Dữ liệu được tìm kiếm ở các bảng con chứa dữ liệu tương ứng và bảng master.

                                               QUERY PLAN                                                   

Append (cost=0.00..48.37 rows=23 width=44) (actual time=0.028..0.039 rows=2 loops=1) -> Seq Scan on public.parent (cost=0.00..0.00 rows=1 width=44) (actual time=0.011..0.011 rows=0 loops=1)

     Output: parent.id, parent.childname, parent.range
     Filter: (parent.range = ANY ('{1,199999}'::bigint[]))

     Output: child1.id, child1.childname, child1.range
     Filter: (child1.range = ANY ('{1,199999}'::bigint[]))

     Output: child2.id, child2.childname, child2.range
     Filter: (child2.range = ANY ('{1,199999}'::bigint[]))

partition (1 row)

PostgreSQL bỏ qua tìm kiếm dữ liệu dựa vào ràng buộc CHECK thông qua tham số constraint_exclusion. Nếu tham số này thiết lập là

10000 postgres@postgres=# \d+ parent

                                Table "public.parent"

insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()

          child2,
          child3

10000 postgres@postgres=# \d+ parent

                                Table "public.parent"

insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()

          child2,
          child3

                                               QUERY PLAN                                                   

Append (cost=0.00..72.55 rows=34 width=44) (actual time=0.021..0.053 rows=2 loops=1) -> Seq Scan on public.parent (cost=0.00..0.00 rows=1 width=44) (actual time=0.006..0.006 rows=0 loops=1)

     Output: parent.id, parent.childname, parent.range
     Filter: (parent.range = ANY ('{1,199999}'::bigint[]))

     Output: child1.id, child1.childname, child1.range
     Filter: (child1.range = ANY ('{1,199999}'::bigint[]))

     Output: child2.id, child2.childname, child2.range
     Filter: (child2.range = ANY ('{1,199999}'::bigint[]))

     Output: child3.id, child3.childname, child3.range
     Filter: (child3.range = ANY ('{1,199999}'::bigint[]))
     Rows Removed by Filter: 1

UPDATE dữ liệu

Như ví dụ bên dưới vì ta chưa tạo TRIGGER khi UPDATE bảng master. Nên dữ liệu UPDATE không nằm trong khoảng của ràng buộc CHECK sẽ gây ra lỗi. Nếu muốn khắc phục lỗi này ta phải định nghĩa thêm TRIGGER thực hiện điều hướng dữ liệu UPDATE tới các bảng con tương ứng.

10000 postgres@postgres=# UPDATE parent SET range = 200000 WHERE range = 1; ERROR: new row for relation "child1" violates check constraint "child1_range_check" DETAIL: Failing row contains (1, a, 200000). 10000 postgres@postgres=# UPDATE parent SET range = 2 WHERE range = 1; UPDATE 1 10000 postgres@postgres=#

Declarative Partitioning (PostgreSQL 10 ~)

Nếu như ta phải thực hiện nhiều thao tác mới sử dụng được chức năng Partitioning sử dụng kế thừa như trên, thì từ phiên bản 10 ta chỉ đơn giản thực hiện các thao tác bên dưới là có thể sử dụng được.

1. Tạo bảng master với tuỳ chọn partitioning
2. Tạo bảng con tương ứng cho bảng master

Tạo bảng master Declarative Partitioning

Chức năng Declarative Partitioning hỗ trợ các phương thức partitioning như sau:

• range (PostgreSQL 10~) Chỉ định phạm vi giá trị cho cột khoá của mỗi bảng con
• list (PostgreSQL 10~) Chỉ định danh sách giá trị cho cột khoá của mỗi bảng con
• hash (PostgreSQL 11~) Sử dụng hash để chỉ định giá trị cho cột khoá của mỗi bảng con

Ví dụ bên dưới mình sử dụng phương thức range (tương ứng với ví dụ ở chức năng partitioning sử dụng kế thừa).

10000 postgres@postgres=# CREATE TABLE parent_pg10(id integer, name text, range bigint) PARTITION BY RANGE (range); CREATE TABLE

Tạo bảng con tương ứng cho bảng master

10000 postgres@postgres=# CREATE TABLE child1_pg10 PARTITION OF parent_pg10 FOR VALUES FROM (0) TO (99999) ; CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2_pg10 PARTITION OF parent_pg10 FOR VALUES FROM (99999) TO (200000) ; CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3_pg10 PARTITION OF parent_pg10 FOR VALUES FROM (200000) TO (9223372036854775807) ; CREATE TABLE

Sau khi tạo được bảng con. Bảng master sẽ có cấu trúc như bên dưới.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

0

Thực hiện truy vấn

INSERT & SELECT dữ liệu

Ví dụ bên dưới INSERT 3 dòng dữ liệu thông qua bảng master và xác nhận dữ liệu đã được ghi vào các bảng con nhờ chức năng Declarative Partitioning.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

1

Như kết quả EXPLAIN của câu lệnh SELECT bảng master bên dưới. Mặc định dữ liệu được tìm kiếm qua các bảng con chứa dữ liệu tương ứng (bảng master không chứa dữ liệu).

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

2

Cũng giống với chức năng partitioning sử dụng kết thừa. Sau khi set tham số constraint_exclusion sang off, dữ liệu được tìm kiếm trên tất cả các bảng.

Ở phiên bản PostgreSQL 11, PostgreSQL không sử dụng exclusion constraint cho việc loại trừ tìm kiếm dữ liệu bảng con không cần thiết (prunning), nên tham số constraint_exclusion không có hiệu lực, thay vào đó ta sử dụng tham số enable_partition_pruning.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

3

UPDATE dữ liệu

• Ở phiên bản PostgreSQL 10, cũng giống như với partitioning sử dụng kế thừa, câu lệnh UPDATE thất bại nếu dữ liệu mới cho cột key nằm ở partition khác.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

4

• Ở phiên bản PostgreSQL 11, hạn chế bên trên đã được loại bỏ, dữ liệu tự động điều hướng sang partition tương ứng.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

5

Ở trường hợp dữ liệu đồng thời được update trên một cột key, có thể xảy ra lỗi, vui lòng xem mục hạn chế ở cuối bài viết này.

Performance

Như ví dụ bên dưới. Do có overhead bởi TRIGGER, Performance của INSERT giảm rõ rệt (~10 lần) khi sử dụng phương thức partitioning sử dụng kế thừa.

• Kết quả EXPLAIN sử dụng phương thức partitioning sử dụng kế thừa.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

6

• Kết quả EXPLAIN sử dụng phương thức Declarative Partitioning.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

7

Một số hạn chế và chú ý liên quan tới chức năng Declarative Partitioning

1. Không hỗ trợ Primary Key cho cột khoá

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

8

1. Không hỗ trợ khoá ngoại lai Không hỗ hợ Primary Key đồng nghĩa với không hỗ trợ khoá ngoại lai tham chiếu tới cột khoá.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

9

1. Cập nhật dữ liệu sang partition khác (hạn chế ở phiên bản

   10000 postgres@postgres=# \d+ parent

                                   Table "public.parent"  
   

   Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
   ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:

   insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()  
   

   Child tables: child1,

             child2,  
             child3  
   

   4, đã được fixed ở phiên bản 11)
2. Ở phiên bản PostgreSQL 10, Declarative Partitioning không hỗ trợ lệnh UPDATE chuyển dữ liệu từ partition này qua partition khác.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent); CREATE TABLE 10000 postgres@postgres=#

4

• Phiên bản PostgreSQL 11 đã loại bỏ được hạn chế bên trên, nhưng nếu dữ liệu đang được chuyển qua partition khác, cùng lúc đó có lệnh UPDATE đối với dữ liệu tương ứng sẽ có thể xảy ra lỗi như bên dưới.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

1

1. Không hỗ trợ ON CONFLICT (hạn chế ở phiên bản

   10000 postgres@postgres=# \d+ parent

                                   Table "public.parent"  
   

   Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
   ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:

   insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()  
   

   Child tables: child1,

             child2,  
             child3  
   

   4, đã được fixed ở phiên bản 11)
2. Ở phiên bản 10 Declarative Partitioning không hỗ trợ cấu trúc ON CONFLICT.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

2

• Hạn chế này đã được fixed tại phiên bản 11.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

3

1. TRIGGER mức độ dòng dữ liệu phải được định nghĩa ở bảng con, vì bảng cha không chứa dữ liệu.
2. Bảng con của một bảng master không thể có cả bảng cố định và bảng tạm thời (temp table).

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

4

1. Không hỗ trợ full cho postgres_fdw (hạn chế ở phiên bản

   10000 postgres@postgres=# \d+ parent

                                   Table "public.parent"  
   

   Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
   ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:

   insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()  
   

   Child tables: child1,

             child2,  
             child3  
   

   4, đã được fixed ở phiên bản 11).

Ta có thể sử dụng chức năng partitioning table kết hợp với postgres_fdw để scale out hệ thống như bên dưới ở phiên bản PostgreSQL 11. Ở phiên bản 10 có thể cấu hình được hệ thống bên dưới, nhưng hệ thống chưa hỗ trợ hoàn toàn các câu lệnh SQL cho postgres_fdw. Hệ thống ví dụ bên dưới, sử dụng chức năng partitioning với 2 bảng con, 2 bảng con này tiếp tục được đồng bộ sang các DB install khác thông qua chức năng postgres_fdw.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

5

Những thay đổi ở phiên bản PostgreSQL 11

Ở phiên bản PostgreSQL 11, có 3 cải thiện chính như bên dưới.

Faster partition pruning

Ở phiên bản 10 PostgreSQL sử dụng constraint_exclusion để bỏ qua partition không cần thiết. Việc loại bỏ này thực hiện bởi sử dụng giá trị ở WHERE clause rồi so sánh với các metadata của từng partition. Phiên bản 11 không sử dụng constraint_exclusion nữa mà thực hiện tìm kiếm trực tiếp tới partition cần thiết làm tăng performance.

Partition Pruning at Execution Time

Phiên bản 10 thực hiện bỏ qua partition không cần thiết (prunning) ở giai đoạn planning. Nếu chỉ thực hiện ở giai đoạn này, PostgreSQL sẽ không thể thực hiện prunning được những câu lệnh có truy vấn phụ như ví dụ bên dưới.

• Ở phiên bản 10, PostgreSQL thực hiện scan trên tất cả các bảng con khi biểu thức tìm kiếm là một truy vấn phụ vì truy vấn chưa được thực thi ở giai đoạn planning.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

6

• PostgreSQL 11 thực hiện prunning khi thực thi truy vấn. Nên có thể đối ứng cả truy vấn phụ.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER AS $$ BEGIN

IF (NEW.range <= 99999) THEN
    INSERT INTO child1 VALUES (NEW.*);
ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
    INSERT INTO child2 VALUES (NEW.*);
ELSIF (NEW.range > 199999) THEN
    INSERT INTO child3 VALUES (NEW.*);
ELSE
    RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;

7

Như đã nói ở trên. Do PostgreSQL 10 không sử dụng exclusion constraint để bỏ qua partition không cần thiết, nên tham số constraint_exclusion không có hiệu lực đối với Declarative Partitioning trên phiên bản 11, thay vào đó là parameter enable_partition_pruning (mặc định là on).