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

Chúng ta sẽ xây dựng một chatbot Agentic RAG (Retrieval-Augmented Generation – Thế hệ Tăng cường Truy xuất) hoàn chỉnh, sử dụng n8n làm bộ điều phối, PostgreSQL 16 + pgvector làm kho vector, OpenAI cho nhúng và trò chuyện, cùng một giao diện webhook đơn giản mà bạn có thể thử nghiệm từ Postman. Bot sẽ tiếp nhận các tệp của bạn, phân đoạn và nhúng chúng, lưu trữ vector, truy xuất các đoạn văn phù hợp nhất và trả lời kèm trích dẫn — đồng thời duy trì bộ nhớ phiên trong suốt quá trình.

Xây dựng Chatbot Agentic RAG với n8n, Google Drive, Supabase và pgvector

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

Bảng quá lớn, cách khắc phục bằng phân vùng SQL

Các truy vấn SQL của bạn có chậm lại khi bảng đạt vài triệu hàng không? Việc xóa các bản ghi cũ có mất nhiều thời gian không? Bạn có thể đã bỏ qua một công cụ quan trọng: Phân vùng SQL.

Hiệu suất và khả năng mở rộng là yếu tố then chốt. Điều này đúng khi bạn xây dựng nền tảng fintech, ghi dữ liệu cảm biến IoT, hoặc duy trì ứng dụng web lưu lượng cao.

Phân vùng giúp chia các bảng lớn thành các phần nhỏ hơn, dễ quản lý hơn. Việc này giảm đáng kể độ trễ truy vấn. Nó cũng cải thiện hiệu quả phần cứng và đơn giản hóa quản lý vòng đời dữ liệu.

Hướng dẫn này sẽ giải thích:

• Phân vùng SQL thực sự là gì.
• Các trường hợp sử dụng thực tế và mã SQL cho các loại phân vùng khác nhau.
• Cách áp dụng phân vùng cho các bảng hiện có trong môi trường sản xuất mà không gây gián đoạn.

Hãy cùng tìm hiểu để mở rộng cơ sở dữ liệu của bạn.

Phân vùng SQL là gì?

Phân vùng SQL là kỹ thuật chia một bảng lớn thành các phần nhỏ hơn, dễ quản lý hơn. Các phần này được gọi là phân vùng. Mỗi phân vùng hoạt động như một bảng nhỏ độc lập. Nó có thể được truy vấn riêng biệt. Điều này cải thiện hiệu suất, giảm chi phí và đơn giản hóa việc quản lý.

Các lợi ích chính với ví dụ thực tế

1. Cải thiện hiệu suất truy vấn thông qua cắt tỉa phân vùng

Cắt tỉa phân vùng giúp công cụ cơ sở dữ liệu chỉ quét các phân vùng liên quan. Việc này dựa trên tiêu chí lọc của truy vấn. Nó rất hiệu quả với phân vùng dựa trên ngày hoặc các cột trong mệnh đề WHERE. Thay vì quét toàn bộ bảng lớn, công cụ chỉ quét một tập hợp con nhỏ. Điều này giảm sử dụng I/O và CPU.

Ví dụ: Một nền tảng thương mại điện tử có hàng triệu bản ghi trong bảng doanh số. Nó có thể sử dụng phân vùng theo phạm vi hàng tháng. Một báo cáo doanh số tháng 1 năm 2024 sẽ chỉ quét phân vùng tháng 1. Các phân vùng khác sẽ bị bỏ qua.

2. Tăng cường khả năng mở rộng để hỗ trợ bộ dữ liệu đang phát triển

Khi khối lượng dữ liệu tăng, các bảng không phân vùng có thể giảm hiệu suất. Phân vùng cho phép mở rộng theo chiều ngang trong một bảng. Bạn có thể thêm phân vùng mà không cần thay đổi kiến trúc lược đồ. Điều này giúp thiết kế của bạn bền vững trong tương lai. Nó cũng đảm bảo các truy vấn hiệu suất cao khi dữ liệu phát triển.

Ví dụ: Một bảng chuỗi thời gian lưu trữ dữ liệu cảm biến hàng giờ. Nó có thể dùng phân vùng khoảng thời gian để tự động tạo phân vùng hàng ngày. Điều này đảm bảo dữ liệu mới luôn vào đúng phân vùng.

3. Đơn giản hóa quản lý và bảo trì dữ liệu

Phân vùng cho phép các thao tác dữ liệu chi tiết. Sao lưu, xóa, phục hồi và lưu trữ có thể thực hiện ở cấp độ phân vùng. Điều này thay vì quét toàn bộ bảng. Các thao tác này nhanh hơn, an toàn hơn và ít lỗi hơn.

Ví dụ: Để tuân thủ GDPR, một công ty có thể cần xóa dữ liệu cũ hơn 5 năm. Xóa một phân vùng cũ diễn ra tức thì. Việc này nhanh hơn nhiều so với xóa hàng triệu hàng.

4. Giảm I/O và tối ưu hóa tài nguyên phần cứng

Bằng cách giới hạn đọc dữ liệu vào các phân vùng liên quan, phân vùng giảm I/O đĩa và chu kỳ CPU. Kết hợp với các chỉ mục dành riêng cho phân vùng, nó tạo ra một mẫu truy cập hiệu quả. Chỉ dữ liệu tối thiểu cần thiết được truy xuất.

Ví dụ: Trong một ứng dụng đa khu vực, phân vùng hệ thống hỗ trợ theo khu vực. Dữ liệu từ khu vực Hoa Kỳ được lưu trữ riêng biệt với EU hoặc APAC. Điều này đảm bảo các truy vấn chỉ chạm vào các tệp dữ liệu cần thiết.

5. Tăng cường khả dụng và phục hồi sau thảm họa

Khi một bảng được phân vùng, lỗi hoặc hỏng dữ liệu trong một phân vùng không làm hỏng toàn bộ bảng. Quản trị viên có thể chỉ phục hồi phân vùng bị ảnh hưởng. Việc này giảm thời gian ngừng hoạt động và cải thiện độ tin cậy.

Ví dụ: Trong trường hợp dữ liệu bị hỏng trong phân vùng tháng 2 năm 2023 của bảng nhật ký kiểm tra. Chỉ phân vùng đó cần phục hồi. Phần còn lại của bảng vẫn có thể truy cập được.

6. Cân bằng tải trên các tài nguyên

Phân vùng cho phép phân phối dữ liệu qua các đĩa lưu trữ hoặc nút tính toán khác nhau. Điều này tùy thuộc vào cơ sở hạ tầng của bạn. Phân vùng băm hoặc theo phạm vi có thể kết hợp với phân đoạn tài nguyên vật lý. Việc này giúp tránh các điểm nóng (hot spots).

Ví dụ: Hệ thống backend mạng xã hội có thể phân vùng băm dữ liệu người dùng theo user_id. Dữ liệu được phân phối qua nhiều đĩa hoặc máy chủ. Điều này đảm bảo không tài nguyên nào bị quá tải ngay cả khi truy cập cao điểm.

Phân vùng so với Sharding

Phân vùng lý tưởng cho mở rộng theo chiều dọc và tối ưu hóa trong một phiên bản DB. Sharding dùng cho mở rộng theo chiều ngang qua nhiều máy hoặc dịch vụ.

Các phương pháp phân vùng SQL

Phân vùng theo phạm vi (Range Partitioning)

Chia dữ liệu dựa trên các phạm vi giá trị liên tục.

Tốt nhất cho: Dữ liệu chuỗi thời gian (nhật ký, sự kiện). Hệ thống giao dịch với các truy vấn dựa trên thời gian.

Thông tin kỹ thuật: Tự động cắt tỉa các phân vùng không liên quan. Hỗ trợ mở rộng phân vùng dựa trên khoảng thời gian. Dễ dàng lưu trữ các phạm vi dữ liệu cũ.

Ví dụ:

CREATE TABLE trade_orders (
  order_id INT,
  trade_date DATE,
  stock_symbol VARCHAR(10),
  quantity INT,
  price DECIMAL(10,2)
) PARTITION BY RANGE (trade_date) (
  PARTITION p_20230701 VALUES LESS THAN ('2023-07-02'),
  PARTITION p_20230702 VALUES LESS THAN ('2023-07-03')
);

Phân vùng theo danh sách (List Partitioning)

Nhóm dữ liệu theo các giá trị rời rạc.

Tốt nhất cho: Dữ liệu địa lý hoặc phân loại. Ví dụ: Quốc gia, khu vực, ngôn ngữ, phòng ban.

Thông tin kỹ thuật: Mỗi giá trị phải được ánh xạ thủ công vào một phân vùng. Có thể tốn công bảo trì nếu các danh mục thay đổi thường xuyên. Dễ dàng áp dụng các chính sách lưu giữ/sao lưu khác nhau.

Ví dụ:

CREATE TABLE customer_orders (
  order_id INT,
  country_code CHAR(2),
  customer_id INT,
  order_total DECIMAL(12,2)
) PARTITION BY LIST (country_code) (
  PARTITION us VALUES IN ('US'),
  PARTITION ca VALUES IN ('CA'),
  PARTITION eu VALUES IN ('FR', 'DE', 'IT', 'ES')
);

Phân vùng băm (Hash Partitioning)

Phân phối dữ liệu đều bằng cách sử dụng hàm băm.

Tốt nhất cho: Các mẫu truy cập đồng nhất. Tránh lệch dữ liệu khi phân vùng theo phạm vi/danh sách không thực tế.

Thông tin kỹ thuật: Không có cắt tỉa phân vùng. Hữu ích cho các khối lượng công việc nặng về tra cứu. Đảm bảo các phân vùng có kích thước đồng đều. Yêu cầu số lượng phân vùng được xác định trước.

Ví dụ:

CREATE TABLE call_records (
  call_id BIGINT,
  caller_id INT,
  callee_id INT,
  call_start TIMESTAMP,
  duration_sec INT
) PARTITION BY HASH (caller_id)
PARTITIONS 32;

Phân vùng tổng hợp (Composite Partitioning)

Kết hợp hai hoặc nhiều loại phân vùng.

Tốt nhất cho: Các bộ lọc truy vấn đa chiều. Ví dụ: Thời gian + địa lý, hoặc Danh mục + trạng thái.

Thông tin kỹ thuật: Cho phép cắt tỉa phân vùng trên nhiều bộ lọc. Phức tạp hơn trong thiết kế và bảo trì. Có thể phát sinh chi phí dữ liệu siêu dữ liệu.

Ví dụ:

CREATE TABLE transaction_log (
  transaction_id BIGINT,
  transaction_date DATE,
  branch_region VARCHAR(20),
  amount DECIMAL(15,2)
) PARTITION BY RANGE (transaction_date)
SUBPARTITION BY LIST (branch_region) (
  PARTITION jan_2023 VALUES LESS THAN ('2023-02-01') (
    SUBPARTITION east VALUES ('East'),
    SUBPARTITION west VALUES ('West')
  )
);

Phân vùng khoảng thời gian (Interval Partitioning)

Tự động tạo phân vùng mới theo các khoảng thời gian.

Tốt nhất cho: IoT, giám sát, nhật ký sự kiện. Dữ liệu chuỗi thời gian chỉ ghi thêm.

Thông tin kỹ thuật: Yêu cầu lựa chọn kích thước khoảng thời gian cẩn thận. Đơn giản hóa việc lưu giữ bằng cách loại bỏ các phân vùng cũ. Có sẵn trong Oracle, PostgreSQL (qua tiện ích mở rộng), và các hệ thống khác.

Ví dụ:

CREATE TABLE activity_logs (
  log_id BIGINT,
  log_date DATE,
  user_id INT,
  action VARCHAR(100)
) PARTITION BY RANGE (log_date)
INTERVAL (NUMTOYMINTERVAL(1, 'DAY')) (
  PARTITION p0 VALUES LESS THAN (TO_DATE('2023-07-01','YYYY-MM-DD'))
);

Phân vùng tham chiếu (Reference Partitioning)

Căn chỉnh phân vùng bảng con với bảng cha.

Tốt nhất cho: Bảng đơn hàng và chi tiết đơn hàng, các bảng cha-con. Tránh các phép nối toàn bảng trong các bảng liên quan.

Thông tin kỹ thuật: Không được hỗ trợ trong tất cả các RDBMS. Duy trì khóa ngoại và vị trí nối. Giữ dữ liệu cùng vị trí để truy cập nhanh hơn.

Ví dụ:

CREATE TABLE order_items (
  item_id BIGINT,
  order_id BIGINT,
  product_id INT,
  quantity INT
) PARTITION BY REFERENCE (purchase_orders_partition);

Thay đổi các bảng hiện có bằng phân vùng

Thêm phân vùng

Nếu bảng của bạn đã được phân vùng, thêm phân vùng phạm vi hoặc danh sách mới giúp bạn xử lý dữ liệu tăng trưởng.

ALTER TABLE sales
PARTITION BY RANGE (sale_date) (
  PARTITION p_jan_2023 VALUES LESS THAN ('2023-02-01'),
  PARTITION p_feb_2023 VALUES LESS THAN ('2023-03-01')
);

Thêm phân vùng trong tương lai

Bạn có thể thêm các phân vùng trong tương lai để nhập dữ liệu liền mạch.

ALTER TABLE sales ADD PARTITION (
  PARTITION p_mar_2023 VALUES LESS THAN ('2023-04-01')
);

Tóm tắt và các bước tiếp theo

Phân vùng cơ sở dữ liệu không chỉ là một điều chỉnh hiệu suất. Nó là một yếu tố cho phép khả năng mở rộng.

Dù bạn tối ưu hóa 10 triệu bản ghi hay thiết kế cho hàng tỷ bản ghi, phân vùng giúp bạn:

• Giảm thời gian phản hồi truy vấn nhờ cắt tỉa phân vùng.
• Đơn giản hóa bảo trì với sao lưu và xóa dữ liệu có mục tiêu.
• Mở rộng tập dữ liệu mà không cần đại tu kiến trúc tốn kém.
• Cho phép các chiến lược phục hồi sau thảm họa và khả dụng thông minh hơn.

Từ phân vùng theo phạm vi và danh sách đến kết hợp băm và tổng hợp. Giờ đây bạn có các công cụ để thiết kế hệ thống SQL. Các hệ thống này sẽ phát triển cùng người dùng, không chống lại họ.

Điểm khó khăn lớn nhất của bạn khi mở rộng cơ sở dữ liệu SQL là gì?

Hãy bình luận bên dưới hoặc chia sẻ câu chuyện thành công của bạn khi sử dụng phân vùng!

Nếu bài viết này hữu ích, đừng quên:

• Vỗ tay và chia sẻ nó với nhóm của bạn.
• Theo dõi tôi để biết thêm các phân tích chuyên sâu về backend.
• Đánh dấu hướng dẫn này để tham khảo sau này!

Cảm ơn bạn đã là một phần của cộng đồng.

Tham khảo: medium.com

pgroll: Di chuyển Postgres không gián đoạn

Việc di chuyển cơ sở dữ liệu và thay đổi lược đồ thường gây lo lắng. Lý do là các quá trình này có thể làm tê liệt hệ thống sản xuất nếu xảy ra lỗi. Hôm nay, chúng ta sẽ tìm hiểu một công cụ được xây dựng cho Postgres. Công cụ này cung cấp khả năng di chuyển lược đồ không gián đoạn và có thể hoàn tác. Công cụ đó là gì? Đó là pgroll.

pgroll là một công cụ CLI dành cho Postgres. Nó sử dụng các lược đồ dựa trên JSON hoặc YAML. Công cụ này quản lý các thay đổi một cách an toàn. Nó hỗ trợ lược đồ đa phiên bản. Điều này có nghĩa là bạn có thể hoàn tác ngay lập tức nếu có vấn đề. (Mặc dù điều đó khó xảy ra, nhưng vẫn có thể.)pgroll đảm bảo ứng dụng của bạn luôn trực tuyến trong suốt quá trình cập nhật.

Tại sao pgroll lại quan trọng?

Các tính năng của pgroll mang lại lợi ích đáng kể cho doanh nghiệp:

• Không gián đoạn hoạt động: Bạn có thể áp dụng các thay đổi mà không làm gián đoạn người dùng đang hoạt động. Điều này duy trì trải nghiệm người dùng liền mạch và tránh mất doanh thu.
• Khả năng hoàn tác: Nếu phát sinh sự cố, bạn có thể hoàn tác các thay đổi ngay lập tức. Điều này giảm thiểu rủi ro và thời gian ngừng hoạt động tiềm ẩn.
• Cú pháp khai báo: Lược đồ được định nghĩa rõ ràng bằng JSON hoặc YAML. Cách tiếp cận này giúp giảm thiểu lỗi thủ công và tăng tính nhất quán.
• Hỗ trợ đa phiên bản: pgroll xử lý các chuyển đổi phức tạp giữa các phiên bản lược đồ. Điều này cho phép ứng dụng của bạn chạy trên một phiên bản lược đồ cũ trong khi phiên bản mới đang được triển khai.

Những tính năng này làm cho pgroll trở thành lựa chọn lý tưởng. Nó phù hợp với các môi trường Postgres có lưu lượng truy cập cao.

Tại sao các phương pháp cũ không hiệu quả?

Chúng ta hãy xem xét một số phương pháp di chuyển cơ sở dữ liệu truyền thống. Điều này giúp hiểu rõ những hạn chế của chúng.

Bảo trì ngoại tuyến theo lịch trình

Cách tiếp cận này đơn giản bằng cách tạm thời dừng ứng dụng. Việc dừng ứng dụng để thực hiện các thay đổi cơ sở dữ liệu.

• Người dùng không thể truy cập ứng dụng. Đây là một vấn đề lớn đối với các hệ thống cần luôn sẵn sàng.
• Nếu có lỗi, ứng dụng sẽ ngoại tuyến lâu hơn. Điều này gây ra sự khó chịu và tổn thất cho doanh nghiệp.
• Hoàn tác một thay đổi thất bại rất phức tạp. Nó có thể yêu cầu khôi phục toàn bộ cơ sở dữ liệu.

Chuyển đổi từng bước

Phương pháp này giữ cho ứng dụng hoạt động. Tuy nhiên, nó đòi hỏi nỗ lực bổ sung từ đội ngũ phát triển. Họ phải xử lý các thay đổi một cách cẩn thận.

• Quá trình đa bước này tốn thời gian. Nó yêu cầu các nhà phát triển quản lý các thay đổi phức tạp.
• Rất dễ mắc lỗi khi xử lý nhiều cập nhật. Các cập nhật này trải rộng trên cả ứng dụng và cơ sở dữ liệu.
• Lỗi trong quá trình di chuyển dữ liệu có thể gây ra các vấn đề. Những vấn đề này không dễ nhận thấy ngay lập tức.

pgroll khắc phục điều này như thế nào?

pgroll loại bỏ thời gian ngừng hoạt động. Nó cũng loại bỏ các tập lệnh dễ gây lỗi. Điều này được thực hiện với một quy trình làm việc khai báo. Quy trình này dựa trên JSON hoặc YAML.

Khả năng hoàn tác và lược đồ có phiên bản của nó đảm bảo an toàn và linh hoạt. Điều này rất quan trọng đối với hoạt động kinh doanh liên tục.

Quy trình làm việc của pgroll

Bây giờ là lúc xem cách chúng ta có thể thiết lập và triển khai pgroll.

Bước 1: Cài đặt CLI của pgroll

Bước đầu tiên để sử dụng pgroll là cài đặt công cụ dòng lệnh (CLI) của nó.

Đối với macOS hoặc Linux, bạn sử dụng các lệnh sau:

brew tap xataio/pgroll
brew install pgroll

Bước 2: Khởi tạo pgroll

Bước tiếp theo là khởi tạo pgroll. Ở đây, chúng ta sẽ thiết lập nó. Mục đích là để cấu hình nơi pgroll lưu trữ trạng thái của nó.

pgroll init --postgres-url "postgresql://<user>:<password>@<endpoint_hostname>:<port>/<dbname>?sslmode=require&channel_binding=require"

Lệnh này kết nối pgroll với cơ sở dữ liệu Postgres của bạn. Nó tạo ra các bảng nội bộ cần thiết để theo dõi các phiên bản lược đồ và trạng thái di chuyển.

Bước 3: Định nghĩa di chuyển

Công cụ pgroll hỗ trợ cả JSON và YAML để định nghĩa di chuyển cơ sở dữ liệu. Chúng ta sẽ sử dụng YAML trong ví dụ dưới đây.

Cách tiếp cận khai báo này giảm thiểu lỗi thủ công. Nó giúp đảm bảo tính chính xác của các thay đổi lược đồ.

Tạo một tệp có tên migrations/01_create_users.yaml với nội dung sau:

operations:
  # Thao tác đầu tiên là tạo một bảng mới
  - create_table:
      # Tên của bảng cần tạo
      name: users
      # Danh sách định nghĩa cột cho bảng
      columns:
        - name: id
          type: serial
          pk: true
        - name: name
          type: varchar(255)
          unique: true
        - name: description
          type: text
          nullable: true

Tệp này mô tả một cách rõ ràng cấu trúc của bảng users. Nó bao gồm các cột id (khóa chính tự động tăng), name (chuỗi duy nhất) và description (văn bản có thể rỗng).

Bước 4: Áp dụng di chuyển

Bây giờ, chúng ta sẽ áp dụng các thay đổi lược đồ đã định nghĩa. Chúng ta sẽ sử dụng chế độ complete của pgroll.

Chế độ complete yêu cầu pgroll loại bỏ phiên bản lược đồ trước đó. Nó chỉ giữ lại phiên bản mới nhất sau khi di chuyển thành công.

pgroll start migrations/01_create_users.yaml --postgres-url "postgresql://<user>:<password>@<endpoint_hostname>:<port>/<dbname>?sslmode=require&channel_binding=require" --complete

Lệnh này bắt đầu quá trình di chuyển. pgroll sẽ tạo bảng users theo định nghĩa. Trong chế độ complete, nó sẽ ngay lập tức chuyển sang phiên bản lược đồ mới này và loại bỏ mọi phiên bản cũ hơn.

Bước 5: Di chuyển một thay đổi gây hỏng (breaking change)

Chúng ta sẽ thực hiện một thay đổi gây hỏng đối với lược đồ cơ sở dữ liệu của mình. Cụ thể, chúng ta sẽ thêm ràng buộc NOT NULL vào cột description.

Đầu tiên, chúng ta tạo tệp di chuyển. Sau đó, chúng ta sẽ di chuyển các thay đổi.

Tạo một tệp di chuyển có tên migrations/02_make_description_not_null.yaml với nội dung sau:

operations:
  - alter_column:
      table: users
      column: description
      nullable: false
      up: SELECT CASE WHEN description IS NULL THEN 'No description provided' ELSE description END
      down: description

Trong tệp này, chúng ta định nghĩa cách thay đổi cột description. Lệnh up xử lý dữ liệu hiện có. Nó thay thế các giá trị NULL bằng “No description provided” để đáp ứng ràng buộc NOT NULL mới. Lệnh down chỉ định cách hoàn tác thay đổi này nếu cần.

Bây giờ, chúng ta sẽ bắt đầu quá trình di chuyển với pgroll:

pgroll start migrations/02_make_description_not_null.yaml --postgres-url "postgresql://<user>:<password>@<endpoint_hostname>:<port>/<dbname>?sslmode=require&channel_binding=require"

Lệnh này sẽ bắt đầu quá trình di chuyển. pgroll sẽ xử lý việc thêm ràng buộc NOT NULL một cách an toàn.

pgroll di chuyển các thay đổi gây hỏng như thế nào?

Trong ví dụ trên, chúng ta đã tạo và di chuyển một thay đổi gây hỏng. Chúng ta hãy xem pgroll thực hiện những thay đổi này trong cơ sở dữ liệu một cách an toàn như thế nào.

Để thêm ràng buộc NOT NULL vào cột description, pgroll thực hiện các bước sau:

• pgroll thiết lập một cột tạm thời. Cột này có tên là _pgroll_new_description.
• Bước tiếp theo là di chuyển dữ liệu. Dữ liệu được chuyển từ cột description cũ sang cột _pgroll_new_description. Việc này tuân theo các hướng dẫn từ kế hoạch di chuyển (ví dụ: thay thế NULL).
• Quá trình này được thực hiện theo từng phần nhỏ. Mục đích là để tránh làm chậm hoặc khóa toàn bộ bảng cùng một lúc. Đây được gọi là backfilling (điền lại dữ liệu).
• Một cột đặc biệt, _pgroll_needs_backfill, được thêm vào bảng users. Cột này theo dõi những hàng nào đã được cập nhật và những hàng nào vẫn cần được xử lý.
• Ban đầu, _pgroll_needs_backfill được đánh dấu là “true” cho tất cả các hàng. Nó sẽ chuyển sang “false” khi dữ liệu của một hàng đã được di chuyển. Bằng cách này, bất kỳ hàng mới hoặc được cập nhật nào trong quá trình sẽ không bị di chuyển hai lần một cách nhầm lẫn.

Cơ chế này đảm bảo rằng ứng dụng có thể tiếp tục hoạt động. Nó không bị ảnh hưởng bởi quá trình di chuyển dữ liệu lớn. Điều này là cốt lõi của khả năng không gián đoạn hoạt động của pgroll.

Triển khai không gián đoạn hoạt động (Zero downtime rollout)

Với pgroll, bạn có thể kết nối ứng dụng của mình với phiên bản mới nhất của lược đồ. Việc này mà không cần thời gian ngừng hoạt động.

Đầu tiên, chúng ta sẽ tìm tên của phiên bản lược đồ mới nhất. Sau đó, chúng ta đặt nó vào một biến môi trường:

export PGROLL_SCHEMA_VERSION=$(pgroll latest --with-schema --postgres-url "postgresql://<user>:<password>@<endpoint_hostname>:<port>/<dbname>?sslmode=require&channel_binding=require")
echo $PGROLL_SCHEMA_VERSION
# Ví dụ đầu ra: public_02_make_description_not_null

Biến môi trường PGROLL_SCHEMA_VERSION sẽ chứa tên lược đồ mới nhất. Ví dụ, nó có thể là public_02_make_description_not_null.

Chúng ta có thể thiết lập ứng dụng của mình để tự động chuyển đổi lược đồ. Điều này được thực hiện thông qua các biến môi trường nếu được cung cấp. Hãy xem một ví dụ đơn giản về điều này:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users } from './db/schema';
import 'dotenv/config';

// Lấy lược đồ mục tiêu từ biến môi trường
const schema = process.env.PGROLL_SCHEMA_VERSION || 'public';
const client = postgres(process.env.DATABASE_URL!);
const db = drizzle({ client });

async function getUsers() {
  try {
    // Đặt truy vấn của bạn trong một transaction để thiết lập search_path
    const allUsers = await db.transaction(async (tx) => {
      await tx.execute(`SET search_path TO ${schema}`);
      return tx.select().from(users);
    });
    console.log(`Users from schema '${schema}':`, allUsers);
  } catch (error) {
    console.error('Error fetching users:', error);
  } finally {
    await client.end();
  }
}

getUsers();

Đoạn mã này minh họa cách ứng dụng có thể sử dụng biến môi trường PGROLL_SCHEMA_VERSION. Nó thiết lập search_path của Postgres. Điều này cho phép ứng dụng truy vấn phiên bản lược đồ mới nhất. Đồng thời, các ứng dụng cũ hơn vẫn có thể truy vấn phiên bản lược đồ cũ. Điều này tạo ra một quá trình chuyển đổi không gián đoạn.

Nếu mọi thứ hoạt động hoàn hảo, chúng ta có thể yêu cầu pgroll hoàn tất quá trình di chuyển. Nó sẽ hoàn thiện các thay đổi trong cơ sở dữ liệu của chúng ta.

Lệnh này sẽ loại bỏ phiên bản cũ hơn và chỉ giữ lại phiên bản mới nhất trong cơ sở dữ liệu:

pgroll complete --postgres-url "postgresql://<user>:<password>@<endpoint_hostname>:<port>/<dbname>?sslmode=require&channel_binding=require"

Sau khi hoàn tất, chỉ có phiên bản lược đồ mới nhất tồn tại. Không còn các phiên bản trung gian nào được pgroll quản lý nữa.

Làm thế nào để hoàn tác di chuyển chỉ với một lệnh?

Giả sử quá trình di chuyển bạn đã thực hiện gặp một số vấn đề. Bạn muốn hoàn tác các thay đổi.

Điều này có thể được thực hiện dễ dàng chỉ bằng một lệnh duy nhất. Hãy nhớ rằng điều này chỉ hoạt động nếu bạn chưa hoàn tất quá trình di chuyển.

pgroll rollback --postgres-url "postgresql://<user>:<password>@<endpoint_hostname>:<port>/<dbname>?sslmode=require&channel_binding=require"

Lệnh rollback sẽ đưa cơ sở dữ liệu về phiên bản lược đồ trước đó. Điều này giúp nhanh chóng phục hồi từ các lỗi không mong muốn.

Kết luận

Cảm ơn bạn đã theo dõi đến đây. Hôm nay, chúng ta đã xem xét cách làm cho việc di chuyển cơ sở dữ liệu trở nên liền mạch bằng cách sử dụng pgroll.

Hãy tích hợp công cụ này vào quy trình làm việc của bạn. Sau đó, chia sẻ suy nghĩ của bạn.

Bạn có thể kiểm tra tài liệu của pgroll tại đây.

Cảm ơn bạn. Hẹn gặp lại trong một hướng dẫn thú vị khác về cơ sở dữ liệu.

Tham khảo: medium.com