PostgreSQL nâng cao: JSONB, mảng, tìm kiếm văn bản, ROLLUP, partition
Khai thác sức mạnh riêng của PostgreSQL: JSONB và GIN index, mảng, full-text search & trigram, cột sinh tự động, GROUPING SETS/ROLLUP/CUBE, kiểu phạm vi, partitioning và COPY.
PostgreSQL không chỉ là "SQL chuẩn" — nó có một bộ công cụ mà nhiều đội phải thêm cả MongoDB, Elasticsearch hay Redis mới có. Biết dùng JSONB cho dữ liệu bán cấu trúc, full-text/trigram cho tìm kiếm, ROLLUP cho báo cáo tổng-chi tiết, và partitioning cho bảng khổng lồ giúp bạn giữ kiến trúc đơn giản: ít hệ thống hơn, ít lỗi đồng bộ hơn.
🎯 Mục tiêu học tập
- Truy vấn, cập nhật, tổng hợp và đánh index JSONB đúng cách; biết khi nào dùng JSONB và khi nào dùng cột thường
- Dùng kiểu mảng, ANY/unnest/array_agg và toán tử chứa/giao
- Dựng tìm kiếm văn bản (to_tsvector, GIN) và tìm gần đúng bằng pg_trgm, unaccent cho tiếng Việt
- Viết báo cáo nhiều cấp với GROUPING SETS/ROLLUP/CUBE, dùng cột sinh tự động, kiểu range, partitioning và COPY
JSONB — dữ liệu bán cấu trúc ngay trong bảng quan hệ
Cột products.attrs là jsonb: mỗi nhóm sản phẩm có thuộc tính khác nhau (laptop có RAM/SSD, áo có size) — đúng loại dữ liệu "thưa và đa dạng" mà JSONB sinh ra để phục vụ.
Dùng jsonb (nhị phân, có index, so sánh được), không dùng json (lưu nguyên văn bản).
| Toán tử/hàm | Ý nghĩa |
|---|---|
attrs -> 'k' | Lấy giá trị dạng jsonb |
attrs ->> 'k' | Lấy giá trị dạng text (ép kiểu sau đó: (attrs->>'ram_gb')::int) |
attrs #>> '{a,b}' | Lấy theo đường dẫn lồng nhau |
attrs @> '{"brand":"Zen"}' | Chứa (có dùng được GIN index) |
attrs ? 'wireless' | Có khoá này không |
jsonb_set / || / - | Sửa, gộp, xoá khoá |
SELECT name,
attrs ->> 'brand' AS hang,
(attrs ->> 'ram_gb')::int AS ram_gb,
attrs -> 'colors' AS mau_sac
FROM products
WHERE attrs @> '{"brand": "Aurora"}' OR attrs ? 'ssd_gb'
ORDER BY id; name | hang | ram_gb | mau_sac
------------------------+---------+--------+----------------
Điện thoại Aurora X1 | Aurora | 8 | ["đen", "bạc"]
Điện thoại Aurora Lite | Aurora | 4 | ["đen"]
Laptop Zenbook 14 | Zen | 16 | NULL
Laptop Zenbook 16 Pro | Zen | 32 | NULL
Laptop Student 15 | Student | 8 | NULL
(5 rows)-- Bung mảng bên trong JSON thành dòng; đếm sản phẩm theo màu
SELECT mau, count(*) AS so_sp
FROM products, jsonb_array_elements_text(attrs -> 'colors') AS mau
GROUP BY mau ORDER BY so_sp DESC, mau; mau | so_sp
-------+-------
đen | 3
bạc | 1
trắng | 1
xanh | 1
(4 rows)-- Sinh JSON để trả thẳng cho API: đơn #2 kèm các dòng hàng
SELECT jsonb_pretty(jsonb_build_object(
'order_id', o.id, 'trang_thai', o.status,
'items', (SELECT jsonb_agg(jsonb_build_object('sku', p.sku, 'qty', oi.qty, 'gia', oi.unit_price) ORDER BY p.sku)
FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.order_id = o.id)))
FROM orders o WHERE o.id = 2; jsonb_pretty
---------------------------------
{ +
"items": [ +
{ +
"gia": 350000.00, +
"qty": 1, +
"sku": "AC-004" +
}, +
{ +
"gia": 890000.00, +
"qty": 2, +
"sku": "HM-002" +
}, +
{ +
"gia": 22500000.00,+
"qty": 3, +
"sku": "LT-001" +
} +
], +
"order_id": 2, +
"trang_thai": "delivered" +
}
(1 row)-- Cập nhật một khoá trong JSONB mà không ghi đè cả đối tượng
UPDATE products SET attrs = jsonb_set(attrs, '{ram_gb}', '16') WHERE sku = 'PH-001'
RETURNING sku, attrs; sku | attrs
--------+-------------------------------------------------------------
PH-001 | {"brand": "Aurora", "colors": ["đen", "bạc"], "ram_gb": 16}
(1 row)
UPDATE 1-- GIN index cho toán tử @> / ? trên JSONB (jsonb_path_ops nhỏ hơn, chỉ hỗ trợ @>)
CREATE INDEX idx_products_attrs ON products USING gin (attrs jsonb_path_ops);
SET LOCAL enable_seqscan = off; -- chỉ để minh hoạ trên bảng nhỏ
EXPLAIN (COSTS OFF) SELECT name FROM products WHERE attrs @> '{"brand": "Zen"}';CREATE INDEX
SET
QUERY PLAN
----------------------------------------------------------
Bitmap Heap Scan on products
Recheck Cond: (attrs @> '{"brand": "Zen"}'::jsonb)
-> Bitmap Index Scan on idx_products_attrs
Index Cond: (attrs @> '{"brand": "Zen"}'::jsonb)
(4 rows)- Cột thường: thuộc tính ổn định, dùng để lọc/JOIN/ràng buộc/thống kê thường xuyên (giá, trạng thái, khoá ngoại).
- JSONB: thuộc tính thưa/đa dạng theo loại, cấu hình, payload từ bên ngoài (webhook), cần lưu nguyên vẹn.
- Nếu thấy mình viết
(attrs->>'x')::intở khắp nơi và lọc theo nó liên tục → "tốt nghiệp" thuộc tính đó thành cột thật (hoặc generated column trích từ JSONB rồi index).
Mất ràng buộc (không FK, không NOT NULL, không CHECK theo từng khoá), khó thống kê, mỗi lần sửa một khoá là viết lại cả đối tượng, và schema "ngầm" ở code ứng dụng dễ lệch. JSONB bổ trợ cho mô hình quan hệ, không thay thế nó.
Mảng (array) và toán tử tập hợp
SELECT ARRAY[1, 2, 3] || 4 AS them_phan_tu,
(ARRAY['a','b','c'])[2] AS phan_tu_thu_2,
ARRAY[1,2,3] @> ARRAY[2,3] AS chua,
ARRAY[1,2,3] && ARRAY[3,9] AS giao_nhau,
cardinality(ARRAY[10,20,30]) AS so_phan_tu,
array_to_string(ARRAY['x','y','z'], '-') AS noi_chuoi; them_phan_tu | phan_tu_thu_2 | chua | giao_nhau | so_phan_tu | noi_chuoi
--------------+---------------+------+-----------+------------+-----------
{1,2,3,4} | b | t | t | 3 | x-y-z
(1 row)-- Truyền danh sách id từ ứng dụng bằng MỘT tham số mảng (thay vì nối chuỗi "IN (1,2,3,...)")
SELECT id, full_name FROM customers WHERE id = ANY (ARRAY[3, 7, 11]) ORDER BY id; id | full_name
----+----------------
3 | Phạm Đức Phúc
7 | Bùi Thị Bình
11 | Phạm Quốc Dũng
(3 rows)-- Gom thành mảng và bung ngược lại
SELECT o.customer_id, array_agg(o.id ORDER BY o.id) AS cac_don
FROM orders o WHERE o.customer_id <= 3 GROUP BY o.customer_id ORDER BY 1; customer_id | cac_don
-------------+--------------------------------
1 | {54,108,162,216,270,324,378}
2 | {31,85,139,193,247,301,355}
3 | {8,62,116,170,224,278,332,386}
(3 rows)Danh sách nhỏ, thuộc tính của một bản ghi, không cần ràng buộc/khoá ngoại cho từng phần tử (thẻ tag, số điện thoại phụ). Cần JOIN/đếm/ràng buộc theo từng phần tử → dùng bảng con.
Ứng dụng quan trọng nhất của mảng: truyền tham số danh sách (= ANY($1)) và unnest($1::int[]) để chèn hàng loạt (module 14).
Tìm kiếm văn bản: full-text, trigram, unaccent
LIKE '%từ%' không dùng được B-tree. Ba công cụ thay thế:
- Full-text search (
tsvector/tsquery+ GIN): tìm theo từ, xếp hạng liên quan, nhiều từ khoá. Có sẵn cấu hình ngôn ngữ cho tiếng Anh (stemming); với tiếng Việt dùng cấu hình'simple'(không stemming) +unaccent. - pg_trgm: tìm gần đúng / chứa chuỗi con, chịu gõ sai, và làm
LIKE '%x%'dùng được index GIN/GiST. - Cần fuzzy nâng cao, gợi ý, đa ngôn ngữ phức tạp, quy mô khổng lồ → cân nhắc Elasticsearch/OpenSearch/Meilisearch.
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Full-text: tìm sản phẩm có cả hai từ "điện thoại" và "aurora", không phân biệt dấu/hoa thường
SELECT name
FROM products
WHERE to_tsvector('simple', unaccent(name)) @@ plainto_tsquery('simple', unaccent('dien thoai aurora'));CREATE EXTENSION
CREATE EXTENSION
name
------------------------
Điện thoại Aurora X1
Điện thoại Aurora Lite
(2 rows)CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Trigram: chịu gõ sai ('zenbok' ~ 'Zenbook') và xếp hạng theo độ giống
SELECT name, round(similarity(lower(name), 'zenbok')::numeric, 2) AS do_giong
FROM products
WHERE lower(name) % 'zenbok'
ORDER BY similarity(lower(name), 'zenbok') DESC;CREATE EXTENSION
CREATE EXTENSION
name | do_giong
-------------------+----------
Laptop Zenbook 14 | 0.32
(1 row)CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- GIN trigram làm LIKE '%...%' / ILIKE dùng được index
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT name FROM products WHERE name ILIKE '%zenbook%';CREATE EXTENSION
CREATE INDEX
SET
QUERY PLAN
---------------------------------------------------
Bitmap Heap Scan on products
Recheck Cond: (name ~~* '%zenbook%'::text)
-> Bitmap Index Scan on idx_products_name_trgm
Index Cond: (name ~~* '%zenbook%'::text)
(4 rows)Thay vì tính to_tsvector(...) mỗi lần truy vấn, lưu thành generated column và đánh GIN:
ALTER TABLE products ADD COLUMN tim_kiem tsvector GENERATED ALWAYS AS (to_tsvector('simple', unaccent(name))) STORED; rồi CREATE INDEX ON products USING gin (tim_kiem);.
(unaccent mặc định không "immutable" nên có thể cần bọc trong hàm riêng đánh dấu IMMUTABLE khi dùng cho generated column / expression index.)
Cột sinh tự động, GROUPING SETS / ROLLUP / CUBE, kiểu range
Generated column
ALTER TABLE products
ADD COLUMN bien_loi_nhuan_pct numeric GENERATED ALWAYS AS (round((price - cost) / NULLIF(price, 0) * 100, 1)) STORED;
SELECT sku, price, cost, bien_loi_nhuan_pct FROM products ORDER BY bien_loi_nhuan_pct DESC LIMIT 3;ALTER TABLE
sku | price | cost | bien_loi_nhuan_pct
--------+-----------+-----------+--------------------
AC-003 | 120000.00 | 40000.00 | 66.7
FA-001 | 199000.00 | 80000.00 | 59.8
FA-002 | 399000.00 | 170000.00 | 57.4
(3 rows)
ROLLUP — tổng chi tiết và tổng phụ trong một truy vấn
Báo cáo "doanh thu theo danh mục cha → danh mục con, có tổng phụ và tổng cộng" thường bị viết bằng nhiều UNION ALL. ROLLUP(a, b) sinh các nhóm (a,b), (a), (): chi tiết, tổng phụ theo a, tổng cộng.
CUBE(a,b) sinh mọi tổ hợp; GROUPING SETS cho bạn liệt kê tuỳ ý. Hàm GROUPING(cột) = 1 khi dòng đó là dòng tổng hợp ở cột ấy (để phân biệt với NULL thật).
SELECT CASE WHEN GROUPING(par.name) = 1 THEN '== TỔNG CỘNG ==' ELSE par.name END AS danh_muc_goc,
CASE WHEN GROUPING(cat.name) = 1 AND GROUPING(par.name) = 0 THEN ' (tổng phụ)' ELSE cat.name END AS danh_muc_con,
round(sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) / 1e6) AS doanh_thu_trieu
FROM order_items oi
JOIN orders o ON o.id = oi.order_id AND o.status <> 'cancelled'
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
JOIN categories par ON par.id = cat.parent_id
GROUP BY ROLLUP (par.name, cat.name)
ORDER BY par.name NULLS LAST, cat.name NULLS LAST; danh_muc_goc | danh_muc_con | doanh_thu_trieu
-----------------+--------------+-----------------
Điện tử | Điện thoại | 2792
Điện tử | Laptop | 8173
Điện tử | Phụ kiện | 196
Điện tử | (tổng phụ) | 11161
Gia dụng | Nhà bếp | 426
Gia dụng | (tổng phụ) | 426
Thời trang | Áo | 125
Thời trang | Giày | 165
Thời trang | (tổng phụ) | 290
== TỔNG CỘNG == | NULL | 11877
(10 rows)-- GROUPING SETS: chọn đúng những cách gom cần thiết — theo phương thức, theo tháng, và tổng
SELECT method, date_trunc('quarter', paid_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS quy, round(sum(amount) / 1e6) AS trieu
FROM payments
GROUP BY GROUPING SETS ((method), (date_trunc('quarter', paid_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date), ())
ORDER BY method NULLS LAST, quy NULLS LAST; method | quy | trieu
--------+------------+-------
card | NULL | 3614
cod | NULL | 3085
momo | NULL | 3937
NULL | 2024-01-01 | 1487
NULL | 2024-04-01 | 4707
NULL | 2024-07-01 | 3595
NULL | 2024-10-01 | 847
NULL | NULL | 10636
(8 rows)
Kiểu range — khoảng thời gian/số là công dân hạng nhất
SELECT tstzrange('2024-06-01', '2024-06-10') && tstzrange('2024-06-09', '2024-06-20') AS chong_nhau,
daterange('2024-06-01', '2024-06-10') @> date '2024-06-05' AS chua_ngay,
upper(daterange('2024-06-01', '2024-06-10')) - lower(daterange('2024-06-01', '2024-06-10')) AS so_ngay,
daterange('2024-06-01', '2024-06-10', '[]') AS gom_ca_hai_dau; chong_nhau | chua_ngay | so_ngay | gom_ca_hai_dau
------------+-----------+---------+-------------------------
t | t | 9 | [2024-06-01,2024-06-11)
(1 row)Trong kết quả ROLLUP, NULL ở cột nhóm có thể là "tổng hợp" hoặc giá trị NULL thật trong dữ liệu. Dùng GROUPING() để phân biệt (như trên) thay vì kiểm tra IS NULL.
Partitioning (bảng phân vùng) và COPY
Bảng cực lớn theo thời gian (log, sự kiện, đơn hàng nhiều năm) nên chia thành các partition theo khoảng thời gian: truy vấn có điều kiện thời gian chỉ chạm partition liên quan (partition pruning), xoá dữ liệu cũ bằng DROP PARTITION tức thì (thay vì DELETE hàng trăm triệu dòng), bảo trì (VACUUM/index) từng phần.
CREATE TABLE nhat_ky (
id bigint GENERATED ALWAYS AS IDENTITY,
xay_ra_luc timestamptz NOT NULL,
noi_dung text,
PRIMARY KEY (id, xay_ra_luc) -- khoá chính phải chứa cột phân vùng
) PARTITION BY RANGE (xay_ra_luc);
CREATE TABLE nhat_ky_2024_01 PARTITION OF nhat_ky FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE nhat_ky_2024_02 PARTITION OF nhat_ky FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
INSERT INTO nhat_ky (xay_ra_luc, noi_dung)
SELECT timestamptz '2024-01-01' + (i % 58) * interval '1 day', 'dong ' || i FROM generate_series(1, 5000) i;
ANALYZE nhat_ky;
-- Chỉ quét partition tháng 2 (partition pruning)
EXPLAIN (COSTS OFF) SELECT count(*) FROM nhat_ky WHERE xay_ra_luc >= '2024-02-10' AND xay_ra_luc < '2024-02-15';CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 5000
ANALYZE
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate
-> Seq Scan on nhat_ky_2024_02 nhat_ky
Filter: ((xay_ra_luc >= '2024-02-10 00:00:00+07'::timestamp with time zone) AND (xay_ra_luc < '2024-02-15 00:00:00+07'::timestamp with time zone))
(3 rows)Chỉ có lợi khi truy vấn thường lọc theo khoá phân vùng. Quá nhiều partition (hàng nghìn) làm planner chậm. Cần tạo partition mới trước khi dữ liệu mới đến (tự động bằng pg_partman hoặc job định kỳ), và ràng buộc unique phải gồm khoá phân vùng. Chi tiết vận hành nằm ở khoá Database Engineering.
COPY — nạp/xuất hàng loạt nhanh nhất
COPY nhanh hơn INSERT từng dòng hàng chục lần. COPY ... FROM nạp file/stdin; \copy trong psql đọc file ở máy bạn (không cần quyền trên server).
COPY (SELECT id, full_name, city FROM customers ORDER BY id LIMIT 3) TO STDOUT WITH (FORMAT csv, HEADER);id,full_name,city
1,Trần Quốc Dũng,TP.HCM
2,Lê Ngọc Lan,Đà Nẵng
3,Phạm Đức Phúc,Cần Thơ-- Nạp dữ liệu từ nội dung CSV nội tuyến (trên máy bạn thường là: \copy bang FROM 'file.csv' CSV HEADER)
CREATE TEMP TABLE nhap (ma text, so int);
COPY nhap FROM STDIN WITH (FORMAT csv);
A1,10
B2,20
\.
SELECT * FROM nhap;CREATE TABLE
COPY 2
ma | so
----+----
A1 | 10
B2 | 20
(2 rows)- Nạp vào bảng staging (không ràng buộc, cột text) bằng
COPY. - Làm sạch/kiểm tra bằng SQL (kiểu, trùng, thiếu).
INSERT ... SELECT ... ON CONFLICTsang bảng đích; báo cáo dòng bị loại.- Với nạp rất lớn: tạo index sau khi nạp,
ANALYZExong mới cho truy vấn dùng.
🏋️ Bài tập thực hành
Liệt kê name và price của sản phẩm có RAM từ 16GB trở lên (thuộc tính ram_gb trong attrs).
Xem lời giải & giải thích
SELECT name, price FROM products
WHERE (attrs ->> 'ram_gb')::int >= 16
ORDER BY price; name | price
-----------------------+-------------
Laptop Zenbook 14 | 22500000.00
Laptop Zenbook 16 Pro | 34900000.00
(2 rows)Sản phẩm không có khoá ram_gb cho NULL và tự bị loại. Ép kiểu sau ->> vì nó trả về text. Nếu lọc kiểu này thường xuyên: tạo expression index trên ((attrs->>'ram_gb')::int).
Tạo báo cáo tổng tiền thanh toán theo (phương thức thanh toán, thành phố khách) có tổng phụ theo phương thức và tổng cộng (dùng ROLLUP và GROUPING để gắn nhãn).
Xem lời giải & giải thích
SELECT CASE WHEN GROUPING(p.method) = 1 THEN 'TỔNG' ELSE p.method END AS phuong_thuc,
CASE WHEN GROUPING(c.city) = 1 THEN '(tất cả)' ELSE COALESCE(c.city, 'Chưa rõ') END AS thanh_pho,
round(sum(p.amount) / 1e6) AS trieu
FROM payments p JOIN orders o ON o.id = p.order_id JOIN customers c ON c.id = o.customer_id
GROUP BY ROLLUP (p.method, c.city)
ORDER BY p.method NULLS LAST, c.city NULLS LAST; phuong_thuc | thanh_pho | trieu
-------------+-----------+-------
card | Cần Thơ | 517
card | Đà Nẵng | 478
card | Hà Nội | 688
card | Hải Phòng | 676
card | TP.HCM | 815
card | (tất cả) | 3614
card | Chưa rõ | 439
cod | Cần Thơ | 803
cod | Đà Nẵng | 454
cod | Hà Nội | 586
cod | Hải Phòng | 457
cod | TP.HCM | 741
cod | (tất cả) | 3085
cod | Chưa rõ | 44
momo | Cần Thơ | 1088
momo | Đà Nẵng | 758
momo | Hà Nội | 921
momo | Hải Phòng | 480
momo | TP.HCM | 333
momo | (tất cả) | 3937
momo | Chưa rõ | 357
TỔNG | (tất cả) | 10636
... (cắt bớt, chạy trên máy bạn để xem đủ)Một khách có city NULL thật vẫn được gom thành nhóm riêng; hàm GROUPING phân biệt nó với dòng tổng hợp.
Tìm khách có tên chứa 'thu ha' (không phân biệt hoa/thường và dấu) bằng unaccent + ILIKE.
Xem lời giải & giải thích
CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT id, full_name FROM customers
WHERE unaccent(full_name) ILIKE '%thu ha%'
ORDER BY id LIMIT 5;CREATE EXTENSION
id | full_name
----+---------------
8 | Nguyễn Thu Hà
18 | Lê Thu Hà
28 | Hoàng Thu Hà
38 | Đặng Thu Hà
48 | Nguyễn Thu Hà
(5 rows)Với bảng lớn, tạo index GIN (unaccent(full_name) gin_trgm_ops) — cần bọc unaccent trong hàm IMMUTABLE riêng để dùng cho expression index.
📚 Tài nguyên học tập
-
PostgreSQL — JSON Types & FunctionsJSONB: toán tử, jsonpath, indexDocs
-
PostgreSQL — Full Text Searchtsvector/tsquery, cấu hình, xếp hạng, indexDocs
-
PostgreSQL — Table PartitioningPhân vùng khai báo, pruning, bảo trìDocs
-
pg_trgmTìm gần đúng, similarity, index trigramDocs
✅ Tự đánh giá hoàn thành
- Truy vấn, sửa và đánh GIN index cho JSONB; biết ranh giới giữa JSONB và cột thường
- Dùng = ANY(mảng), array_agg, unnest và toán tử @>/&& cho mảng
- Dựng được tìm kiếm full-text/trigram không dấu, có index
- Viết báo cáo nhiều cấp với ROLLUP/GROUPING SETS và hiểu cơ bản về partition pruning