I have a database with a table which is incremently patched and has many indexes. But sometimes the patching does not happen and the new patch becomes very large. Which makes in practice makes it smarter to delete the indexes and patch the table and reset the indexes. But this seems horrible and in practice with users using the table this is not an option. So I though that there was a posibility to RESET the index during the update statement or even better have postgres it self check if it is optimal. (I'm using postgres 10 this might be a problem that is solved by upgrading). I hope you can help me.
asked Jan 21, 2020 at 11:51
Peter Mølgaard Pallesen
2,0092 gold badges23 silver badges38 bronze badges
Add a comment
|
1 Answer
No, there is no good solution, nor any on the horizon for future versions.
Either you keep the indexes and must maintain them during the "patch"; or you drop them in the same transaction as the "patch" and rebuild them later in which case the table is locked against all other uses; or you drop them in a separate transaction and rebuild them later in which case other sessions can see the table in an unindexed state.
There are in principle ways this could be improved (for example, ending the "patch" the same way create-index-concurrently ends, with a merge join between the index and the table. But since CIC must be in its own transaction, it is not clear how these could be shoehorned together), but I am not aware of any promising work going on currently.
