Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Backfill a New Column in Batches While Serving Traffic on Neki

This guide adds a total_cents column to a 2,000,000-row line_items table on a sharded Neki database and fills it in batches of 100 customers while an application keeps inserting new rows and updating existing ones. Batching by the shard key keeps each UPDATE short, so an application update that touches a row the backfill is working on waits for one batch, not for the whole table.

What you will build

1. ALTER TABLE ... ADD COLUMN total_cents bigint        (native, catalog only)
2. application writes total_cents on every new row
3. for each 100 customers: UPDATE ... WHERE customer_id IN (...) AND total_cents IS NULL
4. SELECT count(*) WHERE total_cents IS NULL  -> 0

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, add a column to a large sharded Neki table and backfill it in batches while the application keeps serving traffic.

Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- Add the column as a nullable column without a default with native ALTER TABLE, then wait for every router with __neki.wait_for_ddl using the values from __neki.ddl_versions().
- Change the application to write the new column on every insert before the backfill starts, or the backfill misses rows inserted during it.
- Backfill with UPDATE ... WHERE customer_id IN (<batch of shard keys>) AND <col> IS NULL, one batch per statement, in autocommit. The statement timeout is 30 seconds.
- Run the backfill with default session settings: a batch that spans shards is rejected under __neki.tx_mode = 'single' or __neki.fanout = 'single'.
- Keep an application writer and updater running and log each result and its latency.
- Run the verification step below before finishing.

Prerequisites

  • A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.

  • pscale 0.344.0 with a service token that can create databases, psql 18.6, jq, and python3:

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
  • A Neki database with three shards and a line_items table holding 2,000,000 rows for 5,000 customers, sharded by customer_id. Create them with these commands:
pscale database create metering --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create metering main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
       PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done

pscale branch shard create metering main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
  until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE line_items (customer_id bigint NOT NULL, item_id bigint NOT NULL, qty integer NOT NULL, price_cents integer NOT NULL, PRIMARY KEY (customer_id, item_id))" \
  2>&1 | grep -v NOTICE

SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")

cat > data-topology.json <<JSON
{
  "shard_indexes": { "xxhash_customer_id": { "type": "xxhash", "columns": ["customer_id"] } },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_customer", "default_shard_index": "xxhash_customer_id",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "line_items": { "shard_group": "by_customer" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update metering main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10

python3 -c "
import sys
w = sys.stdout.write
for i in range(1, 2000001): w(f'{(i*7919)%5000+1},{i},{i%7+1},{(i*13)%50000}\n')" > items.csv
psql -c "\copy line_items FROM 'items.csv' WITH (FORMAT csv)"

The COPY prints COPY 2000000.

Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster. PS-10-AWS-ARM-NEKI lists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish with pscale database delete metering --force --org "$PLANETSCALE_ORG".

1. Add the column and wait for every router

psql -At \
  -c "ALTER TABLE line_items ADD COLUMN total_cents bigint" \
  -c "SELECT __neki.wait_for_ddl(schema_version, cluster_version, interval '30 seconds') FROM __neki.ddl_versions()" 2>&1 | grep -v NOTICE
psql -At -c "SELECT count(*) FROM line_items WHERE total_cents IS NULL"

Real output (the empty line is wait_for_ddl returning):

ALTER TABLE

2000000

A nullable column without a default is a catalog change, so this is quick at any table size. Every existing row now has NULL in it.

2. Start the application

The writer stands in for the new application version: it inserts rows with total_cents already filled. The updater stands in for existing traffic: it updates one existing row at a time, chosen across the whole table. Both log each result with its latency in milliseconds.

cat > writer.sh <<'EOF'
#!/bin/bash
n=5000000
while [ ! -f stop.flag ]; do
  n=$((n+1))
  t0=$(python3 -c 'import time; print(int(time.time()*1000))')
  out=$(psql -At -c "INSERT INTO line_items VALUES ($((n % 5000 + 1)), $n, 2, 150, 300)" 2>&1 | head -1)
  t1=$(python3 -c 'import time; print(int(time.time()*1000))')
  echo "$(date -u +%T) $n $((t1 - t0)) $out" >> writer.log
  sleep 0.2
done
EOF
cat > updater.sh <<'EOF'
#!/bin/bash
i=0
while [ ! -f stop.flag ]; do
  i=$((i+1)); item=$(( (i * 104729) % 2000000 + 1 )); c=$(( (item * 7919) % 5000 + 1 ))
  t0=$(python3 -c 'import time; print(int(time.time()*1000))')
  out=$(psql -At -c "UPDATE line_items SET qty = qty WHERE customer_id = $c AND item_id = $item" 2>&1 | head -1)
  t1=$(python3 -c 'import time; print(int(time.time()*1000))')
  echo "$(date -u +%T) $item $((t1 - t0)) $out" >> updater.log
  sleep 0.2
done
EOF
chmod +x writer.sh updater.sh
rm -f stop.flag writer.log updater.log
nohup ./writer.sh >/dev/null 2>&1 &
nohup ./updater.sh >/dev/null 2>&1 &
sleep 10
tail -1 writer.log; tail -1 updater.log

Real output (time, id, latency in milliseconds, result):

22:15:29 5000010 642 INSERT 0 1
22:15:30 1047291 652 UPDATE 1

3. Backfill in batches of 100 customers

t0=$(date +%s)
for start in $(seq 1 100 5000); do
  ids=$(seq $start $((start + 99)) | paste -sd, -)
  psql -At -c "UPDATE line_items SET total_cents = qty * price_cents WHERE customer_id IN ($ids) AND total_cents IS NULL" > batch.out 2>&1 \
    || { echo "batch $start failed: $(head -1 batch.out)"; break; }
  [ $(( (start - 1) % 1000 )) = 0 ] && echo "customers $start-$((start + 99)): $(cat batch.out)"
done
echo "backfill took $(( $(date +%s) - t0 )) s"

Real output (every tenth batch):

customers 1-100: UPDATE 40000
customers 1001-1100: UPDATE 40000
customers 2001-2100: UPDATE 40000
customers 3001-3100: UPDATE 40000
customers 4001-4100: UPDATE 40000
backfill took 39 s

Each batch updates about 40,000 rows across both data shards in its own short transaction. The total_cents IS NULL condition makes a rerun skip rows that are already filled, so a failed batch can be retried.

4. Check the application was not held up

sleep 3; touch stop.flag; sleep 2
for f in writer updater; do
  echo "$f: ops=$(wc -l < $f.log) failed=$(grep -vcE 'INSERT 0 1|UPDATE 1' $f.log) max_ms=$(awk '{print $3}' $f.log | sort -n | tail -1) median_ms=$(awk '{print $3}' $f.log | sort -n | awk '{a[NR]=$1} END {print a[int((NR+1)/2)]}')"
done

Real output:

writer: ops=      54 failed=0 max_ms=761 median_ms=651
updater: ops=      54 failed=0 max_ms=735 median_ms=654

No insert or update failed. The slowest application update took 735 milliseconds against a median of 654, which is the round trip from the client to the region, so no update waited noticeably for a batch.

Verify the result

psql -At -c "SELECT count(*) FROM line_items WHERE total_cents IS NULL"
psql -At -c "SELECT count(*) FROM line_items WHERE total_cents <> qty * price_cents"

Expected: 0 and 0. Every row, including those the writer added during the backfill, has total_cents, and every value matches its source columns.

How it works

Each batch names a set of shard keys, so the router sends the UPDATE only to the shards that hold those customers, and each shard updates and commits its rows in one short transaction. Postgres row locks last until commit, so an application update to a row in the current batch waits at most one batch. One UPDATE over the whole table would hold every row lock until the last row is written. The batch loop is ordinary SQL; nothing in it is Neki-specific apart from choosing batches by shard key. Native DDL and waiting for routers are described on the Neki schema changes page.

Common issues

One statement for the whole table

In testing on the same table, UPDATE line_items SET ... WHERE <col> IS NULL for all 2,000,000 rows finished in about 9 seconds, under the 30-second statement timeout, but one application update to an existing row waited 5.4 seconds for it to commit. The batched run took longer in total, and no application update waited more than 0.9 seconds.

Strict session settings reject the batches

With __neki.tx_mode = 'single', a batch for customers on two shards failed with forbids a transaction from touching more than one shard. With __neki.fanout = 'single' it failed with statement's fan-out (multi) exceeds __neki.fanout (single). Run the backfill job without those settings, or batch one shard's customers at a time.

Rows inserted during the backfill stay empty

When the application did not write the new column, rows it inserted after the backfill had passed their customer were left NULL. Ship the application change first, then backfill, and check the IS NULL count at the end.

Building the batch list on macOS

seq -s, 1 100 on macOS ends the list with a trailing comma, which makes the IN (...) a syntax error. seq 1 100 | paste -sd, - produces a clean list on macOS and Linux.

Next steps

  • Delete the database with pscale database delete metering --force --org "$PLANETSCALE_ORG".
  • Read the Neki schema changes guide for which constraint changes run as online DDL workflows.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "SELECT count(*) FROM line_items WHERE total_cents IS NULL; SELECT count(*) FROM line_items WHERE total_cents <> qty * price_cents"
  expected_result: "0 and 0"