Branching Out Into Hierarchy Data

ltree is a Postgres extension that can give you and your application a lot of options around complex hierarchal relationships.

At some point in an applications development lifecycle, you can end up having to figure out how to represent hierarchal data. In hierarchal data, a root or 'parent' node can have 'child' nodes attached to it. For example, A (parent) -> B, C (children) or grilled cheese (parent) -> bread, cheese, mayo (children).

While these relationships can be stored in a variety of ways, such as raw strings, arrays, lookup tables, bitmasks, and so on... Postgres has a dedicated type and extension for this type of data called ltree.

To get started with ltree, check to see if it is available in your setup:

select * from pg_available_extensions where name = 'ltree';

If you see it listed as an available extension, you can install it:

create extension if not exists ltree;

Confirm installation:

select * from pg_extension where extname='ltree';

Note: Magistrate makes your available extension list just a click away.

Now we can build our first hierarchy:

create table menu (
  id serial,
  title text,
  ingredients ltree
);

insert into menu (title, ingredients) values ('basic', 'tortilla.cheese'), ('starter', 'tortilla.cheese.carne'), ('extra', 'tortilla.cheese.carne.pollo.guac'), ('setup', 'chips.salsa');

If I am curious what on the menu has a tortilla as the parent ingredient:

select * from menu where 'tortilla' @> ingredients;

If want something that has a tortilla and cheese:

select * from menu where 'tortilla*.cheese.*' ~ ingredients;

If I want something that has a tortilla and cheese, but not guac because of an allergy or preference:

select * from menu where 'tortilla.cheese.!guac' ~ ingredients;

One of the reasons you might lean towards a dedicated type for this problem is all the supporting functions and query options that go along with ltree.

With the simple examples above and tiny data set, we could any solution would likely work fine. However, with more complex relationships and datasets, using ltree can give you a lot more options including more intelligent indexing for performance, recursion on your queries, and more confident precision with lquery stanzas. Using ltree can also get you out of the trap of thinking you need to build your own handler for when you leave the trivial examples.

While Magistrate can't solve all your hierarchal data needs, it can certainly get you started in making sure the extension is available and helping you determine when you have a performance issue with your lquery work.