on a table we have 2 unique index's and when i check the table and unique index size, size of index's are twice the size of table.
if have drop both the unique index's and create the new one but again after some time index size is almost twice the size of table.
Is Unique Index consume more disk space ?
How can i manage this kind scenario ? as droping and recreating unique index on the existing data is taking lot of time.
Thanks and Best Regards,
Moin Akther
Do they immediately have the size of 2x the table? If so, that's perfectly normal for an index to sometimes be bigger than the table, especially if it's compound.
Or do they grow this large over time? If so, that's likely bloat. And if they reach a certain larger size and stay there without continuing to grow, that may be perfectly normal as well with whatever the churn rate of table writes is for the table. If that's the case, I would let things be as they are. Reindexing often is just forcing it to reallocate new pages over again instead of just reusing existing, pre-allocated freespace which can actually make IO worse.
But if they continue to keep growing without seeming to stop, it sounds like you may need to tune autovacuum better so the space is getting marked for reuse by new/updated rows properly.
I've written a few blog posts on bloat and autovacuum tuning. Most important thing to realize to read is the "Why Bloat Happens" section in the first part to realize that it's not always a bad thing.