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

Reference

Run ALTER TABLE Through the Router and Wait for Every Router on Neki with the PlanetScale CLI

A native ALTER TABLE sent through a Neki router runs on every shard right away, but the other routers can briefly hold the old schema. This guide adds a column, waits with __neki.wait_for_ddl until every router has applied the change, confirms it per router, and shows what a router that has not caught up looks like.

What you will build

psql: ALTER TABLE ... (router A)        -> NOTICE: wait_for_ddl(<schema_version>, <cluster_version>)
psql: SELECT wait_for_ddl(...) FROM __neki.ddl_versions()   -> returns when routers A, B, C have the version
__neki.list_router_database_sync_states()                   -> applied_version per router
new connections (any router) -> queries that use the new column

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, run ALTER TABLE through a Neki router and make sure every router has applied the change before dependent queries run.

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.
- A native ALTER TABLE runs on every shard immediately and creates no workflow. The router prints a NOTICE naming the exact __neki.wait_for_ddl(schema_version, cluster_version) call to run. The NOTICE is not an error.
- On the same connection, read the versions with SELECT * FROM __neki.ddl_versions() and pass them to __neki.wait_for_ddl(schema_version, cluster_version, timeout). It returns when every router has applied that schema version, and raises an error that lists each lagging router when it times out.
- Confirm with __neki.list_router_database_sync_states(): every router for the database shows synced true and applied_version equal to truth_version.
- 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 an orders table holding 20,000 rows, sharded by customer_id. Create them with these commands:
pscale database create catalogdb --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create catalogdb 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 catalogdb 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 orders (customer_id bigint NOT NULL, order_id bigint NOT NULL, total_cents bigint NOT NULL, PRIMARY KEY (customer_id, order_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": "orders_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": {
    "orders": { "shard_group": "orders_by_customer" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update catalogdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10

python3 -c "
for i in range(1, 20001): print(f'{(i*7919)%2000+1},{i},{(i*31)%100000}')" > orders.csv
psql -c "\copy orders (customer_id, order_id, total_cents) FROM 'orders.csv' WITH (FORMAT csv)"

The COPY prints COPY 20000.

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

1. Run the ALTER TABLE

psql -At -c "ALTER TABLE orders ADD COLUMN channel text"
psql -At -c "SELECT count(*) FROM __neki.list_workflows()"

Real output:

NOTICE:  DDL is committed and the change is visible on this router; other routers may not see it yet. To wait until it is visible on every router, use __neki.wait_for_ddl(8, 0)
ALTER TABLE
0

The change is already on every shard. The NOTICE names the call that waits for the other routers, and no workflow was created.

2. Wait for every router

__neki.ddl_versions() reports the versions the current router has applied. Read them on the same connection as the ALTER TABLE and pass them to wait_for_ddl:

psql -At \
  -c "ALTER TABLE orders ADD COLUMN gift_note text" \
  -c "SELECT schema_version || ',' || cluster_version FROM __neki.ddl_versions()" \
  -c "SELECT __neki.wait_for_ddl(schema_version, cluster_version, interval '30 seconds') FROM __neki.ddl_versions()" \
  -c "SELECT 'every router has the change'" 2>&1

Real output:

NOTICE:  DDL is committed and the change is visible on this router; other routers may not see it yet. To wait until it is visible on every router, use __neki.wait_for_ddl(9, 0)
ALTER TABLE
9,29

every router has the change

wait_for_ddl returns an empty row when it succeeds. Each -c runs as its own statement on the same connection, so ddl_versions already includes the ALTER TABLE.

3. Confirm per router

psql -c "SELECT router_cell, applied_version, synced, truth_version FROM __neki.list_router_database_sync_states() WHERE database = 'postgres' ORDER BY router_cell"

Real output:

  router_cell   | applied_version | synced | truth_version 
----------------+-----------------+--------+---------------
 aws_useast1a_5 |               9 | t      |             9
 aws_useast1b_5 |               9 | t      |             9
 aws_useast1c_5 |               9 | t      |             9
(3 rows)

Each router reports the new schema version as applied.

4. Run dependent queries from new connections

Each psql call opens a new connection, which may land on any router:

ok=0
for c in $(seq 1 12); do
  psql -At -c "UPDATE orders SET gift_note = 'wrapped' WHERE customer_id = $c AND order_id = (SELECT min(order_id) FROM orders WHERE customer_id = $c)" >/dev/null 2>&1 && ok=$((ok+1))
done
echo "updates that used the new column: $ok of 12"
psql -At -c "SELECT count(gift_note) FROM orders WHERE customer_id BETWEEN 1 AND 12"

Real output:

updates that used the new column: 12 of 12
12

5. See what a lagging router looks like

Asking for a schema version that no router has reached yet times out and names every router with the version it has applied:

psql -At -c "SELECT __neki.wait_for_ddl(schema_version + 1000, cluster_version, interval '3 seconds') FROM __neki.ddl_versions()" 2>&1 | head -1

Real output (trimmed after the first router):

ERROR:  timed out waiting for DDL propagation (schema_version=1009, cluster_version=29 on database "postgres"); lagging routers: aws_useast1a_5-nkr-014yxltv7ip9-default-aws-useast1a-5-4f9c4bb4-687f6bff4hq7bk (applied schema version 9, cluster version 29), aws_useast1b_5-nkr-014yxltv7ip9-default-aws-useast1b-5-5b682050-fc58cf468xq9xw (applied schema version 9, cluster version 29), aws_useast1c_5-nkr-014yxltv7ip9-default-aws-useast1c-5-eebabde0-65659ff6fv2fnn (applied schema version 9, cluster version 29)

When a real change is slow to reach a router, the same error tells you which one is behind.

Verify the result

psql -At -c "SELECT bool_and(synced AND applied_version = truth_version) FROM __neki.list_router_database_sync_states() WHERE database = 'postgres'"
psql -At -c "SELECT count(gift_note) FROM orders WHERE customer_id BETWEEN 1 AND 12"

Expected: t, then 12.

How it works

The router that receives native DDL applies it to every shard in one step and then publishes a new schema version. Each router applies schema versions as it learns about them, and until it does, it plans queries against the old schema. wait_for_ddl blocks until every router reports at least the given schema version, and ddl_versions and list_router_database_sync_states expose the same version numbers for scripts and checks. In this guide every router caught up before the next statement ran, so the wait returned at once; the wait is what makes that true in every case rather than most cases. Native DDL is described on the Neki schema changes page.

Common issues

The NOTICE and ddl_versions show different cluster versions

In step 2 the NOTICE suggested __neki.wait_for_ddl(9, 0) while ddl_versions() returned 9,29. Both calls waited successfully. Use whichever is easier to read from your client.

The NOTICE reaches the client as a message, not a result

psql prints the NOTICE on standard error. Drivers deliver it through their notice handler, for example pgx's OnNotice. Reading ddl_versions() on the same connection avoids parsing it.

Not seeing any lag is not proof there is none

In testing, 36 queries on fresh connections right after three ALTER TABLE statements all saw the new column. Propagation was fast enough not to show up, which is not the same as guaranteed. Wait before running code that depends on the change.

The default timeout

Without the third argument, a wait_for_ddl for an unreachable version failed after about 31 seconds. Pass a timeout that fits your deployment step.

Next steps

  • Delete the database with pscale database delete catalogdb --force --org "$PLANETSCALE_ORG".
  • Read the Neki schema changes guide for which changes run natively and which 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 bool_and(synced AND applied_version = truth_version) FROM __neki.list_router_database_sync_states() WHERE database = 'postgres'"
  expected_result: "t, and the 12 updates that used the new column are all present."