📢 Hivemind: `get_discussion` Tree Walk Rewritten as a Single Recursive CTE

Date: 2026-08-13
Images: steemit/hivemind:latest, steemit/hivemind:latest-dbe717d
PR: https://github.com/steemit/hivemind/pull/383 (dbe717d)

A new Hivemind image is out with a significant performance fix for
bridge.get_discussion. Loading a comment thread no longer walks the tree one
depth level at a time — the whole subtree is fetched in a single recursive
CTE query. This removes the last major source of connection-pool pressure
behind the recurring degradation episodes some operators have seen over the
past weeks.

What was wrong

_load_discussion built the comment tree with a level-by-level BFS: one
_child_ids query per depth level, each involving a pool acquire, a parse, and
a DB round-trip. Production timing logs showed 500–1300 ms for threads
4–10 levels deep — and under concurrent load, these multi-query walks were the
main contributor to exhausting the PostgreSQL connection pool, which in turn
produced the intermittent Internal error responses on get_discussion /
get_profile and elevated ELB 4xx rates.

A second, smaller issue: the [DB_SLOW] warning log (3s+ query watchdog)
printed an empty SQL body in production. Multi-line SQL strings start with
a newline, so syslog truncated every entry right after the timing — leaving
operators with a duration but no query to inspect.

What changed

1. Single recursive CTE. The per-level BFS is replaced by one
   WITH RECURSIVE query that fetches the entire subtree in a single DB
   round-trip:

   • Hidden authors (list_type='3') and hidden posts (list_type='1') are
     filtered inside the recursion, so their subtrees are never traversed.
   • MAX_DEPTH / MAX_THREAD_POSTS caps are preserved (LIMIT inside the
     CTE), and BFS ordering is kept so truncation semantics match the old
     implementation.
   • The whole tree is cached under discussion_tree_<root_id> (120s TTL),
     replacing the per-level _child_ids_* cache entries.

2. [DB_SLOW] log fix. The SQL body is now whitespace-collapsed before
   logging (' '.join(sql.split()), same normalization as _normalize_sql in
   stats.py), so slow queries are fully visible in syslog-based collectors.

Verified

• tests/bridge_thread/: 9/9 passed (rewritten for single-CTE semantics).
• CTE SQL tested against real PostgreSQL 16: hidden-post filtering, depth cap,
  post cap, and BFS order on deep chains.
• Wide-thread benchmark: a 5000-child tree resolves in 27 ms with one query
  (vs ~1 s level-by-level).
• Deployed to production beta-hivemind and validated under real traffic:
  tree_walk dropped from 500–1300 ms to 10–118 ms, with queries=1
  confirmed in the DISCUSSION_BREAKDOWN logs.

How to upgrade

docker pull steemit/hivemind:latest      # or :latest-dbe717d to pin this build
# then restart your Hivemind server container/process

No database migration is required. DB_VERSION is unchanged.

Notes for operators

• Large threads are still capped (500 posts / 50 depth), unchanged from
  PR #375 — truncation behavior is identical to before, just much faster.
• Debug timing logs remain in this build. DISCUSSION_SLOW /
  DISCUSSION_BREAKDOWN / POSTS_KEYED_SLOW / DB_SLOW warnings are still
  emitted while the fix is being validated in production. They will be removed
  or gated in a future release.
• If you still see unexpected 4xx responses or degradation after upgrading,
  please open an issue at https://github.com/steemit/hivemind/issues with the
  time and the API method involved.

---

One more thing

Additionally, the refactored version of condenser has released a preview version on the dev environment (https://steemitdev.com).

The condenser refactoring progress is currently around 80%.

The refactored wallet is undergoing final security audit fixes and will be going live soon.