5 Bí Quyết Tối Ưu Hóa Hiệu Suất PostgreSQL

Trước đây, một nhóm đã đổ lỗi cho mọi vấn đề hiệu suất là do PostgreSQL. Họ tuyên bố: “Nó quá chậm!” Vấn đề thực sự là họ đã sử dụng PostgreSQL như một bảng Excel khổng lồ. Họ không sử dụng chỉ mục (indexes). Họ không tinh chỉnh truy vấn (query tuning). Họ thiếu kiến thức cơ bản.

Kiểm tra lại dịch vụ và kết nối

1. Kiểm tra trạng thái dịch vụ PostgreSQL: sudo systemctl status postgresql
Bạn phải thấy trạng thái là active (running).
2. Thử kết nối vào PostgreSQL: Chuyển sang user postgres và mở shell psql.

sudo -u postgres psql

Lần này, bạn sẽ kết nối thành công và thấy lời chào của PostgreSQL. Theo mặc định, PostgreSQL sử dụng một phương thức xác thực gọi là “peer” cho user hệ thống postgres. Điều này có nghĩa là bạn chỉ có thể đăng nhập vào PostgreSQL với tư cách là user postgres mà không cần mật khẩu.

Đặt mật khẩu cho user postgres (Rất quan trọng!)

# Mở trình shell psql
psql

# Bên trong psql, chạy lệnh SQL để đặt mật khẩu
ALTER USER postgres PASSWORD ‘mat_khau_cua_ban’;

# Thoát khỏi psql
\q

Trong thực tế, bạn không nên dùng user postgres cho các ứng dụng của mình. Hãy tạo một database và user riêng.
psql -h localhost -U postgres
postgres=# CREATE DATABASE vectors;
CREATE DATABASE
postgres=# CREATE USER vuser WITH ENCRYPTED PASSWORD ‘Test123$’;
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE vectors TO vuser;
GRANT
Kết nối vào PostgreSQL với quyền superuser: bash

Nếu bạn chưa kết nối đúng database, hãy chạy: \c <ten_database>

Bây giờ bạn có thể kết nối vào database myappdb với user myappuser vừa tạo:
psql -h localhost -U myappuser -d myappdb
Hệ thống sẽ yêu cầu bạn nhập mật khẩu (mat_khau_user_moi). Nếu thành công, bạn sẽ thấy lời chào của psql.

Mặc định, PostgreSQL chỉ lắng nghe kết nối từ localhost (chính máy chủ đó). Để cho phép các máy khác trong mạng kết nối, bạn cần sửa hai file cấu hình.

Lưu ý quan trọng về bảo mật: Chỉ thực hiện bước này nếu bạn thực sự cần. Hãy chắc chắn rằng bạn đã đặt mật khẩu mạnh cho tất cả các user và cân nhắc cấu hình tường lửa.

1\. Chỉnh sửa file postgresql.conf

File này điều khiển các cài đặt chung của máy chủ.
sudo nano /etc/postgresql/15/main/postgresql.conf
Tìm dòng sau:

#listen_addresses = ‘localhost’
Bỏ dấu \# ở đầu và thay đổi localhost thành ‘\*’ để lắng nghe trên tất cả các địa chỉ IP của máy chủ và localhost sẽ vô được:

listen_addresses = ‘*’
Lưu file và thoát (trong nano, nhấn Ctrl+X, rồi Y, rồi Enter).

2\. Chỉnh sửa file pg_hba.conf

File này kiểm soát việc xác thực client (máy khách).

sudo nano /etc/postgresql/16/main/pg_hba.conf
Cuộn xuống cuối file và thêm một dòng mới để cho phép kết nối từ bất kỳ địa chỉ IP nào sử dụng xác thực mật khẩu (md5).

# TYPE DATABASE USER ADDRESS METHOD

# “local” is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256

# —- DÒNG MỚI THÊM VÀO —-
host all all 0.0.0.0/0 md5

Giải thích:
* host: Kết nối TCP/IP.
* all all: Cho phép tất cả user kết nối đến tất cả database.
* 0\.0.0.0/0: Cho phép kết nối từ bất kỳ địa chỉ IPv4 nào.
* md5: Yêu cầu client cung cấp mật khẩu đã được băm bằng MD5.

> Mẹo bảo mật: Thay vì 0\.0.0.0/0, bạn nên giới hạn trong mạng cục bộ của mình, ví dụ: 192\.168.1.0/24.

Lưu file và thoát. khởi động lại dịch vụ:
sudo systemctl restart postgresql

PostgreSQL không chậm

Chính đội ngũ quản lý đang làm chậm nó. Nếu doanh nghiệp muốn cơ sở dữ liệu hoạt động nhanh chóng, đây là năm bí quyết. Năm bí quyết này phân biệt giữa người nghiệp dư và chuyên gia.

1. Lập Chỉ Mục (Indexing) Như Sống Còn

Nếu truy vấn chạy mà không có chỉ mục phù hợp, PostgreSQL phải thực hiện quét toàn bộ bảng (full table scan) mỗi lần. Điều này giống như tìm chìa khóa bằng cách lật tung mọi đệm ghế trong mọi ngôi nhà. Đây là một hành động lãng phí tài nguyên và thời gian nghiêm trọng.

Bước Hành Động:

• Sử dụng lệnh EXPLAIN ANALYZE trên các truy vấn chậm.
• Nếu kết quả hiển thị Seq Scan (Quét tuần tự), doanh nghiệp cần tạo chỉ mục.
• Sử dụng chỉ mục B-Tree cho các bộ lọc so sánh bằng (equality) và phạm vi (range filters). Ví dụ: CREATE INDEX idx_users_email ON users(email);
• Sử dụng chỉ mục GIN cho dữ liệu JSONB và tìm kiếm toàn văn (full-text search). Ví dụ: CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));

Lưu ý: Việc lập chỉ mục quá mức cũng là một sai lầm. Mỗi thao tác chèn (insert) hoặc cập nhật (update) đều phải duy trì các chỉ mục đó. Việc lập chỉ mục phải chính xác, không được cẩu thả.

2. Vacuum Không Phải Tùy Chọn — Đó Là Hỗ Trợ Sự Sống

PostgreSQL không ghi đè lên các hàng dữ liệu cũ. Nó tạo ra các phiên bản mới của hàng dữ liệu. Điều này rất tốt cho tính đồng thời (concurrency). Tuy nhiên, nó sẽ rất tệ nếu không được dọn dẹp.

Nếu không có quá trình vacuuming, cơ sở dữ liệu sẽ bị phình to (bloat). Điều này ảnh hưởng nghiêm trọng đến hiệu suất.

Bước Hành Động:

• Đảm bảo autovacuum được bật. Chức năng này được bật theo mặc định. Không được tắt chức năng này.
• Đối với các bảng có tần suất thay đổi cao (high-churn tables), cần điều chỉnh các tham số autovacuum_analyze_scale_factor và autovacuum_vacuum_scale_factor. Điều này giúp quá trình dọn dẹp diễn ra nhanh hơn.

Ví dụ:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

Thiết lập này đảm bảo bảng orders không trở thành thảm họa hiệu suất do phình to.

3. Viết Truy Vấn Cho Con Người, Sau Đó Tối Ưu Hóa Cho PostgreSQL

Viết câu lệnh SQL phải rõ ràng trước. Sau đó mới tiến hành tối ưu hóa. Các truy vấn được tạo bởi ORM (Object-Relational Mapping) thường kém hiệu quả. Chúng thường sử dụng SELECT *. Chúng kết hợp (join) mọi thứ và lọc sau. Điều này giống như gọi toàn bộ thực đơn chỉ để ăn một miếng khoai tây chiên.

Bước Hành Động:

• Chỉ SELECT các cột dữ liệu cần thiết.
• Đẩy việc lọc dữ liệu xuống cơ sở dữ liệu. Không thực hiện lọc ở tầng ứng dụng.
• Chia nhỏ các truy vấn phức tạp. Đôi khi, hai truy vấn nhỏ, được lập chỉ mục tốt, chạy nhanh hơn một truy vấn kết hợp khổng lồ.

Ví dụ về thực hành tốt:

SELECT id, name FROM users WHERE active = true LIMIT 50;

Truy vấn này ngắn gọn, tập trung, và nhanh chóng.

4. Tinh Chỉnh PostgreSQL Như Xe Đua, Không Phải Xe Tiết Kiệm Nhiên Liệu

Các cài đặt mặc định của PostgreSQL rất thận trọng. Chúng được thiết kế để chạy trên phần cứng yếu. Nếu doanh nghiệp có phần cứng mạnh, cần khai thác tối đa tiềm năng của nó.

Các Tham Số Chính Cần Điều Chỉnh:

• shared_buffers: Thường đặt từ 25% đến 40% tổng dung lượng RAM của máy chủ.
• work_mem: Tăng giá trị này nếu hệ thống thực hiện các thao tác sắp xếp (sorts) hoặc kết hợp (joins) lớn. Không nên đặt quá cao.
• effective_cache_size: Thông báo cho PostgreSQL biết dung lượng bộ nhớ đệm hệ điều hành (OS cache) có sẵn.

Ví dụ cấu hình:

shared_buffers = 8GB
work_mem = 64MB
effective_cache_size = 24GB

Đây không phải là cấu hình sao chép nguyên mẫu. Doanh nghiệp phải đo lường, điều chỉnh, và lặp lại. Việc tinh chỉnh là sự kết hợp giữa khoa học và nghệ thuật.THự hiện tối ưu cấu hình PostgreSQL:

sudo nano /etc/postgresql/<version>/main/postgresql.conf

Dưới đây là các tham số nên chỉnh cho máy 8GB RAM:

Sau khi chỉnh:

sudo systemctl restart postgresql

5. Giám Sát Như Một Người Bị Ám Ảnh Kiểm Soát

Không thể cải thiện những gì không được đo lường. Vận hành mà không giám sát là hành động nguy hiểm.

Bước Hành Động Giám Sát:

• Sử dụng pg_stat_activity để xem các truy vấn đang hoạt động.
• Kiểm tra pg_stat_statements để xác định các truy vấn tiêu tốn nhiều tài nguyên nhất.
• Sử dụng các công cụ như PgHero hoặc pganalyze để có giao diện quản lý thân thiện hơn.

Một truy vấn chạy mất 100ms có vẻ không tệ. Nhưng nếu nó được thực thi 10.000 lần mỗi phút, đó là sự suy giảm hiệu suất nghiêm trọng. Đây là cái chết do nhiều vết cắt nhỏ (death by papercuts).

Tóm Lại:

PostgreSQL có thể cực kỳ nhanh chóng. Tuy nhiên, nó không thể tự sửa chữa. Hãy lập chỉ mục một cách thông minh. Thực hiện vacuum một cách nghiêm ngặt. Viết các truy vấn sạch hơn. Tinh chỉnh các cấu hình. Giám sát như thể công việc phụ thuộc vào nó. Cơ sở dữ liệu của doanh nghiệp chỉ tốt bằng kỷ luật của đội ngũ quản lý. Đừng đổ lỗi cho PostgreSQL khi thủ phạm thực sự là sự lơ là.

Tham khảo: medium.com

Dùng PGVector làm database lưu trữ vector cho n8n

Trước hết cài extention đi đã, xem phiên bản PostgreSQL đang dùng (psql –version để kiểm tra). Nên dùng version mới >14 thì tốt nhất.

sudo apt install postgresql-16-pgvector

Sau khi chạy lệnh này, bạn có thể thử lại việc kích hoạt extension trong PostgreSQL.

Bạn cần khởi động lại dịch vụ PostgreSQL để tải thư viện mới. sudo systemctl restart postgresql

sudo systemctl restart postgresql@16-main

Kết nối vào database của bạn và chạy:

CREATE EXTENSION vector;

Lệnh này bây giờ sẽ thành công vì nó có thể tìm thấy file vector.control thông qua symlink bạn vừa tạo. Trong thư mục extension: ls -l /usr/share/postgresql/16/extension/ | grep vector

Model text-embedding-nomic-embed-text-v1.5 (của Nomic AI) là một trong những embedding model phổ biến và mạnh ngang hoặc hơn OpenAI text-embedding-3-small, dùng nhiều trong RAG và semantic search. Phiên bản đầu tiên tạo ra vector có 768 chiều. Hiện dùng LLM Studio thì có vector 192 chiều. Bắt đầu lại như sau:

Kết nối vào psql với quyền superuser: sudo -u postgres psql

psql (16.9)
postgres=# CREATE DATABASE vectors;
CREATE DATABASE
postgres=# CREATE USER vuser WITH ENCRYPTED PASSWORD ‘Test123$’;
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE vectors TO vuser;
GRANT
postgres=# \c vectors
vectors=# CREATE EXTENSION vector;
CREATE EXTENSION
vectors=# GRANT ALL PRIVILEGES ON SCHEMA public TO vuser;
GRANT

vectors=> \dx vector
List of installed extensions
Name | Version | Schema | Description
——–+———+——–+——————————————————
vector | 0.8.0 | public | vector data type and ivfflat and hnsw access methods
(1 row)

vectors=> CREATE TABLE embeddings
(
id bigserial PRIMARY KEY,
embedding vector(192) NOT NULL,
text text,
metadata jsonb
);
CREATE TABLEGRANT ALL PRIVILEGES ON TABLE embeddings TO your_user_name;

GRANT USAGE, SELECT ON SEQUENCE embeddings_id_seq TO your_user_name;

Kiểm tra thử table được tạo: \d+ tên bảng

Liệt kê bảng: \d

Insert vào phải đúng số chiều. Không thì phải khai báo lại bảng.

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding vector(768)
);

Có thể thêm cột mới rồi xoá cột cũ:

ALTER TABLE documents ADD COLUMN embedding_new vector(768);
UPDATE documents SET embedding_new = NULL;
ALTER TABLE documents DROP COLUMN embedding;
ALTER TABLE documents RENAME COLUMN embedding_new TO embedding;

Ví dụ tìm văn bản gần nhất với một vector truy vấn:

SELECT id, content, embedding <-> ‘[0.11, 0.33, 0.55, 0.77, 0.91, 0.10]’::vectors.vector(6) AS distance
FROM documents
ORDER BY distance
LIMIT 3;

Toán tử <-> trong vectors là khoảng cách cosine hoặc Euclidean (tùy cấu hình extension).
Giá trị nhỏ hơn = tương tự hơn.

Bây giờ chúng ta đã sẵn sàng chuyển sang n8n và tạo một quy trình làm việc đơn giản để tạo nhúng và lưu trữ chúng trong pgvector.

JSON tại đây .

Hãy đảm bảo chọn text-embedding-3-large mô hình nhúng. Trong bộ chia mã thông báo, hãy chọn kích thước khối là 1.000 và độ chồng chéo là 250 để có kết quả khả thi.

Đối với nút Postgres PGVector Store, hãy tạo thông tin đăng nhập mới bằng cơ sở dữ liệu, tên người dùng và mật khẩu từ quá trình thiết lập trước đó. Đối với máy chủ, hãy chọn tùy thuộc vào cách bạn đang chạy n8n:

Xác minh trực tiếp trong cơ sở dữ liệu…

vectors=> select count(*) from embeddings;

Tuyệt vời, giờ chúng ta đã sẵn sàng để tiếp tục và tạo một tác nhân RAG đơn giản trong n8n sử dụng các nhúng của chúng ta.

Ví dụ về tác nhân RAG sử dụng pgvector làm công cụ

Cuối cùng, hãy tạo một tác nhân RAG đơn giản trong n8n sử dụng kho vector pgvector đã được điền sẵn của chúng ta để trả lời các chi tiết về quỹ Templeton. Tác nhân này hoàn toàn đơn giản. Mẫu Json

Hãy đảm bảo sử dụng thông tin xác thực pgvector đã tạo trước đó và chọn mô hình nhúng text-embedding-3-large. Điều thú vị nhất trong quy trình làm việc này là lời nhắc hệ thống trong nút AI Agent.

You are an assistant for the analysis and determination of key figures and
information on Franklin Templeton funds. Your task is to answer questions on
this subject with the help of your vector database.
If you cannot answer a question with the help of your database or if the
question is unclear, answer accordingly.

Structure of your answers:
– Clear and to the point
– Provide specific figures and information that were requested
– Indicate from which document in the vector database you obtained the information

Trong ví dụ này, gpt-4o được sử dụng làm mô hình. Đối với nút Trả lời Câu hỏi, bạn có thể sử dụng một mô tả đơn giản.

Delivers facts & figures about Templeton funds.

Vậy là xong. Bây giờ bạn có thể khởi động tác nhân RAG và đặt câu hỏi sẽ được trả lời bằng kho lưu trữ vector pgvector.

Nguồn