Key Takeaways

• A write into compressed chunks triggers decompression. Its disk cost depends on how many compressed batches and chunks the write touches, not on how many rows change.
• Batch the backfill in windows no larger than one chunk interval, and give every statement both a start and an end time.
• After each batch, run VACUUM (ANALYZE), recompress the affected chunk, and check disk usage before starting the next batch.
• Keep timescaledb.max_tuples_decompressed_per_dml_transaction enabled, so an unbounded write fails with an error instead of decompressing the table’s history.
• Before you start, check your TimescaleDB version and the compression policy schedule.

Teams often need to backfill historical data into TimescaleDB hypertables, and backfilling into chunks that are already compressed needs more care than backfilling into uncompressed ones. Even a backfill that looks small, such as a single INSERT or UPDATE covering a narrow date range, can spike disk usage, delay compression work, and leave a hypertable partially compressed for an extended period.

In one production case, the problem was not the number of records being changed. The backfill touched more compressed chunks than the available disk could accommodate. As disk usage climbed, the database had less room to complete the remaining work safely, and the compression policy on the hypertable failed and unscheduled itself, leaving a large volume of chunks uncompressed for weeks. The backfill was not unusually large by business standards. It was simply not scoped, bounded, or sequenced around how compression works.

Our earlier guide to TimescaleDB backfills covered manual decompress-and-recompress methods, which recent TimescaleDB releases no longer recommend. This article explains why compressed backfills cause these problems and lays out a field-tested workflow for writing into compressed chunks without putting production disk space at risk.

How Compression Changes the Risk Profile of a Backfill

Compressed chunks store data in a format that is not directly writable. When a backfill inserts or updates data in a compressed chunk, TimescaleDB decompresses the data needed to apply the change. A selective query may affect only a small number of compressed batches, while a broadly scoped query can involve many more. Disk usage also depends on the data layout and compression settings.

TimescaleDB has supported inserting into compressed chunks since version 2.3, and update and delete support followed in version 2.11. For these operations, TimescaleDB attempts to decompress only the data required to apply the change.

The query filter still matters. If an UPDATE or DELETE has no usable filter, it can convert a large amount of compressed data to the rowstore before recompressing it. (In recent TimescaleDB releases, compressed storage is called the columnstore and uncompressed storage the rowstore, which is why the recompression function used later in this workflow is named convert_to_columnstore.) Where possible, filter on the columns used for segmentby and orderby to limit the amount of data that must be decompressed.

This is why scoping matters during planning. A statement that changes only a few rows can still require significant storage and I/O because those rows may be stored across many compressed batches. Before running a backfill, you need to know which compressed TimescaleDB chunks fall within the selected time range.

Automatic decompression still consumes disk space. When a write touches a compressed segment, that segment is decompressed to disk before it can be modified, and the old compressed data plus the relevant write-ahead log records remain in place until the transaction commits and the table is vacuumed. A single update touching a handful of rows is not a problem.

The main factor is the scope of the operation. A write limited to one chunk is easier to contain, while the same write across several chunks can require substantially more temporary disk space. Keeping a batch within a known chunk boundary makes its storage impact easier to predict.

A backfill that touches many chunks at once, or a query whose time filter is wider than intended, can decompress a large portion of a hypertable inside one transaction. The resulting disk spike is the same as a fully manual decompression in earlier versions of TimescaleDB.

Recommended Workflow for Backfilling Compressed TimescaleDB Data

The workflow has two parts: checks you run once before the backfill starts, and steps you repeat for every batch.

Before You Start

1. Always Bound Both the Start and End of the Time Interval

A backfill should only change data from a defined period. Every batch needs both a start time and an end time.

Using only a start time leaves the query open-ended. For example, a condition such as time >= '2024-01-01' can continue matching every chunk created after January 1, even if the backfill was only meant to cover one week.

On a compressed hypertable, that can cause TimescaleDB to decompress chunks that were not part of the intended backfill. Providing both time boundaries keeps each batch limited to the required data.

-- never leave the end of the range open or NULL
WHERE time >= :start_ts AND time < :end_ts

2. Confirm the Decompression Safety Limit Is Not Disabled

TimescaleDB includes a safety limit that is easy to overlook and, in practice, easy to accidentally disable. The setting timescaledb.max_tuples_decompressed_per_dml_transaction caps how many rows a single write operation (INSERT, UPDATE, or DELETE) can decompress at once, and it defaults to 100,000 rows. When a write exceeds this limit, TimescaleDB raises an error instead of quietly decompressing the rest of the table.

It is common to disable this setting after it blocks a large write and then forget to restore it. It can also be disabled through a shared configuration setting without anyone noticing. Without this limit, nothing stops a poorly scoped backfill from decompressing far more data than intended. Checking the setting before the backfill gives you a chance to correct it before disk usage becomes a problem.

Confirm this limit is not disabled on your deployment, and keep it at its default or another reasonable non-zero value, so that an unbounded write fails with an error. If a valid backfill reaches the limit, reduce the batch size and run the work in smaller time ranges. Do not disable the limit simply to allow one large statement to complete.

3. Stay on a Recent TimescaleDB Version

TimescaleDB has improved the way compressed chunks are handled during data modification. For example, TimescaleDB 2.16 filters compressed data before decompression for UPDATE and DELETE operations, reducing unnecessary work when rows do not match the query. For inserts with unique constraints, it can use index scans to check constraints more efficiently. Later releases also improved repeated writes to the same compressed chunk and added batch-level optimizations for deletes.

These changes can reduce the amount of work required for a backfill, but the benefit depends on the TimescaleDB version, the query, and the compression settings in use.

Before scheduling a backfill, check the TimescaleDB version running in the target environment. Review the release notes for the versions between your installation and the current stable release, then test any relevant upgrade in a non-production environment before applying it in production.

4. Check the Compression Policy Schedule

Compression policies run on a configured schedule. If a backfill touches many chunks within a single scheduling window, all of those chunks can sit uncompressed until the next scheduled run, which recreates the disk usage risk that batching was meant to avoid.

Check the compression policy schedule before starting the backfill. Waiting for the next scheduled run is reasonable only if it is due soon and there is enough free disk space to keep the affected chunks uncompressed until then. For a longer backfill, recompress each completed batch (step 7) or temporarily shorten the compression job’s schedule interval so it runs more often. This prevents uncompressed chunks from accumulating while the remaining batches are processed.

For Each Batch

5. Batch the Backfill by Time Range or Chunk

Do not run one large insert or update across many chunks in a single transaction. For example, if a hypertable uses a seven-day chunk interval, batching the backfill in windows of seven days or less aligns each batch to roughly one chunk and keeps each individual decompression small and bounded. It also makes the backfill resumable: if something goes wrong partway through, only the current batch is affected, rather than the entire operation.

-- run once per 7-day window, in a loop, instead of one large UPDATE/INSERT
UPDATE time_observation_values
SET sub_variant_id = :new_value
WHERE observation_id = :obs_id
AND time >= '2024-01-01' AND time < '2024-01-08';

6. Run VACUUM (ANALYZE) After Each Batch

After each batch has committed, run VACUUM (ANALYZE) before starting the next time range. VACUUM makes space from dead row versions available for reuse, while ANALYZE refreshes planner statistics.

VACUUM (ANALYZE) time_observation_values;

7. Recompress Each Batch

After vacuuming each batch, call convert_to_columnstore on the affected chunk directly instead of waiting for the next scheduled run. Alternatively, if you shortened the compression job’s schedule interval in step 4, let the job recompress the batch.

-- run immediately after VACUUMing each batch, instead of waiting for the next scheduled run
CALL convert_to_columnstore('<chunk>'::regclass, if_not_columnstore => false, recompress => true);

8. Monitor Disk Usage Between Batches

Even with careful batching, it is worth watching disk usage as the backfill progresses, so an unexpected spike is caught early. A simple check between batches is enough:

SELECT pg_size_pretty(pg_database_size(current_database()));