4 ms·

"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

by ars 2y ago

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

victorbjorklund 2y ago
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).
shayonj 2y ago
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.
choilive 2y ago
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.
- whartung 2y ago
  Do what is the recovery/restart process for a partially created index? And how do you clean up after an aborted index build?
  - asabil 2y ago
    REINDEX INDEX CONCURRENTLY[1] [1]: https://www.postgresql.org/docs/current/sql-reindex.html https://www.postgresql.org/docs/current/sql-reindex.html
- Groxx 2y ago
  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.
- deleted 2y ago
  [deleted]
- SoftTalker 2y ago
  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.
AmericanChopper 2y ago
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 https://www.postgresql.org/docs/current/sql-dropindex.html
merb 2y ago
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)

by ars 2y ago

victorbjorklund 2y ago

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

shayonj 2y ago

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.

choilive 2y ago

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.

whartung 2y ago
Do what is the recovery/restart process for a partially created index? And how do you clean up after an aborted index build?
- asabil 2y ago
  REINDEX INDEX CONCURRENTLY[1] [1]: https://www.postgresql.org/docs/current/sql-reindex.html https://www.postgresql.org/docs/current/sql-reindex.html
Groxx 2y ago
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.
deleted 2y ago
[deleted]
SoftTalker 2y ago
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.

AmericanChopper 2y ago

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 https://www.postgresql.org/docs/current/sql-dropindex.html

merb 2y ago

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)