Date: Fri, 27 Mar 2020 10:09:54 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Lewis <mlewis@entrata.com>
Cc: Ekaterina Amez <ekaterina.amez@zunibal.com>,
	pgsql-performance@postgresql.org
Subject: Re: Best way to delete big amount of records from big table
Message-ID: <20200327150954.GG20103@telsasoft.com>
References: <6e456b07-a396-f9e3-6a22-23a96b73420c@zunibal.com>
 <CAHOFxGp+3jXUEin6BdEbkJKy2t+f3rceVmE0bm-LvnYUj1cfTQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAHOFxGp+3jXUEin6BdEbkJKy2t+f3rceVmE0bm-LvnYUj1cfTQ@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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