Run an Online Schema Change Across Neki Shards with the PlanetScale CLI

This guide adds a NOT NULL DEFAULT column to a 400,000-row table that is sharded across two Neki shards, using __neki.online_ddl_create while a writer keeps inserting rows. The workflow builds a shadow table on each data-bearing shard, then a single workflow_complete call cuts over; the writer paused for about five to six seconds around cutover and lost no rows.

What you will build

A products table sharded by merchant_id that gains an availability column on every shard through a managed workflow, with checks that the column exists everywhere and no rows were lost.

online_ddl_create -> shadow table copy + stream on sh2, sh3  (sh1 waits to apply directly)
                  -> every shard ready -> workflow_complete -> cutover
                  -> online_ddl_cleanup (drops the retained original table)

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, add a column to a sharded table on a Neki database with a managed online schema change while writes continue.

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. The router lists shards before their primaries are healthy.
- Apply the topology before loading data, then bulk-load with COPY and include the shard key column. The statement timeout is 30 seconds.
- Create the workflow with __neki.online_ddl_create(workflow, database, ddl, migration_id). The database argument is the logical database, postgres. Double any single quotes inside the DDL string. Use an empty migration id to have one generated.
- Run a writer during the change and log every statement result.
- Do not complete the workflow until every shard reports current_readiness true. Complete is a separate call.
- After completion, verify the new column exists on every shard with SET __neki.shard, not only through the router. Then run online_ddl_cleanup.
- 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 (versions 18.6 and 16.15 both produced the documented output), 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, a sharded products table, and 400,000 rows. Create it with these commands:

pscale database create catalog --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create catalog 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 catalog 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 <<'SQL'
CREATE TABLE products (
  product_id bigint PRIMARY KEY,
  merchant_id bigint NOT NULL,
  sku text NOT NULL,
  price_cents integer NOT NULL
);
CREATE INDEX products_merchant_idx ON products (merchant_id);
SQL

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_merchant_id": { "type": "xxhash", "columns": ["merchant_id"] } },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "products_by_merchant", "default_shard_index": "xxhash_merchant_id",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": { "products": { "shard_group": "products_by_merchant" } } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update catalog main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10

python3 - > products.csv <<'PY'
import random
random.seed(11)
for i in range(1, 400001):
    print(f"{i},{random.randint(1,500)},SKU-{i:07d},{random.randint(100,99999)}")
PY
psql -c "\copy products (product_id, merchant_id, sku, price_cents) FROM 'products.csv' WITH (FORMAT csv)"
psql -At -c "ANALYZE products;" 2>&1 | grep -v NOTICE
for u in "$SHARD_2" "$SHARD_3" "$SHARD_1"; do
  echo -n "$u: "; psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM products;" | tail -1
done

The COPY prints COPY 400000. The per-shard counts are 192408 and 207592 on the two data shards and 0 on the authoritative shard, because the generator and the hash are deterministic. The shard ids differ per database.

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 catalog --force --org "$PLANETSCALE_ORG".

1. Start a writer

The writer inserts one product per statement with an explicit id above the loaded range and logs every outcome:

cat > writer.sh <<'EOF'
#!/bin/bash
n=1000000
while [ ! -f stop.flag ]; do
  n=$((n+1))
  out=$(psql -At -c "INSERT INTO products (product_id, merchant_id, sku, price_cents) VALUES ($n, $((RANDOM % 500 + 1)), 'LIVE-$n', 999);" 2>&1 | tail -1)
  echo "$(date -u +%T) $n $out" >> writer.log
  sleep 0.25
done
EOF
chmod +x writer.sh
rm -f stop.flag writer.log
nohup ./writer.sh >/dev/null 2>&1 &
sleep 5

2. Create the workflow

psql -x -c "SELECT * FROM __neki.online_ddl_create('add-availability','postgres','ALTER TABLE public.products ADD COLUMN availability text NOT NULL DEFAULT ''in_stock''','');"

Real output:

workflow     | add-availability
migration_id | 03h1dwuhszziv8ksk9hnc6lz0

Wait until every shard is ready, then read the per-shard status:

until [ "$(psql -At -c "SELECT bool_and((e.value->>'current_readiness')::boolean) FROM __neki.online_ddl_status('add-availability'), jsonb_each(status_by_shard) AS e")" = "t" ]; do sleep 5; done
psql -x -c "SELECT workflow, ddl, status, jsonb_pretty(status_by_shard) AS status_by_shard FROM __neki.online_ddl_status('add-availability');"

Real output, trimmed to one entry per kind of shard:

workflow        | add-availability
ddl             | ALTER TABLE public.products ADD COLUMN availability text NOT NULL DEFAULT 'in_stock'
status          | running
status_by_shard | {
                |     "sh2wm85donzyul": {
                |         "ddl_status": "running",
                |         "direct_apply": false,
                |         "migration_id": "03h1dwuhszziv8ksk9hnc6lz0",
                |         "controller_state": "running",
                |         "current_readiness": true
                |     },
                |     "shlpv5ue3tcfdc": {
                |         "ddl_status": "running",
                |         "direct_apply": true,
                |         "migration_id": "03h1dwuhszziv8ksk9hnc6lz0",
                |         "controller_state": "running",
                |         "current_readiness": true
                |     },
                |     ...

The two shards that hold products rows run Online DDL (direct_apply: false). The authoritative shard holds no rows for the table and applies the change directly (direct_apply: true).

3. Complete the workflow

date -u +%T.%N | cut -c1-12
psql -x -c "SELECT * FROM __neki.workflow_complete('add-availability');"
date -u +%T.%N | cut -c1-12
sleep 6
psql -x -c "SELECT workflow, status FROM __neki.online_ddl_status('add-availability');"

Real output. The completing call returned after about 3.9 seconds:

21:15:41.023
workflow_complete | 

21:15:44.897
workflow | add-availability
status   | completed

4. Check the writer

echo "writer outcomes:"; awk '{ $1=""; $2=""; print }' writer.log | sort | uniq -c
awk '{split($1,t,":"); s=t[1]*3600+t[2]*60+t[3]; if (NR>1 && s-prev>m) {m=s-prev; at=$1}; prev=s} END{print "largest gap between consecutive writer statements:", m, "s, ending", at}' writer.log

Real output:

writer outcomes:
  21   INSERT 0 1
largest gap between consecutive writer statements: 5 s, ending 21:15:49

Every statement succeeded. The longest pause between two statements was five seconds here and six seconds in another run, and it spans the completing call. The log timestamps have one-second resolution.

5. Look for the retained original table, then clean up

The router does not list the retained original table. Each data-bearing shard still has it until cleanup:

for u in "$SHARD_2" "$SHARD_3"; do
  echo -n "$u: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT tablename FROM pg_tables WHERE schemaname='public' ORDER BY 1;" | grep -v '^SET' | tr '\n' ' '; echo
done
psql -x -c "SELECT * FROM __neki.online_ddl_cleanup('add-availability');"
sleep 5
for u in "$SHARD_2" "$SHARD_3"; do
  echo -n "after cleanup $u: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT tablename FROM pg_tables WHERE schemaname='public' ORDER BY 1;" | grep -v '^SET' | tr '\n' ' '; echo
done

Real output:

shk65tcph46cn3: __neki__03h1dwuhszziv8ksk9hnc6lz0_000000_shdw_20261012211534_ products 
sh2wm85donzyul: __neki__03h1dwuhszziv8ksk9hnc6lz0_000000_shdw_20261012211534_ products 
after cleanup shk65tcph46cn3: products 
after cleanup sh2wm85donzyul: products

Verify the result

Stop the writer, then confirm the column exists on every shard, existing and new rows carry the default, and the row count matches the writer's successes:

sleep 20; touch stop.flag; sleep 3
OK=$(grep -c "INSERT 0 1" writer.log); echo "writer successes: $OK"
for u in "$SHARD_1" "$SHARD_2" "$SHARD_3"; do
  echo -n "$u: "
  psql -At -c "SET __neki.shard='$u';" -c "SELECT column_name||' '||data_type||' default='||column_default||' nullable='||is_nullable FROM information_schema.columns WHERE table_name='products' AND column_name='availability';" | tail -1
done
psql -At -c "SELECT count(*) AS total, count(*) FILTER (WHERE availability='in_stock') AS in_stock, count(*) FILTER (WHERE sku LIKE 'LIVE-%') AS live_rows FROM products;"
echo "expected total: $((400000 + OK))"

Real output (55 writer successes in the run):

writer successes: 55
shlpv5ue3tcfdc: availability text default='in_stock'::text nullable=NO
shk65tcph46cn3: availability text default='in_stock'::text nullable=NO
sh2wm85donzyul: availability text default='in_stock'::text nullable=NO
400055|400055|55
expected total: 400055

Expected: all three shards report the column as text, default 'in_stock', and nullable=NO; total equals in_stock and equals the expected total; live_rows equals the writer successes. The writer counts vary with how long the steps took.

How it works

online_ddl_create records the statement and starts work on every managed shard. Shards that hold rows for the table copy them into a shadow table that already has the new definition and stream ongoing changes into it, and shards that hold no rows for the table wait to apply the statement directly. When every shard reports ready, workflow_complete asks each shard to swap its table, with queries for that table buffered briefly, and each shard commits its own transaction. The original table is retained on each data-bearing shard until online_ddl_cleanup drops it. The Neki schema changes reference describes Online DDL, direct DDL, cancel and retry.

Common issues

The workflow record is gone after cleanup

After online_ddl_cleanup, online_ddl_status('add-availability') failed with ERROR: get workflow add-availability: node doesn't exist: /clusters/.../workflows/add-availability/Workflow. Read the final status before running cleanup.

The retained original table does not appear through the router

Through the router, pg_tables listed only products after completion. Querying each shard directly showed the retained __neki__..._shdw_... table, which uses disk until cleanup removes it.

Router lists shards before their primaries are healthy

In one run the router listed all three shards and the first DDL then failed with ERROR: no healthy sidecars available for shard ... with type SIDECAR_TYPE_PRIMARY. The prerequisites wait for a direct SELECT 1 on every shard before creating tables.

A single `INSERT ... SELECT` of many rows hits the statement timeout

SHOW statement_timeout returns 30s. Load large tables with COPY and include the shard key column. COPY of 400,000 rows into the sharded table took about 1.9 seconds.

Next steps

Reshard an existing table onto more shards: see "Reshard a Live Table onto New Neki Shards with the PlanetScale CLI".
Keep transactions on one shard: see "Keep Ledger Transactions on One Neki Shard with the PlanetScale CLI".
To run your own schema change, read the Neki schema changes guide.

verification:
  status: verified
  tested_at: "2026-10-12"
  product_version: "pscale 0.344.0; Neki PostgreSQL 18.6 (Platform Preview)"
  command: "query information_schema.columns for availability on each shard with SET __neki.shard, and compare SELECT count(*) FROM products with 400000 plus the writer successes"
  expected_result: "The availability column exists on every shard with default 'in_stock' and NOT NULL, all rows carry the default, and the total equals 400000 plus the writer successes. Writer counts and gap timings vary per run."