Materialized Views¶

Materialized views are database-maintained read models: a stored Select whose result can be queried like a table and rebuilt when its source data changes. They are useful when a downstream application repeatedly performs the same expensive join, filter, or aggregation.

This page describes the contract that downstream packages should depend on. The implementation lives in orm_loader; consumers should declare a view once and call its lifecycle methods rather than reproducing the DDL themselves.

The ownership boundary¶

orm_loader owns the physical materialized-view lifecycle:

create the view from __mv_select__;
create its declared indexes;
refresh views in dependency order; and
drop views during teardown or replacement.

The consumer owns when those operations happen. A typical application creates views during database setup, loads or updates its source tables, and refreshes the views as part of the same operational workflow. A view is not a second source of truth: its Select and declarations in code are the source of truth, while the database object is a refreshable read cache.

This is deliberately separate from the ordinary ORM table mixins and from the loader lifecycle. Loaders populate source tables; materialized-view orchestration makes derived read models available after that work has completed.

Declare the read model once¶

The class declaration is the integration point that downstream code can import and reuse. It names the physical view, defines its contents, records materialized-view dependencies, and optionally declares indexes.

class RecentObservationMV(Base, MaterializedViewMixin):
    __mv_name__ = "mv_recent_observation"
    __mv_select__ = (
        sa.select(
            Observation.observation_id,
            Observation.person_id,
            Observation.observation_date,
            Observation.value_as_number,
        )
        .where(
            Observation.observation_date
            >= sa.func.current_date() - sa.text("INTERVAL '30 days'")
        )
    )
    __mv_dependencies__ = {"observation"}
    __mv_indexes__ = (
        MaterializedViewIndex(
            name="mv_recent_observation_id_uq",
            columns=("observation_id",),
            unique=True,
        ),
    )

The mixin can also be used without an ORM mapping when the view is consumed through SQLAlchemy Core. When it is combined with a declarative base, the class can additionally describe how application code queries the resulting rows. In both cases, keep the physical name and lifecycle declaration in one shared class so setup code and query code cannot drift apart.

__mv_dependencies__ may contain source-table names as documentation. Only names that belong to the list passed to resolve_mv_refresh_order participate in the topological sort. This means a source table does not need to be registered as a materialized view, while dependencies between registered materialized views are refreshed in the correct order.

A safe lifecycle¶

For a collection of views, make the refresh list explicit and share it between setup and refresh jobs:

ALL_MVS = [RecentObservationMV, DailyObservationCountsMV]

for view in ALL_MVS:
    view.create_mv(engine)

# Run after the source-table load or update has committed.
refresh_all_mvs(engine, ALL_MVS)

create_mv() is idempotent with its default if_not_exists=True, but it does not compare or migrate an existing definition. If __mv_select__ changes, the consumer must choose a replacement/migration strategy; calling create_mv() again will not rewrite the existing view.

refresh_all_mvs() is the useful boundary for downstream orchestration. It sorts the supplied classes and raises RuntimeError for a dependency cycle. Refreshing one class directly is appropriate when the caller already knows that its prerequisites are current.

Choose creation and refresh semantics deliberately¶

Operation	Default	Consumer decision
`create_mv()`	Create and populate the view; create declared indexes	Use `with_data=False` when population should happen in a later refresh; use `create_indexes=False` only when index creation is managed elsewhere
`refresh_mv()`	Blocking refresh	Use `concurrently=True` only when the declaration includes a simple unique index that is created in the target database
`drop_mv()`	`IF EXISTS`, no cascade	Use `cascade=True` only when dependent database objects should be removed as part of teardown

with_data=False leaves a newly created view uninitialized. It must be refreshed before consumers query it. if_not_exists=False and drop_mv(if_exists=False) are useful when setup should fail loudly instead of treating an existing or missing object as acceptable.

The lower-level DDL contracts are available for migration systems that need to compose statements themselves, but most downstream code should use the mixin methods so indexes and refresh eligibility remain tied to the declaration.

Concurrent refresh is a declaration-and-database contract¶

PostgreSQL permits REFRESH MATERIALIZED VIEW CONCURRENTLY only when the view has a suitable simple unique index. Declare that index with MaterializedViewIndex(unique=True) in __mv_indexes__:

class PatientSummaryMV(MaterializedViewMixin):
    __mv_name__ = "mv_patient_summary"
    __mv_select__ = patient_summary_select
    __mv_indexes__ = (
        MaterializedViewIndex(
            name="mv_patient_summary_patient_id_uq",
            columns=("patient_id",),
            unique=True,
        ),
    )


PatientSummaryMV.create_mv(engine)
PatientSummaryMV.refresh_mv(engine, concurrently=True)

create_mv() creates declared indexes immediately after the view. The concurrently=True pre-check is intentionally cheap and conservative: it checks the declaration before issuing SQL, then lets PostgreSQL verify that the index really exists and is valid. If no unique index is declared, or if PostgreSQL rejects the concurrent refresh, the operation raises ConcurrentRefreshNotEligibleError without hiding the underlying cause.

This is fail-closed by design. An index created manually outside __mv_indexes__ does not satisfy the mixin's declaration contract; declare it in the class even if another migration is responsible for creating it. Expressions, partial indexes, and other index forms are outside this simple contract and should not be represented as MaterializedViewIndex entries.

By default, schema=None leaves the view name unqualified. PostgreSQL resolves that name through the connection's search_path, matching the behavior of existing callers. Pass schema="reporting" only when the caller intentionally wants an explicit schema-qualified target.

# Existing/default behavior: search_path resolves the target.
RecentObservationMV.create_mv(engine)

# Explicit schema: the target is quoted and schema-qualified.
RecentObservationMV.create_mv(engine, schema="reporting")
RecentObservationMV.refresh_mv(engine, schema="reporting")

Explicit schema targets are quoted component by component. This matters for embedded quotes, spaces, and mixed-case identifiers. It also means an unqualified mixed-case name and the same name passed with schema= can address different PostgreSQL relations. Keep schema selection at the call site and do not assume that this API provides schema_translate_map, role-token, or general multi-schema behavior.

Failure handling and backend support¶

The built-in implementation is PostgreSQL-oriented. SQLite rejects materialized-view operations with NotImplementedError; this is intentional, not an emulation using ordinary views.

drop_mv() and declared-index creation wrap execution failures in MaterializationError.

API reference¶

MaterializedViewMixin

Mixin providing materialized view lifecycle helpers.

Classes using this mixin must define:

__mv_name__: the name of the materialized view
__mv_select__: a SQLAlchemy Select defining the view contents
optionally, __mv_dependencies__: names of tables or materialized views this MV depends on

This mixin does not define ORM mappings; it is intended for schema-level helpers used during migrations, setup, or administrative workflows.

Examples:

class RecentObservationMV(MaterializedViewMixin):

    __mv_name__ = "mv_recent_observation"

    __mv_select__ = (
        select(
            Observation.observation_id,
            Observation.person_id,
            Observation.observation_date,
            Observation.value_as_number,
            Concept.concept_id,
            Concept.concept_name,
            Concept.domain_id,
        )
        .join(
            Concept,
            Observation.observation_concept_id == Concept.concept_id
        )
        .where(
            Observation.observation_date
            >= func.current_date() - text("INTERVAL '30 days'")
        )
    )

__mv_select__ is a normal SQLAlchemy Select. No special syntax required.

By combining with declarative base, you can define columns to query the mv as an object too:

daily_counts_select = (
    select(
        Observation.observation_date.label("observation_date"),
        Observation.observation_concept_id.label("concept_id"),
        sa.func.count().label("n_observations"),
        sa.func.row_number().over().label('mv_id')
    )
    .group_by(
        Observation.observation_date,
        Observation.observation_concept_id,
    )
)

class DailyObservationCountsMV(Base, MaterializedViewMixin):

    __mv_name__ = "mv_daily_observation_counts"
    __mv_select__ = daily_counts_select
    __mv_pk__ = ["mv_id"]
    __table_args__ = {"extend_existing": True}
    __tablename__ = __mv_name__

    __mv_dependencies__ = {
        "observation",
        "concept",
    }

    mv_id = sa.Column(primary_key=True)
    observation_date = sa.Column(sa.Date, nullable=False)
    concept_id = sa.Column(sa.Integer, nullable=False)
    n_observations = sa.Column(sa.Integer, nullable=False)

Query like a normal mapped class:

rows = (
    session.query(DailyObservationCount)
    .filter(DailyObservationCount.observation_date >= date(2025, 1, 1))
    .order_by(DailyObservationCount.n_observations.desc())
    .all()
)

Best practices

No inserts / updates
Composite PK required for ORM identity map
Treat as immutable cache

`__mv_dependencies__ = set()` `class-attribute` `instance-attribute` ¶

`__mv_indexes__ = ()` `class-attribute` `instance-attribute` ¶

`__mv_name__` `instance-attribute` ¶

`__mv_select__` `instance-attribute` ¶

`create_mv(bind, *, schema=None, with_data=True, if_not_exists=True, create_indexes=True)` `classmethod` ¶

Create the materialized view if it does not already exist.

Parameters:

Name	Type	Description	Default
`bind`	`Connection \| Engine`	A SQLAlchemy Engine or Connection used to execute the DDL.	required
`schema`	`str \| None`	Explicit schema override. When omitted, the view name remains unqualified for the connection's `search_path` to resolve.	`None`
`create_indexes`	`bool`	When True, create every index declared in `__mv_indexes__`.	`True`

Notes

The underlying SQL is emitted via a custom DDL element and executed through the resolved backend. With the built-in backends, this means PostgreSQL. Unsupported backends raise NotImplementedError.

Examples:

with engine.begin() as conn:
    RecentObservationMV.create_mv(conn)

This emits SQL equivalent to:

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_recent_observation AS
SELECT
    observation.observation_id,
    observation.person_id,
    observation.observation_date,
    observation.value_as_number
FROM observation
WHERE observation.observation_date >= CURRENT_DATE - INTERVAL '30 days';

`drop_mv(bind, *, schema=None, if_exists=True, cascade=False)` `classmethod` ¶

Drop the materialized view using the resolved backend.

When schema is omitted, the view name remains unqualified for the connection's search_path to resolve.

`refresh_mv(bind, *, schema=None, concurrently=False)` `classmethod` ¶

Refresh the contents of the materialized view.

Parameters:

Name	Type	Description	Default
`bind`	`Connection \| Engine`	A SQLAlchemy Engine or Connection used to execute the refresh.	required
`schema`	`str \| None`	Explicit schema override. When omitted, the view name remains unqualified for the connection's `search_path` to resolve.	`None`
`concurrently`	`bool`	Request concurrent refresh, requiring a declared unique index.	`False`

Notes

This method issues a backend-specific refresh statement. With the built-in backends, materialized views are PostgreSQL-only. Concurrent refresh semantics are not handled here.

Examples:

with engine.begin() as conn:
    RecentObservationMV.refresh_mv(conn)

resolve_mv_refresh_order

Resolve materialized view refresh order using topological sort.

Raises:

Type	Description
`RuntimeError`	If a dependency cycle is detected.

refresh_all_mvs

Handle refreshing multiple materialized views in dependency order.

Examples:

    ALL_MVS = [
        ObservationWithConceptMV,
        DailyObservationCountsMV,
    ]

    refresh_all_mvs(engine, ALL_MVS)

Bases: DDLElement

CreateMaterializedView

SQLAlchemy DDL element representing a CREATE MATERIALIZED VIEW statement.

This custom DDL construct allows a SQLAlchemy Select construct to be compiled into a backend-specific CREATE MATERIALIZED VIEW statement, enabling materialized view creation to be expressed using SQLAlchemy's DDL execution model.

Parameters:

Name	Type	Description	Default
`name`	`str`	Name of the materialized view to be created.	required
`selectable`	`Select[Any]`	A SQLAlchemy Select construct defining the query backing the materialized view.	required
`with_data`	`bool`	When False, emit `WITH NO DATA`.	`True`
`if_not_exists`	`bool`	When True, emit `IF NOT EXISTS`.	`True`

A simple column index declared on a materialized view.

Only plain column indexes are representable here (no expressions or partial predicates). That restriction is deliberate: it's what keeps "is this index eligible for CONCURRENTLY refresh" decidable from the declaration alone, without inspecting the live catalog first.

`columns` `instance-attribute` ¶

`name` `instance-attribute` ¶

`unique = False` `class-attribute` `instance-attribute` ¶

`init(name, columns, unique=False)` ¶

`__post_init__()` ¶

Bases: DDLElement

Create one declared index on a materialized view.

Parameters:

Name	Type	Description	Default
`target`	`str`	Fully qualified, quoted name of the materialized view to index (see `orm_loader.helpers.sql.qualify_identifier`).	required
`index`	`MaterializedViewIndex`	The index to create.	required
`if_not_exists`	`bool`	Emit `IF NOT EXISTS` so creating an already-present index is a no-op rather than an error.	`True`

Bases: DDLElement

Drop one materialized view.

Parameters:

Name	Type	Description	Default
`name`	`str`	Fully qualified, quoted name of the materialized view to drop (see `orm_loader.helpers.sql.qualify_identifier`).	required
`if_exists`	`bool`	Emit `IF EXISTS` so dropping an already-absent view is a no-op rather than an error.	`True`
`cascade`	`bool`	Emit `CASCADE` to also drop objects that depend on this view.	`False`

Bases: RuntimeError

Base exception carrying structured materialization failure context.

`failure = failure` `instance-attribute` ¶

`init(failure)` ¶

Bases: MaterializationError

Raised when a concurrent materialized-view refresh is not eligible.

This can happen before execution when no eligible unique index is declared, or after execution when PostgreSQL rejects the refresh.

Bases: MaterializationError

Raised before executing Postgres-only DDL/catalog SQL against a non-Postgres connection. Defense in depth: the normal resolve_backend dispatch path already prevents this via _require_capability; this guards direct/manual PostgresBackend() use.

Materialized Views¶

The ownership boundary¶

Declare the read model once¶

A safe lifecycle¶

Choose creation and refresh semantics deliberately¶

Concurrent refresh is a declaration-and-database contract¶

Failure handling and backend support¶

API reference¶

__mv_dependencies__ = set() class-attribute instance-attribute ¶

__mv_indexes__ = () class-attribute instance-attribute ¶

__mv_name__ instance-attribute ¶

__mv_select__ instance-attribute ¶

create_mv(bind, *, schema=None, with_data=True, if_not_exists=True, create_indexes=True) classmethod ¶

drop_mv(bind, *, schema=None, if_exists=True, cascade=False) classmethod ¶

refresh_mv(bind, *, schema=None, concurrently=False) classmethod ¶

columns instance-attribute ¶

name instance-attribute ¶

unique = False class-attribute instance-attribute ¶

__init__(name, columns, unique=False) ¶

__post_init__() ¶

failure = failure instance-attribute ¶

__init__(failure) ¶

`__mv_dependencies__ = set()` `class-attribute` `instance-attribute` ¶

`__mv_indexes__ = ()` `class-attribute` `instance-attribute` ¶

`__mv_name__` `instance-attribute` ¶

`__mv_select__` `instance-attribute` ¶

`create_mv(bind, *, schema=None, with_data=True, if_not_exists=True, create_indexes=True)` `classmethod` ¶

`drop_mv(bind, *, schema=None, if_exists=True, cascade=False)` `classmethod` ¶

`refresh_mv(bind, *, schema=None, concurrently=False)` `classmethod` ¶

`columns` `instance-attribute` ¶

`name` `instance-attribute` ¶

`unique = False` `class-attribute` `instance-attribute` ¶

`init(name, columns, unique=False)` ¶

`__post_init__()` ¶

`failure = failure` `instance-attribute` ¶

`init(failure)` ¶