Gareth.Williams@xxxxxxxx wrote:
So the rest of the question is, if I have two indexes with identical definitions, what happens? I've confirmed that I can create indexes with identical definitions (except name) without postgres complaining - and without breaking the client on my test system - but I am wary of trying it on my production system where there is much more data (8GB) and I care about it's integrity so much more.
The database doesn't care one bit if you create a bunch of redundant indexes. So long as one of them is around to satisfy the queries that need the index to run well, you're fine.
The main thing you can't do with the index concurrently/rename shuffle you've discovered here is use that approach to concurrently rebuild an index that enforces a constraint or unique index. If your index is enforcing a PRIMARY KEY for example, you'll discover a major roadblock were you to try this same technique to rebuild it. Those are tied into the constraint portion of the system catalogs and manipulating them isn't so easy.
Regular indexes that exist just to speed up queries, those you can rename around as you've been figuring out without any downside. From a general paranoia perspective, you should run an explicit ANALYZE on the underlying table after you finish the shuffle, just to make absolutely sure the right statistics are available afterwards.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
