Thread: bloating index, pg_restore
Hello,
I have a database which is bloated because of vacuum full, so you find indexes bigger than the table itself.
I have a database which is bloated because of vacuum full, so you find indexes bigger than the table itself.
I have dumped this database and restored it without reindixing and it was extremely slow. So, my question what is the relation between bloated database and pg_restore.
Regards
Regards
Hello, 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. The fact that an index is larger then the table it it built on does not say that something is bloated. Use the pgstattuple extension to determine bloat http://www.postgresql.org/docs/9.2/static/pgstattuple.html. > I have dumped this database and restored it without reindixing and it was > extremely slow. So, my question what is the relation between bloated > database and pg_restore. > > Regards -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
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
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
salah jubeh <s_jubeh@yahoo.com> wrote: > 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? You are still leaving way to much to the imagination here. What version of pg_dump are you using for the dump? Why are there enough dumps to have a "normallY' timing? What is this "one or two day" gap you're talking about? What happened during that time? Are you doing multiple tests with a new database to restore to each time, dumping and restoring multiple databases within one cluster, or what? Without more detail, we can only guess. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company