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@xxxxxxxxx> 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 ?
