1. WITH queries (CTE - Common Table Expressions)

CTE là một công cụ mạnh mẽ giúp đơn giản hóa các truy vấn phức tạp bằng cách tạo ra các bảng tạm thời trong một lần thực thi. Nó đặc biệt hữu ích cho các truy vấn đệ quy.

Ví dụ cơ bản với WITH

postgres=# with t as (select generate_series(1,5)) select * from t;
 generate_series 
-----------------
               1
               2
               3
               4
               5
(5 rows)

CTE đệ quy (Recursive CTE)

Chúng ta tạo bảng funds lưu trữ thông tin quỹ và mối quan hệ cha-con giữa chúng.

CREATE TABLE funds (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    parent_id INTEGER REFERENCES funds(id)
);

INSERT INTO funds (name, parent_id) VALUES
('Quỹ A', NULL),
('Quỹ B', 1),
('Quỹ C', 1);

UPDATE funds SET parent_id = 3 WHERE id = 2;

postgres=# select * from funds;
 id |  name   | parent_id 
----+---------+-----------
  1 | Quỹ A   |          
  3 | Quỹ C   |         1
  2 | Quỹ B   |         3

Yêu cầu: Truy xuất toàn bộ hệ thống phân cấp quỹ, bao gồm cả quỹ trực tiếp và gián tiếp.

WITH RECURSIVE fund_tree AS (
    SELECT id, name, parent_id
    FROM funds
    WHERE id = 1

    UNION ALL

    SELECT f.id, f.name, f.parent_id
    FROM funds f
    INNER JOIN fund_tree ft ON f.parent_id = ft.id
)
SELECT * FROM fund_tree;

Kết quả:

 id |  name   | parent_id 
----+---------+-----------
  1 | Quỹ A   |          
  3 | Quỹ C   |         1
  2 | Quỹ B   |         3
(3 rows)

Giải thích:

• UNION ALL: Kết hợp nhiều tập kết quả, không loại bỏ trùng lặp. Trong đệ quy, nó nối kết quả từ phần đệ quy với kết quả hiện tại. UNION (không ALL) sẽ loại bỏ trùng lặp, không phù hợp cho đệ quy cần giữ cấu trúc cây.
• INNER JOIN: Chỉ trả về các hàng khớp nhau giữa hai bảng. Trong ngữ cảnh này, nó nối bảng funds với kết quả đệ quy để tìm ra các quỹ con.

Cách hoạt động:

• Bước khởi tạo (anchor): Chọn quỹ gốc (id=1).
• Bước đệ quy: Với mỗi quỹ trong kết quả hiện tại, tìm các quỹ con bằng cách nối bảng funds (với vai trò bảng con) vào kết quả đệ quy (với vai trò bảng cha) thông qua cột parent_id.
• Kết hợp: UNION ALL thêm các quỹ con vừa tìm được vào tập kết quả chung.
• Lặp lại: Quá trình đệ quy tiếp diễn cho đến khi không tìm thấy thêm quỹ con nào nữa.

2. Chèn dữ liệu hàng loạt với INSERT

PostgreSQL hỗ trợ chèn nhiều dòng trong một câu lệnh INSERT:

INSERT INTO funds (name, parent_id) VALUES ('Quỹ D', NULL), ('Quỹ E', 2), ('Quỹ F', 3);

Ví dụ minh họa:

postgres=# select * from funds;
 id |  name   | parent_id 
----+---------+-----------
  1 | Quỹ A   |          
  3 | Quỹ C   |         1
  2 | Quỹ B   |         3
(3 rows)

postgres=# INSERT INTO funds (name, parent_id) VALUES ('Quỹ D', NULL), ('Quỹ E', 2), ('Quỹ F', 3);
INSERT 0 3

postgres=# select * from funds;
 id |  name   | parent_id 
----+---------+-----------
  1 | Quỹ A   |          
  3 | Quỹ C   |         1
  2 | Quỹ B   |         3
  4 | Quỹ D   |          
  5 | Quỹ E   |         2
  6 | Quỹ F   |         3
(6 rows)

Phương pháp này có lợi thế về hiệu suất vì giảm số lần tương tác với database và lượng WAL (Write-Ahead Log) sinh ra, đặc biệt khi làm việc với dữ liệu lớn. (Cần kiểm chứng lại về khía cạnh giao dịch).

3. So sánh COPY và \copy

Cả hai lệnh đều dùng để xuất/nhập dữ liệu nhanh chóng từ/tới file, phù hợp cho khối lượng lớn. COPY thường được ưu tiên hơn do hiệu suất cao hơn.

Sự khác biệt chính:

• COPY: Là lệnh SQL, yêu cầu quyền SUPERUSER (trừ trường hợp xuất/nhập qua stdin/stdout). Làm việc với file trên máy chủ database.
• \copy: Là meta-command của psql, không yêu cầu SUPERUSER. Làm việc với file trên máy khách (client). Tuy nhiên, hiệu suất thấp hơn do mỗi dòng dữ liệu đều cần giao tiếp qua mạng.

Kiểm tra hiệu suất

Tạo dữ liệu mẫu:

CREATE TABLE test_data (
    id BIGINT PRIMARY KEY,
    value TEXT
);

DO $$
DECLARE
    i int;
BEGIN
    FOR i IN 1..20000000 LOOP
        INSERT INTO test_data (id, value) VALUES (
            i,
            'Value' || floor(random() * 100000)::text
        );
    END LOOP;
END $$;

Xuất dữ liệu với COPY:

postgres=# COPY test_data to '/var/lib/pgsql/test_data.txt';
COPY 500000
Time: 80.702 ms
postgres=# \q

Nhập dữ liệu 500k bản ghi:

postgres=# truncate test_data ;
TRUNCATE TABLE
postgres=# COPY test_data from '/var/lib/pgsql/test_data.txt';
COPY 500000
Time: 620.223 ms

postgres=# truncate test_data ;
TRUNCATE TABLE
Time: 11.474 ms

postgres=# \copy test_data from '/var/lib/pgsql/test_data.txt';
COPY 500000
Time: 648.234 ms

Nhập dữ liệu 20 triệu bản ghi:

-- Thời gian tạo dữ liệu: 132524.928 ms (02:12.525)

-- Xuất dữ liệu:
postgres=# COPY test_data to '/var/lib/pgsql/test_data.txt';
COPY 20000000
Time: 5083.841 ms (00:05.084)

-- Sao lưu file:
postgres=# truncate test_data ;
TRUNCATE TABLE
Time: 850.747 ms

-- Nhập với COPY:
postgres=# COPY test_data from '/var/lib/pgsql/test_data.txt';
COPY 20000000
Time: 26875.071 ms (00:26.875)

-- Nhập với \copy:
postgres=# truncate test_data ;
TRUNCATE TABLE
Time: 135.217 ms

postgres=# \copy test_data from '/var/lib/pgsql/test_data.txt';
COPY 20000000
Time: 27416.673 ms (00:27.417)

Kết luận: COPY có hiệu suất cao hơn \copy, đặc biệt rõ rệt với khối lượng dữ liệu lớn (mặc dù trong bài test này sự khác biệt không quá lớn, nhưng nhìn chung COPY tốt hơn). Việc thực thi trong một giao dịch giúp đảm bảo tính nguyên tử (atomicity) và cải thiện hiệu suất. Dung lượng file dữ liệu 369 MB.