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 1rj05f-002tbP-Fm for pgsql-general@arkaria.postgresql.org; Sat, 09 Mar 2024 17:02:31 +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 1rj05d-001W7X-93 for pgsql-general@arkaria.postgresql.org; Sat, 09 Mar 2024 17:02:29 +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 1rj05c-001W7O-UR for pgsql-general@lists.postgresql.org; Sat, 09 Mar 2024 17:02:29 +0000 Received: from fhigh4-smtp.messagingengine.com ([103.168.172.155]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rj05a-003din-BL for pgsql-general@lists.postgresql.org; Sat, 09 Mar 2024 17:02:28 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailfhigh.nyi.internal (Postfix) with ESMTP id A0F4011400B1; Sat, 9 Mar 2024 12:02:24 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Sat, 09 Mar 2024 12:02:24 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1710003744; x=1710090144; bh=P01WzKG6WxBI/5xbpic/x5zWH1c0HE+puv6QqDbQoQU=; b= mrbzNXRyMAnR2q92I3snODKtXssCE49LNCWcBpjdj5CXa7sOIQUajfFqUFSU959J 2515iIijthqPbh8J9k3XStdiAdf2XoUu+o/BJRQvq6PJexo2s1prIJ5qP5BgZyRU m78EZ/YRnuLAO9J5zzeTt3orBB1KbJSsBWzNb6/Sx6cdY570Vp3SL66SnWY0CXCo eOFHBjMG3jGSNIMOV29pP+S93lCv3wdg2tB4vuidCm/NTamCITPNv9ZkPi3wEh1g Y0kDIoHjQhqFogBnK3Fwfgzcayc7y5LpYZObAfGUS04CM7EYDfMSZxr4AH1OQx8a S1Jgdf6nG37K6FTudnkbCw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1710003744; x= 1710090144; bh=P01WzKG6WxBI/5xbpic/x5zWH1c0HE+puv6QqDbQoQU=; b=B 9+nqpjoViADAognsu3y1w9TbVnitt6SMJ18RKurDGotJ0zhq3H5VGAbu0YKB40ed 035WvHtv38woBjkmav+QuJVMpkRGwuRuFIcprzjSwT2Gp4e1YvBuD/3MLy/E+6P6 i4QbPx0qQgjC8mt8uN/hoeNfNoGS0r/qxC7kYiO3abkfSQcAnguWZ8NyfTLQNfbB GERWbp+r8jajNoL1QcAvr0ZQmr9/eFlNppIjweY7tg237X0Abx2nj1MUPj1706jh MR/a1QqcO+nQqNHnzpjVdu4Whs/4x6uTXMn+8rBmoAx6WnACBTfkn+94zeqr9FWu xmh/cTkffwPjwIlfNvSzQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrieejgdelvdcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvfhfhjggtgfesthekre dttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeffleegieefgf evudehtdfhkeeutdffjeevgeffgeejvedthefgudeiteefheejheenucevlhhushhtvghr ufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvg hrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sat, 9 Mar 2024 12:02:23 -0500 (EST) Message-ID: Date: Sat, 9 Mar 2024 09:02:23 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: creating a subset DB efficiently ? Content-Language: en-US To: David Gauthier , pgsql-general@lists.postgresql.org References: From: Adrian Klaver 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 3/8/24 08:22, David Gauthier wrote: > Here's the situation.... > > - The DB contains data for several projects. > - The tables of the DB contain data for all projects (data is not > partitioned on project name or anything like that) > - The "project" identifier (table column) exists in a few "parent" > tables with many child... grandchild,... tables under them connected > with foreign keys defined with "on delete cascade".  So if a record in > one of the parent table records is deleted, all of its underlying, > dependent records get deleted too. How many "... child... grandchild,... tables" ? Do these tables constitute all the tables in the database? > Related question... > The "delete from par_tbl_a where project <> 'a' " is taking forever.  I > fear it's because it's trying to journal everything in case I want to > rollback.  But this is just in the archive DB and I don't mind taking > the risk if I can speed this up outside of a transaction.  How can I run > a delete command like this without the rollback recovery overhead ? I am assuming that at the point you do "delete from par_tbl_a where project <> 'a' " project a is no longer receiving data and its records are static. Further assuming there is a PK that you could order by, then it would seem the way to go would be to delete in batches as determined by the PK. -- Adrian Klaver adrian.klaver@aklaver.com