This guide removes a NOT NULL column from a live sharded table on Neki without breaking either version of the application. Step one relaxes the column so the new application version can stop writing it; step two drops it once nothing reads it. Both steps are native DDL through the router, followed by a wait until every router has the change.
What you will build
old app: reads and writes legacy_code
step 1: ALTER COLUMN legacy_code DROP NOT NULL -> new app can insert without it
deploy new app, stop old app
step 2: ALTER TABLE ... DROP COLUMN legacy_code -> wait_for_ddl
old app query after step 2: column "legacy_code" does not exist
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, retire a NOT NULL column from a live sharded Neki table in two steps.
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.
- Step 1: ALTER TABLE ... ALTER COLUMN <col> DROP NOT NULL as native DDL, then wait for every router with __neki.wait_for_ddl using the values from __neki.ddl_versions(). Only then deploy the application version that no longer reads or writes the column.
- Step 2, after no running code references the column: ALTER TABLE ... DROP COLUMN <col> as native DDL, then wait for every router again.
- Keep the new application version writing during step 2 and log every result.
- Confirm the column is gone on every shard with SET __neki.shard.
- 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, andjq:
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
accountstable with 5,000 rows, sharded bycustomer_id, whoselegacy_codecolumn isNOT NULL. Create them with these commands:
pscale database create crm --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create crm 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 crm 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 accounts (customer_id bigint PRIMARY KEY, plan text NOT NULL, legacy_code text NOT NULL)" \
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": {
"accounts": { "shard_group": "by_customer" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update crm main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
psql -At -c "INSERT INTO accounts SELECT g, 'basic', 'L' || g FROM generate_series(1, 5000) g"
The insert prints INSERT 0 5000.
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 crm --force --org "$PLANETSCALE_ORG".
1. See why the column cannot simply be dropped
The new application version no longer sets legacy_code. Against the current schema, its inserts fail:
psql -At -c "INSERT INTO accounts (customer_id, plan) VALUES (900001, 'pro')" 2>&1 | head -1
Real output:
ERROR: null value in column "legacy_code" of relation "accounts" violates not-null constraint
The old application version still reads the column, so dropping it first would break the old version instead. The two steps below avoid both failures.
2. Step 1: relax the column
psql -At \
-c "ALTER TABLE accounts ALTER COLUMN legacy_code DROP NOT NULL" \
-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 "INSERT INTO accounts (customer_id, plan) VALUES (900001, 'pro')"
psql -At -c "SELECT legacy_code FROM accounts WHERE customer_id = 7"
Real output:
ALTER TABLE
INSERT 0 1
L7
Both versions now work: the new version's insert succeeds, and the old version's read still finds the column.
3. Deploy the new version
The new version inserts accounts without legacy_code. Start it, and leave it running through step 2:
cat > newapp.sh <<'EOF'
#!/bin/bash
n=910000
while [ ! -f stop.flag ]; do
n=$((n+1))
out=$(psql -At -c "INSERT INTO accounts (customer_id, plan) VALUES ($n, 'pro')" 2>&1 | head -1)
echo "$(date -u +%T) $n $out" >> newapp.log
sleep 0.2
done
EOF
chmod +x newapp.sh
rm -f stop.flag newapp.log
nohup ./newapp.sh >/dev/null 2>&1 &
sleep 5
tail -1 newapp.log
Real output:
22:13:42 910004 INSERT 0 1
Before the next step, retire every instance of the old version, including workers, scheduled jobs and reports that select the column.
4. Step 2: drop the column
time psql -At \
-c "ALTER TABLE accounts DROP COLUMN legacy_code" \
-c "SELECT __neki.wait_for_ddl(schema_version, cluster_version, interval '30 seconds') FROM __neki.ddl_versions()" 2>&1 | grep -v NOTICE
sleep 5; touch stop.flag; sleep 2
echo "new app inserts=$(wc -l < newapp.log) failed=$(grep -vc 'INSERT 0 1' newapp.log)"
Real output:
ALTER TABLE
real 0m0.853s
user 0m0.021s
sys 0m0.018s
new app inserts= 11 failed=0
The new version kept inserting through the drop.
5. See what a forgotten old client gets
psql -At -c "SELECT legacy_code FROM accounts WHERE customer_id = 7" 2>&1 | head -1
Real output:
ERROR: column "legacy_code" does not exist
This is the failure step 3 exists to prevent.
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 string_agg(attname, ',' ORDER BY attnum) FROM pg_attribute WHERE attrelid = 'accounts'::regclass AND attnum > 0 AND NOT attisdropped" | tail -1
done
psql -At -c "SELECT count(*) FROM accounts"
Expected: customer_id,plan on every shard, and 5,001 rows plus every successful insert in newapp.log (5,012, which is 5,000 + 1 + 11 in this run).
How it works
Each step is native DDL: the router applies it to every shard at once, and wait_for_ddl returns when every router plans queries against the new schema. Dropping NOT NULL and dropping a column are both catalog changes in Postgres; neither rewrites the table, so each took about a second here regardless of row count. The two steps exist for the application, not the database: between them, both the old and the new application versions are valid against the schema, which gives you room to deploy and confirm the old version is gone. Native DDL is described on the Neki schema changes page.
Common issues
An index or constraint uses the column
Dropping a column also drops indexes and constraints that use only that column. Check \d accounts before step 2 so nothing the application needs disappears with it.
The disk space is not returned at once
DROP COLUMN marks the column dropped in the catalog. Postgres reclaims the space as rows are rewritten, for example by later updates or by a table rewrite.
A dropped column cannot be restored
Restoring a column's data after step 2 means restoring from a backup. To make step 2 reversible for a while, rename the column first, watch for errors from clients that still use the old name, and drop it later. In testing, ALTER TABLE accounts RENAME COLUMN old_flag TO old_flag_retired made a query on the old name fail with column "old_flag" does not exist, and renaming it back restored it.
Online DDL for dropping a column
The schema changes page lists dropping a column among the changes an online DDL workflow runs on the copy path, which copies every row into a shadow table. For a drop, the native statement in this guide is a catalog change and is much cheaper.
Next steps
- Delete the database with
pscale database delete crm --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: "per-shard column list of accounts; row count"
expected_result: "customer_id,plan on every shard, and every successful insert from the new application present."