From: Bob Lunney <bob_lunney@yahoo.com>
To: pgsql-admin@postgresql.org, Nic Chidu <nic@chidu.net>
Subject: Re: full vacuum of a very large table
Date: Tue, 29 Mar 2011 09:38:20 -0700 (PDT)
Message-ID: <967268.12623.qm@web39705.mail.mud.yahoo.com> (raw)
In-Reply-To: <1301414165.2351.1435136873@webmail.messagingengine.com>
--- On Tue, 3/29/11, Nic Chidu <nic@chidu.net> wrote:
> From: Nic Chidu <nic@chidu.net>
> Subject: [ADMIN] full vacuum of a very large table
> To: pgsql-admin@postgresql.org
> Date: Tuesday, March 29, 2011, 11:56 AM
> Got a situation where a 130 mil rows
> (137GB) table needs to be brought down in size to 10
> mil records (most recent)
> with the least amount of downtime.
>
> Doing a full vacuum would be faster on:
> - 120 mil rows deleted and 10 mil active (delete most of
> them then full vacuum)
> - 10 mil deleted and 120 mil active. (delete small batches
> and full vacuum after each delete).
>
> Any other suggestions?
>
> Thanks,
>
> Nic
>
> --
Nic,
Since you know the where clause to delete the 120 mil rows why not use the converse of that to select the 10 mil rows to retain into another table, then drop the original table? No vacuum required!
Be sure to use the "create table as select..." syntax to avoid WAL during creation of the new table, and use a transaction to drop the original table and rename the new one. That way users will be querying the original table right up until the switch over, when they will start using the new table.
Foreign keys and other constraints may complicate things a bit, so check those out first. Also, don't forget to index and analyze the new table before the switch over.
Good luck!
Bob Lunney
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bob_lunney@yahoo.com, nic@chidu.net
Subject: Re: full vacuum of a very large table
In-Reply-To: <967268.12623.qm@web39705.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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