From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ekaterina Amez <ekaterina.amez@zunibal.com>
To: pgsql-performance@postgresql.org
Subject: Re: Best way to delete big amount of records from big table
Date: Fri, 27 Mar 2020 15:46:57 +0100
Message-ID: <5e44d4e8ae73193efa82fb2744dbd95cc5130669.camel@cybertec.at> (raw)
In-Reply-To: <6e456b07-a396-f9e3-6a22-23a96b73420c@zunibal.com>
References: <6e456b07-a396-f9e3-6a22-23a96b73420c@zunibal.com>
On Fri, 2020-03-27 at 15:13 +0100, Ekaterina Amez wrote:
> I'm trying to clean up a database with millions of records of
> useless-but-don't-remove-just-in-case data. [...]
>
> But also I'm cleaning tables with 150million records where I'm going to
> remove 60% of existing data and after a few tests I'm not sure what's
> the best approach as all seem to take similar time to run. These tables
> are grouped in 4 tables group with master, detail, master_history,
> detail_history structure. None of the tables have primary key nor
> foreign key or any constraint but the sequence used for what should be
> the PK column, though this column is not defined as PK.
You should define primary and foreign keys if you can, but I guess
I don't have to tell you that.
> I've decided to delete from the last one in chunks (10 days of data per
> chunk but it coud be any other quantity) so I've created a function.
> I've tested it with indexes (in master_hist for filtering data and in
> detail_hist for the fk and pk), without indexes, after analyzing table,
> and no matter what I always end up with more or less the same execution
> time. I can afford the time it's getting to run but I'd like to know if
> it's there a better way to do this.
There is no need to delete in batches unless you have a need to keep
transactions short (danger of deadlock because the data are still
modified, or you cannot afford to block autovacuum that long).
If you can drop the indexes while you do it (downtime), go for it.
Perhaps there is a way to use partial indexes that exclude all the
data that you have to delete, then work could go on as normal.
> I'm testing on version 9.2 BUT
> production server is 8.4 (legacy application, supposed to be in at least
> 9.2 but recently discovered it was 8.4, planning upgrade but not now).
> Config parameters are default ones.
Now that is a seriously bad idea. You should test on the same version
as you have running in production. And you should insist in an upgrade.
People who insist in running ancient software often insist in ancient
hardware as well, and both is a good way to get data corruption.
If the system blows up, they are going to blame you.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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: laurenz.albe@cybertec.at, ekaterina.amez@zunibal.com
Subject: Re: Best way to delete big amount of records from big table
In-Reply-To: <5e44d4e8ae73193efa82fb2744dbd95cc5130669.camel@cybertec.at>
* 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