Dù PostgreSQL và MySQL tương tự về mặt khái niệm nhưng còn có rất nhiều điểm khác biệt cần xem xét trước khi triển khai.
Tuân thủ ACID
Tính nguyên tử, tính nhất quán, tính cô lập và tính bền vững [ACID] là những thuộc tính cơ sở dữ liệu đảm bảo rằng cơ sở dữ liệu duy trì được trạng thái hợp lệ ngay cả sau khi xảy ra lỗi bất ngờ. Ví dụ: nếu bạn cập nhật một số lượng lớn các hàng nhưng hệ thống giữa chừng gặp sự cố, sẽ không có hàng nào được sửa đổi.
MySQL cung cấp khả năng tuân thủ ACID chỉ khi bạn sử dụng kèm với các công cụ lưu trữ InnoDB và NDB Cluster hoặc mô-đun phần mềm. PostgreSQL hoàn toàn tuân thủ ACID trong tất cả các cấu hình.
Kiểm soát đồng thời
Kiểm soát đồng thời nhiều phiên bản [MVCC] là một tính năng cơ sở dữ liệu nâng cao có khả năng tạo ra các bản sao trùng lặp của bản ghi để đọc và cập nhật cùng một dữ liệu song song một cách an toàn. Khi bạn sử dụng MVCC, nhiều người dùng có thể đồng thời đọc và sửa đổi cùng một dữ liệu mà không làm ảnh hưởng đến tính toàn vẹn của dữ liệu.
Cơ sở dữ liệu MySQL không cung cấp MVCC, nhưng PostgreSQL có hỗ trợ tính năng này.
Chỉ mục
Cơ sở dữ liệu sử dụng các chỉ mục để truy xuất dữ liệu nhanh hơn. Bạn có thể lập chỉ mục những dữ liệu được truy cập thường xuyên bằng cách định cấu hình hệ thống quản lý cơ sở dữ liệu để sắp xếp và lưu trữ những dữ liệu đó theo cách khác với các dữ liệu khác.
MySQL hỗ trợ lập chỉ mục B-tree và R-tree lưu trữ dữ liệu được lập chỉ mục theo thứ bậc. Các loại chỉ mục của PostgreSQL bao gồm cây, chỉ mục biểu thức, chỉ mục một phần và chỉ mục băm. Có nhiều tùy chọn khác để tinh chỉnh các yêu cầu về hiệu năng cơ sở dữ liệu khi bạn điều chỉnh quy mô.
Loại dữ liệu
MySQL đơn thuần là một cơ sở dữ liệu quan hệ. Mặt khác, PostgreSQL là một cơ sở dữ liệu quan hệ đối tượng. Điều này có nghĩa là trong PostgreSQL, bạn có thể lưu trữ dữ liệu dưới dạng đối tượng mang các thuộc tính. Đối tượng là kiểu dữ liệu phổ biến trong nhiều ngôn ngữ lập trình như Java và .NET. Các đối tượng hỗ trợ các mô hình như mối quan hệ cha-con và kế thừa.
Quá trình làm việc với PostgreSQL trực quan hơn đối với các nhà phát triển cơ sở dữ liệu. PostgreSQL cũng hỗ trợ các kiểu dữ liệu khác như mảng và XML.
Chế độ xem
Chế độ xem là một tập con dữ liệu mà hệ thống cơ sở dữ liệu tạo ra bằng cách lấy dữ liệu liên quan từ nhiều bảng.
Dù MySQL có hỗ trợ các chế độ xem, PostgreSQL cung cấp các tùy chọn về chế độ xem nâng cao. Ví dụ: bạn có thể tính toán trước một số giá trị [như tổng giá trị của tất cả các đơn hàng trong một khoảng thời gian nhất định] để tạo ra các chế độ xem cụ thể hóa. Chế độ xem cụ thể hóa cải thiện hiệu năng cơ sở dữ liệu cho các truy vấn phức tạp.
Quy trình được lưu trữ
Các quy trình được lưu trữ là các truy vấn hoặc câu lệnh mã bằng ngôn ngữ truy vấn có cấu trúc [SQL] mà bạn có thể viết và lưu trước. Bạn có thể tái sử dụng cùng một mã nhiều lần, qua đó giúp các tác vụ quản lý cơ sở dữ liệu hiệu quả hơn.
Dù cả MySQL và PostgreSQL đều hỗ trợ các quy trình được lưu trữ, PostgreSQL cho phép bạn gọi các quy trình được lưu trữ viết bằng ngôn ngữ khác chứ không chỉ bằng SQL.
Điều kiện kích hoạt
Điều kiện kích hoạt là một quy trình được lưu trữ chạy tự động khi có sự kiện liên quan xảy ra trong hệ thống quản lý cơ sở dữ liệu.
Trong cơ sở dữ liệu MySQL, bạn chỉ có thể sử dụng các điều kiện kích hoạt AFTER và BEFORE cho câu lệnh INSERT, UPDATE và DELETE trong SQL. Điều đó có nghĩa là quy trình sẽ chạy tự động trước hoặc sau khi người dùng sửa đổi dữ liệu. Trái lại, PostgreSQL hỗ trợ điều kiện kích hoạt INSTEAD OF, do đó, bạn có thể chạy các câu lệnh SQL phức tạp bằng cách sử dụng các hàm.
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.
- Tạo bảng master
- Tạo các bảng con
- 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 and 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
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:Table "public.parent"
Child tables: child1,insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child[]
1].child2, child3 - 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 AND 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.
10000 postgres@postgres=# EXPLAIN [ANALYZE,VERBOSE] SELECT * FROM parent WHERE range IN [1,199999]; 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.
- Tạo bảng master với tuỳ chọn partitioning
- 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 and 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 and 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 and 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 and 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 and 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 and 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 and 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 and 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
- Không hỗ trợ Primary Key cho cột khoá
10000 postgres@postgres=# CREATE TABLE child1[check[range 99999 and range 199999]] inherits[parent]; CREATE TABLE 10000 postgres@postgres=#
8
- 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 and range 199999]] inherits[parent]; CREATE TABLE 10000 postgres@postgres=#
9
- Cập nhật dữ liệu sang partition khác [hạn chế ở phiên bản
10000 postgres@postgres=# \d+ parent
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:Table "public.parent"
Child tables: child1,insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child[]
4, đã được fixed ở phiên bản 11]child2, child3 - Ở 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 and 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 AND NEW.range 199999] THEN
INSERT INTO child3 VALUES [NEW.*];
ELSE
RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;
1
- Không hỗ trợ ON CONFLICT [hạn chế ở phiên bản
10000 postgres@postgres=# \d+ parent
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:Table "public.parent"
Child tables: child1,insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child[]
4, đã được fixed ở phiên bản 11]child2, child3 - Ở 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 AND 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 AND NEW.range 199999] THEN
INSERT INTO child3 VALUES [NEW.*];
ELSE
RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;
3
- 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.
- 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 AND NEW.range 199999] THEN
INSERT INTO child3 VALUES [NEW.*];
ELSE
RAISE EXCEPTION 'out of range';
END IF;
RETURN NULL;
4
- Không hỗ trợ full cho postgres_fdw [hạn chế ở phiên bản
10000 postgres@postgres=# \d+ parent
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---+-+---+--+-+--+--+- id | integer | | | | plain | | childname | text | | | | extended | | range | bigint | | | | plain | | Triggers:Table "public.parent"
Child tables: child1,insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child[]
4, đã được fixed ở phiên bản 11].child2, child3
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 AND 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 AND 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 AND 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].