Popularity Depends on How You Define It: How 700 RSS Bot Posts Took Over DEV's Community Hub 🤖💥

A few days ago, I opened the Forem mobile app and navigated over to the Community Hub (/community) 📱.

Naturally, you expect the "Popular Community Members" section to feature the people driving great technical discussions—authors writing insightful deep-dives, answering tough engineering questions, and earning genuine community appreciation 💬✨.

Instead, sitting proudly at the top of the leaderboard were accounts like:

• 🤖 Account A: 779 posts this month. Reactions: ~0.
• 🤖 Account B: 415 posts this month. Reactions: ~0.

Unless there is an engineer on Earth with 16 arms who writes a blog post every 40 minutes while sleeping 😴, we had a classic platform bug: Automated RSS syndication scrapers were being crowned the most "popular" developers in the community.

The RSS bot publishing 779 posts a month while the rest of us spend 3 days refining a single paragraph.

Here is the story of how that happened, why popularity metrics break when you measure volume instead of engagement, and how fixing it led to a wild deep-dive into PostgreSQL query planner costs with Ben Halpern 🔍📉.

🕵️‍♂️ The Crime Scene: What Did "Popular" Actually Mean?

If you don't explicitly define what "popular" means in code, PostgreSQL will define it for you—and usually in the most chaotic way possible 😅.

In CommunityController#index, the @top_authors query looked something like this:

# The old query (simplified)
@top_authors = User.where(id: recent_articles.select(:user_id))
                   .order("total_score DESC, recent_articles_count DESC")
                   .limit(8)

Look closely at what was missing:

1. 🚫 No filter on published_from_feed: Forem has an official feature that lets creators syndicate their personal blogs via RSS feeds. But the query never checked published_from_feed: false. If a feed bot syndicated 700 markdown drafts, every single one counted toward their author standing.
2. 📊 Post Count as a Tie-Breaker: By ordering with recent_articles_count DESC, sheer output volume trumped community feedback.
3. 💔 Reactions Were Ignored: A creator who spent 20 hours crafting an engineering masterclass with 100 reactions was mathematically outranked by a script dumping weather logs.

(📸 Before: Automated feed bots dominating the Popular Community Members section with hundreds of 0-reaction posts)

🛠️ Round 1: The "Simple" Fix

The first solution felt completely intuitive:

1. 🧹 Exclude feed-imported posts: .where(published_from_feed: false)
2. ❤️ Rank primarily by real human engagement: SUM(articles.public_reactions_count) DESC
3. 🛡️ Exclude suspended/spam users: .without_role(:suspended).without_role(:spam)

I opened PR #24324, added request specs, and pushed 🚀.

Me pushing the PR thinking I solved open-source with 3 lines of ActiveRecord.

Then, @ben Sir's automated AI review agent (open hands) ran an EXPLAIN query on the production schema...

And the query planner cost completely exploded. 💥🤯

⚠️ The Database Trap: Rolify's Hidden Sequential Scans

Here’s the baseline cost of the query on origin/main:

• 📊 Baseline Planner Cost: 216,129.56 (Bitmap index scan on articles + nested loop index scan on users_pkey).

With my chained .without_role(:suspended).without_role(:spam), look at what happened:

• 🚨 Planner cost jumped 4.4x to 953,586.49!

Why? 🤔

Forem uses the rolify gem for role management. Under the hood, without_role(:role_name) generates a NOT IN subquery:

WHERE users.id NOT IN (
  SELECT users_roles.user_id FROM users_roles ...
)

When you chain .without_role(:suspended).without_role(:spam), ActiveRecord nests the first NOT IN subquery inside the second NOT IN subquery.

PostgreSQL looked at that nested monstrosity across nearly 1 million users and essentially threw its hands in the air:

1. 🛑 It abandoned the fast index scan on users_pkey.
2. 🔄 It triggered a Parallel Seq Scan on users across 6 workers (~348,000 rows per worker).
3. 🐢 It ran an outer full-table Seq Scan across 988,000 rows.
4. 💣 To make matters worse, /community calls <% if @top_authors.any? %> and <% @top_authors.each %>, executing that 950k-cost query twice per uncached render!

Waiting for PostgreSQL to run two full-table scans across 1,000,000 user rows.

My simple bug fix accidentally generating a database denial-of-service vector.

⚡ Round 2: The Correlated NOT EXISTS Anti-Join

To fix the query regression without dropping role protection, we replaced the nested NOT IN calls with a correlated NOT EXISTS anti-join:

# app/controllers/community_controller.rb
.where.not(
  UserRole.joins(:role)
    .where("users_roles.user_id = users.id")
    .where(roles: { name: %w[suspended spam], resource_type: nil, resource_id: nil })
    .select(1)
    .arel.exists
)

🧠 Why this changes everything for PostgreSQL:

Instead of dumping nearly a million IDs into memory for a sluggish NOT IN array, PostgreSQL executes a clean Nested Loop Anti-Join using:

• 🎯 An Index-Only Scan on index_users_roles_on_user_id_and_role_id
• 🎯 An Index Scan on roles_pkey

🏆 The Scoreboard:

• 📉 Old baseline on main: 216,129
• 💥 My initial attempt: 953,586 (4.4x regression!)
• ⚡ With NOT EXISTS anti-join: 179,613 (Faster than main!)

Zero sequential scans on the users table. Pure index scans.

🧪 Bonus: Slashing Test Suite Overhead

While profiling the specs in spec/requests/community_spec.rb, we noticed the test suite was creating 35 database records using 25.times and 10.times loops just to verify author ordering ⏱️.

By tightening the fixtures down to just 3 targeted articles (e.g., Author A with 1 post & 50 reactions vs. Author B with 2 posts & 0 reactions), we tested the exact same ordering branches while cutting down factory churn across CI shards ⚡🏎️.

🏁 The Aftermath

(📸 After: Popular Community Members now showcases actual community writers based on verified engagement and public reactions)

The PR (#24324) was approved and merged into forem:main by @mikeydorje! 🎉🤝

💡 Key Lessons for Platform Builders:

1. 📢 Activity ≠ Engagement: If your algorithm rewards sheer publishing volume without weighting community response, bots will always game the system.
2. ⚠️ Beware of without_role chains: Convenience helpers in ORMs can hide expensive nested NOT IN subqueries that degrade exponentially as your tables scale.
3. 🔬 Always check EXPLAIN: An index scan vs. a sequential scan is the difference between a snappy 50ms render and taking down production during peak traffic.

Have you ever encountered a metric or leaderboard on your platform that got completely hijacked by automation? Let's chat in the comments! 👇💬