On Wed, Mar 27, 2019 at 9:51 AM Moin Akther <moindba@xxxxxxxxxxx> wrote:
Dear Experts,
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.
