UI Performance Gain: Materialized View + Mojo::IOLoop->recurring job for History Endpoint (Minion::Backend::Pg)

[rshingleton]

Summary

I've refactored the /history endpoint to use a PostgreSQL materialized view, refreshed on a recurring timer and guarded by an advisory lock. This approach has reduced history endpoint response time from over 5 seconds to under 130 ms on a dataset of ~7.5 million records (48 hours). The advisory lock ensures only one process or node runs the refresh at a time, which is essential for preforked or multi-node environments.

---

Timing Results

Implementation	Request	Status	Response Size	Time
Original (dynamic query)	/history	200	0.8 kB	5.21 s
Materialized View (MV)	/history	200	0.8 kB	127 ms

Note: An initial or rare /history request may be slower if a refresh hasn't run recently.

---

Current Updated Implementation

Materialized View SQL

CREATE MATERIALIZED VIEW minion_jobs_history_hourly AS
SELECT EXTRACT(EPOCH FROM ts) AS epoch,
       COALESCE(failed_jobs, 0) AS failed_jobs,
       COALESCE(finished_jobs, 0) AS finished_jobs
FROM (
  SELECT EXTRACT(DAY FROM finished) AS day,
         EXTRACT(HOUR FROM finished) AS hour,
         COUNT(*) FILTER (WHERE state = 'failed') AS failed_jobs,
         COUNT(*) FILTER (WHERE state = 'finished') AS finished_jobs
    FROM minion_jobs
   WHERE finished > NOW() - INTERVAL '24 hours'
   GROUP BY day, hour
) AS j
RIGHT OUTER JOIN (
  SELECT *
    FROM GENERATE_SERIES(
      date_trunc('hour', NOW() - INTERVAL '24 hour'),
      date_trunc('hour', NOW()),
      '1 hour'
    ) AS ts
) AS s
ON EXTRACT(HOUR FROM ts) = j.hour AND EXTRACT(DAY FROM ts) = j.day
ORDER BY epoch ASC;

Materialized View Refresh Timer (Perl, Logging Omitted)

sub _history_refresh_timer_id {
    my $self = shift;

    return $self->{_history_refresh_timer_id} if $self->{_history_refresh_timer_id};
    return undef unless Mojo::IOLoop->is_running;

    my $interval = 300; # 5 minutes
    my $hash = sha1_hex("MINION_HISTORY");
    my $lock_key = hex(substr($hash, 0, 8));

    my $callback = sub {
        my $dbh = $self->pg->db->dbh;
        my $got = $dbh->selectrow_array('SELECT pg_try_advisory_lock(?)', undef, $lock_key);
        if ($got) {
            eval {
                $self->pg->db->query('REFRESH MATERIALIZED VIEW minion_jobs_history_hourly');
            };
        }
    };

    $self->{_history_refresh_timer_id} = Mojo::IOLoop->recurring($interval => sub {
        $callback->();
    });

    $callback->(); # Run immediately on startup

    return $self->{_history_refresh_timer_id};
}

Updated history Method

sub history {
    my $self = shift;
    $self->_history_refresh_timer_id; # Starts/ensures the refresh loop

    my $daily = $self->pg->db->query(
        "SELECT epoch, failed_jobs, finished_jobs FROM minion_jobs_history_hourly ORDER BY epoch ASC"
    )->hashes->to_array;

    return { daily => $daily };
}

---

How It Works

• When the history endpoint is hit, the periodic timer and advisory lock are initialized if not already present.
• The advisory lock is not released, but has a TTL that expires it after the interval to prevent subsequent runs from drift or parallel processes.
• Every 5 minutes (interval configurable), one process acquires the advisory lock and refreshes the materialized view—preventing redundant, overlapping refreshes across preforked or multi-node deployments.
• All stats are served from the precomputed view, providing consistent sub-150ms UI response even at multimillion-record scale.
• A priming or rarely used endpoint may briefly reflect refresh time, but subsequent calls are instant.

---

• Any further observations or edge case cautions for advisory lock use with periodic refresh in clustered/HA settings?
• Suggestions for a better place or way to schedule the view update?

---

PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit

[kraih]

That looks very fragile and i'm afraid i don't see how a timer is of any value here. The delay from updating the view only gets moved from the /history request to some other random request that's active in the app server.

[rshingleton]

It's potentially fragile and I was using this as a test case/POC, but was able to reduce the load times in my admin UI for a large environment. The expense of maintaining the materialized view is moved to an asynchronous process with a timer and advisory lock, so UI load times improve since history queries read from the precomputed view. Once the timer starts, updates run in the background, and subsequent UI/API requests do not wait for aggregation. This is helpful when multiple users load pages, as it eliminates frontend delays, but it doesn't actually reduce the total database workload—just shifts the expense so it's less visible to the user.

As a more robust and maintainable alternative, I plan to prototype a dedicated history table next week. On every job finish or fail event, an insert can record the relevant stats directly. This would allow the UI to query historical data instantly, without timers or background view refreshes. The existing backend repair routines can periodically remove/archive old entries to keep the history table performant and sized appropriately. This should aligns well with the backend, as job state changes are already processed, and maintenance hooks exist for scheduled cleanup.

[rshingleton]

This update introduces a dedicated minion_jobs_history table and modifies job lifecycle methods to record aggregated job completion statistics. The main goal is to speed up history queries, especially for systems with large datasets where querying the entire minion_jobs table is expensive.

---

Updates

1. Schema Changes

• Added minion_jobs_history table to store hourly aggregates of finished and failed jobs.
• A migration populates this table with the last 24 hours of historical data from minion_jobs.

CREATE TABLE minion_jobs_history (
    hour_epoch BIGINT PRIMARY KEY,
    finished_jobs INTEGER DEFAULT 0,
    failed_jobs INTEGER DEFAULT 0
);

-- optional pre-load for systems with pre-existing data in the pipeline
INSERT INTO minion_jobs_history (hour_epoch, finished_jobs, failed_jobs)
SELECT
    CAST(EXTRACT(EPOCH FROM date_trunc('hour', finished)) AS BIGINT) AS hour_epoch,
    COUNT(*) FILTER (WHERE state = 'finished') AS finished_jobs,
    COUNT(*) FILTER (WHERE state = 'failed') AS failed_jobs
FROM minion_jobs
WHERE finished >= NOW() - INTERVAL '24 hours'
    AND finished IS NOT NULL
GROUP BY hour_epoch
ORDER BY hour_epoch ASC
ON CONFLICT (hour_epoch) DO UPDATE
SET finished_jobs = EXCLUDED.finished_jobs + minion_jobs_history.finished_jobs,
    failed_jobs = EXCLUDED.failed_jobs + minion_jobs_history.failed_jobs;

2. Job Updates (_update)

• Extended _update method to increment counters in minion_jobs_history whenever a job finishes or fails.
• Uses UPSERT logic to handle both new and existing hourly entries efficiently.

my $hour_epoch = int(time / 3600) * 3600;
$self->pg->db->query(
    "INSERT INTO minion_jobs_history (hour_epoch, finished_jobs, failed_jobs)
     VALUES (?, ?, ?)
     ON CONFLICT (hour_epoch) DO UPDATE
       SET finished_jobs = minion_jobs_history.finished_jobs + EXCLUDED.finished_jobs,
           failed_jobs = minion_jobs_history.failed_jobs + EXCLUDED.failed_jobs",
    $hour_epoch, $fail ? 0 : 1, $fail ? 1 : 0
);

This avoids scanning minion_jobs for aggregates, making history retrieval much faster.

3. History Query (history)

• Rewritten history method to query directly from minion_jobs_history instead of recalculating counts.
• Returns hourly aggregated results.

sub history {
    my $self = shift;

    my $daily = $self->pg->db->query(
        "SELECT hour_epoch AS epoch, failed_jobs, finished_jobs
         FROM minion_jobs_history
         ORDER BY hour_epoch ASC"
    )->hashes->to_array;

    return { daily => $daily };
}

4. Repair Enhancements (repair)

• Added cleanup of outdated history entries to avoid unbounded growth of the minion_jobs_history table.
• Currently removes entries older than 24 hours.

sub repair {
    my $self = shift;

    # ... existing repair code

    # Remove outdated history entries
    $db->query(
        "DELETE FROM minion_jobs_history WHERE hour_epoch < EXTRACT(EPOCH FROM date_trunc('hour', NOW() - INTERVAL '24 hours'))"
    );
}