Feature request: mechanism for creating indexes concurrently
Closed this issue · 4 comments
The existing mechanism for creating an index in orville using the Index constructor of SchemaItem is a common foot-gun for developers on our team, as this mechanism of creating an index will lock out concurrent selects, inserts, updates, and deletes on the table in question. On large tables, this can be especially problematic.
Developers on our team would benefit from an additional constructor on SchemaItem (e.g. ConcurrentIndex) that would cause the index to be created concurrently.
This is an interesting one. Looking over the docs at https://www.postgresql.org/docs/13/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY it appears these cannot be done in a transaction. Which means we can't simply add it to the normal SchemaItem processing for migrations. There are some ideas around being able to at least provide this functionality though. Just might need some more thought around how exactly we can get there.
Not sure if this is an orville problem, but the mechanism seems to be flaky if there are multiple instances starting up at the same time. The index is only partially created in this case.
@aaronallen8455 That's likely because concurrent index creation cannot be done inside a transaction, so it's not possible for orville to provide the normal protections using transactions and application locks to handle migration by multiple instances. I think this just has to be handled by application deployment procedures somehow. That's why we didn't add the concurrent index creation as a "normal" migration feature.