PostgreSQL Bài 4: Khởi tạo Database, Schema và cấu trúc Catalog của PostgreSQL

Ở Bài 3, bạn đã dựng thành công môi trường PostgreSQL bằng Docker. Khi bắt tay vào thiết kế ứng dụng thực tế, câu hỏi kiến trúc đầu tiên luôn là: Nên gom tất cả bảng vào một Database, chia theo Schema, hay tạo nhiều Database riêng biệt?

Bài viết này sẽ làm rõ hệ phân cấp logic trong PostgreSQL, cơ chế hoạt động của search_path, và cách khai thác kho metadata hệ thống (System Catalogs) để quản lý cơ sở dữ liệu như một kỹ sư chuyên nghiệp.

1. Hệ phân cấp logic: Cluster vs Database vs Schema

Nhiều lập trình viên chuyển từ MySQL sang thường nhầm lẫn giữa Database và Schema vì trong MySQL hai khái niệm này gần như đồng nghĩa (CREATE SCHEMA tương đương CREATE DATABASE). Trong PostgreSQL, chúng là hai tầng hoàn toàn khác biệt.

Cluster (1 cụm tiến trình PostgreSQL & 1 cổng kết nối)
 │
 ├── Database A (Cô lập hoàn toàn, không thể JOIN trực tiếp sang Database B)
 │    ├── Schema: public (mặc định)
 │    ├── Schema: sales
 │    │    └── Tables, Views, Sequences...
 │    └── Schema: inventory
 │
 └── Database B (Tách biệt logic, user permission riêng)
      └── Schema: public

1.1. PostgreSQL Cluster

• Là một tập hợp các cơ sở dữ liệu được quản lý bởi một instance server duy nhất (chia sẻ chung tiến trình mẹ, bộ nhớ Shared Buffers và thư mục vật lý PGDATA).

1.2. Database (Cơ sở dữ liệu)

• Đóng vai trò là ranh giới cô lập logic cao nhất trong một cluster.

• Ranh giới cứng: Bạn không thể thực hiện truy vấn JOIN chéo giữa hai database khác nhau bằng cú pháp SQL tiêu chuẩn (muốn làm vậy phải dùng extension postgres_fdw).

• Mỗi database có bảng phân quyền và các thiết lập default collate/encoding độc lập.

1.3. Schema (Không gian tên - Namespace)

• Nằm bên trong Database, chứa các đối tượng vật lý: bảng (table), hàm (function), view, sequence.

• Ranh giới mềm: Các bảng nằm ở hai Schema khác nhau trong cùng một Database có thể JOIN với nhau bình thường thông qua cú pháp định danh đầy đủ: schema_name.table_name.

• Tránh trùng lặp tên bảng khi nhiều module hệ thống phát triển song song.

2. Thực hành tạo Database và Schema

Mở terminal psql để thao tác:

SQL

-- 1. Tạo Database mới với bảng mã UTF-8
CREATE DATABASE ecommerce_db 
    WITH ENCODING = 'UTF8' 
    LC_COLLATE = 'C.UTF-8' 
    LC_CTYPE = 'C.UTF-8';

-- 2. Chuyển kết nối sang database vừa tạo
\c ecommerce_db

-- 3. Tạo các Schema phân tách module nghiệp vụ
CREATE SCHEMA IF NOT EXISTS identity;
CREATE SCHEMA IF NOT EXISTS orders;
CREATE SCHEMA IF NOT EXISTS warehouse;

Tạo bảng bên trong Schema cụ thể:

SQL

-- Bảng users thuộc schema identity
CREATE TABLE identity.users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Bảng orders thuộc schema orders có khóa ngoại tham chiếu sang identity.users
CREATE TABLE orders.orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES identity.users(id),
    total_amount NUMERIC(15, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

3. Cơ chế search_path: Cách PostgreSQL tìm bảng

Khi bạn gõ lệnh SELECT * FROM users; mà không chỉ định rõ schema (ví dụ identity.users), PostgreSQL dựa vào biến môi trường search_path để tìm kiếm.

3.1. Kiểm tra giá trị hiện tại

SQL

SHOW search_path;

Kết quả mặc định thường là:

Plaintext

   search_path   
-----------------
 "$user", public

• "$user": Nếu user hiện tại tên là dev_admin, engine sẽ kiểm tra xem có schema nào tên dev_admin không. Nếu có, ưu tiên tìm trước.

• public: Schema dùng chung mặc định có sẵn trong mọi database.

3.2. Cấu hình lại search_path

Bạn có thể thay đổi thứ tự ưu tiên tìm kiếm theo từng session hoặc gán mặc định cho một user:

SQL

-- Thay đổi cho phiên làm việc hiện tại
SET search_path TO orders, identity, public;

-- Bây giờ có thể query trực tiếp mà không cần tiền tố schema:
SELECT * FROM orders; -- Trỏ thẳng vào orders.orders
SELECT * FROM users;  -- Trỏ thẳng vào identity.users

Gợi ý kiến trúc (Multi-tenancy): Mô hình Schema-per-tenant là kỹ thuật phổ biến trong ứng dụng SaaS B2B. Mỗi công ty/khách hàng được cấp một Schema riêng biệt (ví dụ: tenant_acme, tenant_globex) dùng chung cấu trúc bảng. Khi ứng dụng nhận request từ tenant nào, backend chỉ cần thực thi:

SQL

SET search_path TO tenant_acme, public;

Mọi câu SQL sau đó tự động đọc/ghi dữ liệu của đúng tenant đó mà không sợ rò rỉ dữ liệu hay phải thêm điều kiện WHERE tenant_id = ... ở từng câu lệnh.

4. Giải mã System Catalogs & Information Schema

PostgreSQL lưu trữ toàn bộ định nghĩa metadata hệ thống (danh sách bảng, cột, khóa, index, kiểu dữ liệu, trigger) ngay trong chính các bảng hệ thống. Có hai cách chính để truy vấn thông tin này.

4.1. pg_catalog (PostgreSQL Native Engine)

Schema pg_catalog chứa các bảng vật lý và view nội bộ do chính PostgreSQL engine sử dụng. Đây là nơi chứa thông tin chi tiết và sát phần cứng nhất.

Các bảng quan trọng nhất trong pg_catalog:

• pg_class: Quản lý thông tin về tables, indexes, sequences, views, composite types (mỗi đối tượng là một hàng).

• pg_namespace: Danh sách tất cả các schema.

• pg_attribute: Thông tin chi tiết về từng cột (column) trong từng bảng.

• pg_database: Danh sách tất cả database trong cluster.

Ví dụ thực tế: Truy vấn kích thước vật lý của từng bảng trên ổ đĩa

SQL

SELECT 
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS external_and_index_size
FROM pg_catalog.pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

4.2. information_schema (Chuẩn ANSI SQL)

Là một tập hợp các View được xây dựng bọc lấy pg_catalog nhằm đảm bảo tuân thủ tiêu chuẩn ANSI SQL.

• Ưu điểm: Viết code tương thích chéo tốt (truy vấn chạy được cả trên PostgreSQL, MySQL, SQL Server).

• Nhược điểm: Chậm hơn pg_catalog khi dữ liệu bảng hệ thống lớn và không phản ánh các tính năng độc quyền của PostgreSQL (như cấu trúc TOAST, Table OID, Index Type chuyên biệt).

Ví dụ: Lấy danh sách tất cả các cột và kiểu dữ liệu của một bảng

SQL

SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default
FROM information_schema.columns
WHERE table_schema = 'identity' 
  AND table_name = 'users';

5. Bảng so sánh chiến lược phân vùng dữ liệu

6. Tóm tắt & Bài tiếp theo

• Database cung cấp sự cô lập dữ liệu tuyệt đối; Schema cung cấp không gian tên linh hoạt cho phép tổ chức module và chia sẻ dữ liệu khi cần.

• Biến search_path quyết định thứ tự ưu tiên phân giải tên bảng trong truy vấn.

• pg_catalog cung cấp metadata hiệu năng cao đặc thù của Postgres, trong khi information_schema đem lại tính tương thích chuẩn SQL quốc tế.

Bài 5 xem tiếp: Phân quyền và Bảo mật: Role, User, Grant, Revoke và xác thực qua file pg_hba.conf — chúng ta sẽ tìm hiểu khái niệm thống nhất về Role trong PostgreSQL, cách cấp quyền chi tiết từ cấp Database, Schema tới từng dòng (Row-Level Security), và kỹ thuật cấu hình tường lửa truy cập qua pg_hba.conf.