Backfilling Data

In this case, users have several options:

1. Modify your query to backfill ranges e.g. `WHERE timestamp BETWEEN 2024-12-17 08:00:00 AND 2024-12-17 09:00:00`, `WHERE timestamp BETWEEN 2024-12-17 07:00:00 AND 2024-12-17 08:00:00` etc.
2. Use a [Null table engine](/docs/engines/table-engines/special/null) to fill the materialized view. This replicates the typical incremental population of a materialized view, executing it's query over blocks of data (of configurable size).

(1) represents the simplest approach is often sufficient. We don't include examples for brevity.

We explore (2) further below.

#### Using a Null table engine for filling materialized views[​](#using-a-null-table-engine-for-filling-materialized-views "Direct link to Using a Null table engine for filling materialized views")

The [Null table engine](/docs/engines/table-engines/special/null) provides a storage engine which doesn't persist data (think of it as the `/dev/null` of the table engine world). While this seems contradictory, materialized views will still execute on data inserted into this table engine. This allows materialized views to be constructed without persisting the original data - avoiding I/O and the associated storage.

Importantly, any materialized views attached to the table engine still execute over blocks of data as its inserted - sending their results to a target table. These blocks are of a configurable size. While larger blocks can potentially be more efficient (and faster to process), they consume more resources (principally memory). Use of this table engine means we can build our materialized view incrementally i.e. a block at a time, avoiding the need to hold the entire aggregation in memory.

  

Consider the following example:

```
CREATE TABLE pypi_v2
(
    `timestamp` DateTime,
    `project` String
)
ENGINE = Null

CREATE MATERIALIZED VIEW pypi_downloads_per_day_mv_v2 TO pypi_downloads_per_day
AS SELECT
 toStartOfHour(timestamp) as hour,
 project,
    count() AS count
FROM pypi_v2
GROUP BY
    hour,
 project
```

Here, we create a Null table, `pypi_v2,` to receive the rows that will be used to build our materialized view. Note how we limit the schema to only the columns we need. Our materialized view performs an aggregation over rows inserted into this table (one block at a time), sending the results to our target table, `pypi_downloads_per_day`.

Note

We have used `pypi_downloads_per_day` as our target table here. For additional resiliency, users could create a duplicate table, `pypi_downloads_per_day_v2`, and use this as the target table of the view, as shown in previous examples. On completion of the insert, partitions in `pypi_downloads_per_day_v2` could, in turn, be moved to `pypi_downloads_per_day.` This would allow recovery in the case our insert fails due to memory issues or server interruptions i.e. we just truncate `pypi_downloads_per_day_v2`, tune settings, and retry.

To populate this materialized view, we simply insert the relevant data to backfill into `pypi_v2` from `pypi.`

```
INSERT INTO pypi_v2 SELECT timestamp, project FROM pypi WHERE timestamp < '2024-12-17 09:00:00'

0 rows in set. Elapsed: 27.325 sec. Processed 1.50 billion rows, 33.48 GB (54.73 million rows/s., 1.23 GB/s.)
Peak memory usage: 639.47 MiB.
```

Notice our memory usage here is `639.47 MiB`.

##### Tuning performance & resources[​](#tuning-performance--resources "Direct link to Tuning performance & resources")

Several factors will determine the performance and resources used in the above scenario. Before attempting to tune, we recommend readers understand the insert mechanics documented in detail in the [Using Threads for Reads](/docs/integrations/s3/performance#using-threads-for-reads) section of the [Optimizing for S3 Insert and Read Performance guide](/docs/integrations/s3/performance). In summary:

- **Read Parallelism** - The number of threads used to read. Controlled through [`max_threads`](/docs/operations/settings/settings#max_threads). In ClickHouse Cloud this is determined by the instance size with it defaulting to the number of vCPUs. Increasing this value may improve read performance at the expense of greater memory usage.
- **Insert Parallelism** - The number of insert threads used to insert. Controlled through [`max_insert_threads`](/docs/operations/settings/settings#max_insert_threads). **Note**: This value is capped by `max_threads`, so the effective insert parallelism is `min(max_insert_threads, max_threads)`. In ClickHouse Cloud this is determined by the instance size (between 2 and 4) and is set to 1 in OSS. Increasing this value may improve performance at the expense of greater memory usage.
- **Insert Block Size** - data is processed in a loop where it is pulled, parsed, and formed into in-memory insert blocks based on the [partitioning key](/docs/engines/table-engines/mergetree-family/custom-partitioning-key). These blocks are sorted, optimized, compressed, and written to storage as new [data parts](/docs/parts). The size of the insert block, controlled by settings [`min_insert_block_size_rows`](/docs/operations/settings/settings#min_insert_block_size_rows) and [`min_insert_block_size_bytes`](/docs/operations/settings/settings#min_insert_block_size_bytes) (uncompressed), impacts memory usage and disk I/O. Larger blocks use more memory but create fewer parts, reducing I/O and background merges. These settings represent minimum thresholds (whichever is reached first triggers a flush).
- **Materialized view block size** - As well as the above mechanics for the main insert, prior to insertion into materialized views, blocks are also squashed for more efficient processing. The size of these blocks is determined by the settings [`min_insert_block_size_bytes_for_materialized_views`](/docs/operations/settings/settings#min_insert_block_size_bytes_for_materialized_views) and [`min_insert_block_size_rows_for_materialized_views`](/docs/operations/settings/settings#min_insert_block_size_rows_for_materialized_views). Larger blocks allow more efficient processing at the expense of greater memory usage. By default, these settings revert to the values of the source table settings [`min_insert_block_size_rows`](/docs/operations/settings/settings#min_insert_block_size_rows) and [`min_insert_block_size_bytes`](/docs/operations/settings/settings#min_insert_block_size_bytes), respectively.

Note

**Tip for trivial INSERT SELECT queries**: For simple `INSERT INTO t1 SELECT * FROM t2` queries without complex transformations, consider enabling `optimize_trivial_insert_select=1`. This setting (disabled by default since version 24.7) automatically adjusts the SELECT parallelism to match `max_insert_threads`, reducing resource usage and the number of parts created. This is particularly useful for bulk data migrations between tables.

For improving performance, you can follow the guidelines outlined in the [Tuning Threads and Block Size for Inserts](/docs/integrations/s3/performance#tuning-threads-and-block-size-for-inserts) section of the [Optimizing for S3 Insert and Read Performance guide](/docs/integrations/s3/performance). It shouldn't be necessary to also modify `min_insert_block_size_bytes_for_materialized_views` and `min_insert_block_size_rows_for_materialized_views` to improve performance in most cases. If these are modified, use the same best practices as discussed for `min_insert_block_size_rows` and `min_insert_block_size_bytes`.

To minimize memory, you may wish to experiment with these settings. This will invariably lower performance. Using the earlier query, we show examples below.

Lowering `max_insert_threads` to 1 reduces our memory overhead.

```
INSERT INTO pypi_v2
SELECT
    timestamp,
 project
FROM pypi
WHERE timestamp < '2024-12-17 09:00:00'
SETTINGS max_insert_threads = 1

0 rows in set. Elapsed: 27.752 sec. Processed 1.50 billion rows, 33.48 GB (53.89 million rows/s., 1.21 GB/s.)
Peak memory usage: 506.78 MiB.
```

We can lower memory further by reducing our `max_threads` setting to 1.