Materialized views are great for performance, but unlike normal SQL views, they physically store data, which makes schema changes painful. We’ve been working more and more with materialized views lately, and at some point, I had to do something I knew would eventually come up: add a new column. Nothing dramatic, just the usual "oh, we need that field too" moment.

If you've dealt with materialized views, you probably know the catch: you can't really alter them. You end up dropping and recreating the whole thing:

DROP MATERIALIZED VIEW my_view;
CREATE MATERIALIZED VIEW my_view AS
SELECT * FROM my_table
WITH DATA

Dropping a materialized view isn't just "recreating a table": it instantly breaks every query, index, and dependency pointing at it.

The first time around, I wrapped that in a BEGIN/COMMIT. It felt neat: instant switch on commit, no intermediate state. But that reassuring feeling didn't last very long. Two problems showed up almost immediately:

1. Production code might still expect the old schema when the view suddenly switches to the new one.

2. WITH DATA is harmless for small views, but for large ones, it becomes a multi-minute recompute, and our migration pipeline times out if any statement runs too long.

This approach is fine for tests or PoCs, but I didn't feel comfortable using this process in the long run. I wanted something calmer and more controllable.

A More Comfortable Approach

What ultimately worked better for us was splitting the process into clear, distinct steps.

1. Create a new version of the view in parallel

Instead of mutating the existing my_view, I create a new, versioned one:

BEGIN;

CREATE MATERIALIZED VIEW my_view_2025_01_01_1 AS
SELECT * FROM my_table
WITH DATA;

COMMIT

The naming scheme doesn't matter as long as it's predictable: timestamps, versions, deploy IDs… whatever your team can read without squinting.

For small-ish views, WITH DATA is fine. For bigger ones, it's a different story:

• A plain REFRESH MATERIALIZED VIEW re-runs the entire query.

• If your migrations have strict timeouts (ours do), the deployment will simply terminate it halfway through.

• PostgreSQL does not have a built-in “incremental view refresh” feature for standard materialized views.

So I ended up trying alternatives.

Option A: WITH NO DATA + offline refresh

This keeps the migration fast:

CREATE MATERIALIZED VIEW my_view_2025_01_01_1 AS
SELECT *
FROM my_table
WITH NO DATA

Then later (outside the migration) you backfill it:

REFRESH MATERIALIZED VIEW

CONCURRENTLY requires a unique index on the view, but it avoids locking readers while refreshing. It also allows us to run the heavy refresh as a background job instead of during deployment.

The trade-off: the view exists but is empty until that refresh completes. I'm okay with that as long as the application doesn't switch to it prematurely.

Option B: Fake "incremental refresh" using scheduling

PostgreSQL doesn't do incremental view maintenance by default. It always recomputes the whole query. If you really need incremental behavior, you eventually land on:

• Manually-maintained summary tables

• TimescaleDB continuous aggregates

• or an extension like pg_ivm

For us, those were overkill. So I kept the materialized view and controlled when refreshes happen: smaller batches, more frequent runs, and never during migrations.

How to Pick Your Option

Here's the decision matrix:

• Tiny views → WITH DATA is fine

• Medium views → WITH NO DATA + async refresh

• Huge views → schedule controlled refresh windows or migrate to TimescaleDB/pg_ivm

2. Point the application to the new view (SQLAlchemy example)

Once the new view exists and is populated, I open a second PR to update our ORM model. The only real change is:

stop pointing to my_view, start pointing to my_view_2025_01_01_1.

Here's a simplified SQLAlchemy model using your structure, just adapted:

from sqlalchemy import Table, Column, Integer, String
from sqlalchemy_utils import create_materialized_view

MV_NAME = "my_view_2025_01_01_1"

class MyView(models.Base):
    __table__ = create_materialized_view(
        name=MV_NAME,
        selectable=sqlalchemy.select(
            # updated SELECT definition
        ),
        metadata=models.Base.metadata,
        indexes=[
            # updated indexes
        ],
    )

    # ORM fields...

The key idea: the ORM doesn't create or drop the materialized view: migrations do. create_materialized_view does not create the view in the database: Alembic does. This avoids accidental schema drift.

SQLAlchemy simply maps Python classes to the view name you provide.

This keeps the deploy flow clean: the migration creates my_view_2025_01_01_1, then the code switches to using it.

3. Drop the old view (eventually)

Once we know the new view is stable in production, we drop the old one:

DROP MATERIALIZED VIEW IF EXISTS

How long you wait before dropping it depends on how predictable your rollbacks are. Keeping the old version around temporarily isn't unreasonable. It's just clutter you have to clean up eventually.

A Small Side Quest: Catching Dangerous Migrations

One habit we're trying to break is writing migrations like this:

DROP MATERIALIZED VIEW my_view;
CREATE MATERIALIZED VIEW my_view AS

This is easy to miss in a review, and it's exactly the kind of thing that takes your database on a fun little rollercoaster ride.

So we started using AI to flag migrations that drop a materialized view and recreate it in the same migration file.

It's simple, but it's enough friction to make people stop and think before shipping an in-place DROP/CREATE on a busy view.

Looking Back

Nothing here is revolutionary. It's just the process I ended up with after realizing that materialized views are heavier than they look, and deploy-time refreshes aren't great for anyone’s heart rate.

Versioned views keep things boring in the best possible way. The ORM change becomes a one-line diff. The migrations stay short. And I don't need to rely on luck for WITH DATA to finish before the CI clock gives up.

Next time I have to touch one of these, I'll probably still overthink it, but at least I now have a predictable setup, and predictability is pretty much all I want out of a materialized view.