PostgreSQL là relational database mã nguồn mở, mạnh về SQL chuẩn, transaction ACID, indexing, extensibility, JSONB, full-text search, replication và hệ sinh thái extension.

Nên chọn PostgreSQL khi dữ liệu có quan hệ rõ, cần consistency mạnh, query phức tạp, reporting, transaction nhiều bước hoặc cần database linh hoạt vừa SQL vừa JSONB. Không nên chọn chỉ vì quen tay nếu workload chủ yếu là key-value cache, time-series cực lớn hoặc document-only không cần relational integrity.

Primary key định danh duy nhất mỗi row và thường là target chính cho references. Unique key đảm bảo một hoặc nhiều column không trùng nhưng không nhất thiết là identity chính. Foreign key đảm bảo giá trị ở bảng con phải tồn tại ở bảng cha.

Trong thiết kế schema, primary key phục vụ identity, unique constraint phục vụ business invariant như email duy nhất, foreign key phục vụ referential integrity. Không nên bỏ foreign key chỉ để code linh hoạt hơn nếu dữ liệu cần consistency.

NULL nghĩa là unknown/missing, không phải empty string, zero hay false. So sánh với NULL bằng = thường không cho kết quả như mong muốn; dùng IS NULL hoặc IS NOT NULL.

Trong unique constraint, PostgreSQL cho phép nhiều NULL vì NULL không bằng nhau. Nếu business rule cần chỉ một row có giá trị null trong một tập, dùng partial unique index hoặc constraint phù hợp.

Transaction gom nhiều statement thành một đơn vị all-or-nothing. Nếu commit thành công, tất cả thay đổi được ghi bền vững; nếu rollback, các thay đổi bị hủy. Trong lúc transaction chưa commit, thay đổi chưa visible như dữ liệu hoàn chỉnh cho transaction khác theo isolation rules.

Ví dụ:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Transaction là nền tảng để tránh dữ liệu nửa vời trong payment, inventory, booking và các workflow nhiều bước.

Constraint biểu diễn rule dữ liệu như primary key, foreign key, unique, check, not null. Index là cấu trúc truy cập để tăng tốc query hoặc hỗ trợ constraint. Một unique constraint thường tạo unique index phía dưới, nhưng không phải index nào cũng là business constraint.

Ví dụ business invariant nên dùng constraint:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT positive_total CHECK (total >= 0);

Nếu chỉ tạo index để nhanh hơn, đừng nhầm nó với rule dữ liệu nếu không có uniqueness/check semantics.

Index giúp database tìm row nhanh hơn thay vì scan toàn bảng, đặc biệt cho filter, join, order by và unique lookup. Trade-off là tốn disk, làm write chậm hơn vì insert/update/delete phải cập nhật index, và có thể bị planner bỏ qua nếu selectivity kém.

Ví dụ index cho lookup theo email:

CREATE INDEX idx_users_email ON users (email);

Không nên index mọi column.

Hãy dựa vào query thực tế, cardinality, selectivity, pattern filter/sort và EXPLAIN ANALYZE.

INNER JOIN trả rows match ở cả hai bảng. LEFT JOIN giữ toàn bộ bảng trái và fill null khi không match. RIGHT JOIN tương tự nhưng giữ bảng phải. FULL OUTER JOIN giữ cả hai phía. CROSS JOIN tạo tích Descartes.

Sai lầm hay gặp: đặt condition của bảng phải trong WHERE sau LEFT JOIN, vô tình biến nó thành inner join. Predicate liên quan bảng phải nên đặt trong ON nếu muốn vẫn giữ row không match.

UPSERT dùng INSERT ... ON CONFLICT để insert nếu chưa có, hoặc update/do nothing nếu vi phạm unique constraint/index.

Ví dụ:

INSERT INTO users (email, name)
VALUES ('a@example.com', 'Ada')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

UPSERT cần conflict target rõ ràng.

Với logic phức tạp, chú ý race condition, trigger side effects và updated_at semantics.

RETURNING cho phép INSERT, UPDATE, DELETE, MERGE trả về rows bị ảnh hưởng mà không cần query lại. Nó hữu ích để lấy id generated, audit updated rows hoặc update rồi trả response.

Ví dụ:

UPDATE orders
SET status = 'paid'
WHERE id = 42
RETURNING id, status, updated_at;

Dùng RETURNING giúp giảm round-trip và tránh race condition do query lại row sau khi write.

CTE (WITH) giúp đặt tên subquery, chia query phức tạp thành bước dễ đọc, hoặc dùng trong data-modifying statements. PostgreSQL hiện đại có thể inline CTE khi phù hợp; nhưng materialization vẫn là điều cần hiểu khi tối ưu.

Ví dụ:

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, count(*) FROM paid_orders GROUP BY user_id;

Dùng CTE để làm rõ logic, nhưng kiểm plan nếu query performance quan trọng.

B-tree là mặc định, tốt cho equality, range, sorting trên scalar values. GIN phù hợp inverted index như JSONB, arrays, full-text search. GiST phù hợp geometric/range/custom operators. BRIN phù hợp bảng rất lớn có dữ liệu correlated theo physical order như timestamp append-only.

Ví dụ JSONB GIN:

CREATE INDEX idx_events_payload_gin ON events USING gin (payload);

Chọn index theo operator/query pattern, không theo tên nghe mạnh hơn.

Trong B-tree composite index, thứ tự column rất quan trọng. Index (tenant_id, status, created_at) hỗ trợ tốt query filter theo tenant_id, rồi status, rồi range/order theo created_at. Nó không tương đương với (created_at, status, tenant_id).

Ví dụ:

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

Đặt column equality có selectivity tốt trước, range/order sau.

Kiểm chứng bằng query thật và EXPLAIN ANALYZE.

Partial index chỉ index một phần rows thỏa điều kiện. Nó hữu ích khi query thường xuyên lọc một subset nhỏ như active records, unprocessed jobs hoặc non-deleted rows.

Ví dụ unique email chỉ cho user chưa bị soft delete:

CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE deleted_at IS NULL;

Partial index nhỏ hơn và nhanh hơn index toàn bảng, nhưng chỉ được dùng khi query predicate khớp điều kiện index.

PostgreSQL hỗ trợ Read Committed, Repeatable Read và Serializable; Read Uncommitted được xử lý như Read Committed. Read Committed là mặc định, mỗi statement thấy snapshot mới. Repeatable Read giữ snapshot ổn định trong transaction. Serializable cố đảm bảo kết quả như chạy tuần tự và có thể abort khi conflict.

Chọn isolation theo invariant. Với booking/payment/inventory dễ race condition, cần lock hoặc Serializable/retry, không chỉ dựa vào read-then-write ở Read Committed.

SELECT FOR UPDATE khóa selected rows để transaction khác không update/delete chúng cho đến khi transaction hiện tại commit/rollback. Nó phù hợp khi cần read-modify-write an toàn như trừ tồn kho hoặc xử lý job queue.

Ví dụ:

BEGIN;
SELECT * FROM inventory WHERE sku = 'A' FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A';
COMMIT;

Cần giữ transaction ngắn.

Lock lâu gây blocking, timeout và deadlock risk.

serial là shorthand cũ tạo sequence ngầm. GENERATED ... AS IDENTITY là cách chuẩn SQL rõ hơn cho auto-increment integer. UUID phù hợp khi cần tạo ID phân tán, tránh lộ thứ tự hoặc merge dữ liệu từ nhiều nguồn.

Trade-off chính: integer identity nhỏ, index locality tốt và dễ debug; UUID rộng hơn, index lớn hơn và random UUID có thể kém locality. Không nên chọn UUID chỉ vì phổ biến nếu hệ thống không cần distributed ID.

json lưu text JSON gốc và parse khi dùng. jsonb lưu dạng binary đã phân tích, mất formatting/key order nhưng query/index hiệu quả hơn. Trong hầu hết app, jsonb là lựa chọn thực tế hơn.

Ví dụ query JSONB:

SELECT * FROM events
WHERE payload @> '{"type":"checkout"}'::jsonb;

Dùng JSONB cho attributes linh hoạt, event payload hoặc metadata; không dùng nó để né thiết kế relational cho dữ liệu có quan hệ và constraint rõ ràng.

Normalize khi field cần join, filter nhiều, constraint, foreign key, update độc lập hoặc reporting. Dùng JSONB khi schema linh hoạt, ít query sâu, metadata thay đổi thường xuyên hoặc cần giữ payload từ external system.

Rule thực tế: dữ liệu core domain nên là columns/tables rõ ràng; dữ liệu phụ hoặc semi-structured có thể là JSONB. Nếu liên tục tạo expression indexes để query nhiều field trong JSONB, đó có thể là dấu hiệu nên normalize.

Generated column lưu giá trị derived từ columns khác, giúp tránh duplicate logic ở app và tạo index/query dễ hơn. Nó phù hợp khi công thức deterministic và thuộc về dữ liệu, ví dụ full name, normalized key hoặc calculated amount.

Ví dụ:

ALTER TABLE users ADD COLUMN full_name text
GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

Không dùng generated column cho logic phụ thuộc external state, timezone biến động hoặc function không immutable.

Window function tính toán trên tập rows liên quan mà không collapse rows như GROUP BY. Nó phù hợp ranking, running total, moving average, dedup theo row_number hoặc so sánh row hiện tại với row trước.

Ví dụ lấy order mới nhất mỗi user:

SELECT * FROM (
  SELECT orders.*, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

Window function thường giúp tránh query N+1 hoặc logic xử lý ranking ở app.

PostgreSQL dùng MVCC nên update/delete tạo dead tuples. VACUUM dọn dead tuples để tái sử dụng space và cập nhật visibility map; autovacuum tự chạy nền để tránh bloat và transaction ID wraparound.

Không nên tắt autovacuum bừa bãi. Nếu bảng write-heavy bị bloat hoặc query chậm dần, cần kiểm autovacuum settings, long-running transactions, index bloat và VACUUM (ANALYZE) khi phù hợp.

ANALYZE thu thập statistics về phân phối dữ liệu để planner ước lượng row count/selectivity. Nếu statistics cũ hoặc không đủ, planner có thể chọn sai index, join strategy hoặc scan type.

Sau bulk load/update lớn, chạy ANALYZE giúp planner có thông tin mới. Với columns có distribution phức tạp hoặc correlated, có thể cần tăng statistics target hoặc dùng extended statistics.

Mỗi PostgreSQL connection là process/backend tương đối nặng. App mở quá nhiều connection có thể làm DB hết memory, context switching cao và latency xấu. Connection pool giới hạn và tái sử dụng connections.

Trong production thường dùng pool ở app hoặc PgBouncer. Cần tính tổng connections từ tất cả replicas/workers, không chỉ một instance. Pool size phải dựa vào DB capacity và workload, không phải số request concurrent.

Migration ít downtime nên tránh rewrite/lock lớn trong giờ cao điểm. Pattern an toàn: add nullable column, backfill theo batch, deploy app đọc/ghi tương thích, sau đó add constraint/index concurrently khi có thể.

Ví dụ index production:

CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

Không chạy migration nguy hiểm chung với deploy app nếu không biết lock level.

Luôn test trên dữ liệu gần production và có rollback plan.

Soft delete thường dùng deleted_at. Nó làm query nào cũng cần filter deleted_at IS NULL, ảnh hưởng index và unique constraints.

Ví dụ unique email chỉ active users:

CREATE UNIQUE INDEX idx_users_email_not_deleted
ON users (email)
WHERE deleted_at IS NULL;

Cần cân nhắc retention, GDPR/hard delete, bloat, query mặc định và nguy cơ quên filter deleted rows.

PostgreSQL enum giúp ràng buộc tập giá trị và làm schema tự mô tả. Nó phù hợp cho trạng thái ổn định ít thay đổi như order status. Nhưng enum khó evolve hơn lookup table nếu business thường xuyên thêm metadata, ordering, localization hoặc soft delete value.

Rule thực tế: dùng enum cho tập nhỏ, ổn định, không cần metadata. Dùng lookup/reference table khi values do business quản lý hoặc có thuộc tính riêng.