From: | Atul Kumar <akumar14871(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org, pgsql-in-general(at)lists(dot)postgresql(dot)org, pgsql-performance(at)lists(dot)postgresql(dot)org, pgsql-docs(at)lists(dot)postgresql(dot)org, pgadmin-support(at)lists(dot)postgresql(dot)org |
Subject: | time taking deletion on large tables |
Date: | 2020-12-03 08:49:24 |
Message-ID: | CA+ONtZ541OzW3dzrUKcXscTYAK8G_3DEtUhFz+i2+HsD3-5ffQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-docs pgsql-in-general pgsql-performance |
Hi,
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.
explain plan is given below
"Delete on feed_posts (cost=1156.57..195748.88 rows=15534 width=6)"
" -> Bitmap Heap Scan on feed_posts (cost=1156.57..195748.88
rows=15534 width=6)"
" Recheck Cond: ((created_at >= '2020-05-11 00:00:00'::timestamp
without time zone) AND (created_at <= '2020-05-12 00:00:00'::timestamp
without time zone))"
" Filter: (feed_definition_id = 'bf33573d-936e-4e55-8607-72b685d2cbae'::uuid)"
" -> Bitmap Index Scan on feed_posts_created_at (cost=0.00..1152.68
rows=54812 width=0)"
" Index Cond: ((created_at >= '2020-05-11 00:00:00'::timestamp without
time zone) AND (created_at <= '2020-05-12 00:00:00'::timestamp without
time zone))"
please help me on deleting the rows, Do I need to anything in postgres
configuration ?
or in table structure ?
Regards,
Atul
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Emery | 2020-12-03 11:41:52 | Re: pg_upgrade + replica servers + rsync --size-only is unsafe |
Previous Message | John Scalia | 2020-12-02 19:01:42 | Re: Seeing privileges on a schema |
From | Date | Subject | |
---|---|---|---|
Next Message | Atul Kumar | 2020-12-03 14:45:06 | time taking deletion on large tables |
Previous Message | Bruce Momjian | 2020-12-02 21:41:52 | Re: maybe ignore my previous comment |
From | Date | Subject | |
---|---|---|---|
Next Message | Atul Kumar | 2020-12-03 14:45:06 | time taking deletion on large tables |
Previous Message | Durgamahesh Manne | 2020-01-17 13:25:57 | Regarding automatic table partitioning without using trigger function in pgsql 12 is possible or not |
From | Date | Subject | |
---|---|---|---|
Next Message | Atul Kumar | 2020-12-03 14:45:06 | time taking deletion on large tables |
Previous Message | aditya desai | 2020-12-02 10:36:38 | Re: Pg_locks and pg_stat_activity |