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 1pBGfq-0007S8-9p for pgsql-general@arkaria.postgresql.org; Fri, 30 Dec 2022 14:47:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pBGfn-0002pt-7n for pgsql-general@arkaria.postgresql.org; Fri, 30 Dec 2022 14:47:51 +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 1pBGfm-0002pj-RW for pgsql-general@lists.postgresql.org; Fri, 30 Dec 2022 14:47:50 +0000 Received: from mail-qv1-xf2b.google.com ([2607:f8b0:4864:20::f2b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pBGfk-0006ji-DZ for pgsql-general@lists.postgresql.org; Fri, 30 Dec 2022 14:47:49 +0000 Received: by mail-qv1-xf2b.google.com with SMTP id u2so1152568qvo.12 for ; Fri, 30 Dec 2022 06:47:48 -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:content-language :references:to:subject:user-agent:mime-version:date:message-id:from :to:cc:subject:date:message-id:reply-to; bh=rLgdz0yZkUC9qvrZ94rRf7H9VCekSN3pKjCkxFXxmgs=; b=qCC6NwNyfZeXRR/rb17tnQct+kLtg0+kSnNvnY9pkFa34RLn7x6zI+hAen80hbn4bA Ro4FdynIE7oR9Ii74XjKuMxEC8oob/UpMCzMa6ptWRgGca3vkP2IxQTeUyxnkbu9Uk0m PkviojMnX+KIf6+uoyi2mDiGy6OXWg8Opxgj1C9wr+gyoH+W3TowfNFSs+BXAdwJUHj/ AwR60QMTexT5ImRac6Y6rDNdwrmHzq1wiE+GO0X0n8+ANrjAcaSHoo1QpMlJA7s4m7nT 32nT2969JqmE3CciFYNU27CbDipoD8mDQRhjt6/QycKZ5ZmCgzjZXKkjM5pVdwNa3T7G a2cg== 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:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=rLgdz0yZkUC9qvrZ94rRf7H9VCekSN3pKjCkxFXxmgs=; b=aVgjyQmQzvZo0Wz6FlxgKmP3mPnc+KpCeJ6fdxnzH0CYxGWWjVtuK+xYZaL6S4dxe1 XzX/bBvzDHyjtXCJoHPcNLFBqjF64tS+H458TNI02ZEO3Ci+LeDsx2MGrxg4vS14W59d xUFWujGCElyspB1ugONcYNJ6K9SdMe98myRCXtKb8hleZqVNyhgI6ClrdbozaF5VyjEr P2VgDoVGgiRa1rgVCzDHniKRvNQZ+67UgGvBfqENr6tqd/uBRw9akS2NmB59RWWq/PdS vt5a1zJ2ZAXuQousNhglBmC9Ls1EUN7+8+3ukQpmIbWZHoNa+GFYWKDTLYlvlpsUeoSK X5tA== X-Gm-Message-State: AFqh2kpK4UUdNyvswyjPnbl2MO0AjGQx7irSUYpsVE2m3J766N2MJUlA pcE4InNYFifiiVEflkigDa2+vOirnZDtcQ== X-Google-Smtp-Source: AMrXdXs0tCFvtulaZIz6pdgPXsoKxzkZa19kMAN1zbq3xTdHxJaB971rfaAwh/cERsmM1oMtpFgyaw== X-Received: by 2002:a05:6214:3c09:b0:52d:e201:c3d2 with SMTP id nt9-20020a0562143c0900b0052de201c3d2mr45462883qvb.33.1672411666672; Fri, 30 Dec 2022 06:47:46 -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 d8-20020a05620a158800b006fcaa1eab0esm15180637qkk.123.2022.12.30.06.47.46 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 30 Dec 2022 06:47:46 -0800 (PST) Message-ID: <6f623e3a-d93d-bc89-b553-d2f87a82bd73@gmail.com> Date: Fri, 30 Dec 2022 08:47:45 -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 To: pgsql-general@lists.postgresql.org References: Content-Language: en-US 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 12/30/22 00:39, Ranjith Paliyath wrote: > Hi, > > We have a PostgreSQL (slightly old version, something like - PostgreSQL 11.2 You know, of course, that you should update to the latest version. It's quick and painless. > on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit) production, where one particular table and its related 5 tables need to be purged of 3 months prior data. Each of these tables' daily record increment is on an average 2 to 3 million. 270M rows isn't that much.  Deleting 3M rows should not take 3 hours, even when there are six tables. Are the tables tied together by FK? How big are the rows? > Approach needed is to do a daily purge of 90days prior data. Probable purge processing window is expected to be 2hrs. Observed test timing for deletion is exceeding 2-3hrs and we are trying to do vacuuming after the deletes, which is again taking exceeding another 2hrs. Is there an index on the date field? Can you drop unneeded indices during the window, and then rebuild them afterward? How beefy is your hardware? > There is a suggestion for re-creating the tables with partitions, and as purge approach could then be a deletion/dropping of these partitions, which would not really require a vacuuming later on. > > When we go for a Daily purge approach it should not put a strain on other processes which could be affecting this same set of tables, like these tables should not get locked because of the purge. > > Questions are - > (a) Should we recommend PostgreSQL upgrade, if possible, to v15.1? Could this bring in some benefits related to vacuuming? > (b) Would partitioning be an optimal approach? We tried this with Postgresql 12.x declarative partitioning on tables with synthetic keys.  Query performance suffered, since the date field needs to be added to the PK, and Pg tends to scan all the partitions, even when the date field is part of the WHERE clause. Thus, we departitioned all but the two with large bytea columns. -- Born in Arizona, moved to Babylonia.