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 1urFNO-002zuT-Bd for pgsql-general@arkaria.postgresql.org; Wed, 27 Aug 2025 12:35:43 +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 1urFMN-00EtHI-Fz for pgsql-general@arkaria.postgresql.org; Wed, 27 Aug 2025 12:34:40 +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 1urFMN-00EtH0-0N for pgsql-general@lists.postgresql.org; Wed, 27 Aug 2025 12:34:39 +0000 Received: from mout.gmx.net ([212.227.17.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1urFMI-00211L-2Z for pgsql-general@lists.postgresql.org; Wed, 27 Aug 2025 12:34:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1756298073; x=1756902873; i=jimis@gmx.net; bh=F3HrbYWsPT1xyl1J+sS5WY7eJLsmdh7/nXBfxmTnk9E=; h=X-UI-Sender-Class:Date:From:To:cc:Subject:In-Reply-To:Message-ID: References:MIME-Version:Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=ClK727xL6UkMmN+IDIKA9p8Q+Vnm5i4LvWxlPMQoBo/tt7BwDH5A7Q9KfYhgoNbQ Ccu0wapW82koGJDGUa73qdgxQIoGGpIBhLok42k+Y1fHr9zrFLO5gYDwTzzPAHl9X aDxhQVuxT4EkqImwteGZdlSk6XM7VfQ0AfABSMUqW3Eqwa97Ukh02hx3WkXnBMlvK TxaoPlTxRZCHsuynjrdIpBHQxXHbeDexJsiTQpzbgq4+yhic0MFUkVaguQdkbz3dD AzI7FbxZOWSJI8u7H9Ipvr2I/Y8t1Vv9uQodxg9+vxpr9XD4TkvC+vDHFAN6Tpka6 MNzg7TrKxduP/ok4iQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.59] ([185.55.106.54]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M9Fnj-1uuFtW0Uhr-00GnuS; Wed, 27 Aug 2025 14:34:33 +0200 Date: Wed, 27 Aug 2025 14:34:31 +0200 (CEST) From: Dimitrios Apostolou To: Tom Lane cc: pgsql-general@lists.postgresql.org Subject: Re: In-order pg_dump (or in-order COPY TO) In-Reply-To: <1273426.1756246099@sss.pgh.pa.us> Message-ID: <6n02q94n-q0so-8rp8-55p3-09pqr27r8409@tzk.arg> References: <1273426.1756246099@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-Provags-ID: V03:K1:GFGuxKwmqe12fqUyio+fkGz1wQxjjw4902qW173/yLoxxOSKB3b Y0EHHk1tosVIpPBfITcPvihiocCTqN2piwVeRK+RNtNJy/vwklql76pi5UFVbK0rH6rE9SM V3avg2qsfqnPHT1REQiLrIecZFTntEXZ+EgaKbCT+AW1nKNbTQC7fUCQ8v1fcuImPV97Fke sRRA5uEeBCrfYvKNkXTWg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:w2nj8GG8fGs=;dnin/wLTW3bF38R6GC7KkxachKl nwwYg6hALzCAt+WkIjb6XGyxTxnHObkuBk61bHztXFvl7sV6z2CcOsNHHCNXbFcMKoBIvERHq bMPvfK0gxqsGQ9sFsvcWGmPmnUwENiU3tS1rb+kn5omAzTiqOutUC+H6QhF06WaIqHImnANTH fp8wXKswDzwTG95P29v6nMZGoMespoR2w6SZzAJIlgYDnmC3S9PxTTNgSIYnNK+UUmkU23wy+ MIbW+GClpshl+KECfKxcGk7GFmNgngPdDBKTwIUDjcqXAxNLLzHHR+d6EQ9W89IbzwhPqZUJ7 UTljsB4119OloTwiNOvQ0jQUYDOM9onGCCLTSUOEa0MBDpNJdIkMI92URxys096OIqkJRxB7q SghrUp5YVoqkYAt1H1CEftQCRHzayFFeWk9q9QgXj7LfuTbM8Eu3fJZjySk8zQEMDYTD4wuQh mGYWXgFfZ0nMs06dztKCmZk00hu8x46b+I2fooNoEeDrZEjZXZ9haO56gmyXqYnNmVvtDb8WE rky533S50G9JCTvrcP64Y6GKUWZ5hpXSyVrCwqIedD4EFWkO+I4xEhMbRKyBWNxFYTCUP02UO U/8I4udCCDBSrfnb+tUJhkm5MF8BY0j9qFC9oiw3LSSzZJvj3nzgazGYQZTxn3AtbePyjbx2r TM6JjvgPrg8x9WmGDZhBV1R2OQs3VpHahFnzw/Ji8rfR8/xl7G7XB7uMSl9netUjmd0XOeuio QI9tYzsfsOJkGu/kc80e8vSZ9cnHX+xqyHI70JY8RvgtyPi6Y6KztdqBNmnznAocWt4tTNuCu 8Lz0lPFa/JqRUEjrHVuiMw5kp7fj6RZYdw0VfPNy0v1ePn+8UPza+7pGrjbVDwZvKLH2eXDH0 ZiFfu4sl08gdCay3WS/fhLd4wDWi8rO/x4sYHg9jOa1MLQFSxzp6W3CTcZ6biec3x8h7aFlfk LXc7xaooAPiAbGKOvv94vTGy0SBUflHaj3v6QoZmGOtJPkl6Bgo/Sbx7k7G4psw7aqbzE4l44 CADHjO+N//h9jKx4/1nnf1NsIAc/Xem+/eYXbgi0Rb9/FgN1vqS6RmkMA+cTSo9V5MCaCtb16 QsCo4gyM0U0l+dGtZkg6iYFgZ844OUW+Qru5X+cBDsjL16YrFCuRMVmdE3rRpUFiXlk4q8pJt odY3T8ZESj4BVYkm4XxWyt6ZZnOavRfHHSgEwA04hx285c8gyW4IAA8KwyeN5D1EacVSXMmdh mQNERoDIYV7tXQp+HIeg64baVu6RbkZwMqODj0DQsu1j+oXseZWE4ToAB8yrg+2TyREcF+QMM S2/QWJZr+/H1HdfYybmFHVWOlluvomuS7s/JTgj0MMgJXnooa03pS883P+37Yd1Jc1bynItRi u7zharFCfqX02mBoZ3rBl6asBdDU+Ng8vRLZZqb2SKA9TtChl8ny+Bk8HRo23Jy6TSsdsHAL/ fsHkVMyrgGHLY7KY/yL7DiJDYwj7QiVavtOWKCiCjfs4mUxHwzYBwdC6UQch3vnYqXYsaWvzI ypwBBqiZkHNOOGMrCaCMByotQqwltGcSHL7lcg7CjvLAqQPk68llxUPaCuK9G74EC4bYeopED Z68XU50aqts9aeD4YjYeomFA/ngASF7N0kxdfG+XPOrRmBteFlzlvVw7/N9IJLJQzZbGPXIgE jS5PPXAZEvgyAzFim2UuV2+DON84vQ0L7kZyLKRBQYRSiVeJfcupF1trN7LRwHxOCL7U5xGcN r8YV0xj4U5jf1J40m2UhIOJKiHMjp0QVpSwss+wEJS1E/kqHRI13aNgF6j70iFfb0BML3oqV6 BuVj40obqg5MNjcsE8TzyCSlzmL5tAOOjd+vJSUrwSejuhC3tnWmknX0TTTElZPClqP/6xNrG O9lSJx1f/jjKRWmYF5cNQwQE1+FdZ/KnPzrRF7UeiyypipwChUapTEDzGt2Wg+jiZdttMczzJ oUaK/GhEb//XjRAjhRrxIbvAhuKSmsfpDCLHkzjRqAxEAZPSZ2bQ/ZiqRzzDkXEhZU2acRUok tHa79tGLoxLLD5CizFhxuvPp+gJHRSkehXCguSFgct2e+GdYPUIrWFN+4GjQ+VxFgplOOfqGU nDkFWGgtOL/Jy5loIbUI24XsmpkhGR4hHgcnLbqh75h2Dk3ato1i+ibOj0uv3nJgXlyS2CewG opnX+G6VUZpZGEbifdCr1qIARqGz1J4vIwcTKPIs6bnGFNjtJJ15OkkBVVW5uEUvJ83w8vL83 B52bJWU4o6O83SDm+37mR2NUlOBnN3bldSBZ+wcEDtMjhVnoHU8DpVFVVM+834COS1m1iHK7P XRk7MC7HyW4a1YqQ876YPDzpainoUieI2l7SCWitkHnZOfRWTXgFGKLZAj3LFko//SQRBGyWe 6oqF4YE+1FfV0FGZcV4uVlt8nTWPg9LyUMwXcVoy3DyF1IZpPcwkJgAvKpECSqh57P58yN5+2 oYXi+i2MF7WhRF7X1M7eROgepr6k4gfjliz7R6fwi/mpOrN/ybDCU9WJHIF1G8JyiXg99X141 JsZfuzWxv1IOmzYWnu/n7rKpMnyltpkQ98vc9CwAs3y2wO8jUwYMTpq8vsrdyxtg4twBm0px9 En9iTyCka1OFZGw1Y5DkN/ASTjkAAbAajxyOWLqZ1igl2fv9+dDGXhYurSwT34grA45qMvt4g 0+tf5138w2AyQW4/UW9WXqUaqudG2r8JFREyMvsEF+oTPRebcwLK6tZjIdeCryiL+3Syoscez NcWGsQlyClPgKtFqOKYYCNK7HjkYYAvlB3+4JOKt22rfjXbqKE66ymaEY07sSFfn9irPOYo1S AG/uWV3a0ek4Ej4bQJg9jKOCKTCX4LtW1BryrNJ75wmbdkpEOEqQmopdBN2T3x5XILmLMWyI0 sS8FTfrYRr3+IQ4oYL50d4QjUEz0LFGBbGzBmwj/zIwhNcgRnfGDYOedCuLcVqgkghhBSuJ4/ 1VaFqz5K+kBGMxtK+dUgUC5+SSOd4mlcCm+HKqu/8Fz8/a+w7HKUEwbAmp1vI/aMVHpKHZAUZ 96kYp8fKbdGaHm9PR5ftWbhRmiCEPpqS3unlIHaFzFgzkUz8cg1JwkTXl/WahOEqlOz5SfIdb 3/8tWy2qjud0CCbdvUcWA6bjvxhLL2WX2/bffEatOoSCYmphKBUTmrgHYoAzaW8KtOhxtfSvE OWjSTRusXck7xLsnFO+162aaZUZF2mMchWsmgbnp/9EYaD6Sd8Jo7w9ZkAQzWV+sdymR6v+/9 ui1UUt8RzfTFjYU4SKD8keUp87ncYWabD5N+6j0o1uyGB0nSGMoEKktcjcusmF0fRlBzXLeqL 6rxUM1b2zNPNU3CEJlB4OFKAT133yLx9PX06j4Pdwqgf9sgC4b1/hXS5jxBJVtFj7WNpshMQN Z8O206tHvsc3Fzx9I5K2zwZIZM5pMLYFiEWWF5b5msJ+BCjAdbvVCKfjzU9Wf4M/ilFKgQXGW axxUBhGFMwy5VNpXVsHuUIyCrAJW1m4clYnhyzkQTmUeC7QRSVslhRS+wkIFFMc+LctKwA8WC MwqXj/VQhLgTPmVVwPFviVJl1SCVDPLdegt5TWdDbSOF27fojwscF43l8DwsK7gAcA3IeEgLt kIFCrL3n4oCQBvTIIobedvKm2SvVBUOt3X9izQGrO490iI3upDRfFZi2vfN521ZXwkWTg2zQr zkVOhMv1Y/vYJ5/T+1cNyqHmuDNyoCfjawFXg/x+rXydnU56W33MBv7Fpkp+r1pfqrA40SGtD ueix63UhF6gSbKnQsfzRSuzwdnVtK68CRfZMJjztCfYqdI/S1SlcsUtU2qkvc1lz1QI2aR0SN q8SBAQ8XFZDWaqqoO47749lP9JRW5buJPYPYZEDXfI5Hp289bHbb4U5zrPdJmoWPI6l1cGupa BlhLhV8gJlGWaDgrzpCPObECpdOfMLzvUdyyAgceN8qafh77pBBMn89e04671LfWqnNRfKds1 GQau36j4Ze+MAlxz3d4emWGP41RiSOVLYrjnWyuKQUnUh2QsmtTEIYIrD+SGPJK8mOLQk4wPt /hsxQ4ywv4f2kmhWbWjg45PAPhzXMT2StVrqr33b0gcE5ykBP9rbVulP8Tv8SpNOteI0gS7wM RUkT56nBjgDtnUmr/U9ZMJTaEtaOENzILVGTkM/s5JBKSKoxEQgo8ml9EVZBeUfhPPRIP9UWO rXVDXlrHt8BY54Rl3FgDc7t1HxMtOJrzZXOczl9wsrk207Ue10dA6QYLcU+T0ONW+aWe9tDyn RM+SwRxzEPcpMI7GpQ8mxvzEOJo12YELvr/QQWIrSPdseT6KJvVxZAu7OViQeMoRDNknDCRFK PSwNQm/t5H6SDPSlR1Cak24sUZF1dVmk6Ni8huSvpe3oqiVDXVM3uaodU5OEJCankutOmfFPI rvuKn0FqETpZDcm/cs03q4uwxoiYr66X2ruLEfQsR6U2Wqih6yZaRYo5Z0Ryc8mBOYxq3Miwo ITkOjBQBUZr3xgUnqXedH3E4ZAP2q6Vh4pDxgxdEmJiOmW3SvAgqbqcm1K01dVbc37bjHVRU6 DzZPMNNRQb6cCG6rt2OEFKOTQN0QG6Xfn8bhCSpvsy96+47ZFm32NbMgCy1GAAQpZg50sEMIr HtPfWgWoSzbBMO6gko9iBeoFGfLRTJ4THaDqf Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wednesday 2025-08-27 00:08, Tom Lane wrote: > However, restoring into an empty table would result in a table with > minimal free space, whereas the original table probably had a > meaningful amount of free space thanks to updates and deletes. Thus > for example TIDs would not be the same. If your "rolling checksum" > methodology is at all sensitive to page boundaries, the table would > look quite different to it. Thanks Tom. I'm not following how the empty space in the tables could=20 affect the custom format dumps. Where can I read more about these TIDs?=20 Are they stored in the pg_dump custom format archive? The rolling checksum method should iron out shifting of data chunks that= =20 are around a couple MB in size. Shorter shifts will not be caught, and=20 I assume that the "page boundaries" changes you mentioned would happen=20 every 8KB. So that is definitely too fine grained for the deduplicated=20 algorithm. FYI something that I forgot to mention is that pg_restore is --data-only= =20 and writes go through walwriter. The database with the tables has been=20 created from scratch so every table is empty before the pg_restore. Not=20 sure how this affects the above. > I'd expect pg_dump/pg_restore to preserve the physical row ordering, > simply because it doesn't do anything that would change that. Regardless of my specific case, it's scary to think that doing VACUUM=20 FULL, CLUSTER, or who knows what other maintenance command, will modify=20 the logical dumps. Some implicit ordering could be enforced by pg_dump=20 if possible, for example when a primary key exists. Does it make sense?=20 Is it even possible? Thanks, Dimitris