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

Reference

Confirm Neki Router Plan-Cache Reuse and Detect Forced Replans

Run a TypeScript program against a live Neki database that uses EXPLAIN (NEKI_PLAN, FORMAT JSON) to show the router normalizes literals and reuses the same cached plan, then changes the data topology and shows the plan changes because Neki replans after topology invalidation.

What you will build

A TypeScript program that connects to a live Neki branch and demonstrates that the router normalizes query literals, reuses the resulting plan, and replans after a data-topology change.

Create and seed public.plan_cache_demo → EXPLAIN with literal 1 → EXPLAIN with literal 2 → compare fingerprints → call __neki.set_data_topology to move the table to a new shard group → wait for propagation → EXPLAIN again → compare → restore original topology and clean up.

AI prompt

Build a verified TypeScript example for Neki that demonstrates router query-plan cache reuse and invalidation. The program should create a table, EXPLAIN the same query with different literal values, prove the plans are identical, then force a replan by changing the data topology via __neki.set_data_topology, and restore the original topology. Report the exact response keys observed from the live run and capture any assumptions that the run disproved.

Prerequisites

  • Node.js 22 with npm
  • A Neki database branch on PlanetScale
  • A Postgres role for the branch with inherited postgres permission (used to call __neki.set_data_topology)
  • The role's host, username, password, and database name

Background on the behavior this example checks lives in the Neki query-planning guide; the topology primitives (__neki.set_data_topology, __neki.wait_for_data_topology, __neki.get_data_topology) are described in the data-topology guide.

Environment variables

  • NEKI_HOST: Neki branch access host (e.g., aws-us-east-1-2.pg.psdb.cloud)
  • NEKI_USER: Postgres role username
  • NEKI_PASSWORD (secret): Postgres role password
  • NEKI_DATABASE: Database name, usually postgres

1. Create the project and install the Postgres driver

Initialize a Node project and install pg so we can connect to Neki over the standard Postgres protocol.

mkdir -p neki-plan-cache && cd neki-plan-cache
npm init -y
npm install pg@8.13.1
npm install -D typescript @types/node @types/pg tsx

2. Add src/plan_cache.ts

The script creates a table, seeds it, runs EXPLAIN (NEKI_PLAN, FORMAT JSON) with two different literals, compares the plans, then moves the table to a different shard group with __neki.set_data_topology and checks the plan again. It restores the original topology and drops the table at the end.

import { Client } from 'pg';

const host = process.env.NEKI_HOST!;
const user = process.env.NEKI_USER!;
const password = process.env.NEKI_PASSWORD!;
const database = process.env.NEKI_DATABASE || 'postgres';

const client = new Client({
  host,
  port: 5432,
  user,
  password,
  database,
  ssl: { rejectUnauthorized: true },
});

function planFingerprint(rows: any[]) {
  return JSON.stringify(rows[0]?.['QUERY PLAN']);
}

async function explainPlan(literal: number) {
  return client.query(
    `EXPLAIN (NEKI_PLAN, FORMAT JSON)
     SELECT status FROM public.plan_cache_demo WHERE account_id = ${literal}`,
  );
}

let originalTopology: string | null = null;
let topologyChanged = false;

async function restoreTopology() {
  const res = await client.query<{ success: boolean; revision: string }>(
    `SELECT * FROM __neki.set_data_topology($1, true)`,
    [originalTopology],
  );
  await client.query(`SELECT __neki.wait_for_data_topology($1)`, [res.rows[0].revision]);
  topologyChanged = false;
  return res;
}

async function main() {
  await client.connect();

  await client.query(`DROP TABLE IF EXISTS public.plan_cache_demo`);
  await client.query(`
    CREATE TABLE public.plan_cache_demo (
      id uuid PRIMARY KEY,
      account_id bigint NOT NULL,
      status text NOT NULL
    )
  `);

  for (let i = 0; i < 5; i++) {
    await client.query(
      `INSERT INTO public.plan_cache_demo (id, account_id, status)
       SELECT gen_random_uuid(), $1, 'active'
       FROM generate_series(1, 500) AS g`,
      [i + 1],
    );
  }

  const beforeA = (await explainPlan(1)).rows;
  const beforeB = (await explainPlan(2)).rows;

  const baselineFingerprint = planFingerprint(beforeA);
  const cachedReuse = baselineFingerprint === planFingerprint(beforeB);

  originalTopology = (await client.query(`SELECT __neki.get_data_topology() AS t`)).rows[0].t;
  const parsedOriginal = JSON.parse(originalTopology!);
  const shardUid = parsedOriginal.shard_groups[0].key_ranges[0].shard_uid;

  const newTopology = {
    authoritative_shard_group: 'authoritative',
    default_shard_group: 'authoritative',
    shard_groups: [
      { uid: 'authoritative', key_ranges: [{ shard_uid: shardUid }] },
      { uid: 'app_data', key_ranges: [{ shard_uid: shardUid }] },
    ],
    databases: {
      postgres: {
        schemas: {
          public: {
            default_shard_group: 'authoritative',
            tables: {
              plan_cache_demo: { shard_group: 'app_data' },
            },
          },
        },
      },
    },
  };

  topologyChanged = true;
  const setResult = await client.query<{ success: boolean; revision: string }>(
    `SELECT * FROM __neki.set_data_topology($1, true)`,
    [JSON.stringify(newTopology)],
  );
  await client.query(`SELECT __neki.wait_for_data_topology($1)`, [
    setResult.rows[0].revision,
  ]);

  const afterTopology = (await explainPlan(1)).rows;
  const topologyPlanChanged = baselineFingerprint !== planFingerprint(afterTopology);

  const restoreResult = await restoreTopology();

  await client.query(`DROP TABLE IF EXISTS public.plan_cache_demo`);

  console.log(
    JSON.stringify(
      {
        baselinePlan: beforeA[0]['QUERY PLAN'],
        literalPlansIdentical: cachedReuse,
        planAfterTopologyChange: afterTopology[0]['QUERY PLAN'],
        topologyChangeForcedReplan: topologyPlanChanged,
        topologyRevisions: {
          afterChange: setResult.rows[0].revision,
          afterRestore: restoreResult.rows[0].revision,
        },
      },
      null,
      2,
    ),
  );

  await client.end();
}

main().catch(async (e) => {
  console.error(e);
  try {
    if (topologyChanged) await restoreTopology();
    await client.query(`DROP TABLE IF EXISTS public.plan_cache_demo`);
    await client.end();
  } catch {}
  process.exit(1);
});

3. Run the example

Export the connection details for the Postgres role you created for the Neki branch, then run the script with tsx.

export NEKI_HOST="your-branch-host.pg.psdb.cloud"
export NEKI_USER="your-role-username"
export NEKI_PASSWORD="your-role-password"
export NEKI_DATABASE="postgres"
npx tsx src/plan_cache.ts

4. Read the result

The program prints a JSON report. literalPlansIdentical is true when the router reused the same normalized plan for both literals, and topologyChangeForcedReplan is true when the data-topology change produced a different router plan.

# Expected shape:
# {
#   "baselinePlan": [...],
#   "literalPlansIdentical": true,
#   "planAfterTopologyChange": [...],
#   "topologyChangeForcedReplan": true,
#   "topologyRevisions": { ... }
# }

Verify the result

Run against Neki v0.0.0-20261103220313-94b14dbc88e1 on 2026-11-05.

NEKI_HOST="$NEKI_HOST" NEKI_USER="$NEKI_USER" NEKI_PASSWORD="$NEKI_PASSWORD" NEKI_DATABASE="$NEKI_DATABASE" npx tsx src/plan_cache.ts

Observed output:

{
  "baselinePlan": [
    {
      "Plan": {
        "Node Type": "Route",
        "Variant": "AnyShard",
        "Query": "SELECT status FROM public.plan_cache_demo WHERE account_id = $1",
        "ShardGroupUid": "data",
        ...
      }
    }
  ],
  "literalPlansIdentical": true,
  "planAfterTopologyChange": [
    {
      "Plan": {
        "Node Type": "Route",
        "Variant": "AnyShard",
        "Query": "SELECT status FROM public.plan_cache_demo WHERE account_id = $1",
        "ShardGroupUid": "app_data",
        ...
      }
    }
  ],
  "topologyChangeForcedReplan": true,
  "topologyRevisions": {
    "afterChange": "146223",
    "afterRestore": "146226"
  }
}

Expected: A JSON report with literalPlansIdentical: true and topologyChangeForcedReplan: true, showing the router normalized the query and replanned after the topology change.

How it works

Neki caches query plans keyed by the normalized query shape. When you send a simple query with a literal like account_id = 1, the router rewrites it as account_id = $1 before looking up or storing the plan, so account_id = 2 reuses the same cached plan. EXPLAIN (NEKI_PLAN, FORMAT JSON) exposes that normalized plan in the Query field, along with the routing decision (Node Type, Variant, ShardGroupUid). Schema, topology, or statistics changes invalidate affected cached plans. In this example we trigger invalidation by replacing the data topology so the table moves to a different shard group; __neki.set_data_topology returns a new revision and __neki.wait_for_data_topology blocks until routers see it. The next EXPLAIN then produces a different ShardGroupUid, proving the router replanned. The script restores the original topology and drops the table so the example is idempotent.

What the run corrected

These are beliefs the documentation supported and the run disproved.

  • Assumed: Neki exposes a plan-cache view or setting such as __neki.plan_cache that reports cache hits. Observed: There is no SQL-accessible plan-cache view or setting; the only way to confirm reuse is to compare EXPLAIN (NEKI_PLAN, FORMAT JSON) output for semantically identical queries. Evidence: SHOW __neki.plan_cache and SELECT * FROM __neki.plan_cache both return "unrecognized" / "does not exist" errors, while EXPLAIN (NEKI_PLAN) returns a real plan whose Query field is parameterized.

  • Assumed: Adding a Postgres B-tree index (CREATE INDEX) would change the router plan and demonstrate a forced replan. Observed: The router plan remained identical after CREATE INDEX; the Neki router plan is above the shard-level Postgres planner, so index changes do not always alter the route decision. Evidence: After CREATE INDEX, EXPLAIN (NEKI_PLAN, FORMAT JSON) still returned the same Route/AnyShard plan; changing the data topology with __neki.set_data_topology changed the ShardGroupUid and produced a different plan.

  • Assumed: Calling __neki.set_data_topology requires an explicit neki_operator inherited role. Observed: The call succeeded with a role whose inherited_roles included postgres; the permission is granted through the postgres role in this environment. Evidence: set_data_topology returned { success: true, revision: ... } and wait_for_data_topology completed without error.

Common issues

EXPLAIN (NEKI_PLAN) returns a relation-not-found error after a topology change

Cause: The table was created before it was declared in the data topology, or the topology has not propagated to all routers.

Fix: Create the table, declare it in the topology, and call __neki.wait_for_data_topology(revision) before running queries that depend on it.

__neki.set_data_topology fails with a permission error

Cause: The Postgres role does not have permission to mutate cluster metadata.

Fix: Create or update the role with inherited_roles that include postgres (and neki_operator if your PlanetScale organization requires it).

Literal values still appear in the cached Query field

Cause: The query was sent through an extended query protocol path that keeps the literals, or the literals are not eligible for normalization.

Fix: Use simple-query-protocol SQL text with plain literal comparisons in the WHERE clause, and confirm with EXPLAIN (NEKI_PLAN, FORMAT JSON).

Plan does not change after adding a Postgres index

Cause: EXPLAIN (NEKI_PLAN) shows the router-level route decision, not the Postgres shard plan. Indexes affect the shard-level plan (EXPLAIN (NEKI_PG_PLAN)), not necessarily the router plan for this query.

Fix: Use a schema or topology change that alters routing, such as changing the shard group or adding a global secondary index.

Next steps

  • Replace the topology change in the script with a schema change that affects routing, such as adding a reference table or global secondary index, and observe the new plan.
  • Capture per-query latency before and after the topology change to correlate the replan with a planning-time spike.
  • Store the fingerprint of critical queries in CI and fail the build if a schema or topology deploy changes the plan shape.
verification:
  status: verified
  tested_at: "2026-11-05"
  product_version: "Neki v0.0.0-20261103220313-94b14dbc88e1"
  command: "create and seed public.plan_cache_demo, run EXPLAIN (NEKI_PLAN, FORMAT JSON) with two literals, compare plans, move the table to a new shard group via __neki.set_data_topology, re-EXPLAIN, restore topology, drop table"
  expected_result: "baselinePlan shows Route/AnyShard with normalized Query 'SELECT status FROM public.plan_cache_demo WHERE account_id = $1' and ShardGroupUid 'data'; literalPlansIdentical=true; planAfterTopologyChange returns ShardGroupUid 'app_data'; topologyChangeForcedReplan=true; topology revisions change on set and restore"