From: Justin Pryzby <pryzby@telsasoft.com>
To: Ravikumar Reddy <urravikumarreddy@gmail.com>
Cc: Atul Kumar <akumar14871@gmail.com>
Cc: pgsql-performance <pgsql-performance@lists.postgresql.org>
Subject: Re: time taking deletion on large tables
Date: Thu, 3 Dec 2020 09:36:12 -0600
Message-ID: <20201203153612.GM24052@telsasoft.com> (raw)
In-Reply-To: <CANMO9LBud=vzUc7Pa4B7Ebe3QvMPJi=bQtqtwTPCNFVEitsH+Q@mail.gmail.com>
References: <CA+ONtZ541OzW3dzrUKcXscTYAK8G_3DEtUhFz+i2+HsD3-5ffQ@mail.gmail.com>
<CA+ONtZ7rNziZKMjgvocR9EHH=L8V93C-K4E20qP8JwEfJpW5Zg@mail.gmail.com>
<CANMO9LBud=vzUc7Pa4B7Ebe3QvMPJi=bQtqtwTPCNFVEitsH+Q@mail.gmail.com>
On Thu, Dec 03, 2020 at 08:43:57PM +0530, Ravikumar Reddy wrote:
> Please try the code below. Execute all the statements in one transaction.
>
> select * into new_table from old_table where type = 'abcz';
> truncate table old_table;
> inesrt into old_table select * from new_table;
This looks like advice for when most of the rows are being deleted, but I don't
think that's true here. It'd need to LOCK old_table, first, right? Also,
truncate isn't MVCC safe.
Atul: What server version? Do you have an index on feed_definition_id ?
https://wiki.postgresql.org/wiki/Slow_Query_Questions
If explain (analyze,buffers) SELECT runs in a reasonable time for that query,
include its output.
On Thu, Dec 3, 2020 at 8:16 PM Atul Kumar <akumar14871@gmail.com> wrote:
> The feed_posts table has over 50 Million rows.
>
> When I m deleting all rows of a certain type that are over 60 days old.
>
> When I try to do a delete like this: it hangs for an entire day, so I
> need to kill it with pg_terminate_backend(pid).
>
> DELETE FROM feed_posts
> WHERE feed_definition_id = 'bf33573d-936e-4e55-8607-72b685d2cbae'
> AND created_at > '2020-05-11 00:00:00'
> AND created_at < '2020-05-12 00:00:00';
>
> So– I need help in figuring out how to do large deletes on a
> production database during normal hours.
>
> please help me on deleting the rows, Do I need to anything in postgres
> configuration ?
> or in table structure ?
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, urravikumarreddy@gmail.com, akumar14871@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: time taking deletion on large tables
In-Reply-To: <20201203153612.GM24052@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