Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vl73z-007Fdl-01 for pgsql-general@arkaria.postgresql.org; Wed, 28 Jan 2026 15:02:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vl73y-001q8a-0E for pgsql-general@arkaria.postgresql.org; Wed, 28 Jan 2026 15:02:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vl73x-001q8S-2G for pgsql-general@lists.postgresql.org; Wed, 28 Jan 2026 15:02:34 +0000 Received: from mail-pf1-x42f.google.com ([2607:f8b0:4864:20::42f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vl73v-00000000tQZ-3siJ for pgsql-general@lists.postgresql.org; Wed, 28 Jan 2026 15:02:33 +0000 Received: by mail-pf1-x42f.google.com with SMTP id d2e1a72fcca58-81f47610542so3729410b3a.0 for ; Wed, 28 Jan 2026 07:02:32 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1769612550; cv=none; d=google.com; s=arc-20240605; b=fR/cSEqscCiS6fKBs6RWJiEagOzQ2hHV6vMfn+d4T1NEA2dXBkyUW6JY5Hs/TkEPMR AMuSIycayV/8TR+q2vp0L3YLPqxwdxnVkGiGe+Ta+OOskIq8ymXepBz6Bi1a9zforLMa LP/KLQUoP8ghaG/F0FW7Nhkg87GejLi6SAkVLMnxD64+GUloe7Bb3L5h0M0DW6dfP8Qz krbL+xidLcMB9f989GW0A1IHRD1TKApq5MAXnAoOYwLuf+sTDGDT3Y+yfT1hudVM3OfZ svDoB1qy4jDSGtPvadW6S/8BWtRmk4XUtJNvL/HACKiGDOKpzWnIBzZr+P722rhTnO4k NlQg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=zIT2P56eYH4nEA9fMdJIx+VsOvlGETuWTpsST6uUsYE=; fh=SBE4MchX+y0SW0v43uzKPaLUC7wl/+eheBl/1RUyJ3Y=; b=QGrja9ND1cTgBWS1U0wLi6MTu2NNyeYwvpPmqw1eDtv7MUhAJfQ5QnmeXLlF/idQb3 Sva8uyvz2XKW1eJKmXjLirmRFGASLMYZ4uLEQA8Fo5bdc2yBZk28UMaiwqau4my4zABZ O+CWXLPYQrbCKq5+PNlAO7Fhoe6xWJeZcFJIqu7092NB/NFkVbf+PTIyhVJTLQdljw9t zKwlfc8gfvu56oTOGt2465zr0dz+R9Ud+V9B3Fek3bzbFyaAn2oxtk3y97CyKwyyQ4CU QQWIRpyjHtOR++Nl149Rb3E7uHyup8SoHlUp8CBh0MvFiEGSXiyThU9gVf41avShzrgK 9POw==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1769612550; x=1770217350; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zIT2P56eYH4nEA9fMdJIx+VsOvlGETuWTpsST6uUsYE=; b=Yuz6vrULuIRjYkMvFugfNJEk3RDg5abnH6yzWtS2SyT9hk63kXtn7h5OIVUXaM+cjw VL1wE+jagFmJUJPY9yJltE2xz76Uzs9EbxKyyc5MJ3hMYr42XpvB1zjsHRLAzA4epgN4 RX9lr4qyorIkVCBwuq31Kx4doCd3rAksZ6HmzHJURULAaUrcJsPruNIlGPvm2EGvPxzh +qNKuWujo00TEIzdZ9jdL91Mo893ibjyiQZEY/yf26X3brCf/W10Q/xrYlSE/8K74suD vlKFOn3kRPKnqcrwtuD5axWo7Q7IfIxEoze2jOzorvXb3g8vDFsH2AyGEK4axZVHmjFx emjA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1769612550; x=1770217350; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=zIT2P56eYH4nEA9fMdJIx+VsOvlGETuWTpsST6uUsYE=; b=BJyHu5nXraT5fiHbPk8hVXugOjSBYazlBQsJ0bVzgoltlR9trxAaG82RMNDWuVNfOF YEw/N03dZbXChmYd3qSh87NEfX8fmPKyiCB2IkUKQP+MrThM3KkpUtyATN0jLR7rN8ud J/Wqb48fZGJo/Z17cSU9UBuPYvuI3ZlUPicu53eEIQ7jRRz6Hf735QLIg6w2hlp/fn7q F32U5uflbhL/lCgVgYW/MYsaa+ZDoUkQkPwYcQkq1h+OIjCiT+yP9Nvlkayu1XE38z1n pgWt2fxycBna05p8trSwGjoB3lHGRe8Yzefk94XCfkCrFFseh4nRwrlQ/lb8qyt3mz0u NLGQ== X-Forwarded-Encrypted: i=1; AJvYcCWUJ6c/VcvHSiQBp1ItrglnMZuc740vuZkGIi7YP2zAOTz/VaGeVL1sRj7FDp4SSjk+jo/GrjKNe9k3qIAa@lists.postgresql.org X-Gm-Message-State: AOJu0YxkAh0GW0oBj1LNa7s/PFxI2F9YlILILPCJaNwSHYHN48+OzN02 0hGEkoT8G7f4olgjsYq9h8SN5o0lmfHULOK51PzL+P5ZN+IjIfmTXwpVXiesw2sTALzW97+x1lN +Xum+lJSHju6IcBpqQNt4b7JiQ+fXQfM= X-Gm-Gg: AZuq6aILE1FbuMoe2gl1GFGE0k7dra4lHvPx2aVQ/W4VSKDadlAHdImhwO+/BdsW/9f IhwoYAscIIm/Hztal41e+enWH3SoupzIQ/O73nAiWuhvQWxHVG7wZAlZXyDhguxX4A7WyJlD9Gd UfGD74o5O9X1CoHSv+eKeQjNJ4RCjW7UgkrzAGvgo1Vz2zxyXUkB+LWGvNxr1Sc6Gn9Nu4fOb+z m2vGfC45hjOmFxO79ChgjBYLLKXy/zLwlxsICstaaeFbvTwajw7z8sncvRecnzn1ylRuJ46 X-Received: by 2002:a05:6a21:6f07:b0:38d:ebdc:3558 with SMTP id adf61e73a8af0-38ec6280d5cmr5199864637.6.1769612549773; Wed, 28 Jan 2026 07:02:29 -0800 (PST) MIME-Version: 1.0 References: <729685.1769572320@sss.pgh.pa.us> <731539.1769573201@sss.pgh.pa.us> In-Reply-To: From: Ron Johnson Date: Wed, 28 Jan 2026 10:02:17 -0500 X-Gm-Features: AZwV_QjXKTR9zcpweTqP6Bk1OboCq8FJoDotS3D6cFbxF79Eq8zfzKDS-B0Eeak Message-ID: Subject: Re: Attempting to delete excess rows from table with BATCH DELETE To: Gus Spier Cc: Olivier Gautherot , Tom Lane , "David G. Johnston" , pgsql-general Content-Type: multipart/alternative; boundary="0000000000006b5ce406497407c1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006b5ce406497407c1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable More thoughts: 1. In cases where records are huge (bytea storing images) I added an inner hourly loop. 2. Disable autovaccum on the table you're purging, then run pg_repack on it and re-enable autovacuum. 3. pg_repack --no-order is a lot faster than having it order by the PK. (You might *want* it ordered by an indexed date field, though.) On Wed, Jan 28, 2026 at 5:57=E2=80=AFAM Gus Spier wro= te: > Thanks to all. > > I'll give the bash loop method a try and let you know how it works out. > > Regards to all, > Gus > > > On Wed, Jan 28, 2026 at 2:32=E2=80=AFAM Olivier Gautherot > wrote: > > > > Hi Gus! > > > > This reminds me of a costly mistake I made and you want to avoid: it wa= s > a mission critical database (say physical safety, real people) and the > vacuum froze the DB for 24 hours, until I finally took it offline. > > > > If you can take it offline (and you have a couple of hours) > > - disconnect the DB > > - drop indexes (that's the killer) > > - remove unnecessary data > > - vaccuum manually (or better, copy the relevant data to a new table an= d > rename it - this will save the DELETE above and will defragment the table= ) > > - rebuild indexes > > - connect the DB > > > > The better solution would be partitioning: > > - choose a metrics (for instance a timestamp) > > - create partition tables for the period you want to keep > > - copy the relevant data to the partitions and create partial indexes > > - take the DB off line > > - update the last partition with the latest data (should be a fast > update) > > - truncate the original table > > - connect partitions > > - connect the DB > > > > In the future, deleting historic data will be a simple DROP TABLE. > > > > Hope it helps > > -- > > Olivier Gautherot > > Tel: +33 6 02 71 92 23 > > > > > > El mi=C3=A9, 28 de ene de 2026, 5:06=E2=80=AFa.m., Tom Lane > escribi=C3=B3: > >> > >> Ron Johnson writes: > >> > Hmm. Must have been START TRANSACTION which I remember causing > issues in DO > >> > blocks. > >> > >> Too lazy to test, but I think we might reject that. The normal rule > >> in a procedure is that the next command after a COMMIT automatically > >> starts a new transaction, so you don't need an explicit START. > >> > >> regards, tom lane > >> > >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000006b5ce406497407c1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
More thoughts:
1. In cases where records ar= e huge (bytea storing images) I added an inner hourly=C2=A0loop.
= 2. Disable autovaccum=C2=A0on the table you're purging, then run pg_rep= ack on it and re-enable autovacuum.
3. pg_repack=C2=A0--no-order = is a lot faster than having it order by the PK.=C2=A0 (You might want=C2=A0it ordered by an indexed date field, though.)

O= n Wed, Jan 28, 2026 at 5:57=E2=80=AFAM Gus Spier <gus.spier@gmail.com> wrote:
Thanks to all.

I'll give the bash loop method a try and let you know how it works out.=

Regards to all,
Gus


On Wed, Jan 28, 2026 at 2:32=E2=80=AFAM Olivier Gautherot
<ogauthero= t@gautherot.net> wrote:
>
> Hi Gus!
>
> This reminds me of a costly mistake I made and you want to avoid: it w= as a mission critical database (say physical safety, real people) and the v= acuum froze the DB for 24 hours, until I finally took it offline.
>
> If you can take it offline (and you have a couple of hours)
> - disconnect the DB
> - drop indexes (that's the killer)
> - remove unnecessary data
> - vaccuum manually (or better, copy the relevant data to a new table a= nd rename it - this will save the DELETE above and will defragment the tabl= e)
> - rebuild indexes
> - connect the DB
>
> The better solution would be partitioning:
> - choose a metrics (for instance a timestamp)
> - create partition tables for the period you want to keep
> - copy the relevant data to the partitions and create partial indexes<= br> > - take the DB off line
> - update the last partition with the latest data (should be a fast upd= ate)
> - truncate the original table
> - connect partitions
> - connect the DB
>
> In the future, deleting historic data will be a simple DROP TABLE.
>
> Hope it helps
> --
> Olivier Gautherot
> Tel: +33 6 02 71 92 23
>
>
> El mi=C3=A9, 28 de ene de 2026, 5:06=E2=80=AFa.m., Tom Lane <tgl@sss.pgh.pa.us> = escribi=C3=B3:
>>
>> Ron Johnson <ronljohnsonjr@gmail.com> writes:
>> > Hmm.=C2=A0 Must have been START TRANSACTION which I remember = causing issues in DO
>> >=C2=A0 blocks.
>>
>> Too lazy to test, but I think we might reject that.=C2=A0 The norm= al rule
>> in a procedure is that the next command after a COMMIT automatical= ly
>> starts a new transaction, so you don't need an explicit START.=
>>
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0regards, tom lane
>>
>>


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000006b5ce406497407c1--