Cả bài trong một trang: ý chính, sơ đồ, điểm phỏng vấn.
Stored procedure, function và trigger: khi nào đặt logic trong database
1Procedure, function, trigger
Cả ba là code chạy bên trong database. Procedure làm một việc, function tính ra giá trị, trigger tự chạy khi có INSERT/UPDATE/DELETE.
Gọi thế nào
Ghi dữ liệu
Procedure
CALL transfer(1, 2, 30)
Được, PG 11+ cho COMMIT
Function
SELECT tax(amount)
Thiết kế tốt thì không
Trigger
Database tự gọi khi ghi
Được, trong transaction gây ra
2Logic đặt ở Java hay database
Mặc định để ở Java: dễ test, dễ version, dễ scale. Đưa vào database khi xử lý hàng triệu dòng theo tập hợp, hoặc quy tắc phải đúng cho mọi nơi ghi.
"Nhanh nhờ cache plan": prepared statement JDBC cũng được cache.
"Chống SQL injection": chỉ khi không ghép chuỗi bên trong.
Cái nhanh thật: bớt round-trip, xử lý theo tập hợp.
Thử một câu SQL set-based trước khi viết procedure.
3Generic plan trên dữ liệu lệch
PostgreSQL lập custom plan năm lần đầu, từ lần sáu có thể chuyển sang generic plan dùng chung cho mọi giá trị. Với giá trị hiếm, nó rất tệ.
Plan
'NEW' (1.000 dòng)
'DONE' (phổ biến)
Custom
0,85 ms
7,9 ms
Generic
57 ms
6,9 ms
4Volatility quyết định index
Volatility là lời hứa về hàm: IMMUTABLE không đổi, STABLE không đổi trong một câu, VOLATILE (mặc định) đổi mỗi lần gọi.
-- cutoff() = now() - 1 giờ, 1 triệu dòng
WHERE created_at > cutoff()
-- VOLATILE (quên khai): Seq Scan, 488 ms
-- STABLE: Index Only Scan, 0,13 ms
-- Đọc bảng mà khai IMMUTABLE: index trả sai
5Injection và trigger
Procedure ghép tham số rồi EXECUTE vẫn bị SQL injection. Trigger là logic ẩn chạy theo từng dòng.
flowchart TD
A["p_sort ghép vào ORDER BY"] --> B["Subquery đọc bảng secret"]
B --> C["Fix: whitelist, format %I, USING"]
D["Trigger audit FOR EACH ROW"] --> E["UPDATE 200.000 dòng: 329 → 922 ms"]
D --> F["Entity JPA giữ giá trị cũ"]
F --> G["refresh() hoặc @Generated"]
💡 Ghi nhớ
Constraint trước, trigger sau, logic nghiệp vụ ở Java.
pgjdbc gọi procedure bằng CALL, không bằng {call}.
Phân loại lỗi theo getSQLState(), không theo message.
Đổi chữ ký procedure theo expand/contract khi rolling deploy.
⭐ Điểm phỏng vấn
Tự trả lời trước, rồi bấm từng câu để xem đáp án.
Procedure khác function?
Procedure làm việc bằng CALL; function trả giá trị trong SELECT.
Nhanh trong psql, chậm từ app?
App dùng prepared statement, rơi vào generic plan.
Procedure chống injection không?
Chỉ khi không ghép chuỗi thành dynamic SQL.
Xong phần tự kiểm tra, bài này đã được đánh dấu đã học ✓
⚠️ Lỗi thường gặp
Quên khai volatility, hàm thành VOLATILE và mất index.
SECURITY DEFINER không cố định search_path, dùng nhầm hàm giả quyền cao.
Chuyển logic tính phí sang procedure "cho nhanh" khi chưa đo chậm ở đâu.
Phỏng vấn backend Java hay hỏi “có nên dùng stored procedure không?” và chờ bạn nói được trade-off chứ không phải “có” hay “không”. Bài này giải thích procedure, function và trigger làm gì, khi nào đáng đặt logic trong database, và những cái bẫy hiệu năng, bảo mật đi kèm. Mọi số đo chạy trên PostgreSQL 16.14 và MySQL 8.4.11 bằng Docker, bảng 1 triệu dòng. Nên đọc trước SQL chạy thế nào và execution plan.
Sơ đồ tóm tắt:
flowchart TD
Q{"Logic này đặt ở đâu?"} -->|"Quy tắc nghiệp vụ, gọi API, cần test nhanh"| J["Java service"]
Q -->|"Ràng buộc dữ liệu mọi nơi ghi"| C["Constraint: PK, FK, CHECK, UNIQUE"]
C -->|"Không diễn đạt được bằng constraint"| T["Trigger (cẩn thận)"]
Q -->|"Xử lý hàng triệu dòng theo tập hợp"| S["Một câu SQL set-based"]
S -->|"Nhiều bước, cần gần dữ liệu"| P["Procedure / function"]
P --> R["Rủi ro: plan cache, volatility, dynamic SQL, version"]
1. Procedure, function và trigger là gì
Ý chính: cả ba là code chạy bên trong database. Procedure để làm việc, function để tính ra giá trị, trigger để tự chạy khi có INSERT/UPDATE/DELETE.
Procedure
Function
Trigger
Gọi thế nào
CALL transfer(1, 2, 30)
trong câu SQL: SELECT tax(amount)
database tự gọi khi có sự kiện ghi
Trả về
không, hoặc tham số OUT, result set (MySQL)
một giá trị hoặc một bảng
NEW/OLD đã sửa, hoặc huỷ thao tác
Ghi dữ liệu
được
PostgreSQL được (nếu VOLATILE); thiết kế tốt thì không
được
Quản lý transaction
PostgreSQL 11+ cho COMMIT bên trong
không
chạy trong transaction của câu gây ra nó
Ngôn ngữ thường dùng là PL/pgSQL (PostgreSQL) hoặc SQL/PSM (MySQL). Tên gọi chung trong sách là “stored routine”: code được lưu sẵn trong database, ứng dụng gọi bằng tên.
Khác biệt chi tiết giữa PostgreSQL và MySQL
PostgreSQL trước bản 11 chỉ có function; procedure (CREATE PROCEDURE, gọi bằng CALL) có từ 11 và là thứ duy nhất được COMMIT/ROLLBACK giữa chừng, hợp cho job dọn dữ liệu theo lô.
Function của PostgreSQL viết được bằng sql (có thể được planner nhúng thẳng vào câu gọi) hoặc plpgsql (luôn là một lời gọi riêng).
MySQL: procedure trả được nhiều result set và tham số OUT/INOUT. Function phải khai DETERMINISTIC, NO SQL hoặc READS SQL DATA khi binlog bật. Mình thử tạo function không khai gì trên 8.4: lỗi ERROR 1418 ... binary logging is enabled.
MySQL không cho functional index dùng stored function, chỉ dùng biểu thức với hàm có sẵn.
SQL Server có thêm natively compiled procedure (biên dịch ra mã máy cho bảng In-Memory OLTP). Đây là tính năng riêng, không có ở PostgreSQL hay MySQL.
2. Khi nào nên đặt logic trong database
Ý chính: mặc định để logic ở Java. Chỉ đưa vào database khi lợi ích “gần dữ liệu” lớn hơn cái giá về test, version và scale.
Ba lý do hay được nêu để dùng stored procedure, và sự thật của từng cái:
“Nhanh hơn vì plan được cache.” Prepared statement từ JDBC cũng được cache plan (mục 3). Cái nhanh thật là bớt round-trip (lượt đi về qua mạng) và xử lý theo tập hợp.
“Chống SQL injection.” Chỉ khi procedure không tự ghép chuỗi (mục 6). Bind parameter ở Java chống được y như vậy.
“Logic dùng chung cho nhiều ứng dụng.” Đúng, nhưng thường nên giải bằng một service sở hữu dữ liệu hơn là nhiều app cùng chọc vào một database.
Trước khi viết procedure, thử viết một câu SQL set-based (một câu xử lý cả tập dòng, thay vì vòng lặp từng dòng). Phần lớn lợi ích hiệu năng nằm ở đó, không ở chữ PROCEDURE.
3. Plan cache và “parameter sniffing”
Ý chính: database lập plan một lần rồi dùng lại cho nhiều giá trị tham số. Khi dữ liệu lệch, plan tốt cho giá trị này có thể rất tệ cho giá trị khác.
“Parameter sniffing” là tên SQL Server đặt cho hiện tượng: lần gọi đầu, optimizer “ngửi” giá trị tham số và lập plan cho giá trị đó, các lần sau dùng lại. PostgreSQL làm khác một chút:
Câu trong PL/pgSQL và prepared statement đều được prepare. Năm lần đầu lập custom plan (plan riêng cho từng giá trị).
Từ lần thứ sáu, PostgreSQL thử một generic plan (plan chung không biết giá trị). Nếu chi phí ước lượng không tệ hơn nhiều so với trung bình custom plan thì dùng generic mãi.
pgjdbc (driver PostgreSQL) mặc định chuyển sang server-side prepared statement sau 5 lần (prepareThreshold=5), nên code Java thường cũng đi qua cơ chế này.
Mình đo trên bảng orders 1 triệu dòng, status = 'NEW' chỉ 1.000 dòng, còn lại DONE, câu WHERE status = $1 ORDER BY id LIMIT 100000:
Plan
'NEW' (hiếm)
'DONE' (phổ biến)
Custom plan
index status rồi sort: 0,85 ms
quét PK theo thứ tự: 7,9 ms
Generic plan
quét PK, lọc bỏ 999.000 dòng: 57 ms
quét PK: 6,9 ms
Với giá trị hiếm, generic plan chậm 67 lần. Ở chế độ auto lần này PostgreSQL giữ custom plan (pg_prepared_statements: 0 generic, 7 custom). Nhưng chỉ cần ước lượng sai là nó chuyển sang generic và câu chậm “không lý do”.
Thí nghiệm plan cache và các cách xử lý của SQL Server
SET max_parallel_workers_per_gather = 0;PREPARE q(text) AS SELECT * FROM orders WHERE status = $1 ORDER BY id LIMIT 100000;SET plan_cache_mode = force_generic_plan;EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) EXECUTE q('NEW');-- Index Scan using orders_pkey ... Filter: (status = $1) Rows Removed by Filter: 999000-- Execution Time: 57.038 msSET plan_cache_mode = force_custom_plan;EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) EXECUTE q('NEW');-- Sort -> Index Scan using orders_status_idx Execution Time: 0.851 ms
Đếm số plan đã dùng: SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;.
SQL Server có nhiều nút vặn hơn, cùng một ý tưởng: OPTION (RECOMPILE) (lập plan mỗi lần, đúng nhưng tốn CPU), OPTIMIZE FOR (@p = ...) (lập plan cho một giá trị đại diện), gán tham số vào biến cục bộ (optimizer không “ngửi” được, dùng phân bố trung bình). Bản 2022 có Parameter Sensitive Plan optimization: giữ vài plan cho các khoảng giá trị khác nhau. MySQL không cache plan giữa các lần chạy theo kiểu này; nó tối ưu lại mỗi lần execute.
4. Function trong câu SELECT: volatility và chi phí từng dòng
Ý chính: với optimizer, function là hộp đen. Khai sai volatility thì mất index; viết bằng PL/pgSQL thì mỗi dòng trả thêm một lời gọi hàm.
Volatility (mức “dễ đổi”) là lời hứa của bạn về hàm:
IMMUTABLE: cùng input luôn ra cùng output, không đọc bảng (lower(), abs()). Chỉ loại này được dùng trong expression index.
STABLE: không đổi trong một câu lệnh, có thể đọc bảng (now()).
VOLATILE (mặc định nếu không khai): có thể đổi mỗi lần gọi (random()), nên phải gọi lại cho từng dòng.
Đo trên 1 triệu dòng, hàm cutoff() trả now() - 1 giờ, cột created_at có index:
Hàm cutoff()
Plan
Thời gian
PL/pgSQL VOLATILE (quên khai)
Seq Scan, lọc bỏ 996.416 dòng
488 ms
PL/pgSQL STABLE
Index Only Scan
0,13 ms
Tạo index trên hàm STABLE thì bị từ chối: functions in index expression must be marked IMMUTABLE.
Chi phí từng dòng. Tính sum(round(amount * 0.1, 2)) trên 1 triệu dòng:
Cách viết
Thời gian
Biểu thức trực tiếp
98 ms
Hàm LANGUAGE sql
94 ms (planner nhúng hàm vào câu, EXPLAIN VERBOSE hiện lại biểu thức gốc)
Hàm LANGUAGE plpgsql
241 đến 282 ms
Hàm vô hướng (trả một giá trị) viết bằng ngôn ngữ thủ tục chậm khoảng 2,5 lần vì mỗi dòng là một lời gọi riêng. Ở SQL Server, scalar UDF trước bản 2019 còn chặn cả chạy song song. Logic nhỏ nên viết bằng LANGUAGE sql hoặc để thẳng trong câu.
5. Gọi từ Java: JDBC và Spring
Ý chính: dùng CallableStatement. Với PostgreSQL, procedure gọi bằng CALL ... trực tiếp, function gọi bằng cú pháp escape {? = call ...}.
Lỗi nghiệp vụ nên ném bằng RAISE EXCEPTION ... USING ERRCODE = 'P0001' (hoặc mã riêng). Java phân loại theo getSQLState(), không theo chuỗi message.
Trong Spring: JdbcTemplate.update("CALL transfer(?, ?, ?)", ...), SimpleJdbcCall, hoặc @Procedure của Spring Data JPA. Procedure chạy trong transaction của @Transactional đang mở như mọi câu SQL khác.
6. SQL injection bên trong procedure và quyền
Ý chính: procedure ghép tham số thành chuỗi rồi EXECUTE thì vẫn bị SQL injection như code Java nối chuỗi. Dùng format('%I') cho tên cột, USING cho giá trị, và whitelist.
Một hàm tìm tài khoản cho phép chọn cột sắp xếp:
-- SAI: ghép p_sort vào câuRETURN QUERY EXECUTE 'SELECT * FROM account WHERE balance >= ' || p_min || ' ORDER BY ' || p_sort;
Thử với PostgreSQL 16:
p_sort = 'id; DROP TABLE secret': lỗi DROP TABLE query does not return tuples, bảng không mất. Đừng mừng.
p_sort = '(SELECT CASE WHEN (SELECT v FROM secret) LIKE ''t%'' THEN id ELSE -id END)': chạy bình thường. Thứ tự kết quả đảo theo nội dung bảng secret. Kẻ tấn công đoán từng ký tự là đọc được cả bảng (blind injection, tấn công “mù” dựa trên hành vi kết quả).
Sửa: whitelist tên cột, format('%I', ...) để trích dẫn identifier, và USING cho giá trị (Bài 2).
Quyền tối thiểu (least privilege): user của ứng dụng chỉ có SELECT/INSERT/UPDATE/DELETE trên bảng cần dùng, không có DROP, ALTER, không phải owner của schema. Migration chạy bằng user khác.
Ý chính: trigger đảm bảo một việc luôn xảy ra dù ai ghi dữ liệu, nhưng nó là logic người đọc code Java không nhìn thấy, chạy theo từng dòng và làm bulk operation chậm đi.
Dùng hợp lý: ghi bảng audit, cập nhật updated_at, chặn thao tác sai mà constraint không diễn đạt được. Thứ tự ưu tiên: constraint (CHECK, UNIQUE, FK) trước, trigger sau.
Đo trên PostgreSQL 16, UPDATE item SET price = price + 1 cho 200.000 dòng:
Thời gian
Không trigger
329 ms
Trigger BEFORE UPDATE FOR EACH ROW ghi updated_at + một dòng audit
922 ms (gần 3 lần)
Thêm về trigger: statement-level, thứ tự, đệ quy
PostgreSQL có trigger FOR EACH STATEMENT với transition table (REFERENCING NEW TABLE AS new_rows): chạy một lần cho cả câu, ghi audit bằng một INSERT ... SELECT. Với bulk update, cách này rẻ hơn nhiều so với từng dòng.
Nhiều trigger cùng sự kiện: PostgreSQL chạy theo thứ tự tên (alphabet), MySQL theo FOLLOWS/PRECEDES hoặc thứ tự tạo. Logic phụ thuộc thứ tự là dấu hiệu nên gộp lại.
Trigger ghi vào bảng có trigger khác có thể gây chuỗi dây chuyền hoặc đệ quy. Dùng pg_trigger_depth() để chặn.
TRUNCATE không kích hoạt trigger DELETE. COPY và INSERT hàng loạt vẫn chạy trigger từng dòng, nên import lớn thường tắt trigger tạm thời (ALTER TABLE ... DISABLE TRIGGER, cần quyền owner).
Bài tập
Bài 1: Gọi procedure chuyển tiền từ Java
Đề: viết procedure transfer(from, to, amount) trong PostgreSQL. Nó khoá hai tài khoản theo thứ tự id để tránh deadlock, ném lỗi có mã riêng cho “không đủ tiền”, “không tìm thấy”, “số tiền sai”. Gọi từ Java bằng JDBC và phân loại lỗi theo SQLState. Thêm function balance_of(id) và gọi bằng {? = call ...}.
Lời giải Bài 1: PL/pgSQL và JDBC, chạy với JDK 21 và PostgreSQL 16
CREATE TABLE account(id bigint PRIMARY KEY, balance numeric(12,2) NOT NULL CHECK (balance >= 0));INSERT INTO account VALUES (1, 100), (2, 50);CREATE OR REPLACE PROCEDURE transfer(p_from bigint, p_to bigint, p_amount numeric)LANGUAGE plpgsql AS $$DECLARE v_balance numeric;BEGIN IF p_amount <= 0 THEN RAISE EXCEPTION 'amount must be positive' USING ERRCODE = '22023'; END IF; -- khoá cả hai dòng theo thứ tự id tăng dần để hai lệnh chuyển ngược chiều không deadlock PERFORM 1 FROM account WHERE id IN (p_from, p_to) ORDER BY id FOR UPDATE; SELECT balance INTO v_balance FROM account WHERE id = p_from; IF NOT FOUND THEN RAISE EXCEPTION 'account % not found', p_from USING ERRCODE = 'P0002'; END IF; IF v_balance < p_amount THEN RAISE EXCEPTION 'insufficient funds' USING ERRCODE = 'P0001'; END IF; UPDATE account SET balance = balance - p_amount WHERE id = p_from; UPDATE account SET balance = balance + p_amount WHERE id = p_to; IF NOT FOUND THEN RAISE EXCEPTION 'account % not found', p_to USING ERRCODE = 'P0002'; END IF;END $$;CREATE OR REPLACE FUNCTION balance_of(p_id bigint) RETURNS numericLANGUAGE sql STABLE AS $$ SELECT balance FROM account WHERE id = p_id $$;
Kết quả (autocommit bật, mỗi CALL là một transaction):
transfer 1->2 30: OKtransfer 1->2 500: business rule (P0001)transfer 1->99 10: not found (P0002)transfer 1->2 -5: invalid argument (22023)balance_of(1) = 70.00balance_of(2) = 80.00
Lệnh 1->99 đã trừ tiền tài khoản 1 trước khi phát hiện tài khoản 99 không có. Số dư vẫn là 70 vì lỗi huỷ toàn bộ lời gọi. Nếu gọi trong một @Transactional lớn hơn thì lỗi làm hỏng cả transaction đó, giống mọi lỗi SQL khác.
Bài 2: Sửa function dynamic SQL bị injection
Đề: function find_accounts_bad(p_min, p_sort) ở mục 6 ghép p_sort và p_min vào chuỗi. Viết lại sao cho chỉ cho sắp xếp theo id hoặc balance, giá trị p_min đi qua tham số, và chứng minh payload blind injection không còn chạy.
Lời giải Bài 2: whitelist, format(‘%I’) và USING
CREATE OR REPLACE FUNCTION find_accounts_good(p_min numeric, p_sort text)RETURNS SETOF account LANGUAGE plpgsql AS $$BEGIN IF p_sort NOT IN ('id', 'balance') THEN RAISE EXCEPTION 'invalid sort column %', p_sort USING ERRCODE = '22023'; END IF; RETURN QUERY EXECUTE format('SELECT * FROM account WHERE balance >= $1 ORDER BY %I', p_sort) USING p_min;END $$;SELECT * FROM find_accounts_good(0, 'balance'); -- chạy bình thườngSELECT * FROM find_accounts_good(0, 'id; DROP TABLE secret');-- ERROR: invalid sort column id; DROP TABLE secret
Ba lớp, mỗi lớp chặn một thứ:
Whitelist chặn mọi giá trị ngoài danh sách. Đây là lớp chính cho tên cột, vì tên cột không bind được như giá trị.
%I trích dẫn identifier (thêm "..." khi cần), phòng khi whitelist bị nới sau này.
USING gửi p_min như tham số thật, không bao giờ thành một phần câu SQL. Với literal bắt buộc phải ghép thì dùng %L.
Phía Java cũng làm y như vậy với ORDER BY động: map tham số API sang một enum các cột cho phép, không ghép chuỗi người dùng gửi.
Ghi nhớ
Giải thích được procedure, function và trigger khác nhau ở cách gọi, giá trị trả về và quyền ghi.
Nói được ba lý do hay nêu để dùng stored procedure và chỗ chưa đúng của từng lý do.
Biết vì sao một câu nhanh trong psql lại chậm từ ứng dụng (generic plan), và cách kiểm tra.
Biết volatility ảnh hưởng thế nào tới index, và vì sao không được “khai bừa” IMMUTABLE.
Viết được dynamic SQL an toàn: whitelist, %I, USING. Kể được cái giá của trigger với JPA và bulk update.
Câu hỏi phỏng vấn
Junior
Stored procedure khác function thế nào?
Procedure dùng để làm một việc, gọi bằng CALL; function trả về giá trị và dùng được ngay trong câu SELECT, WHERE.
Procedure được ghi dữ liệu và (PostgreSQL 11+) COMMIT giữa chừng, hợp cho job xử lý theo lô. Function nên nhỏ, không ghi dữ liệu, khai đúng volatility để optimizer dùng được index. MySQL còn bắt function khai DETERMINISTIC/READS SQL DATA khi bật binlog.
Gọi stored procedure từ Java thế nào?
Dùng CallableStatement: conn.prepareCall("CALL transfer(?, ?, ?)"), set tham số rồi execute(); function thì {? = call fn(?)} với registerOutParameter.
Trong Spring có JdbcTemplate, SimpleJdbcCall hoặc @Procedure của Spring Data JPA. Lỗi từ procedure đến dưới dạng SQLException. Nên phân loại theo getSQLState() (mã ERRCODE do procedure đặt), không theo message.
Mid
Stored procedure có chống được SQL injection không?
Chỉ khi bên trong không ghép chuỗi thành dynamic SQL. Procedure EXECUTE 'SELECT ... ORDER BY ' || p_sort vẫn bị injection như code Java nối chuỗi.
Mình đã thử trên PostgreSQL 16: payload DROP TABLE bị lỗi, nhưng payload đặt subquery vào ORDER BY chạy được và đọc lén bảng khác qua thứ tự kết quả. Cách đúng: whitelist tên cột, format('%I') cho identifier, EXECUTE ... USING cho giá trị. Cộng thêm user ứng dụng có quyền tối thiểu.
Vì sao gọi một function do mình viết trong WHERE làm truy vấn bỏ index?
Thường vì hàm mặc định là VOLATILE, nên optimizer phải gọi lại cho từng dòng và không dùng kết quả để dò index.
Đo trên 1 triệu dòng: hàm VOLATILE cho Seq Scan 488 ms, khai STABLE thì Index Only Scan 0,13 ms. Trường hợp hàm bao quanh cột (WHERE lower(email) = ?) thì cần expression index trên đúng biểu thức, và hàm phải IMMUTABLE.
Trigger có nhược điểm gì với ứng dụng dùng JPA?
Trigger là logic ẩn: Hibernate không biết trigger đã sửa cột nào, nên entity trong bộ nhớ giữ giá trị cũ. Trigger theo từng dòng còn làm bulk operation chậm hẳn.
Mình đo UPDATE 200.000 dòng: 329 ms không trigger, 922 ms với trigger audit theo dòng. Xử lý: refresh() sau khi lưu, đánh dấu cột do database sinh, hoặc dùng trigger theo câu lệnh với transition table. Dùng trigger cho audit và quy tắc mà constraint không diễn đạt được; quy tắc nghiệp vụ để ở Java.
Senior
Team muốn chuyển logic tính phí sang stored procedure “cho nhanh”. Bạn review thế nào?
Hỏi trước “nhanh” là bao nhiêu và chậm ở đâu. Thường cái chậm là round-trip hoặc vòng lặp từng dòng, sửa được bằng một câu SQL set-based hoặc batch mà không cần chuyển logic.
Nếu đo thấy procedure thật sự cần (ví dụ đối soát hàng triệu dòng), cân các chi phí: test cần database thật, version qua migration, CPU database khó scale, debug kém hơn. Logic tính phí hay đổi theo nghiệp vụ, nên để ở Java là hợp lý hơn. Nếu vẫn làm, đặt điều kiện: có test bằng Testcontainers, quản lý bằng Flyway, đo trước và sau.
Parameter sniffing là gì? PostgreSQL có gặp không?
Là khi database lập plan theo giá trị tham số của lần đầu rồi dùng lại cho mọi giá trị khác. Trên dữ liệu lệch, plan đó có thể rất tệ cho một số giá trị. PostgreSQL gặp dạng tương tự qua generic plan.
PostgreSQL lập custom plan năm lần đầu, sau đó có thể chuyển sang generic plan. pgjdbc mặc định dùng server-side prepare sau 5 lần. Mình đo: với giá trị hiếm, generic plan 57 ms so với 0,85 ms của custom plan. Xử lý: plan_cache_mode = force_custom_plan cho câu hoặc function đó, tách câu theo nhóm giá trị. SQL Server có RECOMPILE, OPTIMIZE FOR và PSP optimization từ 2022.
Bạn quản lý version và deploy stored procedure thế nào để rolling deploy không vỡ?
Coi procedure như API công khai: migration có version (Flyway), và đổi chữ ký theo kiểu expand/contract, tức tạo bản mới bên cạnh bản cũ rồi mới xoá bản cũ.
Khi rolling deploy, app bản cũ và bản mới cùng chạy. Đổi tham số của transfer ngay lập tức làm bản cũ lỗi. Tạo transfer_v2, deploy app dùng bản mới, đợi hết instance cũ rồi mới drop bản cũ. Function hay đổi logic mà giữ chữ ký có thể dùng repeatable migration (R__transfer.sql, chạy lại khi checksum đổi). Mọi thay đổi có test tích hợp trên database thật.
Từ vựng tiếng Anh
Term
Phát âm
Nghĩa
Ví dụ câu tiếng Anh dùng trong phỏng vấn
stored procedure
/stɔːrd prəˈsiː.dʒɚ/
thủ tục lưu trong database
“We keep business rules in Java and use stored procedures only for heavy set-based jobs.”
set-based
/set beɪst/
xử lý theo tập hợp
“One set-based UPDATE beat the row-by-row loop by two orders of magnitude.”
round-trip
/ˈraʊnd trɪp/
một lượt đi về qua mạng
“The procedure saves round-trips, not query planning.”
generic plan
/dʒəˈner.ɪk plæn/
plan chung không biết giá trị tham số
“After five executions Postgres may switch to a generic plan.”
parameter sniffing
/pəˈræm.ə.t̬ɚ ˈsnɪf.ɪŋ/
lập plan theo giá trị tham số đầu tiên
“It’s classic parameter sniffing: the plan was built for a rare value.”
volatility
/ˌvɑː.ləˈtɪl.ə.t̬i/
mức độ hàm có thể đổi kết quả
“The function defaulted to VOLATILE, so the index was never used.”
dynamic SQL
/daɪˈnæm.ɪk ˌes.kjuːˈel/
câu SQL ghép lúc chạy
“Dynamic SQL inside a procedure is just as injectable as string concatenation in Java.”
least privilege
/liːst ˈprɪv.əl.ɪdʒ/
quyền tối thiểu
“The app user follows least privilege: no DROP, no ALTER.”
trigger
/ˈtrɪɡ.ɚ/
code tự chạy khi có sự kiện ghi
“The audit trigger tripled the cost of our bulk update.”
expand and contract
/ɪkˈspænd ənd kənˈtrækt/
thêm bản mới trước, bỏ bản cũ sau
“We changed the signature with expand and contract so the old pods kept working.”
Ghi chép tự tổng hợp từ tài liệu chính thức và kinh nghiệm thực tế.