time taking deletion on large tables
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
"Required checkpoints occurs too frequently"
Hi, We are getting this alert frequently "Required checkpoints occurs too frequently" on postgres version 11.8 The RAM of the server is 16 GB. and we have already set the max_wal_size= 4096 MB min_wal_size= 192 MB. Please help me in optimizing the same to avoid this alert. Regards, Atul
Re: "Required checkpoints occurs too frequently"
how much size should I increase in "max_wal_size". Do we need to change any other parameter's value also ? Regards, Atul On 12/11/20, Laurenz Albe wrote: > On Fri, 2020-12-11 at 13:42 +0530, Atul Kumar wrote: >> We are getting this alert frequently "Required checkpoints occurs too >> frequently" on postgres version 11.8 >> >> The RAM of the server is 16 GB. >> >> and we have already set the max_wal_size= 4096 MB >> min_wal_size= 192 MB. > > You should increase "max_wal_size" even more. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: "Required checkpoints occurs too frequently"
What do you mean by “how much wal is created” How total Wal files in size or how much total wal files in numbers. Please let me know. Regards Atul On Friday, December 11, 2020, Amine Tengilimoglu < [email protected]> wrote: > it depends on your cluster environment. you need to know how much wal is > created in checkpoint_timeout duration. for example your > checkpoint_timeout = 30 min, you need to measure how much wal is created in > 30 minute. and then you can increase max_wal_size according to this size. > > > > Atul Kumar , 11 Ara 2020 Cum, 12:02 tarihinde şunu > yazdı: > >> how much size should I increase in "max_wal_size". >> >> Do we need to change any other parameter's value also ? >> >> >> >> Regards, >> Atul >> >> On 12/11/20, Laurenz Albe wrote: >> > On Fri, 2020-12-11 at 13:42 +0530, Atul Kumar wrote: >> >> We are getting this alert frequently "Required checkpoints occurs too >> >> frequently" on postgres version 11.8 >> >> >> >> The RAM of the server is 16 GB. >> >> >> >> and we have already set the max_wal_size= 4096 MB >> >> min_wal_size= 192 MB. >> > >> > You should increase "max_wal_size" even more. >> > >> > Yours, >> > Laurenz Albe >> > -- >> > Cybertec | https://www.cybertec-postgresql.com >> > >> > >> >> >>
Re: Master - Slave Replication Window Server
Hi Haseeb, I had configured replication on windows and made a document in an easy way. I am not expert but I hope it will help you. Regards Atul On Tuesday, June 15, 2021, Haseeb Khan wrote: > Hello Rory, > > Yes , I have followed the document and configured each and everything. But > I can’t see archive_Wal_segments is copying to the folder which I have > created. So the issue I am facing is that where should I create the archive > folder should I create on master or slave server ? Might be I am missing > something or doing some mistake. > > Would appreciated, if you could help in this regard > > Kindly send me the Postgres general email, so I can raise this issue over > there as well > > Thanks in advance > > *Br*, > Haseeb Ahmad > > On 15-Jun-2021, at 5:05 PM, Rory Campbell-Lange > wrote: > > On 15/06/21, Haseeb Khan ([email protected]) wrote: > > I have confusion below, Should we create an archive path on the standby > > server and then set it to recovery.conf file ? > > > restore_command = 'cp /path/to/archive/%f %p' > > > Hi Hasseb > > Are you following this procedure? > https://www.postgresql.org/docs/13/continuous-archiving.html > > If so please let us know what problem you are experiencing. > > Also, this is the postgres performance list. Please move this conversation > to postgresql general. > > Cheers > Rory > > > <>
