PostgreSQL: kiến trúc và cách nó chọn đường chạy SQL

Cài PostgreSQL xong, anh em mở thư mục cài đặt lên và thấy một đống folder tên lạ hoắc. base, global, pg_wal, pg_tblspc. Rồi một rổ file .conf. Anh em không biết cái nào được đụng, cái nào đụng vào là chết database.

Tôi gặp chuyện này nhiều lần rồi. Có anh em sửa nhầm một file cấu hình, database không bật lên được. Có anh em xóa nhầm một folder, mất luôn dữ liệu. Không phải vì họ ẩu. Là vì họ chưa bao giờ được ai chỉ cho cái bản đồ.

Bài này là cái bản đồ đó. Tôi đi ba thứ: kiến trúc logic của PostgreSQL, kiến trúc vật lý trên ổ đĩa, và cách nó ra quyết định chạy một câu SQL. Toàn bộ số liệu trong bài đọc ra từ chính màn hình demo, và mỗi luận điểm tôi dẫn lại tài liệu gốc của PostgreSQL để anh em tự kiểm.

Video gốc dài 1 giờ 39 phút, dạy trọn PostgreSQL từ cài đặt tới sao lưu. Bài viết này không chép lại mục lục video. Nó lấy ra phần xương sống là kiến trúc và cách engine ra quyết định, rồi bổ sung phần tài liệu chính chủ mà video không kịp nói.

Vì sao hiểu kiến trúc lại là việc trả tiền nhanh nhất?

Bởi vì mọi thứ khác đều dựa lên nó. Anh em không hiểu postgresql.conf thì không tối ưu được. Không hiểu pg_wal thì không khôi phục được sự cố. Không hiểu cách engine chọn đường chạy thì viết SQL cả đời vẫn chỉ là đoán.

Tôi ví dụ cho dễ hình dung. Anh em thuê một ông thợ về sửa nhà. Ông ấy không biết đường ống nước chạy ở đâu, dây điện đi lối nào. Ông ấy vẫn sửa được cái vòi nước, đúng không? Nhưng hôm nào cần đục tường thì ông ấy đục trúng ống. Đấy.

Làm database cũng thế. Biết viết select là biết vặn vòi nước. Còn hiểu kiến trúc mới là biết đường ống nằm đâu.

Kiến trúc logic của PostgreSQL gồm những tầng nào?

PostgreSQL chia logic thành bốn tầng lồng nhau: database cluster chứa nhiều database, database chứa nhiều schema, schema chứa các object như bảng, index, trigger. Mỗi object được hệ thống đánh một mã riêng gọi là OID, và không mã nào trùng mã nào.

Cài xong một con PostgreSQL, anh em có ngay một database cluster. Trong cluster mặc định đã có sẵn database tên postgres cùng hai database template. Trong dự án thật thì đừng lấy postgres ra làm việc. Tạo database riêng cho từng mảng nghiệp vụ.

Schema chính là cách anh em quy hoạch nhà. Cả nhà một phòng thì vẫn ở được. Nhưng chia phòng ra thì tìm đồ nhanh hơn nhiều. Hệ thống nào chạy ngon đều được quy hoạch ngon từ tầng logic.

OID là gì và vì sao nó quyết định tên file trên ổ đĩa?

OID là mã định danh duy nhất mà PostgreSQL gán cho mọi object. Nó không nằm chơi cho vui. Nó chính là tên folder và tên file mà anh em nhìn thấy trên ổ đĩa.

Trong demo, database wecommit có OID là 16398. Bảng countries trong database đó có OID là 16406. Mở thư mục data\base lên, anh em thấy đúng một folder tên 16398, và trong folder ấy có đúng một file tên 16406. Lý thuyết khớp thực tế, không sai một số nào.

Anh em tự kiểm bằng hai câu này:

-- Xem toàn bộ database trong cluster kèm mã OID
select oid, datname from pg_database;

-- Xem mã của một bảng cụ thể
select oid, relname, relfilenode from pg_class where relname = 'countries';

-- Cách nhanh hơn: hỏi thẳng hệ thống file nào chứa bảng này
select pg_relation_filepath('countries');

Ở đây có một chỗ tinh mà video không kịp nói, tôi bổ sung luôn. Tài liệu PostgreSQL ghi rõ: tên file thật sự là relfilenode, không phải oid. Hai số này thường bằng nhau nên nhìn qua tưởng là một. Nhưng các lệnh TRUNCATE, REINDEX, CLUSTER và vài dạng ALTER TABLE sẽ đổi relfilenode mà giữ nguyên oid. Lúc đó tên file đổi, còn OID thì không.

Nên khi cần chắc chắn, đừng suy từ OID. Gọi thẳng pg_relation_filepath(). Được chưa ạ.

Kiến trúc vật lý: trong thư mục data có những gì?

Toàn bộ PostgreSQL trên ổ đĩa nằm gọn trong một đường dẫn tổng, tài liệu gọi là PGDATA. Trong đó chỉ có hai loại thứ: một nhóm file cấu hình nằm ngay tầng ngoài, và một nhóm sub folder chứa dữ liệu thật.

Nhóm file thì tôi chia làm ba loại cho dễ nhớ.

Còn folder thì trong PGDATA có gần hai chục cái. Anh em không cần thuộc hết. Nhớ bốn cái này là đủ dùng.

Có một chi tiết nữa đáng nhớ. Mỗi bảng, mỗi index là một file riêng. Và khi file đó vượt 1 GB thì PostgreSQL tự cắt thành nhiều segment, file thứ hai tên là <filenode>.1, file thứ ba là <filenode>.2. Nên đừng hoảng khi thấy một bảng có chục file. Đó là bảng to, không phải hỏng.

postgresql.conf và postgresql.auto.conf, cái nào thắng?

postgresql.auto.conf thắng. Tham số nào có mặt ở cả hai file thì giá trị trong postgresql.auto.conf ghi đè giá trị trong postgresql.conf. Tài liệu PostgreSQL ghi đúng câu này.

Tôi hay ví thế này cho anh em dễ nhớ. postgresql.conf là cái đèn giao thông. postgresql.auto.conf là ông công an đứng giữa ngã tư. Đèn đang đỏ mà công an vẫy tay bảo đi, thì anh em nghe ai? Nghe công an. Database cũng nghe đúng như vậy.

Từ đó ra một luật cứng: cấm sửa tay postgresql.auto.conf. File đó do hệ thống tự ghi khi anh em chạy ALTER SYSTEM. Sửa tay vào là tự tạo ra một trạng thái mà không ai đọc được nữa.

-- Cách đúng để đổi tham số ở mức cluster
ALTER SYSTEM SET max_connections = 300;

-- Nạp lại cấu hình cho các tham số cho phép reload
SELECT pg_reload_conf();

-- Xem giá trị đang chạy thật, và nó đến từ file nào
SELECT name, setting, unit, source, sourcefile
FROM pg_settings
WHERE name IN ('max_connections','shared_buffers','work_mem');

Vì sao cấu hình mặc định của PostgreSQL luôn làm dự án thật vấp?

Bởi vì bộ mặc định được chọn để chạy được trên mọi máy, kể cả máy yếu. Nó không được chọn để chạy nhanh. Hai tham số dưới đây là chỗ vấp kinh điển nhất.

Thứ nhất là số kết nối đồng thời.

Mặc định max_connections = 100. Tài liệu PostgreSQL xác nhận con số này. Hệ thống giao dịch trực tuyến hay bệnh viện mà có 300 kết nối đổ vào cùng lúc thì toạch. Không phải database yếu. Là do chưa ai đụng vào tham số này.

Thứ hai là bộ nhớ dùng chung.

Mặc định shared_buffers = 128MB. Con server 64 GB RAM mà cấp cho database 128 MB bộ nhớ dùng chung thì anh em hình dung nó khổ cỡ nào. Tài liệu PostgreSQL khuyến nghị thẳng: máy chuyên chạy database có từ 1 GB RAM trở lên thì điểm khởi đầu hợp lý là 25% RAM của hệ thống, và cấp quá 40% thường không có lợi thêm vì PostgreSQL còn dựa vào cache của hệ điều hành.

Cột cuối cùng của bảng này là chỗ tôi thấy anh em mất thời gian nhiều nhất. Sửa pg_hba.conf xong vẫn không kết nối được từ ngoài, ngồi debug cả buổi. Lý do là listen_addresses mặc định chỉ nghe trên loopback. Hai chỗ phải mở, không phải một.

PostgreSQL ra quyết định thực thi một câu SQL như thế nào?

Nó không chạy theo cách anh em viết. Nó dựng ra nhiều phương án thực thi khác nhau cho cùng một câu lệnh, ước lượng chi phí của từng phương án, rồi chọn phương án có chi phí nhỏ nhất. Chi phí đó gọi là cost.

Giống Google Map thôi. Từ nhà tới công ty có cả chục đường. Anh em không phải chọn, nó chọn hộ. Nó chọn theo con đường mà nó tính ra là rẻ nhất. Muốn chỉnh cho nó đi đường khác thì trước hết phải nhìn được nó đang đi đường nào đã, đúng không?

Câu lệnh để nhìn là EXPLAIN.

-- Xem kế hoạch thực thi mà engine dự định dùng, KHÔNG chạy câu lệnh
EXPLAIN SELECT max(salary) FROM wecommit_test WHERE first_name = 'Huy';

-- Chạy thật và đối chiếu ước lượng với số đo thực tế
EXPLAIN (ANALYZE, BUFFERS)
SELECT max(salary) FROM wecommit_test WHERE first_name = 'Huy';

Đọc kết quả thì nhìn hai thứ. Thứ nhất là tên giải thuật ở đầu mỗi dòng: Seq Scan là quét toàn bộ bảng, Index Scan là đi qua index. Thứ hai là cụm cost=A..B. Số A là chi phí tới lúc trả ra dòng đầu tiên, số B là chi phí chạy hết. Tài liệu PostgreSQL nói rõ: dòng trên cùng mang tổng chi phí của cả kế hoạch, và đó chính là con số mà planner tìm cách kéo xuống nhỏ nhất.

Một điều nữa cần nhớ ngay từ đầu. Cost là đơn vị quy ước, không phải giây. Theo tài liệu, cost được đo theo bội số của seq_page_cost, mặc định đặt bằng 1,0. Nên đừng đọc cost=8.59 thành 8,59 giây. Nó chỉ có nghĩa khi đem so với một cost khác của cùng câu lệnh.

Demo thật: 56 triệu bản ghi, cost từ 1.073.330 xuống 8,59

Đây là phần tôi thích nhất trong video, vì nó có số thật để đối chiếu. Bảng wecommit_test có 56.098.816 bản ghi. Câu lệnh cần chạy là lấy lương lớn nhất của những người có first_name = 'Huy'.

Trước khi làm gì cả, engine chọn quét toàn bộ bảng.

Kế hoạch trả về Parallel Seq Scan on wecommit_test, và tổng cost là 1073330.45. Nghĩa là nó phải hỏi lần lượt từng bản ghi một: ông có phải Huy không, ông có phải Huy không. Hỏi 56 triệu lần. Thời gian chạy thật đo được là 2 giây 322 mili giây.

Bây giờ tạo một index đúng trên cột nằm trong mệnh đề WHERE.

CREATE INDEX idx_firstname ON wecommit_test(first_name);

Chạy lại EXPLAIN, kế hoạch đổi hẳn.

Anh em để ý dòng cuối bảng. Trước là Filter, sau là Index Cond. Chữ khác nhau, ý nghĩa khác hẳn. Filter nghĩa là nó đã đọc dòng đó lên rồi mới vứt đi. Index Cond nghĩa là nó không thèm đọc những dòng không khớp. Đấy mới là chỗ tiết kiệm thật.

Một câu lệnh nhanh hơn 2 giây thì nghe cũng thường. Nhưng hệ thống anh em có hàng nghìn câu như thế chạy mỗi phút thì con số cộng dồn lại rất khủng khiếp. Đây là lý do các đơn vị ngân hàng, chứng khoán, bảo hiểm, viễn thông luôn thiếu người biết tối ưu.

Muốn đi sâu hơn về chuyện cùng một câu lệnh mà lúc nhanh lúc chậm, tôi có phân tích riêng trong bài Tối ưu SQL: cùng một câu lệnh nhưng lúc nhanh, lúc chậm. Còn nếu anh em chưa biết bắt đầu từ đâu thì đọc Tối ưu SQL nên bắt đầu từ đâu trước.

Những chỗ dev hay vấp khi đưa PostgreSQL vào dự án thật

Có năm chỗ tôi gặp đi gặp lại. Không chỗ nào khó. Chỉ là chưa ai nói trước.

Một, tưởng export CSV là đã sao lưu. Export ra CSV chỉ lấy được dữ liệu tại đúng thời điểm bấm nút, và chỉ có dữ liệu chứ không có cấu trúc bảng. Export lúc 9 giờ sáng, sự cố lúc 1 giờ chiều, thì anh em mất trắng bốn tiếng. Muốn khôi phục toàn bộ database thì dùng backup dạng Custom rồi restore, đó mới là đường đi được.

Hai, quên WHERE trong UPDATE và DELETE. Thiếu WHERE thì mọi bản ghi đều thỏa mãn, và anh em cập nhật hoặc xóa sạch cả bảng. Thói quen nên có: viết SELECT với đúng điều kiện đó trước, nhìn số dòng trả về, rồi mới đổi thành UPDATE.

Ba, nghịch file trong thư mục data. Xóa một folder trong base là mất một database. Xóa pg_wal là mất khả năng khôi phục. Cả thư mục đó chỉ có postgresql.conf và pg_hba.conf là thứ anh em được mở ra sửa.

Bốn, hiểu sai cách pg_hba.conf chọn dòng. Tài liệu PostgreSQL ghi rõ: bản ghi đầu tiên khớp cả kiểu kết nối, địa chỉ, database và user sẽ được dùng. Không có chuyện thử tiếp dòng dưới. Nếu dòng đó xác thực thất bại thì kết nối bị từ chối luôn. Nên thêm dòng mới vào cuối file mà phía trên đã có một dòng rộng hơn khớp trước, dòng mới của anh em vô nghĩa.

Năm, nhầm schema với tablespace. Schema là quy hoạch logic, tablespace là quy hoạch vật lý. Hai thứ độc lập nhau.

Tablespace là thứ đáng dùng hơn nhiều anh em nghĩ. Nó cho phép đặt dữ liệu ra một đường dẫn bất kỳ, ngoài PGDATA. Ổ cài đặt sắp đầy thì tạo tablespace mới ở ổ khác, không phải cài lại gì cả. Và nó cho phép chia theo tốc độ đĩa: dữ liệu lịch sử ít truy cập thì để ở ổ chậm, dữ liệu nóng và index thì để ở SSD.

-- Tạo vùng lưu trữ vật lý mới, nằm ngoài thư mục cài đặt
CREATE TABLESPACE wecommit_data LOCATION 'E:\pg_space';

-- Cho một bảng cụ thể nằm vào vùng đó
CREATE TABLE wecommit_test (id int, name varchar(50)) TABLESPACE wecommit_data;

-- Kiểm lại xem bảng đang nằm ở tablespace nào
SELECT tablename, tablespace FROM pg_tables WHERE tablename = 'wecommit_test';

Cột tablespace trả về null nghĩa là bảng đang nằm ở pg_default, tức là trong thư mục cài đặt.

Giới hạn thật

Phần này quan trọng ngang phần trên. Anh em đọc kỹ trước khi mang mấy con số ở trên đi báo cáo.

Cost là ước lượng, không phải thời gian. EXPLAIN không hề chạy câu lệnh. Nó chỉ hỏi planner định làm gì. Cost tính từ thống kê mà ANALYZE thu thập, mà thống kê là mẫu ngẫu nhiên nên có sai số. Thống kê cũ thì kế hoạch chọn sai. Muốn số đo thật thì phải dùng EXPLAIN (ANALYZE, BUFFERS), và nhớ là nó thực thi câu lệnh thật, đừng chạy EXPLAIN ANALYZE với một câu DELETE trên production.

Index không phải lúc nào cũng thắng. Demo trên lọc ra 1 dòng trong 56 triệu dòng. Tỉ lệ chọn cực thấp nên index thắng đậm là chuyện đương nhiên. Nếu điều kiện WHERE của anh em lấy ra 30% hay 40% bảng thì quét tuần tự có khi còn nhanh hơn đi qua index, và planner sẽ tự chọn quét tuần tự. Nó không sai, anh em đừng ép nó.

Index có giá phải trả. Mỗi index làm INSERT, UPDATE, DELETE chậm đi, và tốn thêm dung lượng đĩa. Với PostgreSQL còn thêm một khoản nữa: cơ chế ghi của nó sinh ra bản ghi chết, và chuyện dọn bản ghi chết ảnh hưởng trực tiếp tới hiệu năng. Chỗ này tôi có phân tích riêng trong bài Vacuum trong PostgreSQL ảnh hưởng tới hiệu năng thế nào. Tạo index xong mà không hiểu vacuum thì mới đi được nửa đường.

Một index trên một cột là bài dễ nhất. Thực tế câu lệnh thường lọc theo nhiều cột, và lúc đó thứ tự cột trong index quyết định index có được dùng hay không. Tôi có chia sẻ riêng chuyện đó trong bài Tầm quan trọng của thứ tự các cột trong Index PostgreSQL. Và nếu anh em đã tạo index rồi mà câu lệnh vẫn chậm thì đọc Vì sao có Index mà câu SQL vẫn chậm.

Tên file không phải lúc nào cũng là OID. Như tôi nói ở mục OID, thứ quyết định tên file là relfilenode. TRUNCATE, REINDEX, CLUSTER đổi nó. Với một số catalog hệ thống, pg_class.relfilenode còn bằng 0. Nên đừng viết script vận hành dựa trên giả định OID bằng tên file.

Con số 2,322 giây là của một máy cụ thể. Demo chạy trên máy cá nhân Windows với PostgreSQL 15, không phải server production. Anh em chạy lại trên máy khác sẽ ra thời gian khác. Thứ đáng mang đi là tỉ lệ và cách đọc kế hoạch, không phải con số tuyệt đối.

Bài này không đụng tới bốn thứ. MVCC và cơ chế phiên bản dòng, replication, partitioning, và connection pooling. Video gốc cũng không nói. Bốn thứ đó là nội dung riêng, gộp vào đây là làm loãng.

Bảy ngày tới anh em làm được gì?

Không cần đọc hết tài liệu PostgreSQL. Làm đúng bảy việc này, mỗi việc chưa tới 30 phút.

1. Mở PGDATA của một môi trường dev, đối chiếu với bảng folder ở trên. Gọi tên được base, global, pg_wal, pg_tblspc là xong việc một.
2. Chạy select oid, datname from pg_database, rồi mở base ra tìm đúng folder mang tên đó.
3. Chạy SELECT name, setting, source FROM pg_settings WHERE name IN ('max_connections','shared_buffers'). Xem hệ thống của anh em còn để mặc định không.
4. Lấy câu SQL chậm nhất trong dự án, chạy EXPLAIN với nó. Ghi lại tên giải thuật và cost.
5. Chạy tiếp EXPLAIN (ANALYZE, BUFFERS) trên môi trường dev. So ước lượng với thực tế.
6. Backup một database bằng dạng Custom, rồi restore sang một database trống. Chưa restore thử thì chưa gọi là có backup.
7. Mở pg_hba.conf, đọc từ trên xuống, tìm xem dòng nào đang khớp trước dòng anh em vừa thêm.

Đúc kết

Kiến trúc PostgreSQL gọn hơn anh em tưởng: bốn tầng logic, ba nhóm file, bốn folder cần nhớ. Nắm được từng đó là hết sợ thư mục data.

Còn tối ưu thì bắt đầu bằng đúng một việc: nhìn được kế hoạch thực thi. Xem EXPLAIN, đọc tên giải thuật, đọc cost. Chỉnh xong mà cost giảm là đi đúng hướng. Cứ làm đi, sai lại sửa.

Tôi làm database hơn chục năm, và thứ tạo ra khác biệt lớn nhất giữa hai người cùng biết viết SQL không phải là ai thuộc nhiều cú pháp hơn. Là ai nhìn được engine đang nghĩ gì. Anh em cứ mở EXPLAIN lên với câu lệnh chậm nhất của mình hôm nay. Bắt đầu từ đó là được rồi.

Nguồn tham khảo

Toàn bộ trích dẫn dưới đây lấy từ tài liệu chính chủ của PostgreSQL, bản 15, đúng phiên bản dùng trong video.

• Database File Layout. Nói về nội dung PGDATA, quy tắc đặt tên file theo relfilenode, cảnh báo relfilenode khác oid, và cơ chế cắt segment 1 GB
• Setting Parameters. Nói về postgresql.auto.conf ghi đè postgresql.conf
• Connections and Authentication. Nói về max_connections mặc định 100, listen_addresses mặc định localhost
• Resource Consumption. Nói về shared_buffers mặc định 128MB, khuyến nghị 25% RAM
• Using EXPLAIN. Nói về cách đọc cost, planner tìm cách nhỏ nhất hóa tổng chi phí
• The pg_hba.conf File. Nói về bản ghi đầu tiên khớp sẽ được dùng, không có fall-through