Chương 5. Định nghĩa dữ liệu
5.9.2. Triển khai phân vùng
Để thiết lập một bảng có phân vùng, hãy làm những điều sau:
1. Tạo bảng “chủ” (“master”), từ đó tất cả các phân vùng sẽ kế thừa.
Bảng này sẽ không chứa dữ liệu. Không định nghĩa bất kỳ ràng buộc kiểm tra nào trong bảng này, trừ phi bạn có ý định chúng sẽ được áp dụng y hệt cho tất cả các phân vùng.
Không có nghĩa trong việc định nghĩa bất kỳ chỉ số hoặc hằng số độc nhất nào trong nó.
2. Tạo vài bảng “con” mà từng bảng kế thừa từ bảng chủ. Thông thường, các bảng đó sẽ không thêm bất kỳ cột nào vào tập hợp được kế thừa từ bảng chủ.
Chúng ta sẽ tham chiếu tới các bảng con như là các phân vùng, dù chúng theo mọi cách là những bảng PostgreSQL thông thường.
3. Thêm các ràng buộc bảng vào các bảng phân vùng để định nghĩa các giá trị khóa được phép trong từng phân vùng.
Các ví dụ điển hình có thể là:
CCHECK ( x = 1 )
CHECK ( county IN ( ’Oxfordshire’, ’Buckinghamshire’, ’Warwickshire’ )) CHECK ( outletID >= 100 AND outletID < 200 )
Hãy chắc chắn các ràng buộc đảm bảo rằng không có sự chồng lấn giữa các giá trị khóa được phép trong các phần khác nhau. Một sai sót phổ biến là thiết lập dải các ràng buộc giống như:
CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 )
Điều này là sai vì không rõ phân vùng nào giá trị khóa 200 nằm trong đó.
Lưu ý là không có sự khác biệt trong cú pháp giữa việc phân vùng theo khoảng và phân vùng liệt kê; những khái niệm đó chỉ là diễn tả.
4. Đối với từng phân vùng, hãy tạo một chỉ số trong (các) cột khóa, cũng như bất kỳ các chỉ số khác mà bạn có thể muốn. (Chỉ số chính không nhất thiết là khắt khe, mà trong hầu hết các kịch bản là hữu dụng. Nếu bạn định để cho các giá trị khóa là độc nhất thì bạn nên luôn tạo một ràng buộc độc nhất hoặc khóa chủ cho từng phân vùng).
5. Như một sự lựa chọn, hãy định nghĩa một trigger hoặc qui tắc để tái định tuyến các dữ liệu được chèn vào bảng chủ tới phân vùng phù hợp.
6. Hãy đảm bảo tham số cấu hình constraint_exclusion không bị vô hiệu hóa trong
postgresql.conf. Nếu là thế, các truy vấn sẽ không được tối ưu hóa như mong đợi.
Ví dụ, giả sử chúng ta đang xây dựng một cơ sở dữ liệu cho một công ty làm kem lớn. Công ty đo các nhiệt độ lúc cao điểm mỗi ngày cũng như lượng kem bán trong từng vùng. Về nguyên tắc, chúng ta muốn một bảng giống như:
CREATE TABLE measurement ( city_id int not null,
logdate date not null, peaktemp int,
unitsales int );
Chúng ta biết rằng hầu hết các truy vấn sẽ truy cập chỉ các dữ liệu của tuần, tháng, quý trước, vì sử dụng chính bảng này sẽ là để chuẩn bị cho các báo cáo trực tuyến để quản lý. Để giảm số lượng các dữ liệu cũ mà cần phải được lưu trữ, chúng tôi quyết định chỉ giữ các dữ liệu 3 nằm gần đây nhất.
Vào đầu mỗi tháng chúng tôi sẽ loại bỏ các dữ liệu của tháng cũ nhất.
Trong tình huống này chúng ta có thể sử dụng việc phân vùng để giúp chúng ta đáp ứng được tất cả các yêu cầu khác nhau của chúng ta đối với những đo đếm cho bảng. Tuân theo các bước được phác họa ở trên, việc phân vùng có thể được thiết lập như sau:
1. Bảng chủ là bảng đo đếm, được khai báo chính xác như ở trên.
2. Tiếp theo chúng ta tạo một phân vùng cho từng tháng hoạt động:
CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
Mỗi trong số các phân vùng là các bảng hoàn chỉnh theo quyền của riêng chúng, nhưng chúng kế thừa các định nghĩa của chúng từ bảng đo đếm.
Điều này giải quyết một trong những vấn đề của chúng ta: xóa các dữ liệu cũ. Mỗi tháng, tất cả điều chúng ta sẽ cần phải làm là thực hiện một lệnh bỏ bảng DROP TABLE trong bảng con cũ nhất và tạo ra một bảng con mới cho các dữ liệu của tháng mới.
3. Chúng ta phải đưa ra các ràng buộc bảng không chồng lấn. Thay vì chỉ tạo các bảng phân vùng như ở trên, script tạo bảng thực sự sẽ là:
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE ’2006-02-01’ AND logdate < DATE ’2006-03-01’ ) ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE ’2006-03-01’ AND logdate < DATE ’2006-04-01’ ) ) INHERITS (measurement);
...CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE ’2007-11-01’ AND logdate < DATE ’2007-12-01’ ) ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE ’2007-12-01’ AND logdate < DATE ’2008-01-01’ ) ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE ’2008-01-01’ AND logdate < DATE ’2008-02-01’ ) ) INHERITS (measurement);
4. Chúng ta có lẽ cũng cần các chỉ số trong các cột khóa:
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
Chúng ta chọn không thêm các chỉ số nữa vào thời điểm này.
5. Chúng ta muốn ứng dụng của chúng ta sẽ có khả năng nói INSERT INTO measurement … và có các dữ liệu được tái định tuyến vào trong bảng phân vùng phù hợp. Chúng ta có thể dàn xếp bằng việc gắn một hàm trigger phù hợp tới bảng chủ.
CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Sau khi tạo hàm đó, chúng ta tạo một trigger gọi hàm trigger đó:
CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
Chúng ta phải tái định nghĩa hàm trigger mỗi tháng sao cho nó luôn chỉ tới phân vùng hiện hành. Tuy nhiên, định nghĩa trigger không cần phải được cập nhật.
Chúng ta có thể muốn chèn các dữ liệu và để máy chủ tự động định vị phân vùng vào hàng nào mà sẽ được thêm vào. Chúng ta có thể làm điều này bằng một hàm trigger phức tạp hơn, ví dụ:
CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE ’2006-02-01’ AND NEW.logdate < DATE ’2006-03-01’ ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ’2006-03-01’ AND NEW.logdate < DATE ’2006-04-01’ ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ’2008-01-01’ AND...
NEW.logdate < DATE ’2008-02-01’ ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE RAISE EXCEPTION ’Date out of range. Fix the measurement_insert_trigger() function!’;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Định nghĩa trigger là y hệt như trước. Lưu ý rằng từng kiểm thử IF phải chính xác khớp với ràng buộc kiểm tra CHECK cho phân vùng của nó.
Trong khi hàm này là phức tạp hơn so với trường hợp tháng duy nhất, thì không cần phải được cập nhật thường xuyên, vì các nhánh có thể được thêm vào trước khi cần thiết.
Lưu ý: Trong thực tế có thể là tốt nhất để kiểm tra phân vùng mới nhất trước, nếu hầu hết các vụ chèn đi vào phân vùng đó. Để đơn giản, chúng tôi đã chỉ ra các kiểm thử trigger theo cùng trật tự như trong các phần khác của ví dụ này.
Như chúng ta có thể thấy, một sơ đồ phân vùng phức tạp có thể đòi hỏi một số lượng các DDL đáng kể. Trong ví dụ ở trên chúng ta có thể đang tạo ra một phân vùng mới mỗi tháng, nên có lẽ là khôn ngoan để viết một script mà tạo ra DDL theo yêu cầu một cách tự động.