News for hackers. Stuff that matters.
Re:Hierarchical Structures in PostgreSQL (2020) by vore on Friday June 25, 2021 @05:03PM (#27636255)
This is use case dependent, but one of my favorite methods for implementing e.g. threaded comments is closure tables:

  create table post_paths (
      descendant_id bigint not null references posts (id) on delete cascade on update cascade,
      ancestor_id bigint not null references posts (id) on delete cascade on update cascade,
      depth int not null,
      primary key (descendant_id, ancestor_id)
  );
At the cost of O(n^2) rows per hierarchy, it will allow you to do some pretty nifty things, most notably being able to get a count of all descendants of a parent in O(n) time. Updating entries may be painful, though.

There are no comments on this comment yet.