Обсуждение: Index size growing
Hi,
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. I googled and got the info that reindex will help brining back the actual size of the index. But the disk has only 4 GB left . Due to this, when i run reindex it is getting almost full. I don't know exactly why it is growing like that during reindex. Due to this i am not able to complete the reindex and see if i get back the disk spacce. Please suggest me a good solution and the way that i am doing is logical or not.
Regards,
Rajendra
Rajendra prasad <rajendra.dn@gmail.com> writes: > Hi, > > 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. I googled and got the info that reindex will help brining back > the actual size of the index. But the disk has only 4 GB left . Due to > this, when i run reindex it is getting almost full. I don't know > exactly why it is growing like that during reindex. Due to this i am > not able to complete the reindex and see if i get back the disk > spacce. Please suggest me a good solution and the way that i am doing > is logical or not. Did you try reindexing each index individually? The old index files will be dropped in that case upon completion of each command if you are running autocommit. Less transient disk space demand by that strategy. Even more disk friendly is just to drop/create the indexes. Since you can't reindex without blocking anyway, I assume you are clear of concurrency issues to prevent using this advice (apps down and/or quiescent). -- Jerry Sievers Postgres DBA/Development Consulting e: gsievers19@comcast.net p: 305.321.1144
Hi,
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?
I am new to postgres. Kindly brief me the steps which i need to follow.
Prasad
On Fri, Apr 1, 2011 at 1:28 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
Did you try reindexing each index individually?Rajendra prasad <rajendra.dn@gmail.com> writes:
> Hi,
>
> 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. I googled and got the info that reindex will help brining back
> the actual size of the index. But the disk has only 4 GB left . Due to
> this, when i run reindex it is getting almost full. I don't know
> exactly why it is growing like that during reindex. Due to this i am
> not able to complete the reindex and see if i get back the disk
> spacce. Please suggest me a good solution and the way that i am doing
> is logical or not.
The old index files will be dropped in that case upon completion of each
command if you are running autocommit. Less transient disk space demand
by that strategy.
Even more disk friendly is just to drop/create the indexes.
Since you can't reindex without blocking anyway, I assume you are clear
of concurrency issues to prevent using this advice (apps down and/or
quiescent).
--
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144
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