Thanks for the reply,
Well my question was not very precise, the postgresql version is 8.3 which is not supported, so I wanted to migrate to a newer version which is 9.1.
I have used pg_dump with -Fc option and I was monitoring the pg_restore activity. Normally, the dump and restore takes from 30-40 minutes; but yesterday when the indexes are bloated - I do not know how this could happen in one or two days, the database size increased from 700 MiB to 13 GiB - the pg_restore on 9.1 takes around 6 hours. Since pg_restore is using insert into (....) . How can bloated indexes affect the restore performance.
I have re-indexed one table and the size dropped to again 700 MiB. So what could be the problem here?
Thanks
From: Tom Lane <tgl@xxxxxxxxxxxxx>
To: Sergey Konoplev <gray.ru@xxxxxxxxx>
Cc: salah jubeh <s_jubeh@xxxxxxxxx>; pgsql <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, March 28, 2013 5:08 AM
Subject: Re: bloating index, pg_restore
Sergey Konoplev <gray.ru@xxxxxxxxx> writes:
> On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh <s_jubeh@xxxxxxxxx> wrote:
>> I have a database which is bloated because of vacuum full, so you find
>> indexes bigger than the table itself.
> Table can not be bloated because of vacuum full, it removes bloat from
> the table and its indexes.
Um, well, that depends a lot on which PG version the OP is running
(which he didn't say). The pre-9.0 implementation of VACUUM FULL
was notorious for creating index bloat, because it shuffled heap
entries around to compact heap space, but created an additional
index entry for each such heap-tuple motion.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
