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.94.2) (envelope-from ) id 1qMGfs-001zv4-Mg for pgsql-general@arkaria.postgresql.org; Wed, 19 Jul 2023 23:33:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qMGfq-00COuS-3x for pgsql-general@arkaria.postgresql.org; Wed, 19 Jul 2023 23:33:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMGfp-00COuK-PH for pgsql-general@lists.postgresql.org; Wed, 19 Jul 2023 23:33:37 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qMGfm-000ObK-83 for pgsql-general@lists.postgresql.org; Wed, 19 Jul 2023 23:33:36 +0000 Received: by mail-ot1-x32d.google.com with SMTP id 46e09a7af769-6b9b835d302so151463a34.1 for ; Wed, 19 Jul 2023 16:33:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689809613; x=1692401613; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=8mjWln3Dw/B6rMBp0sEnI5fNd9BHQZjr2PXZfSSvva4=; b=UR1hFhEK+ZwP+3VRLqbRSjJzi0M2IoYBhfvSZmxbIr+hKws5T6TSsQw5166fZ8PjSt lJc4uN9ZOe/Y/OT4tuTeLlS6JuywM8n8pbnzpKlHGmdDlXkUouHcD5fE8szzsQmBWFRU HsXlTIUbTZ/lcaQrF9ODXXE5c3ewnAVdzTCrqTSarrczJMSPgvIOyTh59bWrbHgfr2sw JCBd2oSymM7cxMra7urdNfWSkpACsLkionF0zOeWybGAFOK/LSKMpz5tJj6fJpqb921l jDiAi1epYIAHc4gOhUEZ7ebTe8OH2/Oe1MeCuFmRnlgtYpQUL2IBsXkXgvF78uOSWp/R uAHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689809613; x=1692401613; h=in-reply-to:from:references:cc: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=8mjWln3Dw/B6rMBp0sEnI5fNd9BHQZjr2PXZfSSvva4=; b=S+EsTB6twwbNxtqwGsynKNY5rldLclE07RpJWk/ivZ8J5yH5d+u41nPOTei+4qz4Md H6ANgxmySVuWmJno5di2Zdu2HzN6hy5gcYpRpqTJXJelQBH+LHmKm14YXoY4lzzVpc2t Q8IrXl1NPjSImXsvwMHWGphyRG+olYpzD4xY55w1IivSU1RGKScj0YmdiBkYcfJo80BE 8E4lj4lmRWub1ISK+LOlQkq/440NX/Ico1xLX+iouGmtLq5/NB4t/c8pQYLb1P663HIM tvB+qTM5q39l2Jr+eqt8BW1eQcLGen56tycxWEzhAVBN8sWz7ohZSkjMOM4Y1OAw3cjg cdjg== X-Gm-Message-State: ABy/qLaNNETnr6dL6VtASSuHLoaqufVJ3BmBk11GGqMm+lWQ3mOW7xFP 8ITKt8zvbXDVoSWkzh1836wFSd9FNBomQA== X-Google-Smtp-Source: APBJJlEgOwlUiVdMYFMDW546MqzpbVlPIDpCGvwx4rC07Ilbl7l9iDYz1uGHcd7T+ZAFLFhX7mltVQ== X-Received: by 2002:a9d:738e:0:b0:6b8:6e6c:6b5a with SMTP id j14-20020a9d738e000000b006b86e6c6b5amr745300otk.21.1689809613304; Wed, 19 Jul 2023 16:33:33 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:2948:5305:10bb:4929? ([2601:681:5a00:f790:2948:5305:10bb:4929]) by smtp.gmail.com with ESMTPSA id i5-20020a639d05000000b0055b44a901absm4095806pgd.70.2023.07.19.16.33.32 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 19 Jul 2023 16:33:32 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------b7BbNZKod4pgDkgnhO0GpVRp" Message-ID: Date: Wed, 19 Jul 2023 17:33:31 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.13.0 Subject: Re: Effects of dropping a large table Content-Language: en-CA To: David Rowley Cc: pgsql-general@lists.postgresql.org References: <7e97caed-e625-867d-a4f9-8512c8db3421@gmail.com> From: Rob Sargent 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. --------------b7BbNZKod4pgDkgnhO0GpVRp Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 7/19/23 17:15, David Rowley wrote: > On Wed, 19 Jul 2023 at 07:41, Rob Sargent wrote: >> You might consider deleting portions of the table in separate (consecutive) batches (maybe 5% per delete). And then truncate table is not logged so that might be an alternative. > Can you explain why this would be a useful thing to do? > > It sounds to me like it would just create a load of needless WAL from > the deletes and the vacuum that cleans up the dead rows each of which > is more likely to cause lag problems on the replica servers, which the > OP is trying to avoid. > > David No, you're right.  I was remembering problems with _deleting_ essentially all of a large table (with limited resources).  The drop might not have the same problem.  But aren't they logged/transactional and then in the WALs anyway. --------------b7BbNZKod4pgDkgnhO0GpVRp Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 7/19/23 17:15, David Rowley wrote:
On Wed, 19 Jul 2023 at 07:41, Rob Sargent <robjsargent@gmail.com> wrote:
You might consider deleting portions of the table in separate (consecutive) batches (maybe 5% per delete).  And then truncate table is not logged so that might be an alternative.
Can you explain why this would be a useful thing to do?

It sounds to me like it would just create a load of needless WAL from
the deletes and the vacuum that cleans up the dead rows each of which
is more likely to cause lag problems on the replica servers, which the
OP is trying to avoid.

David
No, you're right.  I was remembering problems with _deleting_ essentially all of a large table (with limited resources).  The drop might not have the same problem.  But aren't they logged/transactional and then in the WALs anyway.

--------------b7BbNZKod4pgDkgnhO0GpVRp--