Matthew Pounsett <m...@conundrum.com> writes: > On Thu, 18 Jul 2019 at 19:53, Rob Sargent <robjsarg...@gmail.com> wrote: > > Can you afford to drop and re-create those 6 indices? > > Technically, yes. I don't see any reason we'd be prevented from doing that. > But, rebuilding them will take a long time. That's a lot of downtime to incur > any time we update > the DB. I'd prefer to avoid it if I can. For scale, the recent 'reindex > database' that failed ran for nine days before it ran out of room, and that > was in single-user. Trying to do > that concurrently would take a lot longer, I imagine.
This may be a stupid question, but are you certain they are all used? It wouldn't be the first time that I've seen someone create indexes and then never use them. This script can tell you if there are any indexes that seems largely unused. https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql If you can run your application without access to the indexes for a while you can create them concurrently in the background using "CREATE INDEX CONCURRENTLY ...". Best regards, Jacob
