📢 Hivemind Performance Incident Postmortem & Fix (PR #373)

Date: 2026-07-13
Affected: hivemind (steemit.com API backend)
PR: #373 — perf: mitigate slow follow/tag/rank queries
Status: Deployed to production (hivemind + hivemindsync), all Green

---

What Happened

On 2026-07-12 (02:00–04:00 UTC), steemit.com users intermittently saw
"Sorry! This page doesn't exist" errors. The root cause was not an
infrastructure outage — RDS CPU (5–8%) and connections (54) were normal,
and Elastic Beanstalk stayed Green throughout. Instead, a small number of
slow SQL queries (10–22 seconds each) saturated the database's
provisioned IOPS (3,000), causing API requests to time out:

slow query → IOPS exhaustion → API timeout → jussi 502 → condenser 404

Over the 2-hour window there were ~4,500 jussi 502s and ~332K condenser
errors, triggered by just three query patterns.

---

Root Cause: Three Slow-Query Classes

All three were identified in the RDS slow query log
(log_min_duration_statement=1s, 31 slow queries, 8 over 10s):

1. Follower/Following lists — up to 21.9s

condenser_api.get_followers / get_following query
hive_follows JOIN hive_accounts ... ORDER BY created_at DESC.

Two compounding problems:

• Parameterized state IN :state tuple binding prevented PostgreSQL's
  planner from matching the WHERE state IN (1,3) partial index
  (idx_follows_follower_state_created_desc, added in v23). The planner
  fell back to a full-ascending index scan with an expensive cross-value
  sort.
• No cache on three of the four follow functions (only get_following
  had a 30s TTL). Every page view hit the database.

2. Tag-filtered post lists — up to 12.9s

bridge.get_ranked_posts (tag filter) and
condenser_api.get_discussions_by_created build
post_id IN (SELECT post_id FROM hive_post_tags WHERE tag = :tag). Hot
tags match tens of thousands of rows, and the outer query re-ran this
subquery on every request with no caching.

3. Account rank rebuild — up to 12.7s

Accounts.fetch_ranks runs
SELECT id FROM hive_accounts ORDER BY vote_weight DESC — a full-table
sort of million-scale rows. It ran every hour on the indexer's sync
connection. Although separate from the API pool, it competed for the same
RDS IOPS during the IOPS-constrained window.

---

What Was Done (PR #373)

Six commits, all short-term code/query changes — no architecture changes,
no new dependencies. The full investigation report and diff are on GitHub.

Fix 1: Follow queries — hardcode state + cache + symmetric index

• Hardcode state IN (1,3) / state IN (2,3) as SQL literals instead
  of a parameterized tuple, so the planner can match the partial index.
• Add a 30s Redis cache to the three uncached follow functions
  (get_followers, get_followers_by_page, get_following_by_page),
  aligning with get_following.
• New symmetric partial index idx_follows_following_state_created_desc
(following, state, created_at DESC, follower) WHERE state IN (1,3)
  (schema v28→v29). The existing v23 index is follower-led and only
  serves get_following; get_followers (WHERE following = :id) had no
  following-led partial index and could not benefit from Fix 1 alone.
• Complete the INNER JOIN migration for get_following_by_page, which
  was missed when its three siblings were converted in 3274329/67c6d5e.

Fix 2: Tag filtering — cache (60s TTL)

Applied the existing pids_by_blog caching pattern (query_col +
cache_key + cache_ttl) to pids_by_category (bridge_api) and
pids_by_query (condenser_api).

Fix 3: Reduce fetch_ranks frequency (hourly → every 6h)

Rank is an approximate score used for notification buckets; a 6h stale
window is acceptable. Startup prefetch is unchanged.

Fix 4: Drop redundant indexes

Dropped hive_follows_5a/5b — legacy v9 duplicates of ix5a/ix5b
(identical column sets), pure write-amplification. This reduces per-write
overhead on hive_follows.

Fix 5: Longer cache for muted (ignore) lists (300s TTL)

Follow-up after production observation: the ignore branch
(state IN (2,3)) cannot match the WHERE state IN (1,3) partial index
and falls back to the slow ix5a/ix5b path (3.5–9s per cold-cache hit).
Since muted relationships change rarely, the ignore cache TTL was raised
from 30s to 300s. Normal follows stay at 30s.

---

Results (Production, via Scalyr)

Query	Before	After	Improvement
get_followers (normal)	21,891ms	14.75ms	↓ 99.93%
get_following (normal)	10,848ms	13.46ms	↓ 99.88%
get_followers_by_page (normal)	10,261ms	18ms	↓ 99.82%
get_followers (ignore, cold)	3,500–9,000ms	31ms (cached)	↓ 99.6%

Both production environments are Green/Ready with the fix:

Environment	Version
beta-hivemindsync	pr373-2-20260713053936
beta-hivemind	pr373-2-20260713053936

---

Deployment Notes for Node Operators

• Schema migration v28→v29 runs automatically on hive sync startup
  via CREATE INDEX CONCURRENTLY (no table lock). On production
  hive_follows it completed in ~2.5 minutes. Recommend deploying during
  a low-traffic window, as index build consumes IOPS.
• Caching requires REDIS_URL to be configured. The @cacher
  decorator in hive/server/db.py silently skips caching when
  redis_cache is None.
• The migration is idempotent (IF NOT EXISTS / IF EXISTS) and
  safe on both fresh and migrated databases.

---

What's Not in This PR (Future Work)

These medium/long-term items were intentionally left out of the short-term
fix and are documented in the investigation report:

• Redis sorted-set caching for the follow graph and tag inverted index
  (the approach used by large-scale social platforms)
• Read replica for API queries (separate from the indexer's primary)
• Increase provisioned RDS IOPS (currently 3,000 on io1)
• Table partitioning for hive_posts_cache / hive_follows

---

References

• PR: https://github.com/steemit/hivemind/pull/373
• Key files: hive/server/condenser_api/cursor.py,
  hive/server/bridge_api/cursor.py, hive/db/schema.py,
  hive/db/db_state.py, hive/indexer/sync.py

---

Questions or feedback are welcome in Here.