PostgreSQL Nâng Cao: Locks, Execution Plan và Partitioning

Nếu đã nắm PostgreSQL cơ bản, thì Advanced PostgreSQL có thể hiểu qua 3 mảng lớn:

Locks & concurrency — nhiều transaction cùng truy cập dữ liệu thì PostgreSQL xử lý thế nào?
Execution plan — PostgreSQL quyết định chạy câu SQL bằng cách nào, và làm sao biết query chậm ở đâu?
Partitioning — làm sao chia một bảng rất lớn thành nhiều phần để truy vấn/quản lý hiệu quả hơn?

1. Locks — PostgreSQL khóa dữ liệu như thế nào?#

Hãy tưởng tượng có hai transaction:

-- Transaction A
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

sql

Trong lúc A chưa COMMIT, transaction B chạy:

BEGIN;

UPDATE accounts
SET balance = balance + 50
WHERE id = 1;

sql

B sẽ phải chờ A xử lý xong.

Đây là một dạng row-level locking.

`SELECT ... FOR UPDATE`#

Một pattern rất phổ biến:

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

-- xử lý logic

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

COMMIT;

sql

FOR UPDATE nói với PostgreSQL:

“Tôi chuẩn bị sửa row này, đừng để transaction khác sửa nó đồng thời.”

Rất hữu ích khi có logic kiểu:

đọc → kiểm tra → thay đổi

text

Ví dụ:

SELECT stock
FROM products
WHERE id = 10
FOR UPDATE;

sql

Sau đó:

UPDATE products
SET stock = stock - 1
WHERE id = 10;

sql

Nếu không lock đúng cách, hai request đồng thời có thể cùng thấy:

stock = 1

text

và cả hai đều tưởng rằng sản phẩm còn hàng.

Các loại lock cần biết#

Không cần học thuộc tất cả ngay. Quan trọng nhất:

Row lock#

SELECT ...
FOR UPDATE;

sql

hoặc:

SELECT ...
FOR SHARE;

sql

Dùng khi concurrency ở mức record.

Table lock#

Ví dụ:

LOCK TABLE accounts;

sql

Khóa ở cấp bảng.

Thông thường application code không nên tùy tiện dùng table lock, vì dễ làm giảm concurrency.

Deadlock#

Đây là phần rất quan trọng.

Transaction A:

lock row 1
↓
đợi row 2

text

Transaction B:

lock row 2
↓
đợi row 1

text

Ta có:

A ──locks──> Row 1
A ──waits──> Row 2

B ──locks──> Row 2
B ──waits──> Row 1

text

Không ai tiến được.

PostgreSQL sẽ phát hiện deadlock và terminate một transaction.

Cách phòng tránh#

Đảm bảo mọi transaction lấy lock theo cùng một thứ tự.

Ví dụ luôn:

lock account nhỏ hơn trước
→ lock account lớn hơn

text

thay vì transaction này:

account 1 → account 2

text

và transaction kia:

account 2 → account 1

text

2. Execution Plan — PostgreSQL thực sự chạy query thế nào?#

Đây là kỹ năng cực kỳ quan trọng khi tối ưu PostgreSQL.

Bạn có query:

SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

Đừng chỉ hỏi:

“Có index chưa?”

Hãy hỏi:

PostgreSQL đang thực sự thực hiện query này như thế nào?

Dùng:

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

Ví dụ PostgreSQL có thể trả:

Index Scan using users_email_idx on users
  Index Cond: (email = 'alice@example.com')

text

Điều đó nghĩa là PostgreSQL dùng index.

`EXPLAIN ANALYZE`#

Quan trọng hơn:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

EXPLAIN cho bạn kế hoạch dự kiến.

EXPLAIN ANALYZE sẽ thực thi query và đo thực tế.

Ví dụ:

Index Scan using users_email_idx on users
(cost=0.42..8.44 rows=1 width=100)
(actual time=0.035..0.037 rows=1 loops=1)

text

Bạn nên tập trung vào:

cost
rows
actual time
loops
loại scan

Sequential Scan vs Index Scan#

Giả sử bảng có:

10,000,000 users

text

Query:

SELECT *
FROM users
WHERE id = 123;

sql

PostgreSQL có thể dùng:

Index Scan

text

vì chỉ cần tìm một row.

Nhưng query:

SELECT *
FROM users
WHERE country = 'Vietnam';

sql

Nếu 70% bảng là Vietnam thì PostgreSQL có thể chọn:

Seq Scan

text

dù country có index.

Đây không nhất thiết là vấn đề.

Một sai lầm phổ biến:

“Seq Scan = query sai.”

Không đúng.

Nếu cần đọc phần lớn bảng, đọc tuần tự đôi khi rẻ hơn dùng index.

Những execution node nên biết#

Ít nhất hãy hiểu:

Seq Scan
Index Scan
Index Only Scan
Bitmap Index Scan
Nested Loop
Hash Join
Merge Join
Sort
Aggregate

text

Ví dụ:

SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id;

sql

PostgreSQL có thể chọn:

Nested Loop

text

hoặc:

Hash Join

text

hoặc:

Merge Join

text

Tùy dữ liệu, statistics và cost model.

Một ví dụ tối ưu query#

Giả sử:

SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

Index:

CREATE INDEX idx_orders_customer
ON orders(customer_id);

sql

Có thể tốt hơn nếu workload thường xuyên query cả hai điều kiện:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

sql

Sau đó:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

Bạn muốn nhìn xem planner có tận dụng index mới hay không.

3. Partitioning — chia bảng lớn#

Giả sử:

orders

text

có:

2 billion rows

text

và dữ liệu có:

created_at

text

Bạn có thể partition theo tháng:

orders
├── orders_2026_01
├── orders_2026_02
├── orders_2026_03
├── ...
└── orders_2026_08

text

Thay vì một bảng khổng lồ.

Range partitioning#

Ví dụ:

CREATE TABLE orders (
    id bigint,
    created_at timestamp,
    customer_id bigint,
    amount numeric
) PARTITION BY RANGE (created_at);

sql

Tạo partition:

CREATE TABLE orders_2026_08
PARTITION OF orders
FOR VALUES FROM ('2026-08-01')
             TO ('2026-09-01');

sql

Partition tháng tiếp theo:

CREATE TABLE orders_2026_09
PARTITION OF orders
FOR VALUES FROM ('2026-09-01')
             TO ('2026-10-01');

sql

Partition pruning#

Đây là lý do partitioning hữu ích.

Query:

SELECT *
FROM orders
WHERE created_at >= '2026-08-01'
  AND created_at < '2026-09-01';

sql

PostgreSQL có thể chỉ đọc:

orders_2026_08

text

thay vì:

orders_2026_01
orders_2026_02
orders_2026_03
...
orders_2026_08

text

Đó gọi là partition pruning.

Nhưng partitioning không phải “magic performance button”#

Ví dụ:

SELECT *
FROM orders
WHERE customer_id = 123;

sql

Nếu partition theo:

created_at

text

thì PostgreSQL có thể vẫn phải tìm qua nhiều partition.

Do đó partition key phải phù hợp với workload.

Một câu hỏi quan trọng trước khi partition:

“Các query lớn nhất của tôi thường filter theo column nào?”

4. Ba chủ đề này liên quan với nhau thế nào?#

Đây mới là phần quan trọng.

Hãy tưởng tượng một hệ thống e-commerce:

orders: 2 billion rows

text

Bạn có:

SELECT *
FROM orders
WHERE customer_id = 123
  AND created_at >= '2026-01-01';

sql

Bạn có thể cần:

Partitioning#

Chia:

orders
→ theo created_at

text

Index#

Trong từng partition:

(customer_id, created_at)

sql

Execution plan#

Kiểm tra:

EXPLAIN ANALYZE

sql

xem PostgreSQL có:

Partition pruning
       ↓
Index Scan
       ↓
ít rows

text

hay không.

5. Locks + Execution Plan cũng liên quan#

Một query chậm không chỉ gây khó chịu cho user.

Ví dụ:

Transaction A
    ↓
UPDATE
    ↓
giữ lock
    ↓
query chạy 30 giây

text

Trong lúc đó:

Transaction B
    ↓
UPDATE cùng row
    ↓
WAIT

text

Một query không tối ưu có thể khiến lock được giữ lâu hơn, từ đó tạo ra:

slow query
   ↓
long transaction
   ↓
lock contention
   ↓
more waiting
   ↓
more slow requests

text

Đây là lý do khi debug production PostgreSQL, không nên chỉ nhìn:

“Query này mất 5 giây.”

Mà phải hỏi thêm:

“Nó đang giữ lock gì trong 5 giây đó?”

6. Một roadmap học Advanced PostgreSQL#

Nếu mục tiêu là senior/backend engineer, mình sẽ học theo thứ tự:

flowchart TD
    A[PostgreSQL] --> B[Transactions]
    A --> C[Query Planning]
    A --> D[Storage]
    
    B --> E[Isolation]
    B --> F[Locks]
    B --> G[Deadlock]
    
    C --> H[EXPLAIN]
    C --> I[Indexes]
    
    D --> J[MVCC]
    D --> K[VACUUM]
    D --> L[Bloat]
    
    F --> M[Concurrency]
    I --> N[Performance]
    
    M --> O[Partitioning]
    N --> O
    O --> P[Large datasets]

Sau 3 chủ đề bạn hỏi, các chủ đề tiếp theo rất đáng học là:

MVCC
VACUUM / autovacuum
Index internals — B-tree, GIN, GiST, BRIN
Transaction isolation
Deadlock debugging
CTE / materialized CTE
Window functions
Query planner & statistics
Table/index bloat
Connection pooling
Replication
Read replicas
Partition maintenance

Nếu học theo kiểu thực chiến, bộ ba EXPLAIN ANALYZE + locks + MVCC nên được ưu tiên trước, vì chúng giúp bạn debug phần lớn các vấn đề PostgreSQL production.

1. Locks — PostgreSQL khóa dữ liệu như thế nào?#

SELECT ... FOR UPDATE#

Các loại lock cần biết#

Row lock#

Table lock#

Deadlock#

Cách phòng tránh#

2. Execution Plan — PostgreSQL thực sự chạy query thế nào?#

EXPLAIN ANALYZE#

Sequential Scan vs Index Scan#

Những execution node nên biết#

Một ví dụ tối ưu query#

3. Partitioning — chia bảng lớn#

Range partitioning#

Partition pruning#

Nhưng partitioning không phải “magic performance button”#

4. Ba chủ đề này liên quan với nhau thế nào?#

Partitioning#

Index#

Execution plan#

5. Locks + Execution Plan cũng liên quan#

6. Một roadmap học Advanced PostgreSQL#

References#

`SELECT ... FOR UPDATE`#

`EXPLAIN ANALYZE`#