Decide whether migrations set lock_timeout, and record it

Ask

Decide whether this repository's goose migrations should set lock_timeout, and record the decision where a migration author can point at it: docs/dev/database-migrations.md, the migration template, or this issue.

Why

Seven migrations defer to a schema-wide lock_timeout decision that has no home. Each carries a variant of:

No migration in this repo sets lock_timeout; that is a schema-wide decision this file does not re-open

lock_timeout appears in no doc, no config, and no Go code. WithLockTimeout(1, 300) in internal/datastore/migrations/migrations.go is goose's advisory-lock wait, which is a different mechanism. So the sentence reads as a reference to a settled decision and points at nothing, and every reviewer who reaches it rediscovers that.

Migrations carrying the deferral:

  • internal/datastore/migrations/sql/20260728120000_create_maven_remote_repositories.sql
  • internal/datastore/migrations/sql/20260730120000_create_maven_remote_packages.sql
  • internal/datastore/migrations/sql/20260731130000_create_maven_remote_versions.sql
  • internal/datastore/migrations/sql/20260804120000_create_container_remote_repositories.sql
  • internal/datastore/migrations/sql/20260805120000_create_maven_remote_files.sql
  • internal/datastore/migrations/sql/20260806120000_create_container_remote_images.sql
  • internal/datastore/migrations/sql/20260807130000_create_container_remote_manifests.sql

What the decision is about

These migrations create a partitioned parent plus 64 partitions. Each CREATE takes ShareRowExclusiveLock on every FK target and, where a target is itself hash-partitioned, on all 64 of its partitions. That conflicts with the RowExclusiveLock every INSERT holds, so a migration is a bounded write stall across the referenced tables. On the container and maven remote tables those targets include blob_storage_attachments and blob_storage_blobs, which every blob upload writes.

Without lock_timeout, a CREATE that cannot get its lock queues behind a long-running transaction and blocks every writer that arrives after it for as long as that transaction runs. With lock_timeout, the statement fails instead, and the migration has to be retried, which is a different operational trade-off rather than a strictly better one. That trade-off is the decision.

Done when

  • docs/dev/database-migrations.md states whether migrations set lock_timeout, with the value and the retry expectation if they do.
  • The seven deferrals above point at that statement, or are reworded to state the lock exposure without claiming a schema-wide decision.

Notes

Raised in review on !1354 (merged). That MR points its own deferral at this issue; the other six are left for whoever closes this one.