Services (Create / Refresh / Delete)

Services are the synchronous building blocks behind all rake tasks and jobs. Each service:

  • validates the definition (name, existence, etc.),
  • builds safe SQL (properly quoted, schema-qualified),
  • executes the operation,
  • and returns a uniform response object your app (or job) can inspect.

For background execution with timing/recording, use the corresponding ActiveJob wrappers (below) and enqueue them via MatViews::Jobs::Adapter.enqueue.

Common concepts

Input: MatViews::MatViewDefinition

A definition provides:

  • name — unqualified identifier (no dots). Example: mv_user_activity
  • sql — a SELECT statement (CTEs allowed)
  • refresh_strategy — regular | concurrent | swap (used by RefreshViewJob)
  • optional metadata (unique_index_columns, dependencies, etc.)

Name validation: services reject dotted names (public.mv_users). Schema is resolved from the DB search_path and quoted separately.

Quoting & schema resolution

All services generate a safe, schema-qualified relation:

"schema"."rel"
  • Schema is resolved from search_path (supports quoted tokens & $user; falls back to public if missing/invalid).
  • We quote schema and relation as identifiers, not as string literals.

Response shape (all services)

Services return a MatViews::ServiceResponse (value object). Pattern:

# success
# :created (create), :updated (refresh), :deleted (delete), or :skipped
# payload: always includes :view => "schema.rel"
# meta: operation details (e.g., :sql, options)
response = service.run
response.success? # => true
response.status   # => :updated
response.payload  # => { view: "public.mv_user_activity", rows_count: 123 } # varies by service
response.meta     # => { sql: 'REFRESH ...', row_count_strategy: :estimated }

# error
response.error?   # => true
response.error    # => "PG::Error: ..."
response.meta     # => { sql: '...', backtrace: [...] }

Jobs record duration and persist run rows. Services may include meta fields like :sql or options, but jobs are the canonical place for timing/observability.

Create

Class: MatViews::Services::CreateView What it does: CREATE MATERIALIZED VIEW from the definition’s SQL (optionally force to recreate).

API 

MatViews::Services::CreateView.new(defn, force: false).run

Options

Option Type Default Meaning
force Boolean false Drop and recreate if the view already exists (service handles quoting/existence).

Success response

  • status: :created
  • payload: { view: "schema.rel" }
  • meta: { sql: "CREATE MATERIALIZED VIEW ...", force: false }

Errors

  • Invalid name format.
  • SQL errors from your definition.sql (syntax, permission, etc.).

Refresh

Refresh picks the exact strategy at the job level based on defn.refresh_strategy. You can also call services directly.

Regular (locking)

Class: MatViews::Services::RegularRefresh SQL: REFRESH MATERIALIZED VIEW "schema"."rel"

Row count strategies

Strategy Symbol Description Perf
Estimated :estimated Reads pg_class.reltuples (approximate) Fast
Exact :exact SELECT COUNT(*) FROM "schema"."rel" Accurate but heavier
None nil Skip counting Fastest

API 

MatViews::Services::RegularRefresh.new(defn, row_count_strategy: :estimated).run

Success response

  • status: :updated
  • payload: { view: "schema.rel", rows_count: <int or nil> }
  • meta: { sql: 'REFRESH MATERIALIZED VIEW ...', row_count_strategy: :estimated }

Errors

  • Invalid name.
  • View does not exist (pre-check).
  • DB errors are wrapped with meta[:sql] and backtrace.

Concurrent (keeps reads available)

Class: MatViews::Services::ConcurrentRefresh SQL: REFRESH MATERIALIZED VIEW CONCURRENTLY "schema"."rel"

Requirement: A unique index covering all rows on the MV.

API 

MatViews::Services::ConcurrentRefresh.new(defn, row_count_strategy: :estimated).run

What the service checks

  • Valid name
  • MV exists
  • Presence of at least one unique index on the MV (e.g., UNIQUE (user_id))

Success response

  • status: :updated
  • payload / meta similar to regular

Errors

  • No unique index → helpful error explaining requirement.
  • PG::ObjectInUse / other concurrency conditions → returned as an error with advice to retry.
  • Same error wrapping as regular refresh.

Tip: Refresh concurrency pairs well with small, frequent refreshes and a good autovacuum/ANALYZE cadence.

Swap (atomic rebuild)

Class: MatViews::Services::SwapRefresh Idea: Build into a temporary MV, then atomically swap names. Optionally recreate indexes/permissions.

Typical steps

  1. Create temp MV: CREATE MATERIALIZED VIEW "schema"."rel__tmp_<nonce>" AS <definition.sql>
  2. Recreate indexes/permissions on temp MV
  3. Transactional swap:
    • ALTER MATERIALIZED VIEW "schema"."rel" RENAME TO "rel__old_<nonce>"
    • ALTER MATERIALIZED VIEW "schema"."temp" RENAME TO "rel"
    • Optional: drop old

API 

MatViews::Services::SwapRefresh.new(defn, row_count_strategy: :estimated).run

When to use

  • You can’t use CONCURRENTLY (no unique key) but need low read disruption.
  • You want to rebuild with complex SQL while keeping the old view intact until swap.

Errors

  • Any step failure returns a wrapped error (with meta[:sql] where relevant).
  • Ensure your service replays index/permission recreation deterministically.

Delete

Class: MatViews::Services::DeleteView SQL: DROP MATERIALIZED VIEW [IF EXISTS] "schema"."rel" [CASCADE|RESTRICT]

API 

MatViews::Services::DeleteView.new(defn, cascade: false, if_exists: true).run

Options

Option Type Default Meaning
cascade Boolean false Use CASCADE. Otherwise RESTRICT (error if dependents exist).
if_exists Boolean true Use IF EXISTS. If true and missing → :skipped. If false and missing → error.

Success responses

  • status: :deleted (when dropped)
  • status: :skipped (not present, if_exists: true)

Errors

  • Invalid name.
  • Not present with if_exists: false.
  • Dependents exist with cascade: false → helpful error suggesting cascade: true.

Meta

  • Includes :sql, :cascade, :if_exists (and backtrace on errors).

Jobs: background + run tracking

Jobs wrap services, measure duration, and record runs:

  • Create: MatViews::CreateViewJob → mat_view_create_runs
  • Refresh: MatViews::RefreshViewJob → mat_view_refresh_runs (selects RegularRefresh / ConcurrentRefresh / SwapRefresh based on defn.refresh_strategy)
  • Delete: MatViews::DeleteViewJob → mat_view_delete_runs

Enqueue via adapter (the adapter uses the backend you configured—no guessing):

MatViews::Jobs::Adapter.enqueue(
  MatViews::RefreshViewJob,
  queue: MatViews.configuration.job_queue,
  args:  [definition.id, row_count_strategy: :estimated]
)

Supported backends today: ActiveJob, Sidekiq, Resque (more welcome). Make sure your queue system is running (unless you use :inline).

Choosing a refresh strategy

Scenario Use
Low traffic, simple MV regular
You must keep reads available, and you can define a unique key for rows concurrent
Highly available swap with custom index/permission choreography; concurrent not feasible swap

Examples

Create → index (for concurrent) → concurrent refresh 

defn = MatViews::MatViewDefinition.find_by!(name: 'mv_user_activity')

# Create (idempotent in your flow; use force if you need to rebuild)
MatViews::CreateViewJob.perform_later(defn.id, force: false)

# Create unique index (required for CONCURRENT refresh)
ActiveRecord::Base.connection.execute <<~SQL
  CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_mv_user_activity_user_id
  ON "public"."mv_user_activity"(user_id)
SQL

# Concurrent refresh (reads stay available)
MatViews::RefreshViewJob.perform_later(defn.id, row_count_strategy: :estimated)

Swap refresh (low downtime without unique index) 

defn = MatViews::MatViewDefinition.find_by!(name: 'mv_expensive_agg')
MatViews::RefreshViewJob.perform_later(defn.id, row_count_strategy: :exact) # job will pick SwapRefresh

Delete (idempotent by default) 

defn = MatViews::MatViewDefinition.find_by!(name: 'mv_temp')
MatViews::DeleteViewJob.perform_later(defn.id, cascade: false) # if_missing => :skipped

Troubleshooting

  • Invalid view name format Ensure definition.name has no dots and matches /^[A-Za-z_][A-Za-z0-9_]*$/.

  • cannot refresh materialized view concurrently without a unique index Add a unique index that covers all rows. Example: UNIQUE (user_id).

  • PG::ObjectInUse during concurrent refresh Another session is locking the relation. Retry; ensure long-running transactions aren’t pinning catalog rows.

  • dependencies exist. Use cascade: true when deleting A view or other object depends on the MV. Either drop dependents first or pass cascade: true.

  • Row counts differ after refresh Use row_count_strategy: :exact to verify. Consider ANALYZE/autovacuum cadence affecting reltuples (estimated). Audit the underlying SQL for non-determinism.

See also