Reducing a Rails dashboard load time from over 2 seconds to under 200 ms

At a glance

The problem

This Rails SaaS crawled customer sites and turned their pages into internal-link suggestions. Customers experienced the work as one workflow: submit a sitemap, wait for Sidekiq to crawl and analyze it, then open the dashboard to review the results. Performance problems at any stage made the whole product feel slow.

The dashboard had reached a load time of more than two seconds. A domain summary loaded every page score into Ruby and then issued another query for the count. Completion checks counted every unfinished record when they only needed to know whether one existed. The main suggestions relation relied on a nested subquery and indexes that did not match its filtering and ordering patterns.

The crawl-and-analysis pipeline had a different scaling problem. The phrase-matching job could eagerly load a domain’s full anchor-text graph before doing useful work, repeatedly normalized the same strings inside nested loops, and traversed associated pages in a way that risked N+1 queries. Larger sitemaps therefore increased database work, retained more Ruby objects, and put avoidable memory and processing pressure on the Sidekiq worker.

The internal support screen also performed per-user usage, page-count, and subscription lookups. Its workload grew with the total user base because the screen was not paginated.

External content added two hardening concerns: crawled pages could contain malformed byte sequences, and newly supported sitemap URLs needed to remain attached to the customer’s registered host rather than an unrelated hostname.

What I changed

Made the database do database work

I replaced Ruby-side score materialization with PostgreSQL’s AVG, reducing the operation from a full score pluck plus a separate count to one aggregate query. Completion checks moved from COUNT to EXISTS, allowing PostgreSQL to stop as soon as it finds an unfinished page.

For the suggestions workflow, I simplified the relation to join directly through the filter table and removed irrelevant ordering from count queries. I also aligned indexes with the actual access paths:

• (domain_id, created_at) for domain-scoped page ordering; and
• (active, duplicate, ignored, matched_phrase_id) for the filter combination used by the suggestions query.

These changes reduced transferred rows and Ruby allocations as well as SQL work.

Removed Sidekiq memory bloat

The phrase-matching job stopped preloading the entire domain and linked-domain anchor graph when loading one page. Instead, anchor texts and their pages are eager-loaded in batches of up to 1,000 records. This removed the largest source of memory bloat: retaining a complete association graph for the duration of a job.

Within each batch, each anchor text is normalized once before it is compared with the page’s content blocks. The normalized strings are then passed into the matching method instead of normalizing that anchor again for every block. Together, smaller working sets and less repeated string work reduced worker load and improved the speed of the sitemap crawl-and-analysis pipeline.

The support screen received the same treatment at the request level:

• eager-loaded current usage and domains;
• replaced per-user page totals with one grouped aggregate;
• fetched the latest subscriptions for the displayed users in one query; and
• paginated the result to 50 users per request.

The important result is a change in scaling behavior: related data is loaded per batch or per page, rather than once per record or all at once.

Hardened external input boundaries

Crawled HTML occasionally contained invalid UTF-8. Instead of allowing one malformed node to fail the entire Sidekiq job, the parser now catches that specific encoding error, removes invalid or undefined bytes, and continues. Other ArgumentError failures are still raised rather than being silently hidden.

For sitemap ingestion, I added hostname validation at the model boundary. A persisted sitemap must use either the customer’s exact registered host or one of its subdomains; a dotted suffix check prevents lookalike hostnames from passing. A composite unique index on domain and sitemap also enforces deduplication in PostgreSQL, including under concurrent writes.

Result

Dashboard load time fell from more than two seconds to less than 200 ms—a reduction of greater than 90%. That improvement came from treating the page as a complete data path rather than optimizing one isolated query: aggregates moved into PostgreSQL, existence checks became short-circuiting queries, the suggestions relation was simplified, and indexes were redesigned around the filters and ordering the dashboard actually used.

The background-processing work produced a second practical result. Removing the full-graph preload and processing associations in bounded batches eliminated memory bloat, reduced pressure on the Sidekiq worker, and improved sitemap crawler throughput. Malformed text could be recovered instead of failing an entire crawl, so the pipeline became faster and more resilient at the same time.

The structural changes explain why the gains hold as data grows:

• score calculation no longer instantiates every score in Ruby;
• boolean status checks no longer count an entire relation;
• anchor processing no longer loads the complete association graph up front;
• analysis loading is capped at 1,000 anchor records per batch;
• the support screen no longer issues its key usage and subscription lookups once per user;
• administrative requests are capped at 50 users; and
• malformed page text no longer discards an otherwise usable crawl.

Validation

The changes were delivered as small branches so each concern could be reviewed and deployed independently. The sitemap rules are enforced in both the Rails model and, for duplicate records, a PostgreSQL unique index. Runtime fixes preserve failure visibility by recovering only from the known UTF-8 condition and re-raising unrelated argument errors.

The dashboard timing is the recorded production outcome of the work. Separating the changes reduced rollout risk: dashboard queries, background-job memory behavior, administrative pagination, and ingestion hardening could be evaluated without combining them with unrelated product and interface changes.

Engagement

This work was delivered through a focused Rails performance optimization engagement: an agreed production problem, a measurable baseline, and time reserved to implement and validate fixes—not only recommend them.