Re: Rearchitecting for storage

Matthew Pounsett <matt(at)conundrum(dot)com> writes:
> On Thu, 18 Jul 2019 at 19:53, Rob Sargent <robjsargent(at)gmail(dot)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