Support concurrent index creation in schema apply
- Dominant language
- TypeScript
- Stars
- 37.9k
- Forks
- 4.9k
- Avg merge
- 3d 21h
- Merged PRs (30d)
- 36
Description
## Summary
When creating indexes through `directus schema apply`, index creation always runs inside a transaction, which prevents the use of concurrent/online index creation mechanisms (e.g., `CREATE INDEX CONCURRENTLY` on PostgreSQL). This can cause table-level locks on large tables, leading to downtime during schema migrations.
## Current Behavior
`applyDiff` (`api/src/utils/apply-diff.ts`) wraps all operations in a single transaction:
```typescript
await transaction(database, async (trx) => {
let fieldsService = new FieldsService({ knex: trx, schema: ... });
await fieldsService.createField(collection, ..., mutationOptions);
// ...
});
```
Since `attemptConcurrentIndex` is never passed, indexes are created via knex's `column.index()` inside the transaction.
Even if `attemptConcurrentIndex` were passed, the `FieldsService` would use the outer transaction's `trx` for `knex.raw('CREATE INDEX CONCURRENTLY ...')`, which would fail on PostgreSQL (concurrent index creation cannot run inside a transaction).
## Existing Infrastructure
The REST API already supports concurrent index creation via the `?concurrentIndexCreation` query parameter. `FieldsService.createField` already implements a two-phase approach:
1. **Inside transaction**: Schema changes (column creation) — index creation is skipped when `attemptConcurrentIndex` is `true`
2. **Outside transaction**: Concurrent index creation via `knex.raw()` with DB-specific syntax
Per-dialect implementations already exist:
| Database | Method |
|----------|--------|
| PostgreSQL | `CREATE INDEX CONCURRENTLY` |
| MySQL | `ALGORITHM=INPLACE LOCK=NONE` (with fallback) |
| CockroachDB | `CREATE INDEX CONCURRENTLY` |
| Oracle | `CREATE INDEX ... ONLINE` |
| MS SQL | `WITH (ONLINE = ON)` (Enterprise Edition only) |
| SQLite | Not supported |
## Proposed Solution
Adopt the same two-phase approach in `applyDiff`:
1. Run all schema changes inside the transaction (skip index creation)
2. Collect pending index operations during the transaction
3. After the transaction commits, create indexes concurrently outside the transaction
This could be controlled by a CLI flag, e.g., `directus schema apply --concurrent-index-creation`.
## Considerations
- Concurrent index creation failure can leave invalid indexes that need manual cleanup
- Since the index creation happens outside the transaction, it won't be rolled back if it fails
- An opt-in flag may be safer than making it the default behavior
Contributor guide
Assessment
This issue has not been assessed yet.