Backfilling geometry columns after a schema change

This page adds a geometry column to a table too large to rebuild: a batched backfill that can stop and resume, index creation timed so it does not slow every batch, and a progress model that tells you where the migration is rather than leaving it to a log file.

When to use this approach

• A full refresh would exceed the maintenance window. Below that, --full-refresh is simpler and safer; this is for tables where it is not an option.
• The new column derives from data already in the table. A projected copy, a grid key, a simplified geometry, a centroid — all derivable, all backfillable.
• The change must be reversible mid-flight. A batched backfill can be paused and the old column kept, which a rebuild cannot. The versioning discipline this fits into is versioning spatial schemas in dbt.

Prerequisites

• An incremental model with a unique_key, since the backfill writes through the same path.
• A monotonic or at least orderable key to batch on — an id range or a date.
• Free storage equal to the new column’s size, plus its index, before you start.
• Agreement on what consumers see during the migration: a half-populated column is visible unless you hide it.

Step-by-step instructions

1. Add the column without computing it

{{ config(
    materialized = 'incremental',
    unique_key = 'ping_id',
    on_schema_change = 'append_new_columns'
) }}

select
    ping_id,
    trip_id,
    observed_at,
    geom,
    cast(null as text) as h3_cell        -- declared now, populated later
from {{ ref('stg_trip_pings') }}
{% if is_incremental() %}
where observed_at > (select max(observed_at) from {{ this }})
{% endif %}

on_schema_change = 'append_new_columns' is what lets dbt add the column to the existing table instead of failing or rebuilding. New rows arriving from this point can populate it immediately; the history is the part that needs backfilling.

Verify the column exists and the incremental path still runs:

select column_name, data_type
from information_schema.columns
where table_name = 'int_pings_zoned' and column_name = 'h3_cell';

2. Backfill in batches, driven by a range

-- macros/backfill_h3_cell.sql
{% macro backfill_h3_cell(batch_days=7, max_batches=20) %}
  {% for i in range(max_batches) %}
    {% set sql %}
      with target as (
          select min(observed_at::date) as batch_start
          from {{ ref('int_pings_zoned') }}
          where h3_cell is null
      )
      update {{ ref('int_pings_zoned') }} p
      set h3_cell = {{ h3_cell('p.geom') }}
      from target t
      where p.h3_cell is null
        and p.observed_at >= t.batch_start
        and p.observed_at <  t.batch_start + interval '{{ batch_days }} days'
    {% endset %}
    {% set result = run_query(sql) %}
    {{ log("backfill batch " ~ (i + 1) ~ " complete", info=True) }}
  {% endfor %}
{% endmacro %}

dbt run-operation backfill_h3_cell --args '{batch_days: 7, max_batches: 20}'

Batching by a date range rather than by LIMIT matters: an unbounded UPDATE on a large table takes one long transaction, holds locks for its duration, and produces dead tuples equal to the rows it touched. Seven-day batches keep each transaction short and let autovacuum keep pace — the tuning for which is in clustering and vacuuming large PostGIS tables.

Verify progress between runs:

select
    count(*) filter (where h3_cell is null) as remaining,
    count(*)                                as total,
    round(100.0 * count(*) filter (where h3_cell is not null) / count(*), 2) as pct_done,
    min(observed_at) filter (where h3_cell is null) as next_batch_start
from {{ ref('int_pings_zoned') }};

3. Index after the backfill, not before

An index on a column being written to slows every batch and bloats as it goes. Create it once, when the column is complete.

# After the last batch reports zero remaining
psql -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS int_pings_zoned_h3_idx
         ON analytics.int_pings_zoned (h3_cell);"
psql -c "ANALYZE analytics.int_pings_zoned;"

CONCURRENTLY avoids the write lock that a plain CREATE INDEX takes, at the cost of a slower build and a second pass — the right trade on a table that is still receiving inserts.

Verify the index is valid, because a concurrent build that fails leaves an invalid index behind:

select indexrelid::regclass as index_name, indisvalid
from pg_index where indrelid = 'analytics.int_pings_zoned'::regclass;
-- indisvalid must be true; drop and rebuild any index where it is false

4. Make the migration resumable and observable

-- models/ops/int_backfill_progress.sql
{{ config(materialized = 'view') }}

select
    'int_pings_zoned.h3_cell'                            as migration,
    count(*) filter (where h3_cell is null)              as rows_remaining,
    count(*)                                             as rows_total,
    round(100.0 * count(*) filter (where h3_cell is not null) / nullif(count(*), 0), 2) as pct_complete,
    min(observed_at) filter (where h3_cell is null)      as oldest_unfilled,
    max(observed_at) filter (where h3_cell is null)      as newest_unfilled
from {{ ref('int_pings_zoned') }}

Because every batch selects its own start from the data, the operation is idempotent and resumable: interrupt it, run it again, and it continues from wherever it stopped. There is no cursor to keep and nothing to reset after a failure.

Verify resumability deliberately — run five batches, stop, and confirm the next invocation picks up at oldest_unfilled rather than starting over.

5. Retire the old column deliberately

Once the new column is complete and consumers have moved, the old one is dead weight — but dropping it is the irreversible step, so it gets its own change.

-- Only after: backfill complete, consumers migrated, one full retention period elapsed
alter table analytics.int_pings_zoned drop column legacy_geohash;

models:
  - name: int_pings_zoned
    columns:
      - name: legacy_geohash
        description: >
          DEPRECATED 2026-08-11, removal planned 2026-11-11. Superseded by h3_cell.
          Consumers: operations map (migrated), export job (pending).

Verify nothing still reads it before dropping — check exposures, check query history for the column name, and give the deprecation a date rather than an intention.

Configuration reference

Gotchas & edge cases

• on_schema_change does not backfill. It adds the column and leaves it null for existing rows; that is the entire reason this page exists.
• An UPDATE rewrites the whole row, geometry included. Backfilling a small text column on a table with a large geometry column still moves the geometry, which is why the batches matter and why storage grows during the migration.
• A batch that finds no rows still costs a scan. Bound the loop with max_batches and check the progress view rather than looping until empty.
• Consumers see nulls in the middle region. Either state that in the column description with the migration window, or expose a view filtered to the completed range until it closes.
• sync_all_columns will drop columns you removed from the model. Use append_new_columns during a migration; the stricter setting belongs after it, when the shape is stable.

FAQ

Related

• Versioning Spatial Schemas in dbt — the change discipline this fits into.
• Tracking Spatial Schema Changes Across Environments — keeping environments in step during a migration.
• Incremental Materialization for Large Geometry Tables — the model shape being migrated.

Up: Part of Versioning Spatial Schemas in dbt.