Hacker News Reader

Comment by limaoscarjuliet | Hacker News Reader

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.

View on HN

Which 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.

Reply on news.ycombinator.com