Backfilling Data

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.

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

Ok.

0 rows in set. Elapsed: 43.907 sec. Processed 1.50 billion rows, 33.48 GB (34.06 million rows/s., 762.54 MB/s.)
Peak memory usage: 272.53 MiB.
```

Finally, we can reduce memory further by setting `min_insert_block_size_rows` to 0 (disables it as a deciding factor on block size) and `min_insert_block_size_bytes` to 10485760 (10MiB).

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

0 rows in set. Elapsed: 43.293 sec. Processed 1.50 billion rows, 33.48 GB (34.54 million rows/s., 773.36 MB/s.)
Peak memory usage: 218.64 MiB.
```

Finally, be aware that lowering block sizes produces more parts and causes greater merge pressure. As discussed [here](/docs/integrations/s3/performance#be-aware-of-merges), these settings should be changed cautiously.

### No timestamp or monotonically increasing column[​](#no-timestamp-or-monotonically-increasing-column "Direct link to No timestamp or monotonically increasing column")

The above processes rely on the user have a timestamp or monotonically increasing column. In some cases this is simply not available. In this case, we recommend the following process, which exploits many of the steps outlined previously but requires users to pause ingest.

1. Pause inserts into your main table.
2. Create a duplicate of your main target table using the `CREATE AS` syntax.
3. Attach partitions from the original target table to the duplicate using [`ALTER TABLE ATTACH`](/docs/sql-reference/statements/alter/partition#attach-partitionpart). **Note:** This attach operation is different than the earlier used move. While relying on hard links, the data in the original table is preserved.
4. Create new materialized views.
5. Restart inserts. **Note:** Inserts will only update the target table, and not the duplicate, which will reference only the original data.
6. Backfill the materialized view, applying the same process used above for data with timestamps, using the duplicate table as the source.

Consider the following example using PyPI and our previous new materialized view `pypi_downloads_per_day` (we'll assume we can't use the timestamp):

```
SELECT count() FROM pypi

┌────count()─┐
│ 2039988137 │ -- 2.04 billion
└────────────┘

1 row in set. Elapsed: 0.003 sec.

-- (1) Pause inserts
-- (2) Create a duplicate of our target table

CREATE TABLE pypi_v2 AS pypi

SELECT count() FROM pypi_v2

┌────count()─┐
│ 2039988137 │ -- 2.04 billion
└────────────┘

1 row in set. Elapsed: 0.004 sec.

-- (3) Attach partitions from the original target table to the duplicate.

ALTER TABLE pypi_v2
 (ATTACH PARTITION tuple() FROM pypi)

-- (4) Create our new materialized views

CREATE TABLE pypi_downloads_per_day
(
    `hour` DateTime,
    `project` String,
    `count` Int64
)
ENGINE = SummingMergeTree
ORDER BY (project, hour)

CREATE MATERIALIZED VIEW pypi_downloads_per_day_mv TO pypi_downloads_per_day
AS SELECT
 toStartOfHour(timestamp) as hour,
 project,
    count() AS count
FROM pypi
GROUP BY
    hour,
 project

-- (4) Restart inserts. We replicate here by inserting a single row.

INSERT INTO pypi SELECT *
FROM pypi
LIMIT 1

SELECT count() FROM pypi

┌────count()─┐
│ 2039988138 │ -- 2.04 billion
└────────────┘

1 row in set. Elapsed: 0.003 sec.

-- notice how pypi_v2 contains same number of rows as before

SELECT count() FROM pypi_v2
┌────count()─┐
│ 2039988137 │ -- 2.04 billion
└────────────┘

-- (5) Backfill the view using the backup pypi_v2

INSERT INTO pypi_downloads_per_day SELECT
 toStartOfHour(timestamp) as hour,
 project,
    count() AS count
FROM pypi_v2
GROUP BY
    hour,
 project

0 rows in set. Elapsed: 3.719 sec. Processed 2.04 billion rows, 47.15 GB (548.57 million rows/s., 12.68 GB/s.)

DROP TABLE pypi_v2;
```

In the penultimate step we backfill `pypi_downloads_per_day` using our simple `INSERT INTO SELECT` approach described [earlier](#timestamp-or-monotonically-increasing-column-available). This can also be enhanced using the Null table approach documented [above](#using-a-null-table-engine-for-filling-materialized-views), with the optional use of a duplicate table for more resiliency.

While this operation does require inserts to be paused, the intermediate operations can typically be completed quickly - minimizing any data interruption.

- [Example dataset](#example-dataset)- [Backfilling scenarios](#backfilling-scenarios)- [Using duplicate tables and views](#using-duplicate-tables-and-views)- [Scenario 1: Backfilling data with existing data ingestion](#scenario-1-backfilling-data-with-existing-data-ingestion)- [Scenario 2: Adding materialized views to existing tables](#scenario-2-adding-materialized-views-to-existing-tables)
          - [Timestamp or Monotonically increasing column available](#timestamp-or-monotonically-increasing-column-available)- [No timestamp or monotonically increasing column](#no-timestamp-or-monotonically-increasing-column)

Was this page helpful?