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

Reference

Retire a Column in Two Steps on a Live Neki Table

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.

  • pscale 0.344.0 with a service token that can create databases, psql 18.6, and jq:

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 accounts table with 5,000 rows, sharded by customer_id, whose legacy_code column is NOT 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-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 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."