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

Reference

Tune a Go pgx Connection Pool for the Neki Router

This guide configures a pgxpool pool (pgx v5.7.6) for a sharded Neki table, sizes it from the pool's own wait statistics, and keeps reads working while the router group is replaced during a resize. Router replacement closes pooled connections, pgx reports the resulting unexpected EOF errors as not safe to retry, and a small retry for idempotent reads absorbs them.

What you will build

pgxpool (MaxConns, MinConns, lifetimes, health check, RuntimeParams __neki.fanout/tx_mode = single)
  load: 64 goroutines, pool sizes 8 / 32 / 64 -> pool.Stat() acquire waits
  soak: 8 goroutines reading during a router resize, without and with retryRead

AI Prompt

Using Go with pgx v5.7.6 (pgxpool), connect to a sharded Neki table through the router and tune the pool.

Requirements:
- Create the Neki database with the PlanetScale CLI (pscale 0.344.0): --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, two added shards (three in total), and a data topology that shards orders by customer_id. Wait until every shard answers a direct SELECT 1 with SET __neki.shard.
- Set cfg.ConnConfig.RuntimeParams["__neki.fanout"] = "single" and ["__neki.tx_mode"] = "single". A query without the shard key then fails with SQLSTATE NK312.
- The default query exec mode (statement cache) works through the router.
- Size MaxConns from pool.Stat(): EmptyAcquireCount and AcquireDuration show how often and how long goroutines waited for a connection.
- A router resize replaces the routers and closes pooled connections. Queries on those connections fail with "unexpected EOF", and pgconn.SafeToRetry returns false for them. Retry idempotent reads on connection errors yourself; do not retry *pgconn.PgError.
- 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, python3, and Go 1.23 or later (1.27.0 was used):

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
go 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 fleet --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create fleet 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 fleet 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 fleet 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, and every customer has 10 orders.

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. Step 4 resizes the router group between NKR-1 and NKR-2 ($11 and $22 per month). Current rates are on the Neki pricing page. Delete the database when you finish with pscale database delete fleet --force --org "$PLANETSCALE_ORG".

1. Write the program

mkdir neki-pgx && cd neki-pgx
go mod init example.com/neki-pgx
go get github.com/jackc/pgx/v5@v5.7.6

cat > main.go <<'EOF'
package main

import (
	"context"
	"errors"
	"fmt"
	"os"
	"strconv"
	"sync"
	"sync/atomic"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

func newPool(ctx context.Context, maxConns int32) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(fmt.Sprintf("postgres://%s:%s@%s:5432/postgres?sslmode=require",
		os.Getenv("PGUSER"), os.Getenv("PGPASSWORD"), os.Getenv("PGHOST")))
	if err != nil {
		return nil, err
	}
	cfg.MaxConns = maxConns
	cfg.MinConns = maxConns
	cfg.MaxConnLifetime = 30 * time.Minute
	cfg.MaxConnIdleTime = 5 * time.Minute
	cfg.HealthCheckPeriod = 15 * time.Second
	cfg.ConnConfig.ConnectTimeout = 5 * time.Second
	// Reject statements that would go to more than one shard, and transactions that reach a second shard.
	cfg.ConnConfig.RuntimeParams["__neki.fanout"] = "single"
	cfg.ConnConfig.RuntimeParams["__neki.tx_mode"] = "single"
	return pgxpool.NewWithConfig(ctx, cfg)
}

// retryRead runs an idempotent read up to three times when the connection fails.
// Errors the server returned (*pgconn.PgError), such as a rejected scatter query, are not retried.
func retryRead(ctx context.Context, read func(context.Context) error) error {
	var err error
	for attempt := 0; attempt < 3; attempt++ {
		err = read(ctx)
		var pgErr *pgconn.PgError
		if err == nil || errors.As(err, &pgErr) || ctx.Err() != nil {
			return err
		}
	}
	return err
}

func ordersFor(ctx context.Context, pool *pgxpool.Pool, customerID int64) (int64, error) {
	var n int64
	err := retryRead(ctx, func(ctx context.Context) error {
		qctx, cancel := context.WithTimeout(ctx, 5*time.Second)
		defer cancel()
		return pool.QueryRow(qctx, "SELECT count(*) FROM orders WHERE customer_id = $1", customerID).Scan(&n)
	})
	return n, err
}

func main() {
	ctx := context.Background()
	switch os.Args[1] {
	case "check":
		pool, err := newPool(ctx, 4)
		if err != nil {
			panic(err)
		}
		defer pool.Close()
		n, err := ordersFor(ctx, pool, 1)
		fmt.Println("orders for customer 1:", n, err)
		var total int64
		err = pool.QueryRow(ctx, "SELECT count(*) FROM orders").Scan(&total)
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) {
			fmt.Println("unkeyed:", pgErr.Code, pgErr.Message)
		}
	case "load":
		// 64 workers share the pool; pool.Stat shows how long they waited for a connection.
		size, _ := strconv.Atoi(os.Args[2])
		pool, err := newPool(ctx, int32(size))
		if err != nil {
			panic(err)
		}
		defer pool.Close()
		start := time.Now()
		var wg sync.WaitGroup
		var failed atomic.Int64
		for w := 0; w < 64; w++ {
			wg.Add(1)
			go func(w int) {
				defer wg.Done()
				for i := 0; i < 25; i++ {
					if _, err := ordersFor(ctx, pool, int64((w*25+i)%2000+1)); err != nil {
						failed.Add(1)
					}
				}
			}(w)
		}
		wg.Wait()
		st := pool.Stat()
		fmt.Printf("max_conns=%d queries=1600 failed=%d elapsed=%s waited_for_conn=%d of %d avg_wait=%s\n",
			size, failed.Load(), time.Since(start).Round(100*time.Millisecond), st.EmptyAcquireCount(), st.AcquireCount(),
			(st.AcquireDuration() / time.Duration(st.AcquireCount())).Round(time.Millisecond))
	case "soak":
		// 8 workers read until stop.flag exists. RETRY=0 disables retryRead to compare.
		pool, err := newPool(ctx, 8)
		if err != nil {
			panic(err)
		}
		defer pool.Close()
		retry := os.Getenv("RETRY") != "0"
		var ok, failed atomic.Int64
		var wg sync.WaitGroup
		for w := 0; w < 8; w++ {
			wg.Add(1)
			go func(w int) {
				defer wg.Done()
				for i := 0; ; i++ {
					if _, err := os.Stat("stop.flag"); err == nil {
						return
					}
					cid := int64((w*1000+i)%2000 + 1)
					var err error
					if retry {
						_, err = ordersFor(ctx, pool, cid)
					} else {
						qctx, cancel := context.WithTimeout(ctx, 5*time.Second)
						var n int64
						err = pool.QueryRow(qctx, "SELECT count(*) FROM orders WHERE customer_id = $1", cid).Scan(&n)
						cancel()
					}
					if err != nil {
						failed.Add(1)
						fmt.Printf("%s error: %v\n", time.Now().UTC().Format("15:04:05"), err)
					} else {
						ok.Add(1)
					}
					time.Sleep(100 * time.Millisecond)
				}
			}(w)
		}
		wg.Wait()
		fmt.Printf("retry=%v ok=%d failed=%d\n", retry, ok.Load(), failed.Load())
	}
}
EOF
go mod tidy
go build -o neki-pgx .

newPool holds every setting this guide tunes. retryRead wraps idempotent reads only.

2. Check the router settings

./neki-pgx check

Real output:

orders for customer 1: 10 <nil>
unkeyed: NK312 statement's fan-out (scatter) exceeds __neki.fanout (single)

3. Size the pool

Each run starts 64 goroutines that share the pool and make 1,600 keyed reads between them:

for size in 8 32 64; do ./neki-pgx load $size; done

Real output:

max_conns=8 queries=1600 failed=0 elapsed=16.1s waited_for_conn=1600 of 1600 avg_wait=556ms
max_conns=32 queries=1600 failed=0 elapsed=4.5s waited_for_conn=1600 of 1600 avg_wait=98ms
max_conns=64 queries=1600 failed=0 elapsed=2.7s waited_for_conn=185 of 1600 avg_wait=23ms

waited_for_conn counts acquires that found no idle connection, and avg_wait is the average time a goroutine spent waiting for one. With 8 or 32 connections every read waited, for 556 ms and 98 ms on average. With 64, 185 of 1,600 waited, for 23 ms on average. Pick the size at which waits stay low for your real concurrency. From a laptop, elapsed time mostly reflects network latency to the region, so compare the wait figures rather than the totals.

4. Keep reads working through a router resize

Run the soak twice, each time across a resize. The first run reads without retryRead:

soak() {
  rm -f stop.flag
  RETRY=$1 ./neki-pgx soak > soak-retry-$1.log 2>&1 &
  sleep 20
  pscale branch router update fleet main default --size $2 --org "$PLANETSCALE_ORG" > /dev/null
  until [ "$(pscale branch router show fleet main default --org "$PLANETSCALE_ORG" --format json | jq -r '.sku.display_name + " " + .state')" = "$2 ready" ]; do sleep 15; done
  sleep 45
  touch stop.flag; wait
  tail -1 soak-retry-$1.log
  grep error: soak-retry-$1.log | cut -d' ' -f3- | sort | uniq -c
}
soak 0 NKR-2

Real output:

retry=false ok=8704 failed=12
  12 unexpected EOF

The second run uses retryRead and resizes back:

soak 1 NKR-1

Real output:

retry=true ok=8686 failed=1
   1 unexpected EOF

Without retries, 12 reads failed during the resize. With retryRead, 1 did: that read hit closed connections on all three attempts. Expect a small number of failures to reach the caller even with retries, and handle them as you would any other transient error.

Verify the result

./neki-pgx check
grep -c "error:" soak-retry-0.log
grep -c "error:" soak-retry-1.log

Expected: orders for customer 1: 10 <nil> and unkeyed: NK312 ..., then an error count for the run without retries that is higher than the count for the run with retries (12 and 1 in this run). The exact counts vary with timing.

How it works

pgx sends RuntimeParams as startup parameters on every new connection, so the router applies __neki.fanout and __neki.tx_mode before the pool hands the connection out. MinConns keeps connections open between bursts, MaxConnLifetime and MaxConnIdleTime recycle them, and HealthCheckPeriod sets how often the pool checks idle ones. During a resize, the routers are replaced and the connections open to them are closed. In testing the errors came in three bursts about 45 seconds apart, which fits one router per cell being replaced in turn. pgx reports a query that was already sent on a closed connection as an error, and the next attempt ran on another connection and succeeded. Because pgx cannot know whether the server ran the statement, it does not mark the error as safe to retry, so only your code can decide that a read is safe to repeat. The router is described on the Neki architecture page.

Common issues

Relying on pgconn.SafeToRetry

In testing, every unexpected EOF during a resize had pgconn.SafeToRetry(err) == false, so a retry that checked it retried nothing. Retry idempotent reads on any non-*pgconn.PgError error, as retryRead does, and make writes idempotent before retrying them.

Retrying server errors

A *pgconn.PgError such as NK312 (scatter rejected) or a unique violation will fail the same way again. retryRead returns those immediately.

Pool size and the router

In testing, 400 client connections to the router opened at once, while SHOW max_connections through the router reported 30. That setting does not cap how many pooled client connections you can open.

Query exec modes

All five default_query_exec_mode values (cache_statement, the default, cache_describe, describe_exec, exec and simple_protocol) returned correct results through the router in testing.

Next steps

  • Delete the database with pscale database delete fleet --force --org "$PLANETSCALE_ORG".
  • Read the Neki data topology guide for choosing the shard key that ordersFor filters on.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview), pgx v5.7.6, Go 1.27.0"
  command: "./neki-pgx check; grep -c error: soak-retry-0.log; grep -c error: soak-retry-1.log"
  expected_result: "Keyed read returns 10, unkeyed read fails NK312, and the soak with retryRead has fewer errors than the one without. Counts vary with timing."