Rajendra prasad <rajendra.dn@gmail.com> wrote:
> Jerry Sievers <gsievers19@comcast.net>wrote:
>> Rajendra prasad <rajendra.dn@gmail.com> writes:
>>> I have 50 GB data base folder for postgres. Out of 50 GB 20 GB
>>> has been occupied by 8 indexes of a single table by growing upto
>>> 2 GB each.
There's a good chance you don't need all of those indexes or that
you could use narrower indexes. Without seeing the table schema,
including indexes, and hearing more about your use case it's hard to
be sure. But PostgreSQL is often able to make good use of a number
of one-column indexes in ways some other databases can't, while wide
indexes designed for the "covering index" optimization can't be used
that way in PostgreSQL. We don't have that optimization at this
point.
>>> I googled and got the info that reindex will help brining back
>>> the actual size of the index.
Well, until you add or modify data, at which point page splits will
likely pop it right back to the size you're seeing now.
> Thank you for the info. I am doing the reindex for an individual
> index. And i am doing this in the live server and apps are
> connecting to it at the same time. Is this not correct?
Attempts to modify the table during the reindex will block, and
queries won't be able to use an index while it is being rebuilt.
You can work around that with CREATE INDEX CONCURRENTLY followed by
an DROP INDEX on the old index, but that takes some spare disk
space.
-Kevin