Hacker News Reader

Comment by ars | Hacker News Reader

"PostgreSQL doesn’t automatically remove the partially created index when the operation is aborted due to a lock timeout. This is intentional, as it allows for potential recovery or manual intervention"

This argument doesn't make much sense. What's the usefulness of a partially created index? The entire rest of the article is working hard to explain how this invalid index is 100% useless and should be dropped!

So this seems like a bug in PostgreSQL to me.

View on HN

Index creation can take a very long time for a large database, think hours or god forbid days. Restarting from scratch for an index that takes 18 hours to build is very painful.. doubly so back when concurrent index creation didn't exist.

Just to +1 this as a non-Postgres-user who briefly skimmed the docs:

    REINDEX INDEX CONCURRENTLY

^ it can be rebuilt.

I'm honestly not sure if this is saving anything except re-defining (i.e. the literal "create index" statement), but they seem definitely not 100% useless despite being invalid. Maybe 99% useless, but "drop it in the background" doesn't seem particularly better either - leaving intermediate state for an admin to tackle by hand seems reasonable.

Do what is the recovery/restart process for a partially created index?

And how do you clean up after an aborted index build?

REINDEX INDEX CONCURRENTLY[1]

[1]: https://www.postgresql.org/docs/current/sql-reindex.html

If your database is that big then it should probably be managed by someone competent to do so, not a bunch of developers who only know ActiveRecord.

I'm pretty sure it's just because it allows you to retry the index creation using REINDEX INDEX CONCURRENTLY, which allows you to avoid the exclusive lock created by DROP INDEX, because you can't always DROP INDEX CONCURRENTLY

https://www.postgresql.org/docs/current/sql-dropindex.html

I went looking in the postgres code and took it more as a "Feature request" where if the index building / table scanning, etc is interrupted for concurrent builds, PG could drop the index in the end. I can see how PG keeps it around however, since invalid indexes can be useful for administrators.

However, for application developers - unless you are familiar with the internals, this comes off as a big surprise.

Would love to learn more from folks who are more familiar with the internals.

Actually the index can only fail because of a deadlock or a unique failure. Deadlocks are rare and are probably an Application bug and for unique failure you need to fix your database anyway and you either need to drop and recreate it or reinfect it (new stuff will use the index for unique checks even in invalid state but not for queries)

You can use reindex to rebuild an invalid index. But many prefer to just drop it and try again (like the author does). I'm sure there are scenarios where reindex is a much better option so not a bug (maybe a bad default).

Reply on news.ycombinator.com