TIL: Postgres truncates long database names, so I hash my worktree name

The failure nobody recognises

Postgres identifiers are capped at 63 bytes. Go over, and Postgres does not complain the way you expect: it truncates the identifier when it creates the object, then carries on. Your config still holds the long name.

So mix ecto.create looks fine, and the next thing you see is a pair of messages that sound like a race condition, not a naming problem:

The database for MyApp.Repo has already been created
FATAL 3D000 (invalid_catalog_name) database
  "my_app_my_app_feature_def_1862_link_event_to_existing_meeting_dev" does not exist

That pair is recorded in my fieldnotes repo from a real project. I re-checked the byte arithmetic locally (Elixir 1.18, OTP 27) rather than re-triggering it.

The first thing everyone writes

Deriving the dev and test database names from the current directory is the right instinct. One worktree, one database, no migration fights between branches:

worktree_name =
  File.cwd!()
  |> Path.basename()
  |> String.replace(~r/[^a-zA-Z0-9_]/, "_")

database: "my_app_#{worktree_name}_dev"

This works fine until you name a branch like a human. Same input from my notes:

my_app_my_app_feature_def_1862_link_event_to_existing_meeting_dev   # 65 bytes

Two bytes over. Postgres keeps 63 of them. Note the doubled my_app_my_app while you are at it — the slug already contains the app name.

What I reach for now

Four parts, in order: a short prefix, a truncated slug, a short hash of the original worktree name, and the suffix.

prefix = prefix |> slugify_part() |> limit_part(@prefix_length_limit)
suffix = suffix |> slugify_part() |> limit_part(@suffix_length_limit)
slug   = worktree_basename |> slugify_part() |> String.replace_prefix("#{prefix}_", "")
hash   = worktree_hash(worktree_basename)   # sha256, first 8 hex chars

max_slug_length = @postgres_identifier_limit - byte_size("#{prefix}__#{hash}_#{suffix}")
[prefix, String.slice(slug, 0, max_slug_length), hash, suffix]
|> Enum.reject(&(&1 == ""))
|> Enum.join("_")

That produces 63 bytes exactly, on the nose, for the same branch:

my_app_feature_def_1862_link_event_to_existing_mee_64f7383d_dev   # 63 bytes

Then the config keeps an escape hatch, so you can still share one database when you want to:

database: System.get_env("DATABASE_NAME", default_database_name)

What bit me the first time

• The error reads like Postgres or Ecto is confused, not like a length problem. “already been created” next to “does not exist” sends you hunting for stale pools and race conditions.
• The truncation happens at creation time only. Nothing rewrites your config, so the mismatch is silent until connect time.
• Slicing alone is not enough. Two long branches that share a prefix truncate to the same name, and then two worktrees quietly share one database. That is what the hash buys you.
• Renaming the worktree folder changes the hash, so the default database name moves with it.
• CI should not use worktree names at all. The recipe keeps my_app_test<partition> when CI is set, and uses the worktree-safe name locally.

Where I keep it

The full recipe, including the config/dev.exs and config/test.exs wiring and a regression test that asserts byte_size(database) <= 63, lives in ricotrevisan/elixir-phoenix-fieldnotes under recipes/phoenix/worktree-postgres-db-names. Snippets are MIT. Copy it.