Hiệu năng của PostgreSQL đóng vai trò then chốt trong quản lý và truy vấn dữ liệu, đặc biệt khi làm việc với các bảng có dung lượng lớn. Để hạn chế tình trạng swap bộ nhớ, tránh quét toàn bộ bảng gây tắc nghẽn hệ thống, phân vùng (partitioning) là một giải pháp hiệu quả giúp chia nhỏ dữ liệu thành các bảng con, từ đó tối ưu tốc độ truy vấn và cải thiện hiệu suất tổng thể. Khi triển khai phân vùng, PostgreSQL cho phép lưu trữ dữ liệu theo các tiêu chí nhất định ngay từ khâu thiết kế cấu trúc bảng. Các phương pháp phân vùng phổ biến gồm Range Partitioning (phân vùng theo khoảng) – chia dữ liệu theo phạm vi giá trị, thường áp dụng cho cột thời gian; List Partitioning (phân vùng theo danh sách) – phân tách dữ liệu dựa trên một danh sách giá trị cụ thể; và Hash Partitioning (phân vùng theo hàm băm) – sử dụng công thức toán học để phân phối dữ liệu đồng đều, phù hợp với các hệ thống có truy vấn đồng thời cao. DataOnline sẽ đưa bạn đi sâu vào từng loại phân vùng trong PostgreSQL, đánh giá ưu điểm của chúng và hướng dẫn triển khai trên nền tảng Managed Database for PostgreSQL của DataOnline Cloud Firewalls. Hãy cùng khám phá cách tối ưu PostgreSQL để nâng cao hiệu suất hệ thống ngay hôm nay!

Mục lục nội dung

Yêu cầu tiên quyết

Sử dụng SSH để truy cập máy chủ.
Tạo một tài khoản sudo không phải root và chuyển sang tài khoản đó.
Cập nhật máy chủ.

Tạo cơ sở dữ liệu mẫu

Cài đặt công cụ khách hàng PostgreSQL psqlTrên hệ thống Ubuntu/Debian:
```
$ sudo apt install -y postgresql-client
```
Trên hệ thống dựa trên CentOS và RHEL:
```
$ sudo yum install postgresql
```
Trên hệ thống Fedora Linux:
```
$ sudo dnf install postgresql
```
Đăng nhập vào Managed Database for PostgreSQLSử dụng psql, đăng nhập vào cơ sở dữ liệu Managed Database for PostgreSQL của DataOnline Cloud Firewalls. Thay thế sample-host.vultrdb.com, 16751, vultradmin bằng thông tin thực tế của bạn:
```
$ psql -h sample-host.vultrdb.com -p 16751 -U vultradmin defaultdb
```
Tạo cơ sở dữ liệu mẫu mới có tên company_db
```
defaultdb=> CREATE DATABASE company_db;
```

Chuyển sang cơ sở dữ liệu company_db

postgres=# \c company_db;

Output:

You are now connected to database "company_db" as user "vultradmin".
company_db=>

Phân vùng PostgreSQL và ưu điểm

Trong PostgreSQL, phân vùng chia dữ liệu thành các bảng nhỏ hơn dựa trên các điều kiện do người dùng chỉ định. Việc triển khai phân vùng mang lại các lợi ích sau:

Cải thiện hiệu năng truy vấn: Bạn có thể tạo báo cáo dựa trên ngày hoặc theo cột cụ thể chỉ bằng cách truy vấn một phân vùng nhất định. Truy vấn sẽ chạy nhanh hơn vì không cần quét toàn bộ bảng gốc.
Tăng cường khả năng truy cập dữ liệu: Truy vấn dựa trên phân vùng giới hạn việc tìm kiếm ở một phân vùng cụ thể, cho phép PostgreSQL lưu vào RAM các phần dữ liệu được sử dụng thường xuyên. Điều này giúp giảm thiểu I/O đĩa và cải thiện hiệu năng truy cập.
Quản lý cơ sở dữ liệu tốt hơn: Với phân vùng, bạn có thể loại bỏ toàn bộ một phân vùng bảng để xóa dữ liệu thay vì phải lọc và xóa từng dòng trong bảng chính, giúp quá trình này nhanh chóng và chính xác hơn.

Dưới đây là cú pháp cơ bản để định nghĩa phân vùng cho một bảng trong PostgreSQL:

postgres=# CREATE TABLE SAMPLE_TABLE_NAME (
               COLUMN_LIST
           ) 
           PARTITION BY PARTITION_TYPE (PARTITION_COLUMN)

Triển khai phân vùng PostgreSQL

Tùy thuộc vào cách nhóm và cấu trúc lưu trữ của cơ sở dữ liệu, PostgreSQL hỗ trợ các phương pháp phân vùng sau:

Phân vùng theo danh sách (List partitions)
Phân vùng theo khoảng (Range partitions)
Phân vùng theo hàm băm (Hash partitions)

Mỗi loại phân vùng sẽ được triển khai như được mô tả trong các phần dưới đây.

Phân vùng theo danh sách

Phân vùng theo danh sách cho phép bạn chia dữ liệu dựa trên các giá trị rời rạc như bộ phận, trạng thái đơn hàng hoặc khu vực. Trong loại phân vùng này, PostgreSQL sẽ kiểm tra dữ liệu trong mỗi trường phân vùng trước khi định tuyến dữ liệu vào bảng cụ thể.

Ví dụ:
Tạo một bảng customers mới với bốn cột, sau đó chỉ định cho PostgreSQL phân vùng bảng theo cột country bằng cách thêm câu lệnh PARTITION BY LIST (country) vào cuối truy vấn SQL:

CREATE TABLE customers (
   customer_id SERIAL,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   country VARCHAR(50),
   CONSTRAINT customers_pkey PRIMARY KEY (customer_id, country)
 ) PARTITION BY LIST (country);

Output:

CREATE TABLE

Tạo hai phân vùng riêng biệt. Phân vùng usa_customers lưu trữ tất cả khách hàng từ USA, trong khi phân vùng canada_customers lưu trữ khách hàng từ CANADA:

company_db=> CREATE TABLE usa_customers PARTITION OF customers FOR VALUES IN ('USA');

             CREATE TABLE canada_customers PARTITION OF customers FOR VALUES IN ('CANADA');

Output:

...
CREATE TABLE

Chèn dữ liệu mẫu vào bảng customers. PostgreSQL sẽ định tuyến dữ liệu vào các phân vùng tương ứng:

company_db=> INSERT INTO customers (first_name, last_name, country) VALUES ('JOHN', 'DOE', 'USA');

              INSERT INTO customers (first_name, last_name, country) VALUES ('MARY', 'SMITH', 'CANADA');

              INSERT INTO customers (first_name, last_name, country) VALUES ('PETER', 'SMITH', 'USA');

              INSERT INTO customers (first_name, last_name, country) VALUES ('JANE', 'ISAAC', 'CANADA');

              INSERT INTO customers (first_name, last_name, country) VALUES ('STEVE', 'JOB', 'CANADA');

              INSERT INTO customers (first_name, last_name, country) VALUES ('FRANCIS', 'JAMES', 'USA');

              INSERT INTO customers (first_name, last_name, country) VALUES ('ANN', 'HENRY', 'CANADA');

Output:

...
INSERT 0 1

Để kiểm tra phân vùng, truy vấn từng bảng phân vùng:

Phân vùng usa_customers:

company_db=> SELECT * FROM usa_customers;

Output:

customer_id | first_name | last_name | country
 -------------+------------+-----------+---------
            1 | JOHN       | DOE       | USA
            3 | PETER      | SMITH     | USA
            6 | FRANCIS    | JAMES     | USA
 (3 rows)

Phân vùng canada_customers:

company_db=> SELECT * FROM canada_customers;

Output:

customer_id | first_name | last_name | country
-------------+------------+-----------+---------
           2 | MARY       | SMITH     | CANADA
           4 | JANE       | ISAAC     | CANADA
           5 | STEVE      | JOB       | CANADA
           7 | ANN        | HENRY     | CANADA
(4 rows)

Phân vùng theo khoảng

Phân vùng theo khoảng chia nhỏ dữ liệu dựa trên một khoảng thời gian. Phân vùng theo khoảng thường được sử dụng trong các bộ dữ liệu thời gian. Ví dụ, trong ứng dụng bán hàng, bạn có thể phân vùng dữ liệu dựa trên cột sales_date; hoặc trong cơ sở dữ liệu đăng ký trường học, bạn có thể phân vùng hồ sơ học sinh dựa trên cột admission_date.

Ví dụ:
Tạo một bảng sales_orders mới và phân vùng theo cột sales_date:

company_db=> CREATE TABLE sales_orders (
                 sales_id SERIAL,
                 sales_date TIMESTAMP,
                 amount DECIMAL(17,2),
                 CONSTRAINT sales_orders_pkey PRIMARY KEY (sales_id, sales_date)
             ) PARTITION BY RANGE (sales_date);

Tạo bốn phân vùng để xử lý dữ liệu bán hàng theo quý cho năm 2023, chỉ định ranh giới ngày trong mỗi phân vùng:

company_db=> CREATE TABLE so_q1_2023 PARTITION OF sales_orders FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

             CREATE TABLE so_q2_2023 PARTITION OF sales_orders FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

             CREATE TABLE so_q3_2023 PARTITION OF sales_orders FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

             CREATE TABLE so_q4_2023 PARTITION OF sales_orders FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

Output:

...
CREATE TABLE

Chèn dữ liệu mẫu vào bảng sales_orders:

company_db=> INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-01-01', 500);

             INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-03-15', 1200);

             INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-04-01', 3600);

             INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-06-30', 800);

             INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-09-15', 2400);

             INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-10-01', 8700);

             INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-11-11', 2780);

             INSERT INTO sales_orders (sales_date, amount) VALUES ('2023-12-31', 3650);

Output:

...
INSERT 0 1

Truy vấn dữ liệu trên từng phân vùng để kiểm tra phân chia dữ liệu:

Phân vùng so_q1_2023:

company_db=> SELECT * FROM so_q1_2023;

Output:

sales_id |     sales_date      | amount
  ----------+---------------------+---------
          1 | 2023-01-01 00:00:00 |  500.00
          2 | 2023-03-15 00:00:00 | 1200.00
  (2 rows)

Phân vùng so_q2_2023:

company_db=> SELECT * FROM so_q2_2023;

Output:

sales_id |     sales_date      | amount
 ----------+---------------------+---------
         3 | 2023-04-01 00:00:00 | 3600.00
         4 | 2023-06-30 00:00:00 |  800.00
 (2 rows)

Phân vùng so_q3_2023:

company_db=> SELECT * FROM so_q3_2023;

Output:

sales_id |     sales_date      | amount
  ----------+---------------------+---------
          5 | 2023-09-15 00:00:00 | 2400.00
  (1 row)

Phân vùng so_q4_2023:

company_db=> SELECT * FROM so_q4_2023;

Output:

 sales_id |     sales_date      | amount
----------+---------------------+---------
        6 | 2023-10-01 00:00:00 | 8700.00
        7 | 2023-11-11 00:00:00 | 2780.00
        8 | 2023-12-31 00:00:00 | 3650.00
(3 rows)

Phân vùng theo hàm băm

Trong phân vùng theo hàm băm của PostgreSQL, dữ liệu được phân chia sử dụng một công thức toán học. Phương pháp phân vùng này lý tưởng cho các trường hợp không có cách rõ ràng để phân chia bản ghi dựa trên ngày hoặc giá trị rời rạc. Thuật toán băm phân phối dữ liệu vào các bảng con để tránh quá tải bảng gốc. Một phân vùng theo hàm băm yêu cầu một số chia (modulus) và phần dư (remainder); hai giá trị này chỉ định vị trí PostgreSQL lưu trữ dữ liệu trong mỗi phân vùng, như được minh họa trong ví dụ sau.

Ví dụ:
Tạo một bảng employees mới, bao gồm câu lệnh PARTITION BY hash(employee_id) để phân vùng bảng theo hàm băm của cột employee_id:

company_db=> CREATE TABLE employees ( 
                 employee_id SERIAL NOT NULL, 
                 first_name varchar(50),
                 last_name varchar(50)
             ) PARTITION BY hash(employee_id);

Output:

CREATE TABLE

Tạo ba phân vùng cho bảng employees:

company_db=> CREATE TABLE emp_p1 PARTITION OF employees FOR VALUES WITH (MODULUS 3, REMAINDER 0);

             CREATE TABLE emp_p2 PARTITION OF employees FOR VALUES WITH (MODULUS 3, REMAINDER 1);

             CREATE TABLE emp_p3 PARTITION OF employees FOR VALUES WITH (MODULUS 3, REMAINDER 2);

Output:

...
CREATE TABLE

Chèn dữ liệu mẫu vào bảng employees:

company_db=> INSERT INTO employees (first_name, last_name) VALUES ('JOHN', 'DOE');

              INSERT INTO employees (first_name, last_name) VALUES ('MARY', 'SMITH');

              INSERT INTO employees (first_name, last_name) VALUES ('JANE', 'ERIC');

              INSERT INTO employees (first_name, last_name) VALUES ('PETER', 'JOB');

              INSERT INTO employees (first_name, last_name) VALUES ('RACHEAL', 'FRANCIS');

              INSERT INTO employees (first_name, last_name) VALUES ('STEVE', 'ISAAC');

Output:

...
INSERT 0 1

Truy vấn các bảng phân vùng:

Phân vùng emp_p1:

company_db=> SELECT * FROM emp_p1;

Output:

employee_id | first_name | last_name
 -------------+------------+-----------
            2 | MARY       | SMITH
            4 | PETER      | JOB
            6 | STEVE      | ISAAC
 (3 rows)

Phân vùng emp_p2:

company_db=> SELECT * FROM emp_p2;

Output:

employee_id | first_name | last_name
-------------+------------+-----------
          3 | JANE       | ERIC
(1 row)

Phân vùng emp_p3:

company_db=> SELECT * FROM emp_p3;

Output:

employee_id | first_name | last_name
-------------+------------+-----------
           1 | JOHN       | DOE
           5 | RACHEAL    | FRANCIS
(2 rows)

Kết luận

Trong bài viết này, bạn đã triển khai thành công ba phương pháp phân vùng khác nhau trong PostgreSQL. Việc chia nhỏ các bảng dữ liệu lớn thành những phân vùng hợp lý không chỉ giúp tối ưu tốc độ truy vấn mà còn giảm tải I/O đĩa, nâng cao khả năng quản lý dữ liệu. Tùy vào quy mô và yêu cầu cụ thể của hệ thống, bạn có thể cân nhắc các kỹ thuật phân vùng nâng cao hơn để tối ưu hiệu suất. Nếu ứng dụng của bạn đòi hỏi xử lý dữ liệu phức tạp hơn, hãy xem xét sử dụng mô hình kế thừa bảng của PostgreSQL hoặc tận dụng câu lệnh UNION để kết hợp nhiều bảng, giúp thực thi các truy vấn phức tạp một cách hiệu quả.

Tài liệu, Hướng dẫn sử dụng Linux, LAMP, LEMP, Webserver...

Cải thiện tốc độ truy vấn dữ liệu lớn với partitioning trong PostgreSQL

Yêu cầu tiên quyết

Tạo cơ sở dữ liệu mẫu

Phân vùng PostgreSQL và ưu điểm

Triển khai phân vùng PostgreSQL

Phân vùng theo danh sách

Phân vùng theo khoảng

Phân vùng theo hàm băm

Kết luận

Nguyễn Trung Văn

Để lại một bình luận Hủy