Node.js Production Engineering 11 — PostgreSQL Chuyên sâu

Một API chuyển tiền có thể vượt toàn bộ unit test nhưng vẫn tạo số dư âm khi hai request đến cùng lúc. Một query có index vẫn có thể chậm hơn Seq Scan. Một transaction đã COMMIT vẫn có thể để hệ thống thiếu sự kiện nếu ứng dụng gửi message sau đó rồi crash.

Ba tình huống này có chung một bài học: database không chỉ là nơi lưu object; nó là nơi thực thi invariant dưới concurrency. Bài này dùng PostgreSQL 18 và node-postgres để xây mental model đó từ schema tới vận hành.

Sau khi hoàn thành, bạn có thể:

• đặt invariant quan trọng vào constraint thay vì chỉ tin application code;
• thiết kế index từ query và kiểm chứng bằng EXPLAIN (ANALYZE, BUFFERS);
• chọn isolation level, lock và retry theo failure mode cụ thể;
• cài transfer idempotent, không âm tiền và chịu được request đồng thời;
• phân trang ổn định bằng cursor ghép;
• sizing pool, quan sát lock/bloat và kiểm thử concurrency có chủ đích.

Baseline của bài: PostgreSQL 18.x, Node.js 24 LTS và pg. Khi dùng bản khác, đối chiếu release note trước khi áp dụng option vận hành.

---

Mental model: bốn lớp bảo vệ dữ liệu

Hãy đọc một đường ghi theo bốn lớp:

HTTP validation
    ↓  hình dạng input hợp lệ
application policy
    ↓  use case được phép
database constraint + transaction
    ↓  invariant vẫn đúng khi có concurrency/crash
durable side effect
       outbox → broker/email/search index

Validation ở HTTP không thay thế constraint. Transaction không tự bảo đảm business invariant. COMMIT database cũng không làm một lệnh gọi HTTP hoặc publish message bên ngoài trở nên atomic.

Các invariant mẫu trong bài:

1. email không trùng khi so sánh không phân biệt hoa thường;
2. số dư không âm;
3. một idempotency key chỉ mô tả đúng một transfer;
4. hai tài khoản của transfer phải khác nhau;
5. một trang feed không bỏ sót item có cùng timestamp.

---

Schema là executable policy

Chạy local bằng image major đã ghim; trong CI/production nên ghim cả patch hoặc digest:

docker run --name pg18 \
  -e POSTGRES_PASSWORD=local-only \
  -p 127.0.0.1:5432:5432 \
  -d postgres:18-alpine

citext là extension, vì vậy migration phải bật nó trước khi dùng:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       citext NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE posts (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id    bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  title        text NOT NULL CHECK (length(title) BETWEEN 1 AND 200),
  body         text NOT NULL,
  status       text NOT NULL DEFAULT 'draft'
               CHECK (status IN ('draft', 'published', 'archived')),
  meta         jsonb NOT NULL DEFAULT '{}'::jsonb,
  published_at timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now(),
  CHECK ((status = 'published') = (published_at IS NOT NULL))
);

CREATE TABLE accounts (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_id      bigint NOT NULL REFERENCES users(id),
  balance_cents bigint NOT NULL DEFAULT 0 CHECK (balance_cents >= 0)
);

CREATE TABLE transfers (
  id               uuid PRIMARY KEY,
  idempotency_key  text NOT NULL UNIQUE,
  from_account_id  bigint NOT NULL REFERENCES accounts(id),
  to_account_id    bigint NOT NULL REFERENCES accounts(id),
  amount_cents     bigint NOT NULL CHECK (amount_cents > 0),
  created_at       timestamptz NOT NULL DEFAULT now(),
  CHECK (from_account_id <> to_account_id)
);

Điểm thiết kế cần nói rõ:

• timestamptz phù hợp cho một thời điểm tuyệt đối. Lịch “9 giờ sáng theo múi giờ cửa hàng” cần lưu thêm timezone/quy tắc nghiệp vụ; không có một kiểu thời gian đúng cho mọi bài toán.
• citext tiện cho email nhưng có semantics/cost riêng. Với hệ thống đa locale, hãy chốt chính sách canonicalization và collation thay vì mặc định mọi chuỗi giống email.
• ON DELETE RESTRICT ở posts thể hiện quyết định không âm thầm xóa nội dung. CASCADE chỉ đúng khi vòng đời của child thực sự thuộc parent.
• constraint là hàng rào cuối. Application vẫn cần trả lỗi domain dễ hiểu khi constraint từ chối write.

---

Index bắt đầu từ query, không từ danh sách cột

Không có quy tắc “cột filter/join/sort nào cũng phải index”. Một index đáng tồn tại khi workload đủ quan trọng và planner có thể dùng nó hiệu quả hơn chi phí duy trì.

Feed dưới đây lọc equality rồi sắp xếp theo thời gian và id:

SELECT id, title, published_at
FROM posts
WHERE author_id = $1
  AND status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20;

Index khớp access path:

CREATE INDEX idx_posts_author_feed
  ON posts (author_id, published_at DESC, id DESC)
  INCLUDE (title)
  WHERE status = 'published';

CREATE INDEX idx_posts_meta_gin
  ON posts USING gin (meta jsonb_path_ops);

CREATE INDEX idx_accounts_owner
  ON accounts (owner_id);

Thứ tự composite index không đơn giản là “cột selectivity cao nhất trước”. Hãy ưu tiên:

1. equality predicate dẫn đường;
2. range hoặc thứ tự cần tránh sort;
3. tie-breaker duy nhất cho pagination;
4. INCLUDE chỉ cho cột cần cover, vì index rộng làm write và cache đắt hơn.

Một partial index chỉ được dùng khi planner chứng minh predicate của query phù hợp điều kiện WHERE của index. Query dùng parameter hoặc biểu thức khác hình dạng có thể không match như bạn kỳ vọng.

Đọc plan thay vì săn Index Scan

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT id, title, published_at
FROM posts
WHERE author_id = 42
  AND status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20;

Đọc từ dưới lên và kiểm tra:

• estimated rows so với actual rows: lệch lớn thường chỉ ra statistics hoặc correlation chưa được mô hình hóa;
• actual time, loops, rows removed và buffer hit/read;
• sort có spill ra disk hay không;
• index scan còn nhiều heap fetch hay không;
• lock/I/O wait nằm ngoài CPU time của plan như thế nào.

Seq Scan không tự động là lỗi. Với bảng nhỏ hoặc query lấy phần lớn bảng, đọc tuần tự có thể rẻ hơn nhảy qua index. Mục tiêu là latency/resource phù hợp SLO, không phải ép planner hiện đúng tên node.

EXPLAIN ANALYZE thực thi statement. Với write query trên production, dùng transaction rollback hoặc replica/anonymized staging và hiểu rõ side effect trước khi chạy.

---

Isolation level: transaction thấy thế giới nào

PostgreSQL triển khai ba mức hữu ích:

Mức	Snapshot và failure mode chính	Khi cân nhắc
READ COMMITTED	mỗi statement có snapshot mới; có non-repeatable read	CRUD ngắn, atomic update
REPEATABLE READ	một snapshot ổn định; PostgreSQL không cho phantom read nhưng vẫn có anomaly cần abort	báo cáo/logic cần snapshot ổn định
SERIALIZABLE	Serializable Snapshot Isolation; có thể abort 40001	invariant nhiều bước khó diễn đạt bằng một statement

Serializable không có nghĩa “không cần nghĩ về concurrency”. Nó đổi anomaly âm thầm thành transaction bị hủy; ứng dụng phải retry toàn bộ transaction.

Ngoài 40001 (serialization_failure), deadlock có SQLSTATE 40P01. Retry chỉ an toàn khi toàn bộ side effect nằm trong transaction hoặc được bảo vệ bởi idempotency.

---

Transfer đúng: lock order, constraint, idempotency, retry

Đầu tiên, validate ngoài transaction để loại input vô nghĩa; database vẫn giữ constraint làm hàng rào cuối:

interface TransferCommand {
  id: string;
  idempotencyKey: string;
  fromAccountId: string;
  toAccountId: string;
  amountCents: bigint;
}

function validateTransfer(cmd: TransferCommand): void {
  if (cmd.amountCents <= 0n) throw new Error('amountCents must be positive');
  if (cmd.fromAccountId === cmd.toAccountId)
    throw new Error('accounts must differ');
  if (!cmd.idempotencyKey || cmd.idempotencyKey.length > 200) {
    throw new Error('invalid idempotency key');
  }
}

Helper retry tạo một transaction mới cho mỗi lần thử:

import { Pool, type PoolClient } from 'pg';
import { setTimeout as sleep } from 'node:timers/promises';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
  application_name: 'payments-api',
});

function sqlState(error: unknown): string | undefined {
  return typeof error === 'object' && error !== null && 'code' in error
    ? String(error.code)
    : undefined;
}

async function withSerializableRetry<T>(
  work: (client: PoolClient) => Promise<T>,
  maxAttempts = 4
): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await work(client);
      await client.query('COMMIT');
      return result;
    } catch (error) {
      await client.query('ROLLBACK').catch(() => undefined);
      const retryable = ['40001', '40P01'].includes(sqlState(error) ?? '');
      if (!retryable || attempt >= maxAttempts) throw error;
      await sleep(25 * 2 ** (attempt - 1) + Math.random() * 50);
    } finally {
      client.release();
    }
  }
}

Use case transfer:

async function transfer(cmd: TransferCommand) {
  validateTransfer(cmd);

  return withSerializableRetry(async (client) => {
    const inserted = await client.query<{
      id: string;
      from_account_id: string;
      to_account_id: string;
      amount_cents: string;
    }>(
      `INSERT INTO transfers
         (id, idempotency_key, from_account_id, to_account_id, amount_cents)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (idempotency_key) DO NOTHING
       RETURNING id, from_account_id, to_account_id, amount_cents`,
      [
        cmd.id,
        cmd.idempotencyKey,
        cmd.fromAccountId,
        cmd.toAccountId,
        cmd.amountCents.toString(),
      ]
    );

    if (inserted.rowCount === 0) {
      const existing = await client.query(
        `SELECT id, from_account_id, to_account_id, amount_cents
         FROM transfers WHERE idempotency_key = $1`,
        [cmd.idempotencyKey]
      );
      const row = existing.rows[0];
      if (
        !row ||
        row.from_account_id !== cmd.fromAccountId ||
        row.to_account_id !== cmd.toAccountId ||
        BigInt(row.amount_cents) !== cmd.amountCents
      ) {
        throw new Error('idempotency key was reused with a different command');
      }
      return { transferId: row.id, replayed: true };
    }

    // Lock both rows in a deterministic order to reduce opposite-direction deadlocks.
    const accounts = await client.query<{ id: string; balance_cents: string }>(
      `SELECT id, balance_cents
       FROM accounts
       WHERE id = ANY($1::bigint[])
       ORDER BY id
       FOR UPDATE`,
      [[cmd.fromAccountId, cmd.toAccountId]]
    );
    if (accounts.rowCount !== 2) throw new Error('account not found');

    const debit = await client.query(
      `UPDATE accounts
       SET balance_cents = balance_cents - $1
       WHERE id = $2 AND balance_cents >= $1
       RETURNING id`,
      [cmd.amountCents.toString(), cmd.fromAccountId]
    );
    if (debit.rowCount !== 1) throw new Error('insufficient funds');

    const credit = await client.query(
      `UPDATE accounts
       SET balance_cents = balance_cents + $1
       WHERE id = $2
       RETURNING id`,
      [cmd.amountCents.toString(), cmd.toAccountId]
    );
    if (credit.rowCount !== 1) throw new Error('destination account not found');

    return { transferId: cmd.id, replayed: false };
  });
}

Điểm đáng chú ý:

• bigint của pg mặc định được trả về dạng string; chuyển sang BigInt có chủ đích, không đi qua number gây mất chính xác.
• cùng idempotency key và cùng payload trả kết quả cũ; cùng key nhưng payload khác bị từ chối.
• constraint balance_cents >= 0 vẫn bảo vệ nếu một code path khác quên conditional update.
• email/webhook không chạy bên trong transaction. Ghi một outbox row trong cùng transaction rồi worker publish sau commit.

---

Keyset pagination không bỏ sót tie

Nếu order là (published_at DESC, id DESC), cursor cũng phải chứa cả hai field:

SELECT id, title, published_at
FROM posts
WHERE author_id = $1
  AND status = 'published'
  AND (
    $2::timestamptz IS NULL
    OR (published_at, id) < ($2::timestamptz, $3::bigint)
  )
ORDER BY published_at DESC, id DESC
LIMIT $4;

Chỉ lọc published_at < cursorTime sẽ bỏ các row có cùng timestamp. Cursor public nên được encode có version; nếu việc sửa sort key được phép, hãy quyết định rõ snapshot semantics hoặc chấp nhận item di chuyển giữa trang.

Offset vẫn phù hợp cho tập nhỏ, trang admin cần nhảy tới số trang và workload đã đo chấp nhận được. Keyset đổi lại không có random page và cần sort order ổn định.

---

JSONB, full-text và LISTEN/NOTIFY: biết ranh giới

SELECT id
FROM posts
WHERE meta @> '{"featured": true}'::jsonb;

SELECT id, title
FROM posts
WHERE to_tsvector('simple', title || ' ' || body)
      @@ websearch_to_tsquery('simple', $1);

JSONB hợp với metadata linh hoạt; field tham gia invariant, FK hoặc query nóng thường nên trở thành cột rõ kiểu. Full-text của PostgreSQL đủ mạnh cho nhiều hệ thống trước khi cần search engine riêng.

LISTEN/NOTIFY là tín hiệu best-effort, payload nhỏ, không có replay và không thay message broker. Pattern an toàn là lưu state/outbox trước; notification chỉ đánh thức consumer đi đọc dữ liệu bền.

---

Pooling và shutdown

Pool là hàng đợi concurrency tới database. Đặt max theo ngân sách toàn hệ thống:

replica tối đa × pool.max
  + migration/admin/worker headroom
  ≤ connection budget của database

Pool quá lớn làm tăng context switching và tranh chấp trong PostgreSQL; pool quá nhỏ làm tăng queue time ở app. Đo cả hai phía trước khi đổi.

process.on('SIGTERM', async () => {
  // Trước đó: readiness=false và HTTP server đã ngừng nhận request mới.
  await pool.end();
});

Với serverless hoặc replica fan-out cao, dùng pooler/proxy phù hợp. Transaction pooling có trade-off với session state, prepared statement và advisory lock; kiểm tra compatibility thay vì chỉ thêm PgBouncer rồi coi là xong.

---

MVCC, VACUUM và failure modes vận hành

PostgreSQL update bằng cách tạo row version mới. Snapshot cũ cho reader nhất quán nhưng dead tuple cần autovacuum dọn.

Theo dõi:

• transaction mở lâu và idle in transaction;
• dead tuples, autovacuum progress và table/index bloat;
• lock wait/deadlock;
• replication lag;
• query fingerprint qua pg_stat_statements;
• pool acquire time, query p95/p99 và serialization retry count ở Node.

Các failure mode thường gặp:

Failure mode	Biểu hiện	Hướng xử lý
missing index	I/O/latency tăng theo dữ liệu	plan + workload-specific index
over-index	write/WAL/bloat tăng	bỏ index không dùng sau chu kỳ quan sát
long transaction	vacuum bị giữ, lock kéo dài	transaction ngắn, timeout, điều tra owner
connection storm	DB hết connection/CPU	bounded pool, backpressure, pooler
retry storm	CPU/lock tăng sau sự cố	retry budget, exponential backoff + jitter
unsafe migration	lock bảng hoặc code/schema lệch	expand → migrate data → contract

Migration “rollback được” không phải lúc nào cũng khả thi. Với thay đổi dữ liệu lớn, forward fix và expand-contract thường an toàn hơn down migration phá dữ liệu.

---

Kiểm thử và lab

Test cần có

• property test: tổng tiền trước/sau transfer không đổi;
• concurrency test: nhiều debit song song không tạo số dư âm;
• idempotency test: 20 request cùng key chỉ tạo một transfer;
• serialization test: ép 40001, xác nhận retry chạy toàn transaction;
• pagination test: nhiều post cùng published_at không thiếu/trùng;
• migration test: chạy từ schema production gần nhất trên database mới và database có dữ liệu mẫu.

Lab: payment ledger tối thiểu

Xây POST /transfers và GET /posts cursor-based trên PostgreSQL 18.

Acceptance criteria:

1. accounts.balance_cents có constraint không âm và transfer có unique idempotency key.
2. 100 request đồng thời không làm âm tiền; tổng balance không đổi.
3. replay cùng key/payload trả cùng transfer; key/payload khác trả 409.
4. transaction retry 40001/40P01 có giới hạn, backoff và metric.
5. feed 1.000 row với nhiều timestamp trùng đi hết không thiếu/trùng.
6. EXPLAIN (ANALYZE, BUFFERS) chứng minh index feed phục vụ query; báo cáo có before/after.
7. shutdown đóng pool sau khi HTTP drain; test không còn open handle.

---

Checklist production

• Invariant quan trọng có constraint hoặc atomic statement ở database.
• Transaction ngắn; external side effect đi qua outbox/idempotency.
• Lock nhiều row theo thứ tự xác định.
• Serializable/deadlock có bounded retry toàn transaction.
• Index được biện minh bằng query + plan, không bằng cảm giác.
• Cursor chứa toàn bộ sort key và tie-breaker duy nhất.
• Pool có budget theo số replica và có acquire timeout/metric.
• Có dashboard query latency, lock, retry, bloat và connection.
• Migration dùng expand-contract và được thử trên dữ liệu gần production.

---

Tài liệu chính thức

• PostgreSQL 18 — Concurrency Control
• PostgreSQL 18 — Transaction Isolation
• PostgreSQL 18 — Serialization Failure Handling
• PostgreSQL 18 — Using EXPLAIN
• PostgreSQL 18 — Indexes
• PostgreSQL 18 — citext
• node-postgres — Pooling

Phần tiếp theo đưa cùng các invariant này qua Prisma 7. Mục tiêu không phải che SQL, mà là giữ type safety và migration workflow mà không đánh mất transaction semantics vừa xây.