schema.Index can say which columns an index covers, whether it is unique, its method and its partial predicate — but not the order of a column within it. So an index like this cannot be declared:
CREATE INDEX idx_tasks_project_position
ON tasks USING btree (project_id, "position" NULLS FIRST, created_at DESC)
AddIndex gets the columns right and the rest wrong, and the diff proposes dropping the live index:
DROP INDEX CONCURRENTLY "idx_tasks_project_position";
Index today:
type Index struct {
Name string
Columns []string
Unique bool
Method string
Where string
}Columns []string has nowhere to put DESC or NULLS FIRST.
Why this one is not cosmetic
An ordered index is not decoration — it is the index, and the ordering is what makes it usable. This one backs the default task ordering (ORDER BY position ASC NULLS FIRST, created_at DESC) on the busiest list in the application. Declaring the table without it means the generated DDL would drop the index that makes that query fast, and the drift gate cannot tell "this index is missing" from "this index is differently ordered".
It is also the first thing in three tables (69-column budget, 11 indexes on this one) that AddIndex could not express, so the escape hatch is close to complete — this is the remaining hole in it.
Suggested
Let a column carry its ordering, keeping the common case a plain string:
type IndexColumn struct {
Name string
Desc bool
Nulls string // "", "first", "last"
}with Columns []string retained for the shorthand builders and AddIndex taking the richer form — or simply allow the existing Columns entries to carry it as written SQL ("position NULLS FIRST", "created_at DESC"), which is what the DDL wants anyway and what pg_indexes hands back.
Whatever the spelling, the property that matters is the same one IndexNamed fixed for names: a schema adopting an existing database has to be able to say what the index is, not merely which columns it touches.
schema.Indexcan say which columns an index covers, whether it is unique, its method and its partial predicate — but not the order of a column within it. So an index like this cannot be declared:AddIndexgets the columns right and the rest wrong, and the diff proposes dropping the live index:Indextoday:Columns []stringhas nowhere to putDESCorNULLS FIRST.Why this one is not cosmetic
An ordered index is not decoration — it is the index, and the ordering is what makes it usable. This one backs the default task ordering (
ORDER BY position ASC NULLS FIRST, created_at DESC) on the busiest list in the application. Declaring the table without it means the generated DDL would drop the index that makes that query fast, and the drift gate cannot tell "this index is missing" from "this index is differently ordered".It is also the first thing in three tables (69-column budget, 11 indexes on this one) that
AddIndexcould not express, so the escape hatch is close to complete — this is the remaining hole in it.Suggested
Let a column carry its ordering, keeping the common case a plain string:
with
Columns []stringretained for the shorthand builders andAddIndextaking the richer form — or simply allow the existingColumnsentries to carry it as written SQL ("position NULLS FIRST","created_at DESC"), which is what the DDL wants anyway and whatpg_indexeshands back.Whatever the spelling, the property that matters is the same one
IndexNamedfixed for names: a schema adopting an existing database has to be able to say what the index is, not merely which columns it touches.