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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1ur1yv-00H6in-8w
	for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 22:17:34 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1ur1yu-00As3m-MU
	for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 22:17:33 +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 <tgl@sss.pgh.pa.us>)
	id 1ur1yu-00As3e-BU
	for pgsql-general@lists.postgresql.org; Tue, 26 Aug 2025 22:17:32 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by magus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1ur1ys-0022Cs-2p
	for pgsql-general@postgresql.org;
	Tue, 26 Aug 2025 22:17:32 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 57QMHSBG1274230;
	Tue, 26 Aug 2025 18:17:29 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ronljohnsonjr@gmail.com>
cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: In-order pg_dump (or in-order COPY TO)
In-reply-to: <CANzqJaAbzGqQK7b6jF-0nuvCf6_JyD7AkSHWqbY4NnP5adfeRg@mail.gmail.com>
References: <s0491qrn-343s-0757-8sn5-120rr8610qqq@tzk.arg> <CAKFQuwYBVFjoNFUzsomdvRQXEEQH5A4T-+_STE=WNUomw6LZSQ@mail.gmail.com> <CANzqJaAbzGqQK7b6jF-0nuvCf6_JyD7AkSHWqbY4NnP5adfeRg@mail.gmail.com>
Comments: In-reply-to Ron Johnson <ronljohnsonjr@gmail.com>
	message dated "Tue, 26 Aug 2025 18:08:39 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1274228.1756246648.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 26 Aug 2025 18:17:28 -0400
Message-ID: <1274229.1756246648@sss.pgh.pa.us>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/1274229.1756246648%40sss.pgh.pa.us>
Precedence: bulk

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Tue, Aug 26, 2025 at 4:31 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> The logical dump has no ordering - it will come out however it comes out.
>> "COPY <table> TO ..." doesn't have an order by clause - there is no way to
>> make or communicate to it that ordering is important.

> Doesn't COPY TO copy out records in the order they appeared in the physical
> files?

It emits whatever a sequential-scan plan would emit.  If you set
synchronize_seqscans = off (which pg_dump does), that will match
physical row order.

At least with our standard table AM.  If you're using Aurora or
one of those other PG forks with proprietary storage layers,
you'd have to ask them.

I suspect the OP's problem is not row order per se, but differing
TIDs or XIDs, which are things pg_dump does not endeavor to
replicate.  Or, given that he said something about blocks, maybe
he's actually sensitive to where the free space is.

			regards, tom lane