pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM FULL vs dump & restore
3+ messages / 2 participants
[nested] [flat]
* VACUUM FULL vs dump & restore
@ 2005-09-24 04:56 Ilya A. Kovalenko <shadow@oganer.net>
0 siblings, 2 replies; 3+ messages in thread
From: Ilya A. Kovalenko @ 2005-09-24 04:56 UTC (permalink / raw)
To: pgsql-sql
Greetings,
What advantages I lose, when using dump-truncate-restore (table
or whole DB) instead of performing VACUUM FULL ?
In both cases I have no access to data, but first is much faster
(by subjective estimate).
Thank you,
Ilya A. Kovalenko (mailto:shadow@oganer.net)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: VACUUM FULL vs dump & restore
@ 2005-09-24 11:19 Aldor <an@mediaroot.de>
parent: Ilya A. Kovalenko <shadow@oganer.net>
1 sibling, 0 replies; 3+ messages in thread
From: Aldor @ 2005-09-24 11:19 UTC (permalink / raw)
To: Ilya A. Kovalenko <shadow@oganer.net>; +Cc: pgsql-sql
Hello Ilya,
you have to check for yourself which method is faster - just test it
with a stopwatch;-)
You have to take care, because when you make VACUUM FULL, then it
vacuums also the system tables, etc. of postgres.
I'm not sure if this is the same way VACUUM goes through all objects,
but I'd make a customized vacuum, which finds out first every object
which should be vacuumed by:
select relname
from pg_class
You can filter out not wanted objects through the query or when
processing the "VACUUM FULL [object]" or only "VACUUM [object].
In this way I can decide for myself what I want to vacuum, and what I
will do by dump-truncate-restore.
In many cases a normal VACUUM was even faster then the primitive
dump-truncate-restore process. The bottlneck on a VACUUM is as I saw
from my experience on tables with long strings inside and an amount of
hundreds of millions.
Regards,
Aldor
Ilya A. Kovalenko wrote:
> Greetings,
>
> What advantages I lose, when using dump-truncate-restore (table
> or whole DB) instead of performing VACUUM FULL ?
> In both cases I have no access to data, but first is much faster
> (by subjective estimate).
>
> Thank you,
>
> Ilya A. Kovalenko (mailto:shadow@oganer.net)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: VACUUM FULL vs dump & restore
@ 2005-09-24 11:28 Aldor <an@mediaroot.de>
parent: Ilya A. Kovalenko <shadow@oganer.net>
1 sibling, 0 replies; 3+ messages in thread
From: Aldor @ 2005-09-24 11:28 UTC (permalink / raw)
To: Ilya A. Kovalenko <shadow@oganer.net>; +Cc: pgsql-sql
Another way how to do it with having access to the data in the same time
is to create a new table, named a little bit differently and do an:
insert into [table]2
select *
from [table];
Then switch to the second table.
Then you have to do on the first table the TRUNCATE and DROP.
For getting out which table is the actual one you can create a table
which holds the originate table name and the actual table name. When
using plpgsql you can check the table name before building the queries
and then build them with EXECUTE.
Be aware that you cannot do:
SELECT col1, col2
FROM gettablename('[table]');
Also be aware to switch back when you do the process again, so you dump
the data from the [table]2 to [table].
For my experience this way was faster then dump-truncate-restore on the
table.
Regards,
Aldor
Ilya A. Kovalenko wrote:
> Greetings,
>
> What advantages I lose, when using dump-truncate-restore (table
> or whole DB) instead of performing VACUUM FULL ?
> In both cases I have no access to data, but first is much faster
> (by subjective estimate).
>
> Thank you,
>
> Ilya A. Kovalenko (mailto:shadow@oganer.net)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-24 11:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-24 04:56 VACUUM FULL vs dump & restore Ilya A. Kovalenko <shadow@oganer.net>
2005-09-24 11:19 ` Aldor <an@mediaroot.de>
2005-09-24 11:28 ` Aldor <an@mediaroot.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox