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
# meta: operation details (e.g., :sql, options), :response always includes :view => "schema.rel"

response = service.call
response.success?         # => true
response.status           # => :updated
response.meta[:response]  # => { view: "public.mv_user_activity", row_count_before: 123, row_count_after: 123 } # varies by service
response.meta[:request]   # => { row_count_strategy: :estimated, ... } # varies by service

# error
response.error?   # => true
response.error    # => { message: "...", class: "...", backtrace: [...] } # always present on error
response.meta     # => { :request, :response } # varies by service

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 :none ornil Skip counting Fastest

Jobs record duration and persist call 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, row_count_strategy: :exact).call

Options

Option Type values Default Meaning
force Boolean true or false false Drop and recreate if the view already exists.
row_count_strategy Symbol or nil :none or :estimated or :exact or nil :none If set, counts rows after creation.

Success response

  • status: :created
  • meta[:request] { row_count_strategy: :estimated, force: true }
  • meta[:response] { sql: ['...'], view: 'schema.rel', row_count_before: -1, row_count_after: 123 }

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"

API 

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

Options

Option Type values Default Meaning
row_count_strategy Symbol or nil :none or :estimated or :exact or nil :none If set, counts rows after creation.

Success response

  • status: :updated
  • meta[:request] { row_count_strategy: :estimated }
  • meta[:response] { sql: ['REFRESH MATERIALIZED VIEW ...'], view: 'schema.rel', row_count_before: 123, row_count_after: 123 }

Errors

  • Invalid name.
  • View does not exist (pre-check).
  • DB errors from REFRESH (syntax, permission, timeout, etc.).

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).call

Options

Option Type values Default Meaning
row_count_strategy Symbol or nil :none or :estimated or :exact or nil :none If set, counts rows after creation.

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
  • meta[:request] { row_count_strategy: :estimated, concurrent: true }
  • meta[:response] { sql: ['REFRESH MATERIALIZED VIEW ...'], view: 'schema.rel', row_count_before: 123, row_count_after: 123 }

Errors

  • Invalid name.
  • View does not exist (pre-check).
  • DB errors from REFRESH (syntax, permission, timeout, etc.).
  • No unique index → helpful error explaining requirement.
  • PG::ObjectInUse / other concurrency conditions → returned as an error with advice to retry.

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).call

Options

Option Type values Default Meaning
row_count_strategy Symbol or nil :none or :estimated or :exact or nil :none If set, counts rows after creation.

Errors

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

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.

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).call

Options

Option Type values Default Meaning
cascade Boolean true or false false Use CASCADE. Otherwise RESTRICT
row_count_strategy Symbol or nil :none or :estimated or :exact or nil :none If set, counts rows after creation.

Success response

  • 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 + call tracking

Jobs wrap services, measure duration, and record runs:

  • Create: MatViews::CreateViewJob → mat_view_runs with operation: :create
  • Refresh: MatViews::RefreshViewJob → mat_view_runs with operation: :refresh (selects RegularRefresh / ConcurrentRefresh / SwapRefresh based on defn.refresh_strategy)
  • Delete: MatViews::DeleteViewJob → mat_view_runs with operation: :drop

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 

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