Reddit comments nest arbitrarily deep. A top-level comment has replies. Each reply has replies. The thread can be 15 levels deep. Storing and querying hierarchical data in a relational database has multiple approaches, and the right one depends on how the data is read: do you fetch the whole tree at once, or do you load levels lazily?

Adjacency List#

The simplest model: each comment stores its parent’s ID.

comments(id, post_id, parent_id, author, body, created_at)

A top-level comment has parent_id = NULL. A reply has parent_id = its_parent_id.

Inserting a comment is O(1). Reading the whole tree requires either recursive CTEs (PostgreSQL supports this) or N+1 queries (one per level). For a post with 1,000 comments across 10 levels, a recursive CTE fetches the whole tree in one query. This is the standard approach for moderate-depth trees.

Closure Table#

A separate table stores every ancestor-descendant pair:

comment_paths(ancestor_id, descendant_id, depth)

Every comment is its own descendant at depth 0. A comment also has entries for every ancestor at depth 1, 2, 3…

Fetching all descendants of comment X: SELECT descendant_id FROM comment_paths WHERE ancestor_id = X. O(1) query regardless of tree depth. Deleting a subtree: delete all rows in comment_paths where descendant_id is in the subtree, then delete the comments. Inserting a new comment requires inserting N rows (one per ancestor plus self).

graph TD A[Comment 1: top-level] --> B[Comment 2: reply to 1] A --> C[Comment 3: reply to 1] B --> D[Comment 4: reply to 2] B --> E[Comment 5: reply to 2] D --> F[Comment 6: reply to 4] style A fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style B fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style C fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style D fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style E fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style F fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff

Path Enumeration#

Store the full path from root to each node as a string: /1/2/4/6/. Sorting by path gives a depth-first ordering of the tree. Fetching all descendants of comment 2: WHERE path LIKE '/1/2/%'. Simple and fast with a prefix index. Depth is COUNT('/' in path) - 1.

Drawback: moving a subtree (if you allow re-parenting comments) requires updating paths for the entire subtree. For immutable comment trees, this isn’t a problem.

Reddit’s Approach#

Reddit uses a variant of path enumeration with base36 encoding to keep paths short and sortable. Comments are sorted by score within each level, not by time. The sort order is embedded in the path, allowing a single sorted scan to return the entire tree in display order.

At Salesforce#

Salesforce Chatter (internal social feed) had threaded comments on posts. We used adjacency list with a depth limit of 5. Fetching a thread was a recursive CTE capped at 5 levels. Beyond depth 5, replies were flattened to level 5. This bound the query complexity and kept comment threads human-readable. The depth limit was a product decision driven by the UI as much as database performance.

What I’m Learning#

Adjacency list with recursive CTEs is the right starting point for most comment systems. Closure tables add query performance for deep subtree operations at the cost of write amplification. Path enumeration is elegant for immutable trees with depth-first display requirements. The choice depends heavily on what operations dominate.

Have you implemented hierarchical data storage and which model did you end up with after production experience?