From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Lewis <mlewis@entrata.com>
Cc: Ekaterina Amez <ekaterina.amez@zunibal.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best way to delete big amount of records from big table
Date: Fri, 27 Mar 2020 10:09:54 -0500
Message-ID: <20200327150954.GG20103@telsasoft.com> (raw)
In-Reply-To: <CAHOFxGp+3jXUEin6BdEbkJKy2t+f3rceVmE0bm-LvnYUj1cfTQ@mail.gmail.com>
References: <6e456b07-a396-f9e3-6a22-23a96b73420c@zunibal.com>
<CAHOFxGp+3jXUEin6BdEbkJKy2t+f3rceVmE0bm-LvnYUj1cfTQ@mail.gmail.com>
On Fri, Mar 27, 2020 at 08:41:04AM -0600, Michael Lewis wrote:
> 2) If you are deleting/moving most of the table (91 of 150 million),
> consider moving only the records you are keeping to a new table, renaming
> old table, and renaming new table back to original name. Then you can do
> what you want to shift the data in the old table and delete it.
You could also make the old table a child of (inherit from) the new table.
That allows you to remove rows separately from removing them.
Partitioning (with legacy inheritence or the new, integrated way available in
postgres 10) allows DROPing oldest tables rather than DELETEing from one
gigantic table.
You should consider somehow cleaning up the old table after you DELETE from it,
maybe using vacuum full (which requires a long exclusive lock) or pg_repack
(which briefly acquires an exclusive lock).
--
Justin
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-performance@postgresql.org
Cc: pryzby@telsasoft.com, mlewis@entrata.com, ekaterina.amez@zunibal.com
Subject: Re: Best way to delete big amount of records from big table
In-Reply-To: <20200327150954.GG20103@telsasoft.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