From: Alex Balashov <abalashov@evaristesys.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\))
Subject: Re: Need an idea to operate massive delete operation on big size
 table.
Date: Wed, 15 Jan 2025 16:24:53 -0500
References: 
 <CAHOGQfWPQhxoyQ5GEUEFwtdXan=krr5Ue0DDPSKJGpcW9Szi4Q@mail.gmail.com>
 <82cbc03d6bae358db66430f5518abd6b1a683571.camel@cybertec.at>
To: pgsql-admin@lists.postgresql.org
In-Reply-To: <82cbc03d6bae358db66430f5518abd6b1a683571.camel@cybertec.at>
Message-Id: <F2F9DB44-8DBA-4606-A570-1C12C588A7BC@evaristesys.com>
Archived-At: 
 <https://www.postgresql.org/message-id/F2F9DB44-8DBA-4606-A570-1C12C588A7BC%40evaristesys.com>
Precedence: bulk

In my experience, mass deletions are tough. There may be a supporting =
index to assist the broadest criteria, but the filtered rows that result =
must still be sequentially scanned for non-indexed sub-criteria[1]. That =
can still be an awful lot of rows and a huge, time-consuming workload.=20=


While it won't help with deduplication, partitioning is a very good, if =
somewhat labour-intensive solution to the problem of aging old data off =
the back of a rolling archive. Once upon a time, I had an installation =
with a periodic hygienic `DELETE` once or twice a year, which took many =
hours to plan and execute, and placed considerable demand on the system. =
We switched to monthly partitioning and the result was, to some, =
indistinguishable from magic.=20

-- Alex

[1] Which normally doesn't make sense to index, in the overall tradeoff =
of index size and maintenance overhead vs. performance payoff.

--=20
Alex Balashov
Principal Consultant
Evariste Systems LLC
Web: https://evaristesys.com
Tel: +1-706-510-6800