Hi Tim,
maintaining the index(es) should be taken care of by the database engine, not Mendix. In other words, the indexes are updated as soon as Mendix issues the actual “Commit” command to the database.
To my knowledge, this happens at the end of each microflow transaction (or when explicitly invoking certain Java actions in the Community Commons module), but always before returning the control to the microflow.
Side note: The community commons module contains a Java action called “commitInSeparateDatabaseTransaction” which might be helpful, but I I am not sure about the implications of using it :)
I am not sure about your question, but based on the docs and based on what I learned once, if you use indices on tables, it could make search process faster but write process slower and should also increase the size of the tables in comparences to have no indices. But I would think it is also related to the used database of how this is handled.
This sounds for my like a awesome test to compare the behavior with the two different scenarios and the results. I would be curious about the results :)