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 1qLqZz-000ofJ-0v for pgsql-general@arkaria.postgresql.org; Tue, 18 Jul 2023 19:41:50 +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 1qLqZv-00ChHy-Bw for pgsql-general@arkaria.postgresql.org; Tue, 18 Jul 2023 19:41:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLqZv-00ChHq-1b for pgsql-general@lists.postgresql.org; Tue, 18 Jul 2023 19:41:47 +0000 Received: from mail-oi1-x233.google.com ([2607:f8b0:4864:20::233]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLqZr-000DMt-I4 for pgsql-general@lists.postgresql.org; Tue, 18 Jul 2023 19:41:46 +0000 Received: by mail-oi1-x233.google.com with SMTP id 5614622812f47-3a1e6022b93so4453296b6e.1 for ; Tue, 18 Jul 2023 12:41:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689709302; x=1692301302; h=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=rstitC8i5nplqDQh0AUCGtvGRklr/TwAMKuVnoNxZNM=; b=CIs1iTJw0lMEq8kjHahVpGwVZSJK6BVvfUMr7SmvR7gLP++KhefREhc5BxI4SwLNSe 0gh7Y9RFNiXE/BP6cWXSu5KjfY5URRSYYk5+ynceKXBHeyAPg9r/4Dp5CRn3FMDDS8xl YNS6Tg2te6lkAP0yABLkZWZa5VQTcQ+owHNqqX3JWv8gVGHGExYx2vffRWvUrId1gs8L 4z5NPW6D0J9rdK/aZ8qdoNLzq5a/WOdqGdpiJltOqiNmUMxGWYIHnZR3aHaEFNxxGahj IVYJUsCCJwB1w93MIGyquoZkA3mJZJWr/ygjoPJsBGQDXQRLVeVZP/QEGcdL775EbZuM TfsQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689709302; x=1692301302; h=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=rstitC8i5nplqDQh0AUCGtvGRklr/TwAMKuVnoNxZNM=; b=Eh68YcpSyobzVWSUTNvElc1Gn25mK0EpOESfUQithDyCcUOpyDjXRJfxLjpOYmhTHJ 6lcWZYDoRB+xvvOmwjprnaAabo38QhMdBq6qB2U6OYpUjIlYXTAvQQFn1SJzaHJLJVFx 5UCRyGWaA0UIhQ4vv4/GQk+Z7/UZtUrx4gXSg6DY3BO2stEaVhwTM2F+dBkeMMsZoDYu 4nEIEL7u+Zo6eC4jABWy36Cmse4AOP+ruVR6aa+L9VlhQ6TVXjzyZihfzXpabcxzXXmi XyPVVZCRIr5FCU9yTUpYDJ+y1n8OS6WFFNco95Q2V96Bo9DFpPwa/98Ff4gCyivmQMay X9tA== X-Gm-Message-State: ABy/qLa3nesu31Yy8EM6XTGkP+XsMFLV4atNwwlE0UfYPcmN+JS59+le Qi1mnETvivvkcv2crqqFoU9vEMlgxJKym2o6 X-Google-Smtp-Source: APBJJlHxRvMV8NZQ03dJsFml3JhG7Djz7mvvlqCz2o5LJcRn33AnP2jlR1HbPfKKguUqSjL6qioUgw== X-Received: by 2002:a05:6358:7189:b0:135:73b0:cc6c with SMTP id t9-20020a056358718900b0013573b0cc6cmr14153941rwt.28.1689709302536; Tue, 18 Jul 2023 12:41:42 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:f556:9a53:6796:73d2? ([2601:681:5a00:f790:f556:9a53:6796:73d2]) by smtp.gmail.com with ESMTPSA id 4-20020a17090a1a4400b00263f8915aa3sm7325032pjl.31.2023.07.18.12.41.41 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 18 Jul 2023 12:41:42 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------YyESidyXubvMzGdIXikPt9nw" Message-ID: <7e97caed-e625-867d-a4f9-8512c8db3421@gmail.com> Date: Tue, 18 Jul 2023 13:41:40 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Effects of dropping a large table To: pgsql-general@lists.postgresql.org References: Content-Language: en-CA 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. --------------YyESidyXubvMzGdIXikPt9nw Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 7/18/23 11:58, Devin Ivy wrote: > Hi all, > I'm hoping to ensure I understand the implications of dropping a large > table and the space being reclaimed by the database and/or OS.  We're > using pg v14. > > This table is quite large with a primary key and one additional > index—all together these are on the order of 1TB. The primary key and > index take-up quite a bit more space than the table itself.  Our hope > is to discontinue use of this table and then eventually drop it.  > However, the database is under constant load and we'd like to avoid > (or at least anticipate) downtime or degraded performance.  The > database also replicates to a standby instance. > > So in short, what can we expect if we drop this table? Will the > strategy that pg takes to give that space back to the rest of the > database and/or OS have significant effects on availability or > performance?  Finally, are there any other considerations that we > should take into account?  I appreciate your time and input, thanks! > > -- > Devin Ivy 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. --------------YyESidyXubvMzGdIXikPt9nw Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 7/18/23 11:58, Devin Ivy wrote:
Hi all,
I'm hoping to ensure I understand the implications of dropping a large table and the space being reclaimed by the database and/or OS.  We're using pg v14.

This table is quite large with a primary key and one additional index—all together these are on the order of 1TB.  The primary key and index take-up quite a bit more space than the table itself.  Our hope is to discontinue use of this table and then eventually drop it.  However, the database is under constant load and we'd like to avoid (or at least anticipate) downtime or degraded performance.  The database also replicates to a standby instance.

So in short, what can we expect if we drop this table?  Will the strategy that pg takes to give that space back to the rest of the database and/or OS have significant effects on availability or performance?  Finally, are there any other considerations that we should take into account?  I appreciate your time and input, thanks!

--
Devin Ivy
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.



--------------YyESidyXubvMzGdIXikPt9nw--