Postgres

• Adding a Column With a Default Broke Every Idempotency Key

  Adding a nullable column, or a column with a default, is the textbook example of a safe migration. Old rows get filled in, nothing breaks, no downtime. I’ve repeated that advice to other people.

  Then I added one to a table whose rows are addressed by a hash of the request that created them, and the safe migration quietly invalidated every idempotency key already in the database.

  How the replay works

  The API is replay-first. A client sends an Idempotency-Key header with a job submission. If that same principal and key already exist, and the incoming request is equivalent to the stored one, the API returns the persisted job instead of doing the work twice. If the key matches but the request is different, that’s a 409, because the client reused a key for something else and that’s a bug on their side.

  “Equivalent” is the interesting word. It’s implemented as a SHA-256 over the canonical JSON encoding of the normalized request:

  func digestJSON(data []byte) string {
  	sum := sha256.Sum256(data)
  	return hex.EncodeToString(sum[:])
  }
  

  Normalization is what makes this defensible. You don’t hash the raw bytes off the wire, because then whitespace and key order would make two identical requests look different. You hash a struct that’s been round-tripped through your own types, with a few explicit fixups applied first.

  One of those fixups is the problem.

  The migration

  I added agent routing to the job table. Two columns:

  ALTER TABLE jobs
  	ADD COLUMN requested_agent_id text NOT NULL DEFAULT 'auto',
  	ADD COLUMN resolved_agent_id text;
  
  ALTER TABLE jobs
  	ADD CONSTRAINT jobs_requested_agent_id_valid CHECK (
  		requested_agent_id = 'auto' OR requested_agent_id ~ '^[a-z][a-z0-9-]{0,62}$'
  	);
  

  Textbook. Every existing row gets 'auto', which is exactly what those jobs did behave like, since automatic selection was the only behavior that existed when they ran. The column is honest about history.

  At the same time, the request normalizer picked up a matching rule: if requested_agent_id is missing or blank, set it to auto before hashing.

  Also reasonable. Now {}, {"requested_agent_id": ""}, and {"requested_agent_id": "auto"} all hash the same, which is what you want.

  Put those two reasonable things together and here’s what happens to a client that submitted a job last month and retries it today with the same key and a byte-identical body:

  • The stored digest was computed when the field didn’t exist. The canonical JSON had no requested_agent_id key at all, because the struct field is omitempty and the value was empty.
  • The new digest is computed with normalization applied. The canonical JSON now contains "requested_agent_id":"auto".
  • Different bytes. Different SHA-256. The digests don’t match.
  • The API concludes the client reused an idempotency key for a different request, and returns 409.

  The client did nothing wrong. It sent the identical request, which is the exact scenario idempotency keys exist to make safe, and it got told it was conflicting with itself. The server changed its mind about what canonical form means, and every historical row was addressed under the old definition.

  The fix is a compatibility set

  The repair is to accept that a stored digest is written in whatever dialect was current when it was written, and to teach the reader more than one dialect:

  out := requestDigests{
  	canonical: json.RawMessage(canonical),
  	primary:   digestJSON(canonical),
  }
  if normalized.RequestedAgentID == agentregistry.AutoID {
  	legacy := normalized
  	legacy.RequestedAgentID = ""
  	legacyDigest := digestJSON(legacyCanonical)
  	if legacyDigest != out.primary {
  		out.compatible = append(out.compatible, legacyDigest)
  	}
  }
  

  When the normalized request resolves to auto, compute a second digest with the field cleared back to empty, which reproduces the pre-migration encoding exactly. Then the comparison accepts a hit on either one.

  The detail I’m happiest with is that the compatible digests are never stored. There’s no second column, no array of historical hashes on the row, and no backfill pass. They’re recomputed on each incoming request and compared against the single value already on the row.

  That matters, because the alternative is rewriting the digest of every historical row to the new canonical form. To do that you’d have to re-marshal request bodies you accepted under older validation rules, and any row you can’t faithfully reconstruct becomes a row whose idempotency key silently stops working. A migration that can corrupt the thing it’s repairing isn’t much of a repair. Leaving old rows exactly as they are and making the reader bilingual has no failure mode worse than a cache miss.

  Sorting the list would have been the bug

  A few days later I added preferred_agent_ids, a bounded ordered list of agents to try, valid only when no explicit agent is requested. Capped at 16.

  There’s a strong instinct, when you’re putting a list into a hash key, to sort it first. Sorting gives you stable digests regardless of client-side ordering, and for something like a set of tags it’s the correct move.

  Here it would have been a bug, because the order is the request. ["hermes","antigravity"] means try Hermes first. ["antigravity","hermes"] means try Antigravity first. Those are different instructions that produce different work, so they have to produce different digests and they must never replay each other’s results. The list goes into the canonical JSON in exactly the order the caller sent it.

  The general rule I pulled out of this: sort a collection before hashing only when the collection is actually a set. If order changes behavior, order is data.

  The rest of that feature is deliberately fail-closed, in ways that are easy to get backwards:

  • An empty preferred_agent_ids: [] is a 422, not “no preference.” An empty list is a caller mistake, and treating it as a default would silently route work somewhere the caller didn’t ask for.
  • Preferences do not fall back outside the list. If none of the named agents are ready, the request fails with no_ready_agents, even when some other configured agent is sitting there ready. A preference list is a boundary, not a hint.
  • Duplicates, unknown IDs, and the reserved value auto inside the list are all rejected at submission time rather than at routing time.
  • The default priority order applies only when the list is omitted entirely. Hermes at 10, Antigravity at 20, the fake adapter at 100, lowest first.

  What I’d tell past me

  Your idempotency digest is a wire format. It’s not an implementation detail, even though it lives entirely inside your process and never appears in a response body, because rows in your database are addressed by it and those rows outlive your assumptions. Adding a field with a default is backward compatible for the table. It is not automatically backward compatible for anything you hashed.

  So: write down what goes into the digest, treat that document as versioned, and before any migration touches a field that feeds it, work out what happens to a client replaying a request from before the change. If the answer is a 409 on an identical retry, you need a compatibility digest, and you need it in the same commit as the migration rather than after someone’s retry storm finds it for you.

  I got lucky on the ordering question. I nearly typed sort.Strings.

  Sources

  • Idempotency-Key HTTP header field — the IETF draft, including request-equivalence and conflict semantics
  • Stripe: idempotent requests — the widely copied implementation, and its take on mismatched replays
  • encoding/json Marshal — field order, and how omitempty decides whether a key exists at all
  • goose — the migration tool behind the up and down blocks above

  I’d appreciate a follow. You can subscribe with your email below. The emails go out once a week, or you can find me on Mastodon at @[email protected].

  Sep 2, 2026 api Go Postgres

  Enjoyed this?

• pgvector vs sqlite-vec: You Probably Don't Need Postgres

  Whenever I start looking into vector search, I always end up finding information on pgvector. It’s totally worth considering, especially if you already have Postgres. But there are situations where it might be overkill. And in those situations, sqlite-vec (the successor to sqlite-vss) is quietly becoming the better answer.

  When SQLite Wins

  Local-first apps and CLI tools. If your thing runs entirely on a user’s machine, a personal knowledge base plugin, a developer CLI, a desktop app, running a full Postgres instance is a huge ask. sqlite-vec gives you a vector store as a single .db file sitting next to your markdown or code. Zero configuration. No background daemon. No port management. It’s just there.

  Edge computing. On Cloudflare Workers or Vercel Edge Functions, cold starts matter. Establishing a connection to a remote Postgres database, even with a connection pooler, adds latency you feel. SQLite can be bundled with your app or mounted as a local read-only resource. Near-instant access to embeddings.

  Testing. Spinning up a Postgres container just to verify your vector search logic adds seconds to every test run. With sqlite-vec, you initialize an in-memory database, run your tests, and discard it in milliseconds. If you care about fast inner loops, might be the right solution.

  Single-user scenarios. Building a personal RAG system for your own research? A private publishing pipeline? The complexity of managing Postgres users, permissions, and backups is unnecessary. A single file on disk is easier to back up (just copy it) and easier to reason about.

  The Actual Trade-offs

  There are real differences and here is what I found.

  Feature	sqlite-vec	pgvector
  Deployment	Library (embedded)	Server (process/container)
  Configuration	Zero	High
  Portability	Single file	Database dump/restore
  Concurrency	One writer	Multi-user
  Ecosystem	Focused vector ops	Full relational SQL

  pgvector has better memory management, using the Postgres buffer cache and background workers. sqlite-vec runs in-process, so a large vector index competes directly with your application’s memory. And Postgres has decades of refinement on indexing strategies, JSONB joins, and all the relational features you’d expect.

  If you’re not using those features, you’re paying for complexity you don’t need. If you prefer lightweight tools over heavy infrastructure, sqlite-vec just gets out of the way. There’s no docker compose file required to start your day. The database is just there when your binary runs.

  Pick the Right One

  The decision is probably easier than you would think.

  If you have multiple users writing concurrently, need complex relational queries alongside your vectors, or are already running Postgres, use pgvector. It’s battle-tested and the ecosystem is deep.

  If you’re building something local-first, single-user, edge-deployed, or just want fast tests without container overhead, reach for sqlite-vec. You’ll spend less time on infrastructure and more time on the actual problem.

  Not every vector search needs a database server.

  I’d appreciate a follow. You can subscribe with your email below. The emails go out once a week, or you can find me on Mastodon at @[email protected].

  Apr 26, 2026 Sqlite Development Postgres Vector-search Pgvector

  Enjoyed this?