Using ltree for hierarchical structures in PostgreSQL

Sử dụng ltree cho cấu trúc dữ liệu phân tầng với PostgreSQL-

Trong bài viết này tôi sẽ giới thiệu với các bên về Ltree của postgresql, kiểu dữ liệu cho phép xử lý dự liệu phân tâng dạng tree.

What is ltree ?

Ltree là 1 module của Postgresql . Được sử dụng như 1 kiểu dữ liệu (ltree) để lưu trữ dữ liệu cấu trúc phân tầng. Và có thể “search” thông qua kiểu dữ liệu này.

Why Ltree ?

• Ltree sử dụng như 1 map thu nhỏ , giúp cải thiện tuyệt vời với các action INSERT /UPDATE /DELETE và đặc biệt nhanh trong việc SELECT operations.
• Sử dụng Ltree sẽ cho kết quả nhanh hơn nhiều khi bạn sử dụng recursive CTE hoặc recursive function, những giải pháp đi liền với sự tính toán phức tạp.
• Xây dựng các cú pháp truy vấn riêng để thao tác với trees data.
• Indexs

Initial data

Để bắt đầu sử dụng được ltree , bạn cần phải enable extension của postgresql . Bạn dễ dàng làm điều này với câu lệnh :

CREATE EXTENSION ltree ;

Tạo 1 table với 1 ít data dạng ltree :

CREATE TABLE comments (user_id integer, description text, path ltree);
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001.0001.0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0001.0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 8, md5(random()::text), '0001.0003.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 11, md5(random()::text), '0001.0003.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0003.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0003.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 7, md5(random()::text), '0001.0003.0002.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 20, md5(random()::text), '0001.0003.0002.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 31, md5(random()::text), '0001.0003.0002.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0004');
INSERT INTO comments (user_id, description, path) VALUES ( 34, md5(random()::text), '0001.0003.0002.0002.0005');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0006');

Tiếp theo, chúng ta sẽ add index cho column “path”

CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);
CREATE INDEX path_comments_idx ON comments USING btree(path);

Ok, Tôi đã tạo table “ comments” với field ‘path’, cái chứa đầy đủ "map" cho dữ liệu trong comments tables. Và bạn có thể thấy, các node của tree được phân cách bởi các dấu chấm.

001.002
001.001

Đến thời điểm hiện tại, hãy vẽ ra giấy, bạn sẽ thấy chúng ta có 1 cấu trúc dạng cây với node root là 001.

Tiếp theo chúng ta thử thao tác trên dữ liệu chúng ta vừa có được.

Thử tìm tất cả các node thuộc nhánh 001.003 :

SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
 user_id |           path
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
(12 rows)

Bạn có thể check cậu lệnh trên với Explain:

EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on comments  (cost=0.00..1.24 rows=2 awidth=38) (actual time=0.013..0.017 rows=12 loops=1)
   Filter: (path <@ '0001.0003'::ltree)
   Rows Removed by Filter: 7
 Total runtime: 0.038 ms
(4 rows)

Cùng quay lại bài toán menu động. Để quyết yêu cầu này chúng ta cần làm gì ?

Chúng ta có thể sử dụng 1 bảng map ? Hay giữa forikey ? Whaterver