Postgres add index with Schema Diff Studio
An orders and customers schema is loaded before and after an index review: a composite index replaces a single-column one, plus a partial index for pending orders, a unique index on lower(email) and a GIN index on a jsonb column. The comparison runs on load and opens the Migration tab with the DROP INDEX and CREATE INDEX statements, verified in a real Postgres in your tab.
- Paste your current tables into Before. pg_dump --schema-only --table=orders --table=customers your_db includes the existing indexes, which matters: an index you forget in Before shows up as "new".
- Write the indexes you want into After as plain CREATE INDEX statements, including partial (WHERE ...), expression (lower(email)) and USING gin indexes.
- Press Compare. The Changes tab lists every index added, dropped or renamed per table; an index whose definition is unchanged but whose name differs becomes ALTER INDEX ... RENAME, not a rebuild.
- Open Migration and confirm it says Verified, then copy it (the .sql download is Pro). For a busy production table, change each CREATE INDEX to CREATE INDEX CONCURRENTLY and run those lines outside BEGIN/COMMIT.
What to know
A plain CREATE INDEX takes a SHARE lock on the table: reads continue, but every INSERT, UPDATE and DELETE waits until the build finishes, which on a table with tens of millions of rows can be minutes. CREATE INDEX CONCURRENTLY takes a SHARE UPDATE EXCLUSIVE lock instead, so writes keep flowing, at the price of scanning the table twice and waiting for older transactions. It cannot run inside a transaction block, so it cannot sit between the BEGIN and COMMIT that this tool wraps around the migration. Move those lines out and run them one by one.
If a concurrent build fails (a duplicate value for a unique index, a deadlock, a cancelled session), Postgres leaves the index behind marked INVALID. It is not used for queries but is still updated on every write. Find it with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid, drop it with DROP INDEX CONCURRENTLY, and run the build again. Building CREATE INDEX CONCURRENTLY IF NOT EXISTS does not help here, because the invalid index already exists under that name.
Column order in a composite index decides which queries it serves. (customer_id, created_at DESC) answers WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20 straight from the index, and it also covers plain WHERE customer_id = $1, which is why the single-column orders_customer_id_idx is dropped in this example. It does not help a query that filters on created_at alone. A partial index only serves queries whose WHERE clause implies its predicate (status = 'pending' here), and an expression index on lower(email) is only used when the query also writes lower(email).
Postgres does not index foreign key columns for you. The REFERENCES on orders.customer_id adds a constraint, not an index, so deleting a customer scans orders to check for children. That is the most common missing index in a real schema, and the comparison makes it visible: if an index is not written in After, it is not in the migration.
Updated · Cosmovex
Questions
Does the generated migration use CREATE INDEX CONCURRENTLY?
No. It writes plain CREATE INDEX inside one transaction, because that is what can be verified as a whole and rolled back. For a production table that takes writes, change those lines to CREATE INDEX CONCURRENTLY and run them separately, outside BEGIN/COMMIT; the resulting index definition is the same.
How long is a table locked when I add an index?
With plain CREATE INDEX, writes are blocked for the whole build, which scales with table size; reads continue. With CONCURRENTLY, writes are not blocked, but the build takes longer and waits for transactions that started before it. Either way, set lock_timeout so the statement fails fast if it cannot get its lock.
Will it detect that an existing index is redundant?
It shows exactly which indexes exist in Before and not in After, and writes DROP INDEX for them. Deciding that orders_customer_id_idx is redundant once (customer_id, created_at) exists is your call; remove it from After and the drop appears in the migration.
Are partial, expression, unique and GIN indexes compared correctly?
Yes. Each schema is executed in a real Postgres and the index definitions are read back with pg_get_indexdef, so the WHERE predicate, the expression, the access method (btree, gin, gist, brin, hash) and operator classes such as jsonb_path_ops are all part of the comparison.
What happens when only the index name changes?
The definition is identical, so the migration renames it with ALTER INDEX ... RENAME TO instead of dropping and rebuilding it. A rename is instant; a rebuild of a large index is not.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
