Postgres Pro
Downloads
Home   >   mailing lists

Re: bloating index, pg_restore - Mailing list pgsql-general

From salah jubeh
Subject Re: bloating index, pg_restore
Date
Msg-id 1364461573.62199.YahooMailNeo@web122204.mail.ne1.yahoo.com
Whole thread Raw
In response to Re: bloating index, pg_restore  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: bloating index, pg_restore
List pgsql-general
Tree view
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@sss.pgh.pa.us>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: salah jubeh <s_jubeh@yahoo.com>; pgsql <pgsql-general@postgresql.org>
Sent: Thursday, March 28, 2013 5:08 AM
Subject: Re: [GENERAL] bloating index, pg_restore

Sergey Konoplev <gray.ru@gmail.com> writes:
> On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh <s_jubeh@yahoo.com> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


pgsql-general by date:

Previous
From: Konstantin Izmailov
Date:
Subject: Re: [HACKERS] money with 4 digits after dot
Next
From: Martín Marqués
Date:
Subject: Re: pg_stat_get_last_vacuum_time(): why non-FULL?