Tạo Index cho Partitioned Table trong PostgreSQL

Khi làm việc với các bảng được phân vùng (partitioned tables) trong PostgreSQL, bạn cần chú ý rằng mỗi phân vùng là một bảng riêng biệt. Do đó, bạn phải tạo index cho từng phân vùng một cách độc lập, hoặc bạn có thể tạo index trên bảng mẹ (parent table), sau đó PostgreSQL sẽ tự động tạo các index tương ứng trên từng phân vùng.

Ví dụ về tạo Index trên Partitioned Table

Giả sử bạn có một bảng phân vùng sales như sau:

CREATE TABLE sales ( id SERIAL PRIMARY KEY, sale_date DATE, amount DECIMAL ) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM (‘2022-01-01’) TO (‘2023-01-01’); CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM (‘2023-01-01’) TO (‘2024-01-01’);

Tạo Index trên Bảng Mẹ

Khi bạn tạo index trên bảng mẹ, PostgreSQL sẽ tự động tạo index tương ứng trên tất cả các phân vùng.

CREATE INDEX sales_sale_date_idx ON sales (sale_date);

PostgreSQL sẽ tự động tạo sales_sale_date_idx trên các phân vùng sales_2022 và sales_2023.

Tạo Index Trên Từng Phân Vùng

Nếu bạn muốn tạo các index riêng lẻ cho từng phân vùng, bạn có thể làm như sau:

CREATE INDEX sales_2022_sale_date_idx ON sales_2022 (sale_date);

CREATE INDEX sales_2023_sale_date_idx ON sales_2023 (sale_date);

Quản Trị Index Trên Partitioned Table

Liệt Kê Các Index

Để liệt kê tất cả các index trên một bảng phân vùng và các phân vùng của nó, bạn có thể sử dụng các truy vấn sau:

— Liệt kê các index trên bảng mẹ SELECT * FROM pg_indexes WHERE tablename = ‘sales’;

— Liệt kê các index trên phân vùng SELECT * FROM pg_indexes WHERE tablename = ‘sales_2022’;

SELECT * FROM pg_indexes WHERE tablename = ‘sales_2023’;

Xóa Index

Để xóa một index từ bảng mẹ hoặc phân vùng, bạn có thể sử dụng lệnh DROP INDEX:

— Xóa index từ bảng mẹ (sẽ tự động xóa index trên các phân vùng) DROP INDEX sales_sale_date_idx;

— Xóa index từ phân vùng cụ thể DROP INDEX sales_2022_sale_date_idx;

Kiểm Tra và Bảo Trì Index

Bạn có thể sử dụng các lệnh sau để kiểm tra và bảo trì các index trên bảng phân vùng:

• Kiểm Tra Index:

REINDEX TABLE sales;

• Vacuum Index:

VACUUM ANALYZE sales;

Quản Lý Partitioned Tables Hiệu Quả

Sử dụng Index một cách hợp lý:

• • Tạo index trên các cột thường xuyên được sử dụng trong các truy vấn.
  • Tránh tạo quá nhiều index để giảm thiểu chi phí ghi dữ liệu.

Tối ưu hóa phân vùng:

• • Đảm bảo rằng các phân vùng được tạo dựa trên các cột có giá trị phân biệt cao (ví dụ: ngày tháng, ID).
  • Sử dụng các phương pháp phân vùng hợp lý như RANGE, LIST, HASH, hoặc COMPOSITE.

Bảo trì định kỳ:

• • Thực hiện REINDEX, VACUUM, và ANALYZE định kỳ để duy trì hiệu suất của cơ sở dữ liệu.
  • Theo dõi và xóa các phân vùng không còn cần thiết để giải phóng tài nguyên.

Với việc sử dụng đúng cách các index trên partitioned table và quản lý chúng một cách hiệu quả, bạn có thể cải thiện đáng kể hiệu suất truy vấn và tối ưu hóa việc sử dụng tài nguyên trong PostgreSQL.

Chi tiết:

chúng ta có table orders đã partition và thực hiện tạo index cho nó:

=>Khi tạo index cho bảng chính => hệ thống tự động tạo index cho toàn bộ các partition của table đó.

Thêm data vào table:

mm=# insert into orders

mm-# SELECT 11, 22, 33,’2024-02-05′

mm-# FROM generate_series(1, 1000000);

INSERT 0 1000000

Kiểm tra lại index: => đã update index, size tăng lên so với trước khi thêm từ 16KB tăng lên 6320KB