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.