Support online/concurrent/etc. index creation/update

rtweeks / xmigra

Database evolution management tool

Other

5 stars 3 forks source link

Support online/concurrent/etc. index creation/update #25

Open rtweeks opened 5 years ago

rtweeks commented 5 years ago

Currently, XMigra only supports immediate creation or update of indexes during the main upgrade transaction. It would be nice if the features of the database that allow indexes to be created concurrently with normal access to the table were accessible/manageable through XMigra.

Database Support

PostgreSQL
MS SQL Server:
- Guidelines for online index operations
- How online index operations work

Considerations

MS SQL Server does not support these operations for all license levels. For organizations that use the more expensive licenses with support for this feature, it is likely that indexes will need to be managed without it in some (lower) environments.
PostgreSQL does not allow CONCURRENT index creation inside transactions.

rtweeks commented 5 years ago

I think this can most easily be accomplished as a special case of a stopping point (#36), so that issue blocks this one. The nonblocking index operation migration might use a special key (or keys) at the top level that gets converted to the standard keys for a stopping point migration.

rtweeks commented 5 years ago

It might also be good to include in this feature (or at least consider for the future) a threshold count of records in the target table, below which the index operation is done within the main upgrade transaction. This could greatly simplify creation of development/testing databases while providing confidence that the nonblocking index operation SQL output in the message for the production (or pre-production) system would be correct. Some kind of in-database flag (see table below) to override the threshold would be helpful.

Database	Flag management operations
PostgreSQL	`set_config(...)`/`current_setting(...)`
MS SQL Server	`sp_addextendedproperty`/`sp_dropextendedproperty`/`sys.extended_properties`

rtweeks commented 5 years ago

A migration for this might look like:

---
starting from: ...
without blocking:
  create index: foo_bar
  on table: foo
  for columns:
  - bar
  inlining if record count below: 100000
description: "Online index build for `bar` on big table `foo`"
changes:
- foo
- public.foo

Note the use of without blocking: instead of sql:; this feature would implement conversion of the without blocking data to sql and whatever additional keys required to create a stopping point (#36) checking for the existence of the foo_bar index (of column bar on table foo) and stopping for a nonblocking creation with a message including the appropriate SQL.