Performance issues on large Solid Queue datasets (overview counts, chart aggregation, and queue page N+1 counters)

[Aiden-Xi]

Hi maintainers, thanks for this gem.

We are using solid_queue_monitor 1.1.0 with PostgreSQL and hit severe performance issues at production scale.

Environment

• solid_queue_monitor: 1.1.0
• solid_queue: 1.3.x
• PostgreSQL
• solid_queue_jobs row count: ~4,014,921

Observed behavior

The monitor root page (/solid_queue) becomes effectively unusable (gateway timeout in production).

A direct DB check:

SELECT count(1) FROM solid_queue_jobs;

Takes about 52,356 ms in our production.

The overview page triggers multiple expensive queries in one request:

• COUNT(*) on solid_queue_jobs
• COUNT(*) WHERE finished_at IS NOT NULL on solid_queue_jobs
• additional counts on execution tables
• chart aggregation queries over time windows

From app logs, this all happens in the same overview request.

Why this hurts

On large datasets, these operations are too heavy for interactive UI endpoints and can exceed upstream timeout thresholds.

Additional pressure points

1. Queue page (/queues) executes multiple per-queue counter queries (N+1 style across queues).
2. Some pages default to sorting by created_at on execution tables. On large tables this requires dedicated indexes and still does not solve count-heavy page design.

Proposed improvements

It would be great to have built-in "large dataset mode" or similar options:

1. Lightweight overview mode

• Skip exact solid_queue_jobs.count and completed exact count by default.
• Optionally use approximate row estimates (e.g. PostgreSQL reltuples).

2. Disable or defer chart data on initial page load

• Lazy-load chart only when requested by user.
• Optional toggle to disable chart entirely.

3. Avoid expensive total counts for pagination on hot pages

• Support keyset/cursor pagination or optional "no total pages" mode.

4. Optimize queues index page aggregation

• Replace per-queue repeated counts with grouped aggregation queries.

5. Safe default root page option

• Optional configuration to land on a lightweight page (/workers or /ready_jobs) instead of overview.

Reference areas in current gem

• overview_controller#index (stats + chart + recent jobs in one request)
• stats_calculator
• chart_data_service
• queues_controller#index
• ready/in-progress pages default ordering and pagination behavior

If helpful, I can provide anonymized EXPLAIN ANALYZE outputs for the top slow queries.

[vishaltps]

Hi @Aiden-Xi — thanks for this detailed report! We've identified and fixed all the performance bottlenecks you described.

What changed in PR #28 (v1.2.0):

1. Overview stats no longer query solid_queue_jobs
All counts are now derived from execution tables (ready_executions, scheduled_executions, claimed_executions, failed_executions) which stay small regardless of how many total jobs you have. The "Total Jobs" and "Completed" stats are replaced with "Active Jobs".

2. Chart data uses SQL GROUP BY
Instead of plucking all timestamps into Ruby memory, chart data is now computed entirely in SQL with bucket-index grouping. Works on both PostgreSQL and SQLite.

3. Filters use subqueries instead of .pluck
All filter methods now use .select(:job_id) (DB subquery) instead of .pluck(:job_id) (loads all IDs into memory).

4. Queue stats pre-aggregated
Eliminated N+1 queries in the queues page, 3 GROUP BY queries replace 3×N per-queue COUNT queries.

5. New config.show_chart option
You can now disable the chart entirely to skip those queries:

SolidQueueMonitor.setup do |config|
  config.show_chart = false
end

Benchmark (100K jobs on PostgreSQL)

Pain Point	Before	After	Speedup
Stats calculation	75ms	3ms	24x
Chart data (1d)	148ms	22ms	7x
Chart data (1w)	771ms	89ms	9x
Filter queries	7ms	1.6ms	5x
Queue stats N+1	37ms	7ms	5x

The stats calculation, your main bottleneck at 52+ seconds with 4M rows, now stays constant regardless of the solid_queue_jobs table size.

This will be released as v1.2.0. Appreciate you taking the time to file such a thorough issue!

[vishaltps]

@Aiden-Xi I would like to know whether the latest version update improved performance.