ltree

What ltree does

ltree stores hierarchical paths as a dedicated data type rather than plain text. Each path is a sequence of labels separated by dots. Labels are case-sensitive and can contain letters, digits, and underscores. The path 'Top.Science.Astronomy' represents a node three levels deep: Top at the root, Science one level down, and Astronomy at the leaf.

The extension provides three related types. ltree is the path itself. lquery is a pattern language for matching paths — similar to regular expressions but designed for label sequences. ltxtquery is a full-text-search-style query that matches paths containing specific labels combined with boolean logic.

The key advantage over storing paths as plain text is that ltree understands the structure. It knows that 'Top.Science' is an ancestor of 'Top.Science.Astronomy', and it can answer that question with a single operator and a GiST index scan — no string parsing, no recursive queries. The hierarchy is in the data itself, not reconstructed at query time.

When to use ltree

ltree is a good fit whenever your data has a tree structure and you frequently query along the hierarchy:

• Category trees — product categories, taxonomy hierarchies, topic classifications where you need "find all subcategories of Electronics"
• Organization charts — reporting hierarchies where you need "find all reports under this manager, at any depth"
• File system paths — document trees, folder structures, any path-like data
• Threaded comments — comment threads where you need to fetch an entire subtree efficiently
• Geographic hierarchies — Country.State.City.Neighborhood for drill-down queries
• Access control trees — permission hierarchies where child nodes inherit from parents

ltree is less suitable when your hierarchy changes frequently. Moving a subtree means updating the path column for every descendant node — every child, grandchild, and so on must learn its new address. If your use case involves heavy reparenting, an adjacency list with recursive CTEs may be simpler to maintain, despite being slower to query. One trades storage for query speed; the other trades query speed for flexibility. A sensible household knows which trade it is making.

Installation and setup

ltree is a contrib module that ships with PostgreSQL — no additional packages are needed. It does not require shared_preload_libraries and does not need a server restart. It is marked as a trusted extension, so non-superusers with CREATE privilege can install it.

-- ltree is a contrib module — no extra packages needed
CREATE EXTENSION ltree;

-- Verify it's working
SELECT 'Top.Science.Astronomy'::ltree;

Core operators

ltree's most important operators handle ancestor and descendant relationships.

Ancestor and descendant operators

The @> operator tests whether the left path is an ancestor of (or equal to) the right path. The <@ operator is the reverse — it tests whether the left path is a descendant of (or equal to) the right path. Both are accelerated by GiST indexes.

-- Create a table with an ltree column
CREATE TABLE categories (
  id    serial PRIMARY KEY,
  path  ltree NOT NULL
);

INSERT INTO categories (path) VALUES
  ('Top'),
  ('Top.Science'),
  ('Top.Science.Astronomy'),
  ('Top.Science.Astronomy.Astrophysics'),
  ('Top.Science.Astronomy.Cosmology'),
  ('Top.Science.Chemistry'),
  ('Top.Collections'),
  ('Top.Collections.Pictures'),
  ('Top.Collections.Pictures.Astronomy');

-- Ancestor check: is 'Top.Science' an ancestor of 'Top.Science.Astronomy'?
SELECT 'Top.Science'::ltree @> 'Top.Science.Astronomy'::ltree;
-- true

-- Descendant check: all descendants of 'Top.Science'
SELECT path FROM categories
WHERE path <@ 'Top.Science';
-- Top.Science
-- Top.Science.Astronomy
-- Top.Science.Astronomy.Astrophysics
-- Top.Science.Astronomy.Cosmology
-- Top.Science.Chemistry

Two operators, no recursion, and the GiST index does the rest. One appreciates when things are properly organized.

lquery pattern matching

The ~ operator matches an ltree value against an lquery pattern. An lquery uses * to match zero or more labels, and *{n} or *{n,m} to match a specific number of labels. This makes it possible to express queries like "all nodes exactly two levels below Science" without knowing the intermediate labels.

-- lquery: pattern matching with wildcards
-- * matches zero or more labels

-- All direct children of Top
SELECT path FROM categories
WHERE path ~ 'Top.*{1}';

-- All paths ending in Astronomy at any depth
SELECT path FROM categories
WHERE path ~ '*.Astronomy';

-- Paths with exactly 3 levels under Top
SELECT path FROM categories
WHERE path ~ 'Top.*{3}';

-- Paths with 1 to 3 levels under Top.Science
SELECT path FROM categories
WHERE path ~ 'Top.Science.*{1,3}';

ltxtquery text search

The ? operator matches an ltree against an ltxtquery, which uses boolean operators (& for AND, | for OR, ! for NOT) to combine label names. Unlike lquery, ltxtquery ignores the position of labels in the path — it only cares whether specific labels are present.

-- ltxtquery: full-text-search-like pattern matching
-- Uses & (AND), | (OR), ! (NOT) between label words

-- Any path containing both Science and Astronomy labels
SELECT path FROM categories
WHERE path ? 'Science & Astronomy';

-- Any path containing Astronomy but not Astrophysics
SELECT path FROM categories
WHERE path ? 'Astronomy & !Astrophysics';

Useful functions

ltree provides functions for inspecting and manipulating paths — the sort of utility work that makes the extension genuinely pleasant to live with.

-- Useful ltree functions

-- nlevel: number of labels in the path
SELECT nlevel('Top.Science.Astronomy'::ltree);
-- 3

-- subltree: extract subpath by position (0-based)
SELECT subltree('Top.Science.Astronomy'::ltree, 1, 3);
-- Science.Astronomy

-- subpath: extract subpath with offset and optional length
SELECT subpath('Top.Science.Astronomy'::ltree, 0, 2);
-- Top.Science

-- lca: lowest common ancestor
SELECT lca('Top.Science.Astronomy'::ltree, 'Top.Science.Chemistry'::ltree);
-- Top.Science

-- Concatenation with ||
SELECT 'Top.Science'::ltree || 'Astronomy'::ltree;
-- Top.Science.Astronomy

Indexing

Without an index, ancestor/descendant queries require a full table scan — which is to say, the household is being run without a proper ledger. A GiST index on the ltree column makes @>, <@, ~, and ? operators fast — typically sub-millisecond even on tables with millions of rows.

-- GiST index for ancestor/descendant queries (@>, <@)
CREATE INDEX categories_path_gist_idx ON categories USING GIST (path);

-- GiST index also supports lquery (~) and ltxtquery (?) operators

-- Alternatively, a B-tree index for sorting and equality
CREATE INDEX categories_path_btree_idx ON categories USING BTREE (path);

A B-tree index is useful if you need to sort by path or query for exact path equality, but it does not accelerate ancestor/descendant or pattern-matching queries. For most ltree use cases, the GiST index is the one you want.

Cloud availability

How Gold Lapel relates

Gold Lapel sees ltree queries the same way it sees any other workload passing through the proxy — ancestor checks, lquery matches, ltxtquery filters all have their patterns tracked and their execution statistics recorded. The extension requires no special handling on your part. ltree is transparent to the proxy, which is precisely how well-designed extensions ought to behave.

Where I can be of particular service: if your ltree queries are slow because a GiST index is missing, Gold Lapel's index recommendations will surface that directly. If a materialized view over a frequently queried subtree would ease the load, that too. A well-ordered hierarchy deserves a well-ordered query plan.