directus / directus/directus

Support concurrent index creation in schema apply

Open
#26,697 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.