Run Tracking (Create / Refresh / Delete)

Every materialized-view operation is recorded in a dedicated table so you can audit, debug, and measure performance over time.

  • Create runs: MatViews::MatViewRun, operation type :create
  • Refresh runs: MatViews::MatViewRun, operation type :refresh
  • Delete runs: MatViews::MatViewRun, operation type :delete

These rows are created and finalized by the corresponding ActiveJob wrappers:

  • MatViews::CreateViewJob
  • MatViews::RefreshViewJob
  • MatViews::DeleteViewJob

Jobs enqueue via the adapter: MatViews::Jobs::Adapter.enqueue(job_class, queue: MatViews.configuration.job_queue, args: [...]) The adapter uses your configured backend (ActiveJob / Sidekiq / Resque) and does not guess.

Status lifecycle

All run models share the same status enum:

  • running (1) - job started, clock started
  • success (2) - operation completed successfully
  • failed (3) - operation failed; error contains details

Jobs set started_at / finished_at and compute duration_ms using a monotonic clock.

Schema

Below are the essential columns for each run table.

Runs

MatViews::MatViewRun has the following key attributes (columns):

Column Type Notes
mat_view_definition_id bigint FK Required
status integer running/success/failed
operation integer create/refresh/drop
started_at datetime Job start time
finished_at datetime Job end time
duration_ms integer Milliseconds (monotonic)
error jsonb Error details (:message, :class, :backtrace)
meta jsonb service (:request, :response)
created_at, updated_at datetime Timestamps

Index: index_mat_view_runs_on_mat_view_definition_id

Relationships (diagram) 

erDiagram
  MAT_VIEW_DEFINITIONS ||--o{ MAT_VIEW_RUNS  : has

  MAT_VIEW_DEFINITIONS {
    bigint id PK
    string name
    text   sql
    int    refresh_strategy
    jsonb  unique_index_columns
  }

  MAT_VIEW_RUNS {
    bigint id PK
    bigint mat_view_definition_id FK
    int    status
    int    operation
    datetime started_at
    datetime finished_at
    int    duration_ms
    jsonb   error
    jsonb  meta
  }

How runs are written

Each job follows the same high-level flow:

  1. Insert a run row with status: :running and started_at.
  2. Call the appropriate service (CreateView, Regular/Concurrent/SwapRefresh, DeleteView).
  3. Finalize the run:
    • on success: status: :success, set finished_at, duration_ms, meta from service response.
    • on error: status: :failed, set error, finished_at, duration_ms, meta from response.

Services return a uniform MatViews::ServiceResponse that includes status, and meta (e.g., sql, strategies, options). Jobs persist the meta into the run row’s meta.

Reading runs (Rails) 

# Latest run for a view
defn = MatViews::MatViewDefinition.find_by!(name: "mv_user_activity")
last_run = MatViews::MatViewRun
  .where(mat_view_definition_id: defn.id)
  .order(created_at: :desc)
  .first

if last_run&.status_success?
  puts "Last refresh OK in #{last_run.duration_ms} ms, rows-before=#{last_run.row_count_before}, rows-after=#{last_run.row_count_after}"
else
  puts "Last refresh failed: #{last_run&.error&.dig('message') || 'no runs yet'}"
  puts "Error class: #{last_run&.error&.dig('class') || 'N/A'}"
  puts "Backtrace:"
  puts last_run&.error&.dig('backtrace')&.join("\n")
end

# Last N runs (any type), newest first
runs = MatViews::MatViewRun
         .where(mat_view_definition_id: defn.id)
         .order(created_at: :desc)
         .limit(20)

Aggregating metrics

A few handy snippets to power dashboards or reports:

duration stats (per view) 

MatViews::MatViewRun
  .joins(:mat_view_definition)
  .where(status: :success)
  .group("mat_view_definitions.name")
  .pluck(
    "mat_view_definitions.name",
    "AVG(duration_ms)::int",
    "MIN(duration_ms)",
    "MAX(duration_ms)",
    "COUNT(*)"
  )
# => [["mv_user_activity", 123, 95, 181, 42], ...]

Failure counts (last 24h) 

MatViews::MatViewRun
  .where(status: :failed)
  .where("created_at >= ?", 24.hours.ago)
  .group(:mat_view_definition_id)
  .count

Latest row_counts (if tracked)

row_count_before 

MatViews::MatViewRun
  .select("DISTINCT ON (mat_view_definition_id) mat_view_definition_id, meta->>'row_count_before' AS row_count_before, created_at")
  .where(status: :success)
  .order("mat_view_definition_id, created_at DESC")

row_count_after 

MatViews::MatViewRun
  .select("DISTINCT ON (mat_view_definition_id) mat_view_definition_id, meta->>'row_count_after' AS row_count_after, created_at")
  .where(status: :success)
  .order("mat_view_definition_id, created_at DESC")

Dashboards & alerts

  • Dashboards: chart duration_ms over time per view; show last status & row_counts.
  • SLOs: alert on failed refreshes, or on unusually high duration_ms (e.g., 2× moving average).

Retention & pruning

Run tables can grow quickly. Consider a periodic cleanup:

  • Keep last N runs per view (e.g., 500)
  • Or keep last X days of history (e.g., 90d)

Example (Postgres SQL):

-- Keep last 500 successful runs per view; keep all failures
WITH ranked AS (
  SELECT id, mat_view_definition_id,
         ROW_NUMBER() OVER (PARTITION BY mat_view_definition_id, status ORDER BY created_at DESC) AS rn
  FROM mat_view_runs
  WHERE status = 2 -- success
)
DELETE FROM mat_view_runs
USING ranked
WHERE mat_view_runs.id = ranked.id
  AND ranked.rn > 500;

Tune by environment (prod vs. staging) and your storage/observability needs.

Troubleshooting

  • Run shows success but MV seems unchanged Confirm which refresh strategy ran (see defn.refresh_strategy and the run’s meta). For :estimated counts, remember reltuples is approximate; use :exact to verify.

  • No runs recorded Ensure you’re enqueuing the job (not just calling services directly) if you want persistence. Services themselves don’t write run rows.

  • Long durations out of nowhere Check for locks/long transactions, autovacuum interference, or compute spikes. Correlate duration_ms with DB logs and pg_stat_activity.

See also