Re: Unique Index Disk Consumption

On Wed, Mar 27, 2019 at 9:51 AM Moin Akther <moindba(at)hotmail(dot)com> 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.

https://www.keithf4.com/checking-for-postgresql-bloat/
https://www.keithf4.com/cleaning-up-postgresql-bloat/
https://www.keithf4.com/per-table-autovacuum-tuning/

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com