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:
- 🚫 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 checkedpublished_from_feed: false. If a feed bot syndicated 700 markdown drafts, every single one counted toward their author standing. - 📊 Post Count as a Tie-Breaker: By ordering with
recent_articles_count DESC, sheer output volume trumped community feedback. - 💔 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:
- 🧹 Exclude feed-imported posts:
.where(published_from_feed: false) - ❤️ Rank primarily by real human engagement:
SUM(articles.public_reactions_count) DESC - 🛡️ 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 onusers_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:
- 🛑 It abandoned the fast index scan on
users_pkey. - 🔄 It triggered a Parallel Seq Scan on
usersacross 6 workers (~348,000 rows per worker). - 🐢 It ran an outer full-table Seq Scan across 988,000 rows.
- 💣 To make matters worse,
/communitycalls<% 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:
- 📢 Activity ≠ Engagement: If your algorithm rewards sheer publishing volume without weighting community response, bots will always game the system.
- ⚠️ Beware of
without_rolechains: Convenience helpers in ORMs can hide expensive nestedNOT INsubqueries that degrade exponentially as your tables scale. - 🔬 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! 👇💬
Top comments (7)
And lets hear from the popular guys on DEV:
Sir @francistrdev, Sir @georgekobaidze, Ma'am @sylwia-lask, my fav @xulingfeng, @hemapriya_kanagala di, @technogamerz di and our beloved and currently most popular: @heyitsjem, Did it help?
Have A Great Day Everybody!
Bro, this is amazing! There are tons of accounts spamming bulk posts nonstop. As someone with a compulsion to clear notification red dots and a liking‑for‑likes habit, I was honestly getting overwhelmed. Congrats on fixing the issue and gaining great insights along the way!🎉
Yeah really!
I mean 720, 250, 350 these are like insane numbers!
Nobody can type this much! I really wanted to fix this issue to ensure atleast we don't give spotlight to bots.
And thanks! Have A Great Day(or Night)! 😄
Totally agree, those volumes are clearly impossible for human writers. It’s such a meaningful fix to keep real creators visible! As a QA person, I can easily imagine how many edge cases you needed to cover for this ranking logic. Have a good one 👍️🎉😆
🤖 779 posts in a month. Almost zero reactions. And somehow, that's what popularity looked like.
What started as a random visit to DEV's Community Hub turned into a rabbit hole involving RSS bots, ActiveRecord, PostgreSQL query plans, and a rather unexpected 4.4x performance regression in my first attempted fix.
The best part? The final solution wasn't just about filtering bots. It was about understanding what the database was actually doing under the hood.
Huge thanks to @mikeydorje Sir who reviewed and helped get the fix merged into Forem! 🤝
Now I'm curious: Have you ever found a leaderboard, ranking system, or popularity metric being completely gamed by automation? I'd love to hear the story!
Great bro! keep going.
Thanks!