Câu hỏi phỏng vấn PostgreSQL

A. Tổng quan & lịch sử

1. Postgres khác MySQL ở điểm nào?

• MVCC native, không có Read Uncommitted thực sự (dirty read).
• Extension mạnh (PostGIS, pgvector, pg_trgm…).
• JSONB chuẩn, có GIN index.
• Array, range type native.
• CHECK constraint thực sự enforce (MySQL 8 mới có).
• Procedural language đa dạng (PL/pgSQL, PL/Python, PL/Perl).
• Standard SQL compliance cao hơn.

2. Có gì mới đáng chú ý ở Postgres 18 & 19?

Postgres 18 (09/2025):

• Async I/O subsystem — đọc storage nhanh ~3×.
• uuidv7() built-in.
• Virtual generated columns mặc định.
• OLD/NEW trong RETURNING của INSERT/UPDATE/DELETE/MERGE.
• Skip scan cho multicolumn B-tree.
• OAuth authentication native.
• pg_upgrade giữ statistics.
• Temporal constraints (PK/UK/FK over ranges).

Postgres 19 (09/2026):

• SQL/PGQ Property Graph Queries — truy vấn đồ thị chuẩn SQL.
• ON CONFLICT DO SELECT — hành vi UPSERT không write khi row đã tồn tại.
• FOR PORTION OF — UPDATE/DELETE trên dữ liệu temporal (khoảng thời gian).
• GROUP BY ALL — nhóm theo mọi cột SELECT không phải aggregate.
• IGNORE NULLS trong window function (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE).
• pg_plan_advice — plan hint cho planner không cần extension thứ 3.
• REPACK — REPACK TABLE CONCURRENTLY rebuild bảng không lock, thay thế nhẹ hơn VACUUM FULL.
• Parallel Autovacuum — autovacuum chạy multi-worker song song trên cùng một bảng.
• JSON COPY TO — export JSON trực tiếp từ câu lệnh COPY.
• Online Data Checksums — bật checksum không cần shutdown cluster.

3. Postgres version release cadence?

Mỗi tháng 9, support 5 năm. v18 release 25/09/2025, v19 release 09/2026.

B. Kiểu dữ liệu

4. VARCHAR vs TEXT trong Postgres?

Không khác về performance. Cả 2 dùng cùng cách lưu, có TOAST. VARCHAR(n) chỉ thêm enforce length. Mặc định dùng TEXT.

5. TIMESTAMP vs TIMESTAMPTZ?

• TIMESTAMP: không timezone, lưu nguyên giá trị.
• TIMESTAMPTZ: convert sang UTC khi insert, convert ngược về session timezone khi read.

Best practice: luôn dùng TIMESTAMPTZ.

6. JSON vs JSONB?

• JSON: text raw, giữ format, parse mỗi query.
• JSONB: binary, nén, query nhanh, index GIN.

Mặc định dùng JSONB trừ khi cần giữ raw.

7. uuidv7 khác uuidv4?

uuidv7 chứa timestamp ở đầu → sort theo thời gian. B-tree index không bị page split như random uuidv4. Khuyến nghị uuidv7 cho mọi project mới Postgres 18+.

8. SERIAL vs IDENTITY?

• SERIAL/BIGSERIAL: legacy, tạo sequence + default.
• GENERATED AS IDENTITY: chuẩn ANSI, kiểm soát tốt hơn.

Postgres 10+ recommend IDENTITY.

9. Array khi nào nên dùng?

Khi: phần tử ít (< 100), không cần FK / JOIN từng giá trị, atomic với row. Còn lại → bảng phụ.

10. Range type là gì?

Khoảng giá trị: INT4RANGE, NUMRANGE, TSRANGE, DATERANGE. Hỗ trợ operator && (overlap), @> (contains). Cực mạnh cho booking, reservation.

C. DDL & ràng buộc

11. Schema trong Postgres là gì?

Namespace bên trong database. Default public. Truy cập: schema.table. Set search_path để default.

12. Database vs Schema vs Cluster?

• Cluster: 1 instance Postgres.
• Database: container độc lập, không JOIN cross-database trực tiếp.
• Schema: namespace trong database.

13. CHECK constraint?

Ràng buộc business rule: CHECK (price >= 0). Postgres support đầy đủ từ lâu.

14. EXCLUDE constraint?

Đặc thù Postgres — ngăn 2 row có overlap theo predicate. Dùng cho booking không trùng giờ:

EXCLUDE USING GIST (room_id WITH =, valid_during WITH &&)

15. Generated column?

GENERATED ALWAYS AS (price * 1.1) STORED   -- vật lý
GENERATED ALWAYS AS (price * 1.1) VIRTUAL  -- tính khi đọc (Postgres 18 default)

16. Domain là gì?

User-defined type với constraint:

CREATE DOMAIN email AS TEXT
    CHECK (VALUE ~ '^.+@.+\..+$');

CREATE TABLE users (email_addr email);   -- tự enforce regex

D. DML & UPSERT

17. Postgres UPSERT làm thế nào?

INSERT ... ON CONFLICT (key) DO UPDATE SET .... Dùng EXCLUDED.col để reference row đang insert.

18. RETURNING làm gì?

Trả về row insert/update/delete trong cùng statement — get ID vừa insert, audit log.

19. Postgres 18 RETURNING có gì mới?

Có OLD và NEW reference:

UPDATE users SET age=age+1 WHERE id=1
RETURNING OLD.age, NEW.age;

20. UPDATE từ JOIN trong Postgres?

Dùng FROM:

UPDATE p SET cat_name = c.name
FROM categories c WHERE c.id = p.cat_id;

21. DELETE từ JOIN?

Dùng USING:

DELETE FROM o USING users u
WHERE u.id = o.user_id AND u.banned = true;

E. JOIN & query

22. LATERAL JOIN dùng làm gì?

Cho phép subquery bên phải reference bảng bên trái. Giải bài "top N per group", parameterized correlated query.

23. DISTINCT ON là gì?

Đặc thù Postgres, giải bài "1 row per group":

SELECT DISTINCT ON (user_id) *
FROM orders ORDER BY user_id, created_at DESC;

24. FILTER (WHERE ...) trong aggregate?

Aggregate có điều kiện, gọn hơn SUM(CASE WHEN ...):

COUNT(*) FILTER (WHERE status='paid')

25. CTE inline vs materialized?

Postgres 12+:

• AS NOT MATERIALIZED: inline, planner tối ưu xuyên CTE (default).
• AS MATERIALIZED: chạy CTE 1 lần, cache.

F. Index

26. Postgres có những loại index nào?

B-tree (default), Hash, GIN, GiST, SP-GiST, BRIN. Mỗi loại tối ưu cho usecase khác nhau.

27. Khi nào dùng GIN?

Multi-value indexing: JSONB, array, full-text search.

28. Khi nào dùng BRIN?

Bảng cực lớn, data sắp xếp tự nhiên theo cột (log time-series). Index siêu nhỏ.

29. Partial index?

Index chỉ subset row có filter. Nhỏ hơn, nhanh hơn cho query cùng filter:

CREATE INDEX ... WHERE status = 'active';

30. Expression index?

Index trên expression — giải bài non-sargable:

CREATE INDEX ... ON users (LOWER(email));

31. CONCURRENTLY trong CREATE INDEX?

Tạo index không lock bảng — production-safe.

32. Index Only Scan là gì?

Khi index có đủ cột query cần (covering) — Postgres không cần lookup heap. Nhanh nhất.

G. Transaction & MVCC

33. MVCC là gì? Postgres dùng ra sao?

Multi-Version Concurrency Control. UPDATE/DELETE không xoá row ngay mà tạo version mới + đánh dấu cũ là dead. Mỗi transaction thấy snapshot tại thời điểm bắt đầu. Đọc không block ghi.

34. VACUUM làm gì?

Reclaim space từ row dead (do MVCC). Auto-vacuum chạy nền. VACUUM FULL rebuild bảng (lock dài).

35. Default isolation của Postgres?

Read Committed (nhưng dựa MVCC, không block ghi như SQL Server).

36. Postgres Read Uncommitted khác gì SQL Server?

Postgres không có dirty read thực sự ngay cả ở Read Uncommitted — vẫn dùng MVCC snapshot. Khác SQL Server cho phép dirty read thực sự.

37. Repeatable Read trong Postgres?

Snapshot toàn transaction — không non-repeatable read, không phantom (mạnh hơn chuẩn ANSI).

38. Serializable trong Postgres?

SSI (Serializable Snapshot Isolation) — optimistic, ít block hơn các DB khác. Nếu phát hiện conflict → abort 1 transaction.

39. WAL là gì?

Write-Ahead Log. Mọi thay đổi ghi vào WAL trước rồi mới ghi vào data file. Cho phép recovery sau crash, basis của replication.

40. pg_hba.conf?

File config authentication. Quy định "ai từ đâu được vào DB nào với method gì". Format: TYPE DATABASE USER ADDRESS METHOD.

H. JSON / Array

41. JSONB index nào hiệu quả?

GIN với jsonb_path_ops cho operator @> (containment). Hoặc B-tree trên (payload->>'key') cho lookup key cụ thể.

42. JSON operator -> vs ->>?

• ->: trả JSONB.
• ->>: trả TEXT (đã unwrap).

43. Tìm row có JSON chứa key?

WHERE payload ? 'key' (key tồn tại). WHERE payload @> '{"key": "value"}' (containment).

44. Array — kiểm tra phần tử?

WHERE 'vue' = ANY(tags) hoặc WHERE tags @> ARRAY['vue']. GIN index trên tags để fast.

I. Extension

45. pgvector dùng làm gì?

Lưu embedding (OpenAI, sentence-transformers) → semantic search bằng cosine/L2 distance. Vector DB built into Postgres.

46. pg_trgm dùng làm gì?

Fuzzy search, similarity. Tìm "iPone" ra "iPhone". Operator %, function similarity(). GIN index gin_trgm_ops.

47. PostGIS?

GIS extension — geometry, geography, spatial query. Postgres + PostGIS gần như standard cho ứng dụng địa lý.

48. pg_stat_statements?

Theo dõi statement performance — top query slow, query gọi nhiều. Bật trong shared_preload_libraries.

J. Performance

49. EXPLAIN vs EXPLAIN ANALYZE?

• EXPLAIN: dự đoán plan, không chạy.
• EXPLAIN ANALYZE: chạy thật, đo time + row thực tế.

50. Seq Scan và Index Scan?

• Seq Scan: full table — OK cho bảng nhỏ, tệ cho bảng lớn.
• Index Scan: dùng index seek.
• Index Only Scan: dùng index + đủ cột (covering).
• Bitmap Index Scan: tốt khi nhiều row match index.

51. Postgres slow query — bạn debug thế nào?

1. EXPLAIN ANALYZE.
2. Check Seq Scan trên bảng lớn — thiếu index?
3. Statistics fresh chưa — ANALYZE.
4. work_mem đủ chưa (sort spill to disk).
5. pg_stat_statements top query.

52. Connection pool — vì sao cần?

Postgres mỗi connection là 1 OS process ~10MB. Web app 100 connection = 1GB RAM ngay. Pool (PgBouncer) chia sẻ connection vật lý cho nhiều client logic.

53. Index không được dùng — tại sao?

Có thể:

• Statistics outdated → ANALYZE.
• WHERE non-sargable (function lên cột).
• Selectivity thấp → planner chọn seq scan.
• Type mismatch → implicit cast hủy index.
• Bảng quá nhỏ.

K. Replication & HA

54. Streaming replication vs Logical replication?

• Streaming: stream WAL byte-level → replica giống hệt master.
• Logical: theo bảng/row, cho phép cross-version, partial replication, dùng cho ETL.

55. Synchronous vs Asynchronous commit?

• Async (default): commit ngay khi WAL local, không chờ replica → fast nhưng có thể mất giao dịch nếu master chết.
• Sync: chờ replica ack → an toàn, chậm hơn.

L. Trick & scenario

56. Bảng 100M row, query WHERE LOWER(email) = ? rất chậm. Fix?

Tạo expression index: CREATE INDEX ... ON users (LOWER(email));

57. UPDATE 10 triệu row, không downtime?

• Batch theo PK range (10K row/batch).
• SET work_mem cao tạm thời cho session.
• Tránh long transaction (block VACUUM, bloat).
• Sau khi xong: VACUUM ANALYZE.

58. Bảng bloat — nguyên nhân và fix?

Do MVCC: UPDATE/DELETE để lại dead row. Auto-vacuum không kịp với heavy write. Fix:

• Tune autovacuum (frequent hơn).
• VACUUM FULL (lock) hoặc pg_repack (online).

59. Khi nào dùng Postgres thay vì NoSQL như MongoDB?

Khi cần ACID, FK, transaction phức tạp. Với JSONB, Postgres làm gần được mọi việc MongoDB làm, plus SQL relational. Khuyến nghị: thử Postgres trước.

60. Postgres có giới hạn về scale không?

Vertical scale rất tốt (đến hàng TB). Horizontal scale write cần sharding (Citus extension, hoặc app-level). Read scale qua read replica dễ.

M. PostgreSQL 19 — tính năng mới

61. SQL/PGQ Property Graph Query là gì? Khi nào dùng?

Đây là chuẩn SQL:2023 cho truy vấn đồ thị, được Postgres 19 hỗ trợ qua extension pgq.

Graph query cho phép mô hình hoá dữ liệu dưới dạng đỉnh (vertex) và cạnh (edge), thay vì table-row. Dùng cho: social network, recommendation, fraud detection, supply chain.

CREATE PROPERTY GRAPH social_graph
  VERTEX TABLES (users KEY (id), posts KEY (id))
  EDGE TABLES (friends KEY (id) SOURCE user_id REFERENCES users(id)
                            DESTINATION friend_id REFERENCES users(id));

SELECT * FROM GRAPH_TABLE (social_graph
  MATCH (u IS users)-[f IS friends]->(friend IS users)
  WHERE u.name = 'Alice'
  COLUMNS (friend.name));

Khi nào dùng: Khi bài toán có quan hệ nhiều tầng (n-hop), recursive traversal hoặc path-finding — các thứ SQL JOIN truyền thống viết dài dòng và chậm.

62. ON CONFLICT DO SELECT khác DO UPDATE / DO NOTHING ra sao?

Trước PG 19, INSERT ... ON CONFLICT chỉ hỗ trợ:

• DO NOTHING — bỏ qua nếu conflict.
• DO UPDATE — ghi đè nếu conflict (dù giá trị không đổi).

DO SELECT mới trong PG 19 cho phép trả về row đã tồn tại mà không cần write:

INSERT INTO users (email, name) VALUES ('john@example.com', 'John')
ON CONFLICT (email) DO SELECT;

Khác biệt chính:

• Không tạo dead tuple hay WAL record → nhanh hơn hẳn DO UPDATE khi row không thay đổi.
• Trả về toàn bộ row conflict (bao gồm cả cột không nằm trong INSERT).
• Lý tưởng cho pattern "get or create" (đọc nếu có, insert nếu chưa).

63. FOR PORTION OF dùng để làm gì?

PG 19 thêm hỗ trợ cập nhật dữ liệu temporal (theo khoảng thời gian) bằng FOR PORTION OF:

CREATE TABLE policy (
    id INT,
    premium NUMERIC,
    valid_from DATE,
    valid_to DATE,
    PERIOD FOR validity (valid_from, valid_to)
);

-- Chỉ update premium trong tháng 6
UPDATE policy FOR PORTION OF validity
FROM '2026-06-01' TO '2026-06-30'
SET premium = premium * 1.1
WHERE id = 100;

Postgres tự động split row tại ranh giới khoảng thời gian — nếu row cũ có validity = [2026-01-01, 2026-12-31], sau UPDATE nó trở thành 3 row:

1. [2026-01-01, 2026-05-31] — giữ nguyên premium cũ.
2. [2026-06-01, 2026-06-30] — premium mới.
3. [2026-07-01, 2026-12-31] — giữ nguyên premium cũ.

Dùng cho: lịch sử giá, policy bảo hiểm, payroll theo kỳ.

64. GROUP BY ALL làm gì?

GROUP BY ALL tự động nhóm theo tất cả cột trong SELECT không phải aggregate, giúp code sạch hơn khi có nhiều cột:

-- Trước PG 19
SELECT department, city, job_level, AVG(salary)
FROM employees
GROUP BY department, city, job_level;

-- PG 19 — ngắn hơn, tránh quên cột
SELECT department, city, job_level, AVG(salary)
FROM employees
GROUP BY ALL;

Co giãn tốt: khi bạn thêm/xoá cột SELECT, không cần sửa GROUP BY. Giống như SELECT * nhưng cho GROUP BY.

Nhưng cẩn thận: Nếu SELECT có cột không mong muốn trong GROUP BY (vd: comment, note text), có thể tạo group sai. Vẫn nên xem lại output.

65. IGNORE NULLS trong window function — những hàm nào hỗ trợ?

PG 19 hỗ trợ IGNORE NULLS và RESPECT NULLS (default) cho các window function:

• LAG(col IGNORE NULLS)
• LEAD(col IGNORE NULLS)
• FIRST_VALUE(col IGNORE NULLS)
• LAST_VALUE(col IGNORE NULLS)
• NTH_VALUE(col, n IGNORE NULLS)

SELECT
    event_time,
    LAG(price IGNORE NULLS) OVER (ORDER BY event_time) AS prev_price
FROM stock_ticks;

Bài toán điển hình: forward-fill / backward-fill missing data trong time series, mang giá trị gần nhất không NULL lên.

Trước PG 19, bạn phải viết subquery hoặc COALESCE + LAG lồng nhau. Giờ sạch hơn và planner tối ưu tốt hơn.

66. pg_plan_advice — plan hint PG 19 khác Oracle/MySQL ra sao?

pg_plan_advice là extension chính thức trong PG 19, cho phép gợi ý planner mà không cần extension bên thứ 3 như pg_hint_plan.

-- Gợi ý dùng Index Scan
SELECT /*+ IndexScan(users) */ * FROM users WHERE email = 'alice@example.com';

-- Gợi ý dùng Hash Join
SELECT /*+ HashJoin(o u) */ * FROM orders o JOIN users u ON o.user_id = u.id;

-- Gợi ý song song hoá
SELECT /*+ Parallel(orders 4) */ * FROM orders;

Khác với Oracle/MySQL hint:

• PG hint là gợi ý mềm (advice), planner vẫn có quyền từ chối nếu thấy không hợp lý.
• Oracle hint cứng hơn — planner gần như bắt buộc tuân theo.
• PG hint không thay đổi semantic của query, chỉ ảnh hưởng plan.

67. REPACK TABLE CONCURRENTLY khác VACUUM FULL thế nào?

VACUUM FULL rebuild toàn bộ bảng để reclaim space do bloat, nhưng lock exclusive (AccessExclusive) — bảng không thể đọc/ghi trong suốt quá trình.

REPACK TABLE CONCURRENTLY (PG 19) rebuild bảng không lock:

REPACK TABLE CONCURRENTLY orders;

Cách hoạt động:

1. Tạo bảng mới với cấu trúc giống bảng cũ.
2. Copy row sang bảng mới (chỉ row live, bỏ dead tuple).
3. Trigger + log để sync thay đổi trong lúc copy.
4. Swap metadata — atomic, gần như không downtime.

68. Parallel Autovacuum — cơ chế và mặc định ra sao?

Trước PG 19, autovacuum chạy single worker cho mỗi bảng. Với bảng lớn, throughput cao (UPDATE/DELETE liên tục), một worker không kịp → bloat tích luỹ.

PG 19 cho phép nhiều autovacuum worker chạy song song trên cùng một bảng, chia theo page range:

autovacuum_max_parallel_workers = 3      -- (mặc định)
autovacuum_parallel_cost_delay = 2ms
autovacuum_parallel_cost_limit = 200

Cơ chế:

• Leader worker quét visibility map, xác định page có dead tuple.
• Chia page range cho parallel workers.
• Mỗi worker VACUUM range của mình độc lập.

69. JSON COPY TO hỗ trợ format nào?

PG 19 mở rộng COPY TO để export trực tiếp ra JSON:

COPY orders TO '/tmp/orders.json' (FORMAT JSON);
COPY orders TO '/tmp/orders.jsonl' (FORMAT JSON, LINESEPARATOR E'\n');

Hỗ trợ:

• JSON array (mặc định): toàn bộ kết quả trong [{...}, {...}].
• JSONL (JSON Lines): mỗi row một dòng JSON object, phù hợp cho streaming processing.
• JSON columns: có thể chỉ export một số cột, hoặc toàn bộ.
• Nested JSON: dùng subquery với jsonb_build_object() để tạo cấu trúc phức tạp.

COPY (
    SELECT jsonb_build_object('id', id, 'items', items)
    FROM orders
) TO '/tmp/orders_nested.jsonl' (FORMAT JSON);

Trước PG 19, bạn phải dùng json_agg() + COPY (SELECT ...) hoặc script external. Giờ tiện hơn, nhanh hơn (C level, không qua SQL aggregation).

70. Online Data Checksums — bật checksum không downtime?

PG checksum bảo vệ dữ liệu khỏi corruption ở mức page (8KB). Trước PG 19, để bật checksum:

1. Shutdown cluster.
2. Chạy pg_checksums --enable.
3. Start lại.

→ Downtime. Trên DB nhiều TB, bước 2 có thể mất hàng giờ.

PG 19 cho phép bật checksum online:

ALTER SYSTEM SET data_checksums = on;
SELECT pg_enable_data_checksums();
-- Kiểm tra tiến độ
SELECT * FROM pg_stat_enable_checksums;

Cách hoạt động:

• Background worker quét tất cả page trong data directory.
• Mỗi page được đọc, tính checksum, ghi lại — từng page một.
• Bảng vẫn đọc/ghi bình thường trong suốt quá trình.
• Sau khi hoàn tất, mọi page mới tự động có checksum.