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.
pscale0.344.0 with a service token that can create databases,psql18.6,jq, andpython3:
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_itemstable holding 2,000,000 rows for 5,000 customers, sharded bycustomer_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-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale 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"