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 1pE9Qk-00048V-4r for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 13:40:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pE9Qj-0005xM-0v for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 13:40:13 +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 1pE9Qi-0005rW-Jy for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 13:40:12 +0000 Received: from mail-oo1-xc2d.google.com ([2607:f8b0:4864:20::c2d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pE9Qb-00052F-RK for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 13:40:11 +0000 Received: by mail-oo1-xc2d.google.com with SMTP id c190-20020a4a4fc7000000b004a3addd10b5so1198485oob.1 for ; Sat, 07 Jan 2023 05:40:05 -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=oDeiI1za3JVZ013k7yPHXSYR/hNAiFXseSN1GhBmVR4=; b=dYzk7Y6ekXre9z0R4W2bbs+GNonAfbKxpNInLBfheTIoM5Kz++0ty6g66S/hRXXp4W TvRqh0RqARTiYTWxEE+kMgNKhVIKiABRu2U7DsdlFKWAb1+dWqmZfSTR7G5m7FCTIHQ+ 6gT7SKUkwRUgsXgg90df838O+Ml6O9nCvqQi/0yaV/UeS66n6RskQAIJobG43CR1Wr8y YM4m6EkxUZjTqAYuonv4DYjAzr698C2XOpiZCPfpOYXyR8SuQmrh08u8jWUSOElBNg2V 9ytACIE6gItDCavnODRsKWdwJjF1W6YFREApqZ6HUhLgpZBGRzUkUeVF+8wHejxj1z/u ucDQ== 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=oDeiI1za3JVZ013k7yPHXSYR/hNAiFXseSN1GhBmVR4=; b=p5VkiAteykw1MyTehaCHM5WfbR0Z5o9isqAAjquUNNJYZmdTCO/gdumo0o/J8aE7u9 QdkVYSIP64qu6NJtA4yCfdFEoUJl3Rjx341/C4pBnR8ZxUTthOKX3lF+3oDNZfKpG/YL EBgrPlubvM63ZekJ2xJJbTYRJOkdfrpy1Jnh9hjMNlnFsZCt9HkbKoRf/m9YopZysLLA BT1HHYwlWxYp7y/7N7tVDKMnyLi7FYAKaC2pgsqqnmR3lo1QXyNiKlL16w8l2g3iF8zB BhvX5z0p/6DbMbP2Z1U/87/EWjBtxmIc8dyRzh0wdsy5W4evoclM8MDJvetqkcPQvqxE HNtw== X-Gm-Message-State: AFqh2kqJFuX/Z/9JAXXgHazA/WAmgPEeWkgGTp51QZVScCSHgicaFkgh AdBOtRa8jVXxRLRdgqGtCcWej8RVz8A= X-Google-Smtp-Source: AMrXdXv6f57B0976B9xcFI+VK+c+3d85MsupK6MuUP/bZKBQMNwPSs3EMD/k6ItHJip5dh06m6Yn2A== X-Received: by 2002:a4a:4f96:0:b0:4a3:dcf2:b164 with SMTP id c144-20020a4a4f96000000b004a3dcf2b164mr26778330oob.6.1673098804349; Sat, 07 Jan 2023 05:40:04 -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 h25-20020a4ad019000000b004ee1aea0bcbsm1660952oor.13.2023.01.07.05.40.02 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 07 Jan 2023 05:40:02 -0800 (PST) Content-Type: multipart/alternative; boundary="------------QKvkx7Hl4tZCAn39JlgYLVxq" Message-ID: Date: Sat, 7 Jan 2023 07:40:01 -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> <90262fac-d6c7-e253-6988-a081c9e47a15@gmail.com> <20230107112946.pamp3ot44sa7aylk@hjp.at> From: Ron In-Reply-To: <20230107112946.pamp3ot44sa7aylk@hjp.at> 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. --------------QKvkx7Hl4tZCAn39JlgYLVxq Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/7/23 05:29, Peter J. Holzer wrote: [snip] > If I understood correctly, you have to delete about 3 million records > (worst case) from the main table each day. Including the other 8 tables > those are 27 million DELETE queries each of which deletes only a few > records. That's about 300 queries per second. I'd be worried about > impacting performance on other queries at this rate. 300 *records*/second.  Fewer DELETE statements if there are one-many relationships with the child tables. > I'd go for a middle ground: Instead of expiring once per day, use a > shorter interval, maybe once per hour or once per minute. That will > (probably) make each expire job really quick but still create much less > load overall. -- Born in Arizona, moved to Babylonia. --------------QKvkx7Hl4tZCAn39JlgYLVxq Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/7/23 05:29, Peter J. Holzer wrote:
[snip]
If I understood correctly, you have to delete about 3 million records
(worst case) from the main table each day. Including the other 8 tables
those are 27 million DELETE queries each of which deletes only a few
records. That's about 300 queries per second. I'd be worried about
impacting performance on other queries at this rate.

300 records/second.  Fewer DELETE statements if there are one-many relationships with the child tables.

I'd go for a middle ground: Instead of expiring once per day, use a
shorter interval, maybe once per hour or once per minute. That will
(probably) make each expire job really quick but still create much less
load overall.

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