Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kktby-0007Kg-E9 for pgsql-admin@arkaria.postgresql.org; Thu, 03 Dec 2020 18:45:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kktbx-0006WZ-45 for pgsql-admin@arkaria.postgresql.org; Thu, 03 Dec 2020 18:45:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kktbw-0006WS-Oc for pgsql-admin@lists.postgresql.org; Thu, 03 Dec 2020 18:45:48 +0000 Received: from impout003aa.msg.chrl.nc.charter.net ([47.43.20.27] helo=impout003.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kktbk-0007Ds-8V for pgsql-admin@lists.postgresql.org; Thu, 03 Dec 2020 18:45:47 +0000 Received: from cerberus.crazybean.net ([71.11.29.254]) by cmsmtp with ESMTP id ktbikRYVfr76UktbikoZan; Thu, 03 Dec 2020 18:45:35 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=dotv9Go4 c=1 sm=1 tr=0 a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17 a=xqWC_Br6kY4A:10 a=zTNgK-yGK50A:10 a=pGLkceISAAAA:8 a=0dGcE5mUCHH5BR_49gUA:9 a=QEXdDO2ut3YA:10 a=m3-3_0tstJFoVqJZ9hQA:9 a=k9pHczx3s9_Ew3S8:21 a=_W_S_7VecoQA:10 Received: from [IPv6:2001:470:e117:4:cce6:b6f8:d74c:4ab8] ( [2001:470:e117:4:cce6:b6f8:d74c:4ab8]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 65cce429 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Thu, 3 Dec 2020 13:45:34 -0500 (EST) From: Rui DeSousa Message-Id: <35E33498-8CD9-4D81-A641-42343342D337@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_D3395D2A-2AC8-45BC-859C-0EE48A86E5F4" Mime-Version: 1.0 (Mac OS X Mail 14.0 $3654.20.0.2.21$) Subject: Re: time taking deletion on large tables Date: Thu, 3 Dec 2020 13:45:33 -0500 In-Reply-To: Cc: pgsql-admin To: Atul Kumar References: X-Mailer: Apple Mail (2.3654.20.0.2.21) X-CMAE-Envelope: MS4wfLq4YLI4L+S7VC+9DeGhjjA0OtM7kT/VD3LC9ZnrdW0RsWAoko0yWUuzq9LyuUatSskTpoGS2QNEBUNmTVEgmUQEsoO8PC5eBeMt5STB0OcftYIGD+F9 FCkeJb6cN1yZWF/8L+iY3HGh7Cvd2AIettD1PDVC5SoMWB3fvJI2G2EaGV0/BDJLcA1z5wnDntvxXPbcLhHFVhMAL23k1wdXMQa29Ve43A7gxGl7GgplcmZ4 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_D3395D2A-2AC8-45BC-859C-0EE48A86E5F4 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Dec 3, 2020, at 9:45 AM, Atul Kumar wrote: >=20 > The feed_posts table has over 50 Million rows. >=20 > When I m deleting all rows of a certain type that are over 60 days = old. >=20 > 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 the records in batches. I have used this approach many times = successfully for large tables that are highly active on live production = systems.=20 You=E2=80=99ll have to find the correct batch size to use for your = dataset while keeping the run time short; i.e. 30 seconds. Then = repeatedly call the function using a script =E2=80=94 I=E2=80=99ve used = a perl script with the DBI module to accomplish it.=20 i.e.=20 create or replace function purge_feed_post (_purge_date date, _limit int = default 5000) returns int=20 as=20 $$ declare=20 _rowcnt int; begin create temp table if not exists purge_feed_post_set ( feed_post_id int ) ; /* Identify records to be purged */ insert into purge_feed_post_set ( feed_post_id ) select feed_post_id from feed_posts=20 where created_at < _purge_date order by created_at limit _limit ; =20 /* Remove old records */ delete from feed_posts using purge_feed_post_set=20 where feed_posts.feed_post_id =3D purge_feed_post_set.feed_post_id ; get diagnostics _rowcnt =3D ROW_COUNT; delete from purge_feed_post_set; return _rowcnt; end; $$ language plpgsql set search_path =3D public ; --Apple-Mail=_D3395D2A-2AC8-45BC-859C-0EE48A86E5F4 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Dec 3, 2020, at 9:45 AM, 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 = the records in batches. I have used this approach many times = successfully for large tables that are highly active on live production = systems.

You=E2=80=99ll have to find the correct batch size to use for = your dataset while keeping the run time short; i.e. 30 seconds. = Then repeatedly call the function using a script =E2=80=94 I=E2=80=99= ve used a perl script with the DBI module to accomplish = it.

i.e.

create or replace function purge_feed_post = (_purge_date date, _limit int default 5000)

= returns int

declare

_rowcnt int;

= begin

create temp table if not exists = purge_feed_post_set (

= feed_post_id int

)

;

/* Identify records to be purged */

insert into purge_feed_post_set (

feed_post_id

= )

select = feed_post_id

from = feed_posts

where = created_at < _purge_date

= order by created_at

limit = _limit

;

/* Remove old records = */

delete from feed_posts using = purge_feed_post_set

= where feed_posts.feed_post_id =3D = purge_feed_post_set.feed_post_id

= ;

= get diagnostics _rowcnt =3D ROW_COUNT;

delete from = purge_feed_post_set;

return _rowcnt;

= end;

$$ language plpgsql

= set search_path =3D public

;

= --Apple-Mail=_D3395D2A-2AC8-45BC-859C-0EE48A86E5F4--