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

Reference

Change Postgres Parameters for One Group of Shards on Neki with the PlanetScale CLI

This guide changes a Postgres parameter for one group of shards on Neki without touching the others. A configuration profile holds the settings for the shards that belong to it, so the guide puts one shard in a second profile, raises work_mem there, and reads the value back from every shard to show that only the new profile's shard changed.

What you will build

profile default   (sh1, sh2, sh3)   work_mem 2MB   <- unchanged
profile reporting (sh4)             work_mem 64MB  <- pscale branch config-profile update ... --parameters pgconf.work_mem=64MB

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, change a Postgres parameter for one group of Neki shards.

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.
- Create a second configuration profile named reporting with pscale branch config-profile create and --replicas 0, add one shard to it with pscale branch shard create --config-profile reporting, and wait until every profile is ready.
- Change pgconf.work_mem on the reporting profile only with pscale branch config-profile update --parameters, and wait until every profile is ready again.
- Read the value from each shard with SELECT setting || unit FROM pg_settings WHERE name = 'work_mem' on a session pinned with SET __neki.shard. SHOW and current_setting are answered by the router and do not show the shard's value.
- List the profile's parameters with pscale branch config-profile parameters and show which ones need a restart.
- 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. Create it with these commands:
pscale database create cfgprof --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create cfgprof 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 cfgprof 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

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'")

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

1. Read the starting value on every shard

shard_setting() {  # shard_setting <parameter>: print the value on each shard, in shard order
  for n in $(psql -At -c "SELECT name FROM __neki.list_shards() ORDER BY name"); do
    u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
    echo "$n $(psql -At -c "SET __neki.shard='$u';" -c "SELECT setting || coalesce(unit, '') FROM pg_settings WHERE name = '$1'" | tail -1)"
  done
}
shard_setting work_mem

Real output:

sh1 2048kB
sh2 2048kB
sh3 2048kB

All three shards belong to the default profile and use the 2MB default.

2. Create a second profile with one shard

pscale branch config-profile create cfgprof main reporting --cluster-size PS_10_AWS_ARM_NEKI --replicas 0 --org "$PLANETSCALE_ORG" > /dev/null
pscale branch shard create cfgprof main --config-profile reporting --count 1 --org "$PLANETSCALE_ORG" > /dev/null

sleep 15
until [ "$(pscale branch config-profile list cfgprof main --org "$PLANETSCALE_ORG" --format json | jq -r '[.[].state] | unique | join(",")')" = "ready" ]; do sleep 15; done
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "4" ]; 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
pscale branch config-profile list cfgprof main --org "$PLANETSCALE_ORG" --format json | jq -r '.[] | "\(.name): \(.shards) shards, \(.replicas) replicas, \(.state)"'

Real output:

default: 3 shards, 2 replicas, ready
reporting: 1 shards, 0 replicas, ready

The reporting profile has one shard with no replicas, and the default profile still has three shards. The new shard is sh4.

3. Change work_mem for the reporting profile only

pscale branch config-profile update cfgprof main reporting --parameters pgconf.work_mem=64MB --org "$PLANETSCALE_ORG" > /dev/null
sleep 15
until [ "$(pscale branch config-profile list cfgprof main --org "$PLANETSCALE_ORG" --format json | jq -r '[.[].state] | unique | join(",")')" = "ready" ]; do sleep 10; done

pscale branch config-profile changes list cfgprof main reporting --org "$PLANETSCALE_ORG" --format json | jq -r '.[0].state'
shard_setting work_mem
echo "-- through the router:"
psql -At -c "SHOW work_mem"

Real output:

completed
sh1 2048kB
sh2 2048kB
sh3 2048kB
sh4 65536kB
-- through the router:
2MB

Only sh4, the shard in the reporting profile, changed to 65536kB. SHOW work_mem still reports 2MB, because the router answers it without asking a shard, so read the value from pg_settings on a pinned shard as shard_setting does.

4. Check the profile's own record and the restart column

for p in default reporting; do
  echo "$p: $(pscale branch config-profile parameters cfgprof main $p --org "$PLANETSCALE_ORG" --format json | jq -r '.[] | select(.name == "work_mem") | .value')"
done
pscale branch config-profile parameters cfgprof main reporting --org "$PLANETSCALE_ORG" --format json \
  | jq -r '.[] | select(.name == "work_mem" or .name == "max_connections" or .name == "shared_buffers") | "\(.namespace).\(.name): restart=\(.restart)"' | sort
pscale branch config-profile update cfgprof main reporting --parameters pgconf.statement_timeout=2min --org "$PLANETSCALE_ORG" 2>&1 | tail -1

Real output:

default: 2MB
reporting: 64MB
pgconf.max_connections: restart=true
pgconf.shared_buffers: restart=true
pgconf.work_mem: restart=false
Error: Parameters include unknown parameters: pgconf.statement_timeout

Each profile keeps its own value. The restart column says which parameters the platform applies by restarting the shard, so a change to max_connections or shared_buffers is a bigger step than work_mem. This guide changed only work_mem, which does not need a restart, and did not test a restart. pgconf.statement_timeout is listed as a parameter with a default of 30s, and the update was rejected as an unknown parameter.

Verify the result

shard_setting work_mem

Expected: sh1, sh2 and sh3 show 2048kB and sh4 shows 65536kB.

How it works

A configuration profile is the set of Postgres settings, cluster size, replica count and storage settings shared by the shards that were created in it. pscale branch config-profile update records a change request for the profile, and the platform applies it only to that profile's shards. The data topology decides which rows live on which shard and is separate from the profile: a table's shard group maps to shards, and the shards map to profiles. To make a table's rows land on the reporting shard, give the table a shard group whose key range points at that shard. The Neki overview describes how shards, routers and sidecars fit together.

Common issues

SHOW does not show the shard's value

SHOW work_mem and SELECT current_setting('work_mem') returned 2MB after the reporting profile changed, because the router answers them. Use pg_settings on a session pinned with SET __neki.shard, as in step 1. SHOW statement_timeout returns 0 for the same reason, and pg_settings on a shard shows 30000ms.

A profile shows applying for a moment

After config-profile create or config-profile update, the list shows applying for a few seconds, and the default profile can show it too. Wait until every profile is ready before reading values, as the steps do.

Some listed parameters cannot be set

pgconf.statement_timeout is listed with a 30s value, and config-profile update --parameters pgconf.statement_timeout=2min returned Parameters include unknown parameters: pgconf.statement_timeout. To change a limit for one session, use SET statement_timeout.

One shard with no replicas

The reporting profile here has --replicas 0, which makes its shard a single node with no failover. Use replicas for any profile that serves production traffic.

Next steps

  • Delete the database with pscale database delete cfgprof --force --org "$PLANETSCALE_ORG".
  • Read the guide on moving cold archive data to a separate shard group on cheaper hardware for a second profile that also holds table data.
  • Read the Neki data topology guide for placing tables in a shard group.

verification:
  status: verified
  tested_at: "2026-10-29"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "work_mem read from pg_settings on each shard after changing one profile"
  expected_result: "sh1, sh2, sh3: 2048kB; sh4: 65536kB"