> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# What happens to refreshable materialized views when the server restarts

> Why refreshable materialized views can all refresh at once when a server restarts, and how to start them again one at a time.

<Warning>
  A refreshable materialized view whose refreshes are not coordinated through ClickHouse Keeper forgets when it last refreshed, so it can refresh again as soon as the server is ready. A server with many such views, or views over large datasets, can put many full-dataset refreshes in flight at the moment it is least able to absorb them.
</Warning>

<h2 id="which-views-keep-their-schedule">
  Which views keep their schedule?
</h2>

Refreshes are coordinated through ClickHouse Keeper for a view in a [`Replicated`](/docs/reference/engines/database-engines/replicated) database. In ClickHouse Cloud, the same is true for a [`Shared`](/docs/cloud/reference/shared-catalog) database. An `APPEND`-family view in either Cloud database can opt out of coordination with `SETTINGS all_replicas = 1`; on an open-source ClickHouse server, only a `Replicated` database has this coordination. A view without coordination holds its last refresh time in memory only, so a restart leaves it overdue on any ordinary schedule. `RANDOMIZE FOR` does not spread these refreshes out.

<h2 id="the-extra-refreshes-can-also-duplicate-data">
  The extra refreshes can also duplicate data
</h2>

An `APPEND` view appends another full result. An uncoordinated `APPEND INCREMENTAL` view loses its in-memory cursor on restart unless its transactional target committed the cursor, so it reprocesses rows it had already appended.

Staging the start as described below limits how many refreshes run at once, but it still runs them. An `APPEND` view avoids the extra append if you start it only when its next refresh is due anyway. An `APPEND INCREMENTAL` view that lost its cursor gains nothing from the timing, because whenever it next runs it reprocesses the whole source table and appends that result, so keep it stopped until you can absorb or remove the duplicated rows.

<h2 id="keep-every-view-stopped-when-the-server-comes-up">
  Keep every view stopped when the server comes up
</h2>

Set [`stop_refreshable_materialized_views_on_startup`](/docs/reference/settings/session-settings/other#stop_refreshable_materialized_views_on_startup) in the server's own settings profile, which is the profile named by `system_profile`, falling back to `default_profile` and then to `default`. This setting is experimental:

```xml title="/etc/clickhouse-server/users.d/refreshable_materialized_views.xml" theme={null}
<clickhouse>
    <profiles>
        <default>
            <stop_refreshable_materialized_views_on_startup>1</stop_refreshable_materialized_views_on_startup>
        </default>
    </profiles>
</clickhouse>
```

The example uses the default `default` profile. Replace it with your custom `system_profile` when you use one.

[`SYSTEM STOP VIEWS`](/docs/reference/statements/system#stop-view-stop-views) is not an alternative, because the stopped state it sets does not survive a restart.

<h2 id="start-the-views-one-at-a-time">
  Start the views one at a time
</h2>

[`SYSTEM START VIEWS`](/docs/reference/statements/system#start-view-start-views) starts all of the views at once, which produces the same burst, so start each view by name and let its refresh finish before starting the next one. [`system.view_refreshes`](/docs/reference/system-tables/view_refreshes) lists them. In ClickHouse Cloud, this system table is node-local, so inspect each node:

```sql theme={null}
SYSTEM START VIEW my_database.my_view;
SYSTEM WAIT VIEW my_database.my_view;
```

Starting a view only resumes its schedule, and unlike [`SYSTEM REFRESH VIEW`](/docs/reference/statements/system#refresh-view) it adds no refresh of its own. A view whose refresh time passed while it was stopped is already overdue on that schedule, so it refreshes as soon as it is started, and [`SYSTEM WAIT VIEW`](/docs/reference/statements/system#wait-view) blocks while that refresh runs.

A view that is waiting for a [`REFRESH ... DEPENDS ON`](/docs/reference/statements/create/view#refresh-dependencies) prerequisite is not refreshing yet, so start those views after the ones they depend on. A circular dependency has no such order, and a restart breaks the cycle whenever its views are not coordinated: start every member, then run the same `SYSTEM REFRESH VIEW` you ran to start the cycle when you created it, and wait for that refresh. A graph that took more than one such refresh to start needs the same set again. Triggering a different member can leave the cycle idle, because a view resumes only once every one of its prerequisites has refreshed. Once triggered the cycle refreshes on its own, so the one-at-a-time pacing stops at the trigger.

<h2 id="what-this-means-for-your-automation">
  What this means for your automation
</h2>

While the setting is in place, an unplanned restart leaves every refreshable materialized view stopped, and a newly created view does not begin refreshing until it is started either. `RESTORE` is the exception for a view whose refreshes are not coordinated: it starts every such view it includes, and that view may then be immediately overdue and replay its source. A coordinated view is not started by the restore, so it stays stopped until you start it. Make whatever automation restarts your servers and creates your views responsible for starting them.