5 ms·

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

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.