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 1pDjzf-0002U6-Ug for pgsql-general@arkaria.postgresql.org; Fri, 06 Jan 2023 10:30:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pDjzd-00010t-0d for pgsql-general@arkaria.postgresql.org; Fri, 06 Jan 2023 10:30:33 +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 1pDjzc-00010g-CT for pgsql-general@lists.postgresql.org; Fri, 06 Jan 2023 10:30:32 +0000 Received: from mail-qv1-xf35.google.com ([2607:f8b0:4864:20::f35]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pDjza-0007wE-5P for pgsql-general@lists.postgresql.org; Fri, 06 Jan 2023 10:30:31 +0000 Received: by mail-qv1-xf35.google.com with SMTP id d13so743950qvj.8 for ; Fri, 06 Jan 2023 02:30:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding: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=X8R7yii0pqcnJKy6lfUpyPiQOZ9CDT67S7U14REJ/o0=; b=ceIHWeBDVmVa5V4D0PMSO2S3tMTQlt+7a/XpdPgNNjuZC5bvLu+2j/eYBdjfmrRVOX OiQ3XnLijymMCwDQ6HDtwYt6tyIUZ6yatguEXenA2EA+bKmMRnTTFEbQRsboaPpIlmne 9YzuuRfDQYLtcE9c2CncQMo7NEkuw18m9kB1FJshzkFlBCFsMxDI4t+xBxCNX+QG/iXR X9dO5FgR+TtjfDkHPYB6BTZSl8o7arMx2emZiAf7+J+Oa5O6t+hHRdijmaWIix5rHoVJ hl7WZl1SYv01IwbLWkJ1JsSWPl/X2Z+xTycYAukijWMhHYbutny1nEWV0xiExvN+5bxT hPlg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding: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=X8R7yii0pqcnJKy6lfUpyPiQOZ9CDT67S7U14REJ/o0=; b=hIng1J12I9AdmPE3qsJSdxNxKorl72JTxGkyw3wFdnQxSxwEU8atz3MzL7K0MHa9MG +3Am49gh+QScREPOpI+o5MgE6+/4BS6msBqaccSIA2RE6cR5oX25sTRUYSfpGCjWHIxo a6ui1O4mz/8ahqWEF/2+VXmSaYuv/qYrTHeTmZ47unFwdMT6QKlhXklbdD32ONZQNBEr HWRlreSo8kvlZB/5P7zAan+XYdMbbqKJO075l+31Z+YOSZ0p29bvxNZor5Ux/Ofc4Sp6 hIzn/p5+A4vGqlr9rn6DMAXvFCcacVQHPNXX23CG0Lu4ZjGaxGwjnBaadPcumtjFzFZq eV8Q== X-Gm-Message-State: AFqh2krTnA21G+3UgB3B8JcdO7iqfyjyS08YoCnq7j8cPl5Hj8IjXgyU DVNkrup3FPDcSgWQrKZjWugXD471aSI= X-Google-Smtp-Source: AMrXdXvdWNz9CfcOwHM8C9/QAsaj6p73m6hbrMaAYcXoHNYdMqqUlaeuZxp/OBIOYcH3e3sPIvBOWA== X-Received: by 2002:a0c:8041:0:b0:531:b375:2cda with SMTP id 59-20020a0c8041000000b00531b3752cdamr35614164qva.9.1673001028988; Fri, 06 Jan 2023 02:30:28 -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 az31-20020a05620a171f00b006fbbdc6c68fsm310752qkb.68.2023.01.06.02.30.28 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 06 Jan 2023 02:30:28 -0800 (PST) Message-ID: <1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com> Date: Fri, 6 Jan 2023 04:30:28 -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> From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/6/23 02:44, Ranjith Paliyath wrote: > Thank you for the details, experience shared and the suggestions. > Apologies for the delay in collecting the response for the queries. > > (1)Are the tables tied together by FK? > - Overall there are 9 tables (sorry not 6 as mentioned originally) that are being purged. Only 4 tables would be having FK relationship. > > (2)How big are the rows? > - The 9 tables now occupy almost 2TB space. Below is the rowsize (in bytes) and record-count details - > > 236 188,055,675 > 297 296,941,261 > 371 58,673,649 > 95 57,477,553 > 904 296,743,680 > 234 188,161,891 > 414 430,411,653 > 707 735,895,015 > 128 155,104,922 > > (3)Is there an index on the date field? > - Yes. But only in one table, which is the main table (records to purge in rest of the tables is based on this table). 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. -- Born in Arizona, moved to Babylonia.