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 1pDoA5-0004xc-AY for pgsql-general@arkaria.postgresql.org; Fri, 06 Jan 2023 14:57:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pDoA3-0007Hx-PZ for pgsql-general@arkaria.postgresql.org; Fri, 06 Jan 2023 14:57:35 +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 1pDoA3-0007Ho-9T for pgsql-general@lists.postgresql.org; Fri, 06 Jan 2023 14:57:35 +0000 Received: from mail-qt1-x82c.google.com ([2607:f8b0:4864:20::82c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pDo9w-00025F-Hp for pgsql-general@lists.postgresql.org; Fri, 06 Jan 2023 14:57:34 +0000 Received: by mail-qt1-x82c.google.com with SMTP id g7so2287635qts.1 for ; Fri, 06 Jan 2023 06:57:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=xTES/HIqSEhIn6DEB8orS4dFyW9Fe+wGnEva6x5SpLM=; b=Nret1vYFgsdLCCxMSsSTrHPeb8MrA2OgA8vbegqPmlq/hFQVWD5vq4iojtBOJGmPV8 P6LfTnp7eXRz0fVnBj8BB5NCnkkX4wfOoXJZbe7yzPFjiUe1+ocfh9ou/JuOzmGGEyAD 6nxasVtzgkgTu53teYzu+6kcdV4pRzXsZklp3WGCcxS/MV3P5a4kqXuzq68VWl8sHd7X qPDDXw5NOvcLH2ppRG043vGXXR1NSjJIVzomEe2Y3I7JIh3zrZxcI73oMlQCSzsln19Y QQTSOGxDXVTq+Pj8yHDpansXp4ecJ0waNYU2kVkTVBuHAOF5r/10hrDdRPTy360Q2QT4 6xYQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=xTES/HIqSEhIn6DEB8orS4dFyW9Fe+wGnEva6x5SpLM=; b=3pD20kptpkGZ7wqaRoZXmrVkMujWNS0UHZoxsqFVSDg9vlJUic+u28VnBUypaiDntm +d3UVFTfThIm31fZNSsMA+uIl4zBOlAk/nIP1dX8vT+nuksa2CkWz3Gy+vpkQM4RPygq 4aZmbedJ+5ycQgYVK21Rj2evlUx3gtcFajNMJ6PeR6b+m/icAd6rsiydiT/bg5pl56QO vl+oA4e2j/oOaGETLjZcoTF1yQH9ZeQwsMcp2m3zexTIExb+pBZ10hNJCxKZ5q55BC6D iadiNXzpoa4YnTg2NVPn7vsAcofHIJIgwl4iCMMY5bLXc+QnJNOQ/v5KnuIUi0gsOz6S n1qA== X-Gm-Message-State: AFqh2krBHV6jfYI7W55X2gq56ZhZSoT2rACasH42gSjARmAapuMdW3V0 rdFCwoNGZBT0Pgx56jSMzbYk5EBNeas= X-Google-Smtp-Source: AMrXdXtB+DGdK8SMH+nfY/zoOrGOPsT/V0f/q1nf2WhoRkEuay9mwZ1sULqSVEAc+yYcY1Lobkut8g== X-Received: by 2002:ac8:6705:0:b0:39c:da20:65f with SMTP id e5-20020ac86705000000b0039cda20065fmr78624230qtp.2.1673017047366; Fri, 06 Jan 2023 06:57:27 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id i25-20020ac860d9000000b003a7fc70d59csm597243qtm.41.2023.01.06.06.57.26 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 06 Jan 2023 06:57:27 -0800 (PST) Content-Type: multipart/alternative; boundary="------------WUlfb9kiw48lCDt5F1k003oJ" Message-ID: <90262fac-d6c7-e253-6988-a081c9e47a15@gmail.com> Date: Fri, 6 Jan 2023 08:57:26 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Purging few months old data and vacuuming in production Content-Language: en-US To: "pgsql-general@lists.postgresql.org" References: <6f623e3a-d93d-bc89-b553-d2f87a82bd73@gmail.com> <1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------WUlfb9kiw48lCDt5F1k003oJ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/6/23 08:27, Ranjith Paliyath wrote: > Thank you very much for the response. > > > Can you do online purging? > > > For example, get a list of the main table's primary keys to be deleted, and > > then nibble away at them all day: in one transaction delete all the records > > for one logically related set of records.  Do that N million times, and > > you've purged the data without impacting production. > > So, with this approach, is the advantage like, manual vacuuming worry may be set aside, because auto-vacuuming would deal with the dead rows? Theoretically, manual vacuuming is never necessary.  I'd occasionally do manual vacuums (after purging a couple of weeks of data, for example). Disable autovacuum on a table, vacuum it, then reenable autovacuum. ALTER TABLE table_name SET (autovacuum_enabled = false); VACUUM table_name; ALTER TABLE table_name SET (autovacuum_enabled = true); > This is because the deletion step is executed record by record in main table, with its connected record(s) delete executions in rest of tables? I don't know if you have ON DELETE CASCADE.  Even if you do, you'll have to manually delete the tables not linked by FK.  I'd write a PL/pgSQL procedure: pass in a PK and then delete records from the 9 tables in the proper order so as to not throw FK constraint errors. > Due to the infra capability that is there in this instance, What is "infra capability"? > the impact could be almost none!!?? It'll use /some/ resources, because it's a thread deleting records, but most of the records and index nodes won't be where new records are being inserted. Note, though, that this will generate a lot of WAL records. -- Born in Arizona, moved to Babylonia. --------------WUlfb9kiw48lCDt5F1k003oJ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/6/23 08:27, Ranjith Paliyath wrote:
Thank you very much for the response.

    > Can you do online purging?

    > For example, get a list of the main table's primary keys to be deleted, and
    > then nibble away at them all day: in one transaction delete all the records
    > for one logically related set of records.  Do that N million times, and
    > you've purged the data without impacting production.

So, with this approach, is the advantage like, manual vacuuming worry may be set aside, because auto-vacuuming would deal with the dead rows?

Theoretically, manual vacuuming is never necessary.  I'd occasionally do manual vacuums (after purging a couple of weeks of data, for example).

Disable autovacuum on a table, vacuum it, then reenable autovacuum.

ALTER TABLE table_name SET (autovacuum_enabled = false);
VACUUM table_name;
ALTER TABLE table_name SET (autovacuum_enabled = true);


 This is because the deletion step is executed record by record in main table, with its connected record(s) delete executions in rest of tables? 

I don't know if you have ON DELETE CASCADE.  Even if you do, you'll have to manually delete the tables not linked by FK.  I'd write a PL/pgSQL procedure: pass in a PK and then delete records from the 9 tables in the proper order so as to not throw FK constraint errors.

Due to the infra capability that is there in this instance,

What is "infra capability"?

the impact could be almost none!!??

It'll use some resources, because it's a thread deleting records, but most of the records and index nodes won't be where new records are being inserted.

Note, though, that this will generate a lot of WAL records.

--
Born in Arizona, moved to Babylonia.
--------------WUlfb9kiw48lCDt5F1k003oJ--