Hybrid schema — column cứng + JSONB, khi nào migrate lên column

TL;DR: Đừng chọn giữa "tất cả là column" và "tất cả vào JSONB". Field chung cho mọi tenant, xuất hiện trong WHERE/JOIN/ORDER BY thường xuyên, cần CHECK hay FK thì là column cứng: có type safety, B-tree, statistics cho planner. Field chỉ vài tenant cần, không ai query trực tiếp thì ở JSONB: thêm key không cần ALTER TABLE. Ranh giới này trôi theo thời gian, nên bạn audit key JSONB định kỳ bằng jsonb_object_keys, và khi một key thành field chung thì dời nó lên column theo 8 bước: thêm column nullable, backfill theo chunk, verify, app đọc column mới, dual-write vài ngày, ngừng ghi JSONB, xoá key, rồi mới thêm NOT NULL và CHECK bằng NOT VALID + VALIDATE.

TaskFlow đang review schema. Field priority nên là column priority TEXT CHECK (priority IN ('low','med','high','critical')) hay nằm trong task_metadata->>'priority'? Cả hai đều chạy. Nhưng chọn sai bây giờ là đau trong 6–12 tháng tới, khi bảng vượt 1 triệu row và một phần ba query đều chạm priority.

1. Analogy — tủ quần áo hai loại ngăn

Extreme thứ nhất: tất cả vào ngăn riêng, 50 ngăn mỗi ngăn một loại phụ kiện, tủ thành mê cung không ai tìm được gì. Extreme thứ hai: tất cả vào ngăn linh tinh, tìm áo mặc hàng ngày mất 5 phút đào bới. Hybrid là thiết kế có chủ đích: item dùng hàng ngày có ngăn riêng, item hiếm dùng vào ngăn chung.

2. Vì sao pure column và pure JSONB đều hỏng?

Pure JSONB cho tất cả là lỗi "NoSQL fanboy" mà bài 01 đã cảnh báo: PG thành document store nhưng mất type safety và optimization. Pure column cho tất cả là lỗi ngược lại: 50 nullable column không ai nhớ dùng để làm gì.

3. Hybrid pattern — schema TaskFlow chuẩn

CREATE TABLE tasks (
  id          BIGSERIAL PRIMARY KEY,
  project_id  BIGINT NOT NULL REFERENCES projects(id),

  -- Common field (phan lon query cham) -> column cung
  status      TEXT NOT NULL CHECK (status IN ('todo','doing','done','archived')),
  priority    TEXT CHECK (priority IN ('low','med','high','critical')),
  assignee_id BIGINT REFERENCES users(id),
  due_at      TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Per-tenant flexible field -> JSONB
  task_metadata JSONB NOT NULL DEFAULT '{}'
);

-- B-tree cho common field, GIN cho JSONB
CREATE INDEX idx_tasks_status_priority ON tasks(status, priority);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_tasks_metadata ON tasks USING gin(task_metadata jsonb_path_ops);

Với schema này, filter theo status và priority đi B-tree với đủ statistics; tenant A thêm {"compliance_tag": "GDPR"}, tenant B thêm {"client_code": "ACME-001"} vào task_metadata mà không ai phải ALTER TABLE; và FK assignee_id REFERENCES users(id) vẫn được enforce, thứ JSONB không làm được.

Thử ngẫmFK assignee_id REFERENCES users(id) chặn insert user không tồn tại; JSONB task_metadata thì không. Nếu bạn xoá thử một user đang có task gán cho họ, PostgreSQL báo lỗi ở đâu?

4. Khi nào nên dời một key JSONB lên column?

Không phải mọi key JSONB đều cần migrate. Decision matrix:

Trong TaskFlow, priority ban đầu nằm trong task_metadata vì schema chưa ổn định. Sau 3 tháng, audit cho thấy 80% query filter theo priority, 75% task có field này, và chỉ có 4 giá trị phân biệt. Ba tiêu chí đo được đều chỉ về column; type validation, FK và aggregation không áp dụng cho priority.

5. Vì sao migrate cần 8 bước thay vì một câu ALTER TABLE?

Dời priority từ task_metadata sang column riêng mà không downtime, không lock bảng lâu:

-- BUOC 1: them column NULLABLE (khong scan, khong rewrite)
ALTER TABLE tasks ADD COLUMN priority_v2 TEXT;

-- BUOC 2: backfill theo chunk (tranh mot UPDATE giu lock hang trieu row + bloat)
UPDATE tasks
SET priority_v2 = task_metadata->>'priority'
WHERE id BETWEEN 1 AND 50000
  AND task_metadata ? 'priority'
  AND priority_v2 IS NULL;
-- lap voi chunk id ke tiep

-- BUOC 3: verify, ket qua phai la 0
SELECT count(*)
FROM tasks
WHERE task_metadata ? 'priority'
  AND priority_v2 IS DISTINCT FROM task_metadata->>'priority';

-- BUOC 4: deploy app doc tu priority_v2 (van ghi ca hai)
-- BUOC 5: dual-write vai ngay -> rollback app version cu van an toan
-- BUOC 6: deploy app ngung ghi priority vao task_metadata

-- BUOC 7: xoa key JSONB (theo chunk nhu buoc 2)
UPDATE tasks
SET task_metadata = task_metadata - 'priority'
WHERE task_metadata ? 'priority';

-- BUOC 8: rename + constraint, them NOT VALID de khong scan ca bang duoi lock
ALTER TABLE tasks RENAME COLUMN priority_v2 TO priority;

ALTER TABLE tasks ADD CONSTRAINT chk_tasks_priority
  CHECK (priority IN ('low','med','high','critical')) NOT VALID;
ALTER TABLE tasks VALIDATE CONSTRAINT chk_tasks_priority;   -- scan voi lock nhe

-- PG 12+: SET NOT NULL bo qua scan neu da co CHECK (col IS NOT NULL) da validate
ALTER TABLE tasks ADD CONSTRAINT chk_tasks_priority_nn
  CHECK (priority IS NOT NULL) NOT VALID;
ALTER TABLE tasks VALIDATE CONSTRAINT chk_tasks_priority_nn;
ALTER TABLE tasks ALTER COLUMN priority SET NOT NULL;
ALTER TABLE tasks DROP CONSTRAINT chk_tasks_priority_nn;

CREATE INDEX CONCURRENTLY idx_tasks_priority ON tasks(priority);

Vì sao không gộp thành một câu? ADD COLUMN priority TEXT NOT NULL không có DEFAULT lỗi ngay trên bảng có dữ liệu; thêm DEFAULT 'low' thì từ PG 11 là tức thời, nhưng 2 triệu row đều thành 'low' kể cả row đang có high trong JSONB. SET NOT NULL và ADD CONSTRAINT CHECK mặc định scan cả bảng dưới ACCESS EXCLUSIVE lock; NOT VALID cho constraint có hiệu lực với row mới ngay, còn scan xác nhận row cũ chạy sau với lock nhẹ hơn. Và dual-write là thứ giữ cho bạn đường lùi: app version cũ vẫn đọc được JSONB nếu phải rollback.

Thử ngẫmbảng tasks có 2 triệu row. Nếu bạn bỏ qua bước dual-write (bước 5) và ngừng ghi JSONB ngay sau backfill, rollback về app version cũ sẽ mất dữ liệu gì?

6. Audit key JSONB định kỳ

Chạy audit này mỗi 3–6 tháng để phát hiện key nên dời lên column:

-- Ti le row co moi key
SELECT key,
       count(*) AS occurrence,
       round(count(*) * 100.0 / (SELECT count(*) FROM tasks), 1) AS pct_rows
FROM tasks, jsonb_object_keys(task_metadata) AS key
GROUP BY key
ORDER BY occurrence DESC
LIMIT 20;
-- pct_rows cao -> ung vien column; thap -> giu trong JSONB

-- So gia tri phan biet per key (ung vien CHECK enum)
SELECT key,
       count(DISTINCT task_metadata ->> key) AS distinct_values,
       array_agg(DISTINCT task_metadata ->> key) AS sample_values
FROM tasks, jsonb_object_keys(task_metadata) AS key
GROUP BY key
HAVING count(DISTINCT task_metadata ->> key) < 20
ORDER BY distinct_values;

Tần suất query theo từng key lấy từ pg_stat_statements (khoá PostgreSQL — Advanced & Scale, module Observability, đang xây). Có ba con số này, quyết định migrate có bằng chứng thay vì cảm tính.

7. Pitfall — JSONB-as-everything và column-explosion

ALTER TABLE tasks ADD CONSTRAINT chk_meta_priority
  CHECK ((task_metadata->>'priority') IN ('low','med','high','critical'));

INSERT INTO tasks (project_id, title, task_metadata) VALUES (1, 'x', '{}');
-- INSERT 0 1  -- lot qua: ->> tra NULL, CHECK coi NULL la pass

ALTER TABLE tasks ADD COLUMN gdpr_compliance_level TEXT;
ALTER TABLE tasks ADD COLUMN client_code TEXT;
ALTER TABLE tasks ADD COLUMN sla_hours INT;
-- ... 50 column nullable, 90% NULL voi moi tenant

8. Applied — ba pattern thực chiến

Cùng một khuôn áp cho ba domain: cột chung cho mọi row là column, phần khác nhau theo loại là JSONB.

Khi một key trong custom_fields như sla_hours được 60% tenant dùng, audit ở section 6 sẽ chỉ ra và bạn dời nó lên column theo 8 bước.

9. Deep Dive

10. Liên hệ các bài khác

• Bài 01 — JSONB vs JSON vs TEXT: pitfall "nhét schema cố định vào JSONB" ở đó là lý do bài này tồn tại.
• Bài 03 — GIN index: GIN không range scan và ghi chậm hơn B-tree, một trong các lý do field lọc thường xuyên nên là column.
• Denormalization tradeoff (khoá SQL & Database): hybrid schema là một dạng denormalize có kiểm soát; đọc để thấy cùng logic đánh đổi.
• Surrogate vs natural key: FK trong column cứng dựa trên key thiết kế ở đây.
• Statistics và cost model (khoá Internals): vì sao planner ước lượng selectivity của column cứng tốt hơn field trong JSONB.

11. Tóm tắt

• Đủ 3/5 tiêu chí ở KeyTakeaways là đáng dời lên column.
• Trong suốt migration, column mới luôn nullable; NOT NULL và CHECK là việc cuối cùng, thêm bằng NOT VALID rồi VALIDATE.
• Backfill và xoá key JSONB đều làm theo chunk id BETWEEN, không một UPDATE cho cả bảng.
• Rollback được tới đâu tuỳ dual-write: chưa ngừng ghi JSONB thì app cũ vẫn chạy.
• CHECK trên ->>'key' không chặn row thiếu key (NULL pass); type safety thật chỉ có ở column.

12. Tự kiểm tra

Bài tiếp theo: Full-text search tiếng Việt — unaccent + GIN + ts_rank