4 ms·
Concurrent index creation or re-indexing is not all magic. It does scan the table multiple times and might lead to deadlocks if the table is heavily modified.
by limaoscarjuliet 2y ago
Concurrent index creation or re-indexing is not all magic. It does scan the table multiple times and might lead to deadlocks if the table is heavily modified.
Furthermore, failed concurrent index or re-index will leave invalid cursors behind that are not usable but incur the update costs. Of course, they consume the disk space.
Use with caution.
- sgarland 2y agoWhich is why you should monitor DDL, manually or automatically, and alert you to invalid indices. This can be as simple as a cron that runs something like this: SELECT indrelid::regclass tbl, indexrelid::regclass idx FROM pg_index WHERE NOT indisvalid; And then sends that to Slack or whatever if it returns a result.