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

Reference

Change a Column Type on a Large Sharded Table Without Downtime on Neki

This guide widens a column from integer to bigint on a 2,000,000-row line_items table sharded across two Neki shards, using an online DDL workflow while a writer keeps inserting. Each data shard copies its rows into a shadow table with the new type and swaps it in at cutover. In this guide no insert failed, and one insert waited 6.3 seconds at cutover.

What you will build

online_ddl_create(... ALTER COLUMN price_cents TYPE bigint ...)
  data shards: copy 1M rows each into a shadow table, stream new writes into it
  every shard ready -> workflow_complete -> cutover (a short pause for writes)
  online_ddl_cleanup -> drop the retained original tables

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, change a column type on a large sharded Neki table with an online DDL workflow while the application keeps writing.

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.
- Load the table with COPY and include the shard key. Statements that run on the shards are cancelled after 30 seconds by default, although SHOW statement_timeout through the router reports 0; SET statement_timeout overrides it.
- Run the change with __neki.online_ddl_create(workflow, 'postgres', 'ALTER TABLE public.<table> ALTER COLUMN <col> TYPE <type>', '') so it uses the copy path. Do not force apply_direct for a type change that rewrites rows.
- Wait until every shard in online_ddl_status(workflow).status_by_shard has current_readiness true, then call __neki.workflow_complete(workflow), then __neki.online_ddl_cleanup(workflow).
- Run a writer during the change and log every result and its latency.
- Verify the new type on every shard with SET __neki.shard, not only through the router.
- 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, sharded by customer_id. Create them with these commands:
pscale database create invoicing --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create invoicing 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 invoicing 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 invoicing 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 invoicing --force --org "$PLANETSCALE_ORG".

1. Start a writer

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, 1, 100)" 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
chmod +x writer.sh
rm -f stop.flag writer.log
nohup ./writer.sh >/dev/null 2>&1 &
sleep 5
tail -1 writer.log

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

22:11:17 5000004 685 INSERT 0 1

2. Create the workflow and wait for every shard

t0=$(date +%s)
psql -x -c "SELECT * FROM __neki.online_ddl_create('widen_price', 'postgres', 'ALTER TABLE public.line_items ALTER COLUMN price_cents TYPE bigint', '')"
until [ "$(psql -At -c "SELECT bool_and((e.value->>'current_readiness')::boolean) FROM __neki.online_ddl_status('widen_price'), jsonb_each(status_by_shard) e")" = "t" ]; do sleep 3; done
echo "ready after $(( $(date +%s) - t0 )) s"
psql -At -c "SELECT e.key || ' direct_apply=' || (e.value->>'direct_apply') || ' controller_state=' || (e.value->>'controller_state') FROM __neki.online_ddl_status('widen_price'), jsonb_each(status_by_shard) e ORDER BY 1"

Real output:

-[ RECORD 1 ]+--------------------------
workflow     | widen_price
migration_id | 03h4v8hweqht5df241zmfjtlm

ready after 9 s
sh1thjw467ivq7 direct_apply=true controller_state=running
sh2ew1h640xpaa direct_apply=false controller_state=running
sh3cu7piqkac2y direct_apply=false controller_state=running

The two data shards (sh2ew1h640xpaa and sh3cu7piqkac2y here) copied their rows (direct_apply=false). The authoritative shard holds no rows of this table and applied the change directly.

3. Complete and clean up

time psql -At -c "SELECT * FROM __neki.workflow_complete('widen_price')"
sleep 5; touch stop.flag; sleep 2
psql -At -c "SELECT * FROM __neki.online_ddl_cleanup('widen_price')"
echo "inserts=$(wc -l < writer.log) failed=$(grep -vc 'INSERT 0 1' writer.log)"
awk '{print $3}' writer.log | sort -n | tail -3 | tr '\n' ' '; echo

Real output (the last line is the three slowest inserts, in milliseconds):


real	0m2.464s
user	0m0.019s
sys	0m0.012s
widen_price
inserts=      16 failed=0
727 729 6274

No insert failed. One insert, made during the cutover, took 6.3 seconds; the others took between 670 and 729 milliseconds, which is the round trip from the client to the region.

Verify the result

for n in sh1 sh2 sh3; do
  u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
  echo -n "$n: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT format_type(atttypid, atttypmod) FROM pg_attribute WHERE attrelid = 'line_items'::regclass AND attname = 'price_cents'" | tail -1
done
psql -At -c "SELECT count(*) FROM line_items WHERE item_id <= 2000000"
psql -At -c "SELECT count(*) FROM line_items WHERE item_id > 5000000"
grep -c 'INSERT 0 1' writer.log

Expected: bigint on every shard, 2000000 original rows, and the count of writer rows equal to the number of successful inserts in writer.log (both 16 in this run).

How it works

On the copy path each data shard creates a shadow table with the new column type, copies its rows into it in batches, and applies writes that arrive during the copy. When every shard reports ready, workflow_complete holds writes briefly, applies the last changes and swaps the shadow table in, which is the pause the writer saw. The original table is kept, renamed, until online_ddl_cleanup drops it. A type change forced onto the direct path instead rewrites each shard's rows under a lock for as long as the rewrite takes. The workflow functions are described on the Neki schema changes page.

Common issues

A value does not fit the new type

Narrowing bigint to smallint with one row holding 70000 failed only on the shard with that row: [NK609] migration finished unexpectedly before cutover: ... value "70000" is out of range for type smallint. The other shards kept running. workflow_cancel removed the workflow and left the column type unchanged. Check the range of existing values before narrowing.

Foreign keys

A copy-path workflow on a table with a foreign key failed with foreign key in table line_items references non-existent table customers, and one on the referenced table was rejected because it is referenced by foreign key. Drop the key for the change, or use '{"apply_direct": true}' and accept the lock.

Forcing the direct path

On the same 2,000,000 rows, the same kind of type change with '{"apply_direct": true}' held the slowest concurrent insert for 2.3 seconds, compared with 0.86 seconds on 200,000 rows. That pause grows with rows per shard, while the copy path's cutover pause was between five and six and a half seconds at both sizes.

Native ALTER TABLE

Sending ALTER TABLE ... ALTER COLUMN ... TYPE directly through the router also works and creates no workflow. In testing it took 1.1 seconds on a 200,000-row table, and like the forced direct path its cost grows with rows per shard.

Next steps

  • Delete the database with pscale database delete invoicing --force --org "$PLANETSCALE_ORG".
  • Read the Neki schema changes guide for which changes use the copy path.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "per-shard format_type of line_items.price_cents; counts of original and writer rows"
  expected_result: "bigint on every shard, 2000000 original rows, and every successful writer insert present."