Node.js Super Senior · Phase 11 — PostgreSQL Deep Dive

This is Bonus Phase 11, the first of three deep dives that follow the core ten — PostgreSQL, Prisma, then NestJS {Đây là Bonus Phase 11, phần đầu trong ba deep dive nối tiếp mười phase cốt lõi — PostgreSQL, Prisma, rồi NestJS}. Phase 4 gave you database breadth across SQL and NoSQL {Phase 4 cho bạn bề rộng về database SQL và NoSQL}. Now we go deep on the one database most senior Node backends actually run on: PostgreSQL {Giờ ta đi sâu vào database mà đa số backend Node senior thực sự chạy: PostgreSQL}. A junior writes queries that return the right rows; a senior writes queries that stay correct under concurrency and fast as the table grows to millions of rows {Junior viết query trả đúng dòng; senior viết query vẫn đúng khi đồng thời và nhanh khi bảng lớn tới hàng triệu dòng}.

---

11.0 Why PostgreSQL {Vì sao PostgreSQL}

Postgres is the default senior choice for relational data: it’s open-source, standards-compliant, and packs features that read like a wishlist {Postgres là lựa chọn senior mặc định cho dữ liệu quan hệ: mã nguồn mở, tuân chuẩn, và gói đủ tính năng như một danh sách ước} — true ACID transactions, rich types (JSONB, arrays, ranges, uuid), powerful indexing (B-tree, GIN, GiST, BRIN), full-text search, window functions, and LISTEN/NOTIFY for pub/sub {transaction ACID thật, kiểu phong phú, đánh index mạnh, full-text search, window function, và LISTEN/NOTIFY cho pub/sub}.

docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:17

Senior note {Ghi chú senior}: reach for Postgres by default; choose something else only when you can name the specific property it lacks {mặc định chọn Postgres; chỉ chọn cái khác khi bạn nêu được đúng tính chất nó thiếu}.

---

11.1 Data modeling & normalization {Mô hình hóa dữ liệu & chuẩn hóa}

Good schema design is the cheapest performance you’ll ever buy {Thiết kế schema tốt là hiệu năng rẻ nhất bạn từng mua}. Normalize to 3NF by default — every non-key column depends on the key, the whole key, and nothing but the key {Chuẩn hóa về 3NF mặc định — mọi cột non-key phụ thuộc vào key, toàn bộ key, và chỉ key}.

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       citext UNIQUE NOT NULL,          -- case-insensitive text
  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 CASCADE,
  title       text NOT NULL,
  body        text NOT NULL,
  status      text NOT NULL DEFAULT 'draft'
              CHECK (status IN ('draft','published','archived')),
  published_at timestamptz,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Senior habits in that DDL {Thói quen senior trong DDL đó}: bigint identity keys (you’ll outgrow int), timestamptz not timestamp (always store UTC with zone), citext for emails, a CHECK constraint instead of trusting the app, and ON DELETE CASCADE to make referential intent explicit {khóa bigint, timestamptz không phải timestamp, citext cho email, CHECK thay vì tin app, và ON DELETE CASCADE để ý định tham chiếu rõ ràng}.

Denormalize deliberately and later — only when a measured read path demands it, never as a starting point {Phi chuẩn hóa có chủ ý và về sau — chỉ khi một đường đọc đã đo đòi hỏi, không bao giờ là điểm bắt đầu}.

---

11.2 Indexes — the right one per query {Index — đúng loại cho mỗi query}

An index is a trade: faster reads for slower writes and more disk {Index là một đánh đổi: đọc nhanh hơn đổi lấy ghi chậm hơn và tốn đĩa hơn}. The senior skill is matching index type to access pattern {Kỹ năng senior là khớp loại index với pattern truy cập}.

Index	Use it for {Dùng cho}
B-tree (default)	equality & range: =, <, >, BETWEEN, ORDER BY
GIN	JSONB, arrays, full-text (@>, ?, @@)
GiST	geometry, ranges, nearest-neighbour
BRIN	huge, naturally-ordered tables (time-series)
Hash	equality only, rarely worth it over B-tree

-- Composite index: column order matters — most selective / leading filter first
CREATE INDEX idx_posts_author_status ON posts (author_id, status);

-- Partial index: index only the rows you actually query
CREATE INDEX idx_posts_published ON posts (published_at DESC)
  WHERE status = 'published';

-- Covering index: satisfy a query from the index alone (index-only scan)
CREATE INDEX idx_posts_list ON posts (author_id) INCLUDE (title, created_at);

-- GIN for JSONB containment
CREATE INDEX idx_posts_meta ON posts USING gin (meta jsonb_path_ops);

Senior note {Ghi chú senior}: a composite index on (a, b) serves queries filtering on a or a,b — but not b alone. Order columns by how you filter {index ghép (a, b) phục vụ query lọc theo a hoặc a,b — nhưng không phục vụ b đứng một mình. Sắp cột theo cách bạn lọc}.

---

11.3 Read the plan: EXPLAIN ANALYZE {Đọc plan: EXPLAIN ANALYZE}

Never guess why a query is slow — ask the planner {Đừng đoán vì sao query chậm — hỏi planner}:

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

What seniors look for {Senior tìm gì}:

• Seq Scan on a big table in a filtered query → a missing index {Seq Scan trên bảng lớn trong query có lọc → thiếu index}.
• Index Scan / Index Only Scan → good; the index is being used {tốt; index đang được dùng}.
• Estimated vs actual rows far apart → stale statistics; run ANALYZE {ước lượng vs thực tế lệch xa → thống kê cũ; chạy ANALYZE}.
• A Sort step that spills to disk → consider an index matching the ORDER BY {bước Sort tràn ra đĩa → cân nhắc index khớp ORDER BY}.

---

11.4 Transactions & isolation levels {Transaction & mức cô lập}

A transaction is all-or-nothing {Transaction là tất-cả-hoặc-không}. But which anomalies it prevents depends on the isolation level — the topic most backend devs never truly learn {Nhưng nó ngăn những bất thường nào phụ thuộc vào mức cô lập — chủ đề đa số dev backend không thật sự học}.

Level	Prevents {Ngăn}	Allows {Cho phép}
READ COMMITTED (default)	dirty reads	non-repeatable & phantom reads
REPEATABLE READ	+ non-repeatable reads	(in PG) blocks phantoms too
SERIALIZABLE	everything — as if serial	may abort with a serialization error

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function transfer(from: number, to: number, cents: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [cents, from]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [cents, to]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err; // on a 40001 serialization_failure, the caller should retry
  } finally {
    client.release(); // ALWAYS release back to the pool
  }
}

Senior note {Ghi chú senior}: under SERIALIZABLE, Postgres may abort a transaction with SQLSTATE 40001. That’s not a bug — it’s the engine telling you to retry. Wrap such transactions in a small retry loop {dưới SERIALIZABLE, Postgres có thể hủy transaction với mã 40001. Đó không phải lỗi — là engine bảo bạn thử lại. Bọc transaction kiểu này trong vòng retry nhỏ}.

The lost-update bug {Lỗi mất cập nhật}

Read-modify-write across two requests silently loses data {Đọc-sửa-ghi qua hai request âm thầm mất dữ liệu}. Fix it by locking the row or computing in SQL {Sửa bằng cách khóa dòng hoặc tính ngay trong SQL}:

-- Pessimistic lock: hold the row until COMMIT
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;

-- Or avoid the round trip entirely — atomic in one statement
UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1;

---

11.5 JSONB — relational meets document {JSONB — quan hệ gặp tài liệu}

JSONB lets you store schemaless data inside a relational table — great for flexible metadata, settings, or event payloads {JSONB cho bạn lưu dữ liệu không schema bên trong bảng quan hệ — tốt cho metadata linh hoạt, cài đặt, hay payload sự kiện}.

ALTER TABLE posts ADD COLUMN meta jsonb NOT NULL DEFAULT '{}';

-- containment query, accelerated by the GIN index from 11.2
SELECT id FROM posts WHERE meta @> '{"featured": true}';

-- extract a value; ->> returns text, -> returns jsonb
SELECT meta->>'source' AS source FROM posts WHERE id = $1;

Don’t use JSONB as an excuse to avoid modeling {Đừng dùng JSONB làm cớ né mô hình hóa}. If you query or join on a field constantly, promote it to a real, indexed column {Nếu bạn liên tục query hay join trên một field, nâng nó thành cột thật, có index}.

---

11.6 Connection pooling with pg {Pool kết nối với pg}

Opening a Postgres connection is expensive (a process per connection on the server) {Mở kết nối Postgres tốn kém (server tạo một process mỗi kết nối)}. Always use a pool, and parameterize every query to kill SQL injection {Luôn dùng pool, và tham số hóa mọi query để diệt SQL injection}:

import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // tune to (cores × 2..4), not "bigger is better"
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

// Parameterized — NEVER string-concatenate user input into SQL
const { rows } = await pool.query(
  'SELECT id, title FROM posts WHERE author_id = $1 LIMIT $2',
  [authorId, 20],
);

Senior note {Ghi chú senior}: in serverless or high-fan-out deployments, put PgBouncer (transaction pooling) in front of Postgres — thousands of app connections collapse onto a few real ones {trong serverless hay fan-out cao, đặt PgBouncer (transaction pooling) trước Postgres — hàng nghìn kết nối app gộp về vài kết nối thật}.

---

11.7 MVCC, VACUUM & bloat {MVCC, VACUUM & phình}

The single concept that explains Postgres’s concurrency behavior is MVCC (Multi-Version Concurrency Control): an UPDATE doesn’t overwrite a row — it writes a new version and marks the old one dead, so readers never block writers and writers never block readers {Khái niệm giải thích hành vi đồng thời của Postgres là MVCC: UPDATE không ghi đè — nó ghi phiên bản mới và đánh dấu cái cũ chết, nên reader không chặn writer và ngược lại}.

The consequence a senior plans for {Hệ quả senior tính đến}: dead row versions accumulate as bloat, and VACUUM (usually autovacuum) reclaims them {phiên bản chết dồn lại thành bloat, và VACUUM (thường autovacuum) thu hồi}.

• High-churn tables need autovacuum tuned more aggressively, or they bloat and slow down {bảng thay đổi nhiều cần autovacuum chỉnh mạnh hơn}.
• A long-running transaction holds an old snapshot and blocks vacuum for the whole DB — kill runaway transactions {transaction chạy lâu giữ snapshot cũ và chặn vacuum cả DB}.
• VACUUM reclaims space for reuse; VACUUM FULL rewrites the table and returns disk to the OS but takes an exclusive lock {VACUUM thu hồi để tái dùng; VACUUM FULL viết lại bảng và trả đĩa cho OS nhưng khóa độc quyền}.

Monitor with the built-ins {Giám sát bằng công cụ sẵn có}: pg_stat_statements (slowest queries by total time), pg_stat_user_tables (dead tuples, last autovacuum), and pg_stat_activity (what’s running now, and for how long) {…}.

---

11.8 Advanced SQL a senior actually uses {SQL nâng cao senior thực dùng}

Pushing logic into SQL often beats fetching rows and looping in Node {Đẩy logic vào SQL thường tốt hơn lấy dòng rồi lặp trong Node}.

-- Upsert: insert or update atomically (no read-modify-write race)
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING id;                                   -- RETURNING gives you the row back

-- Window function: rank posts per author WITHOUT collapsing rows
SELECT id, author_id, title,
       row_number() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rn
FROM posts;

-- CTE for readable multi-step queries (and recursion for trees/graphs)
WITH recent AS (
  SELECT * FROM posts WHERE created_at > now() - interval '7 days'
)
SELECT author_id, count(*) FROM recent GROUP BY author_id;

Two more senior tools {Hai công cụ senior nữa}:

• Full-text search — to_tsvector/to_tsquery with a GIN index gives real search without a separate engine for many apps {full-text search — cho tìm kiếm thật mà không cần engine riêng}.
• LISTEN/NOTIFY — lightweight pub/sub inside Postgres; a write can notify Node listeners in real time (e.g. cache invalidation) {LISTEN/NOTIFY — pub/sub nhẹ trong Postgres; một write có thể báo cho listener Node real-time}.
• Advisory locks (pg_advisory_lock) — app-level locks for things like “only one worker runs this cron” {advisory lock — khóa cấp app cho việc như “chỉ một worker chạy cron này”}.

For analytics that are expensive to compute live, a materialized view caches the result and you REFRESH it on a schedule {Với phân tích đắt khi tính trực tiếp, materialized view cache kết quả và bạn REFRESH theo lịch}. For tables in the hundreds of millions of rows, reach for partitioning {Với bảng hàng trăm triệu dòng, dùng partitioning}.

---

11.9 The master’s warnings {Lời cảnh báo của sư phụ}

• timestamptz, always. Storing local time without a zone is a bug waiting for a DST change {timestamptz, luôn luôn. Lưu giờ local không có zone là lỗi chờ một lần đổi DST}.
• SELECT * in production code is a smell — name columns so an index-only scan is possible and your app survives schema changes {SELECT * trong code production là mùi xấu — nêu tên cột để có index-only scan và app sống sót qua đổi schema}.
• Index foreign keys. Postgres indexes the PK automatically but not the FK column — un-indexed FKs make ON DELETE and joins crawl {Index khóa ngoại. Postgres tự index PK nhưng không index cột FK — FK không index làm ON DELETE và join bò}.
• Migrations, never manual edits. Every schema change goes through a versioned, reversible migration {Migration, không sửa tay. Mọi đổi schema đi qua migration có version, đảo ngược được}.

---

11.10 Practice {Thực hành}

1. Model a blog (users, posts, tags, comments) in 3NF with proper keys, constraints, and FKs {Mô hình blog trong 3NF với khóa, constraint, FK đúng}.
2. Write a slow query, run EXPLAIN ANALYZE, add the right index, and confirm the Seq Scan becomes an Index Scan {Viết query chậm, chạy EXPLAIN ANALYZE, thêm index đúng, xác nhận Seq Scan thành Index Scan}.
3. Reproduce a lost update with two concurrent clients, then fix it with FOR UPDATE and again with a single atomic UPDATE {Tái hiện lost update với hai client đồng thời, rồi sửa bằng FOR UPDATE và lần nữa bằng một UPDATE nguyên tử}.

---

What’s next {Phần tiếp theo}

You can now design and run a relational database that stays correct and fast {Giờ bạn thiết kế và vận hành được database quan hệ vẫn đúng và nhanh}. Writing raw SQL by hand is essential to understand — but in a large TypeScript codebase you want type safety and migrations without losing that control {Viết SQL tay là thiết yếu để hiểu — nhưng trong codebase TypeScript lớn bạn muốn type safety và migration mà không mất kiểm soát đó}. In Phase 12 we layer Prisma on top of this Postgres knowledge — a type-safe ORM whose schema, migrations, and query API map directly onto everything you just learned {Ở Phase 12 ta phủ Prisma lên kiến thức Postgres này — một ORM type-safe mà schema, migration, và API query ánh xạ thẳng vào mọi thứ bạn vừa học}.