On Mon, Jul 21, 2025 at 02:03:45AM +0200, Hannu Krosing wrote:
> Do you think the current patch could be backported to at least some
> latest versions ?
I think that's pretty unlikely. It'd be a pretty big departure from our
versioning policy. In the past, we have back-patched "critical"
perform
Do you think the current patch could be backported to at least some
latest versions ?
On Fri, Jul 18, 2025 at 6:05 PM Nathan Bossart wrote:
>
> Committed.
>
> I'm cautiously optimistic that we can find some better gains for upgrades
> from v16 and newer. That would involve dumping lo_create() co
Committed.
I'm cautiously optimistic that we can find some better gains for upgrades
from v16 and newer. That would involve dumping lo_create() commands for
all LOs with comments/seclabels, dumping the relevant pg_shdepend rows, and
then copying/linking the pg_largeobject_metadata files like we d
On Mon, Jul 14, 2025 at 01:28:03PM -0400, Tom Lane wrote:
> Is it intentional that this does
>
> +#include "catalog/pg_largeobject_metadata.h"
> +#include "catalog/pg_shdepend.h"
>
> rather than including the corresponding *_d.h headers?
Nope, that was an oversight.
--
nathan
>From 99551525801
Nathan Bossart writes:
> Here is what I have staged for commit, which (barring feedback or
> objections) I am planning to do towards the end of the week.
Is it intentional that this does
+#include "catalog/pg_largeobject_metadata.h"
+#include "catalog/pg_shdepend.h"
rather than including the co
Here is what I have staged for commit, which (barring feedback or
objections) I am planning to do towards the end of the week.
--
nathan
>From 9501f31cdf3ce25012cec8f9e2d7c433df749979 Mon Sep 17 00:00:00 2001
From: Nathan Bossart
Date: Mon, 14 Jul 2025 11:17:47 -0500
Subject: [PATCH v4 1/1] pg_u
On Fri, Jul 11, 2025 at 8:21 PM Nathan Bossart wrote:
>
> > Also just would like to confirm that the pg_dump_sort change will go in a
> > different patch.
>
> That's already been committed: http://postgr.es/c/fb6c860.
>
That's great. Thank you.
On Fri, Jul 11, 2025 at 07:49:47PM +0530, Nitin Motiani wrote:
> Thanks. Looks good to me.
Thank you for reviewing.
> Also just would like to confirm that the pg_dump_sort change will go in a
> different patch.
That's already been committed: http://postgr.es/c/fb6c860.
--
nathan
On Fri, Jul 11, 2025 at 3:12 AM Nathan Bossart
wrote:
> On Thu, Jul 10, 2025 at 06:05:26PM +0530, Nitin Motiani wrote:
> > - * pg_largeobject_metadata, after the dump is restored.
> > + * pg_largeobject_metadata, after the dump is restored.
> In versions
> > +
On Thu, Jul 10, 2025 at 06:05:26PM +0530, Nitin Motiani wrote:
> - * pg_largeobject_metadata, after the dump is restored.
> + * pg_largeobject_metadata, after the dump is restored. In
> versions
> + * before v12, this is done via proper large object commands
Hi,
I have a couple of comments/questions.
> There might be an existing issue
> here, because dbObjectTypePriorities has the following comment:
>
> * NOTE: object-type priorities must match the section assignments made in
> * pg_dump.c; that is, PRE_DATA objects must sort before
DO_PRE_DATA_BOUN
Ah, I see.
I retried and now it runs for 21 to 29 seconds for 1 million large
objects with two grants each, 3M total rows inserted.
Yesterday it ran 8 seconds. Unfortunately I do not have the plan from
yesterday saved .
Anyway it looks like just dumping pg_shdepend it is better, and likely
also
On Wed, Jul 09, 2025 at 04:52:16PM +0200, Hannu Krosing wrote:
> If you run it without the last WHERE it is reasonably fast. And it
> behaves the same as just inserting from the dump which also does not
> have any checks against duplicates.
With that change, the query is much faster, but my testin
On Tue, Jul 8, 2025 at 11:06 PM Nathan Bossart wrote:
>
> On Sun, Jul 06, 2025 at 02:48:08PM +0200, Hannu Krosing wrote:
> > Did a quick check of the patch and it seems to work ok.
>
> Thanks for taking a look.
>
> > What do you think of the idea of not dumping pg_shdepend here, but
> > instead ad
On Sun, Jul 06, 2025 at 02:48:08PM +0200, Hannu Krosing wrote:
> Did a quick check of the patch and it seems to work ok.
Thanks for taking a look.
> What do you think of the idea of not dumping pg_shdepend here, but
> instead adding the required entries after loading
> pg_largeobject_metadata bas
Hi Nathan,
Did a quick check of the patch and it seems to work ok.
What do you think of the idea of not dumping pg_shdepend here, but
instead adding the required entries after loading
pg_largeobject_metadata based on the contents of it ?
The query for this would be
WITH db AS (
SELECT oid F
On Mon, May 05, 2025 at 02:23:25PM -0500, Nathan Bossart wrote:
> That leaves pg_shdepend. For now, I've just instructed pg_upgrade to COPY
> the relevant pg_shdepend rows as an independent step, but perhaps there's a
> reasonably straightforward way to put that in pg_dump, too.
It turns out ther
On Tue, Apr 08, 2025 at 01:07:09PM -0400, Tom Lane wrote:
> Nathan Bossart writes:
>> I do think it's worth considering going back to copying
>> pg_largobject_metadata's files for upgrades from v16 and newer.
>
> (If we do this) I don't see why we'd need to stop at v16. I'm
> envisioning that we
And in case there *is* ACL present then each user mentioned in the ACL
adds more overhead
Also the separate GRANT calls cause bloat as the
pg_largeoject_metadata row gets updated for each ALTER USER or GRANT
The following is for 10 million LOs with 1 and 3 users being GRANTed
SELECT on each objec
On Tue, Apr 08, 2025 at 12:22:00PM -0500, Nathan Bossart wrote:
> On Tue, Apr 08, 2025 at 01:07:09PM -0400, Tom Lane wrote:
>> Nathan Bossart writes:
>>> I do think it's worth considering going back to copying
>>> pg_largobject_metadata's files for upgrades from v16 and newer.
>>
>> (If we do thi
On Tue, Apr 8, 2025 at 7:07 PM Tom Lane wrote:
>
> Nathan Bossart writes:
> > I do think it's worth considering going back to copying
> > pg_largobject_metadata's files for upgrades from v16 and newer.
>
> (If we do this) I don't see why we'd need to stop at v16. I'm
> envisioning that we'd use
On Tue, Apr 08, 2025 at 01:42:20PM -0400, Tom Lane wrote:
> Nathan Bossart writes:
>> Unless I'm missing something, we don't seem to have had any dependency
>> handling before commit 12a53c7. Was that broken before we moved to SQL
>> commands?
>
> Sounds like it :-(
Huh. Sure enough, it seems
Nathan Bossart writes:
> On Tue, Apr 08, 2025 at 09:35:24AM +0200, Hannu Krosing wrote:
>> Changing the LO export to dumping pg_largeobject_metadata content
>> instead of creating the LOs should be a nice small change confined to
>> pg_dump --binary-upgrade only so perhaps we could squeeze it in v
On Tue, Apr 8, 2025 at 8:39 PM Nathan Bossart wrote:
>
...
>
> I've also verified that the dependency information is carried over in
> upgrades to later versions (AFAICT all the supported ones).
If I remember correctly the change to not copying
pg_largeobject_metadata data file but instead moving
Hannu Krosing writes:
> I think we do preserve role oids
Oh ... I'd been looking for mentions of "role" in
pg_upgrade_support.c, but what I should have looked for was
"pg_authid". So yeah, we do preserve role OIDs, and maybe that's
enough to make this workable, at least with source versions that
On Tue, Apr 08, 2025 at 01:07:09PM -0400, Tom Lane wrote:
> Nathan Bossart writes:
>> I do think it's worth considering going back to copying
>> pg_largobject_metadata's files for upgrades from v16 and newer.
>
> (If we do this) I don't see why we'd need to stop at v16. I'm
> envisioning that we
This is what the opening comment in pg_upgrade says
I think we do preserve role oids
/*
* To simplify the upgrade process, we force certain system values to be
* identical between old and new clusters:
*
* We control all assignments of pg_class.oid (and relfilenode) so toast
* oids are the s
On Tue, Apr 08, 2025 at 09:41:06PM +0200, Hannu Krosing wrote:
> On Tue, Apr 8, 2025 at 8:39 PM Nathan Bossart
> wrote:
>> I've also verified that the dependency information is carried over in
>> upgrades to later versions (AFAICT all the supported ones).
>
> If I remember correctly the change t
On Tue, Apr 08, 2025 at 01:51:22PM -0400, Tom Lane wrote:
> Nathan Bossart writes:
>> On Tue, Apr 08, 2025 at 01:36:58PM -0400, Tom Lane wrote:
>>> Hmm ... one annoying thing for this project is that AFAICS pg_upgrade
>>> does *not* preserve database OIDs, which is problematic for using
>>> COPY t
On Tue, Apr 08, 2025 at 05:37:50PM -0400, Jan Wieck wrote:
> I remember an incident where large amounts of LOs ran pg_upgrade into a
> transaction-ID wrap around because the restore part would create individual
> single statement transactions per LO to create, then change permissions and
> ownershi
On 4/8/25 15:41, Hannu Krosing wrote:
On Tue, Apr 8, 2025 at 8:39 PM Nathan Bossart wrote:
...
I've also verified that the dependency information is carried over in
upgrades to later versions (AFAICT all the supported ones).
If I remember correctly the change to not copying
pg_largeobject
On Tue, Apr 8, 2025 at 6:37 PM Tom Lane wrote:
>
> Hannu Krosing writes:
> > I think we do preserve role oids
>
> Oh ... I'd been looking for mentions of "role" in
> pg_upgrade_support.c, but what I should have looked for was
> "pg_authid". So yeah, we do preserve role OIDs, and maybe that's
> e
Nathan Bossart writes:
> On Tue, Apr 08, 2025 at 01:36:58PM -0400, Tom Lane wrote:
>> Hmm ... one annoying thing for this project is that AFAICS pg_upgrade
>> does *not* preserve database OIDs, which is problematic for using
>> COPY to load pg_shdepend rows.
> I think it does; see commit aa01051.
Nathan Bossart writes:
> Unless I'm missing something, we don't seem to have had any dependency
> handling before commit 12a53c7. Was that broken before we moved to SQL
> commands?
Sounds like it :-(
regards, tom lane
On Tue, Apr 08, 2025 at 01:36:58PM -0400, Tom Lane wrote:
> Hmm ... one annoying thing for this project is that AFAICS pg_upgrade
> does *not* preserve database OIDs, which is problematic for using
> COPY to load pg_shdepend rows.
I think it does; see commit aa01051.
--
nathan
Hmm ... one annoying thing for this project is that AFAICS pg_upgrade
does *not* preserve database OIDs, which is problematic for using
COPY to load pg_shdepend rows.
regards, tom lane
Hannu Krosing writes:
> In copy case I would expect the presence of grants to not make much
> difference.
aclitemin is slower than a lot of other datatype input functions,
but it's still got to be faster than a GRANT.
regards, tom lane
Nathan Bossart writes:
> I do think it's worth considering going back to copying
> pg_largobject_metadata's files for upgrades from v16 and newer.
(If we do this) I don't see why we'd need to stop at v16. I'm
envisioning that we'd use COPY, which will be dealing in the
text representation of acl
On Tue, Apr 08, 2025 at 12:37:43PM -0400, Tom Lane wrote:
> Hannu Krosing writes:
>> I think we do preserve role oids
>
> Oh ... I'd been looking for mentions of "role" in
> pg_upgrade_support.c, but what I should have looked for was
> "pg_authid". So yeah, we do preserve role OIDs, and maybe th
On Tue, Apr 8, 2025 at 5:46 PM Nathan Bossart wrote:
>
> On Tue, Apr 08, 2025 at 09:35:24AM +0200, Hannu Krosing wrote:
> > On Tue, Apr 8, 2025 at 12:17 AM Nathan Bossart
> > wrote:
> >> That being said, I
> >> regularly hear about slow upgrades with many LOs, so I think it'd be
> >> worthwhile
On Tue, Apr 08, 2025 at 09:35:24AM +0200, Hannu Krosing wrote:
> On Tue, Apr 8, 2025 at 12:17 AM Nathan Bossart
> wrote:
>> That being said, I
>> regularly hear about slow upgrades with many LOs, so I think it'd be
>> worthwhile to try to improve matters in v19.
>
> Changing the LO export to dum
On Tue, Apr 8, 2025 at 12:17 AM Nathan Bossart wrote:
>
> On Mon, Apr 07, 2025 at 10:33:47PM +0200, Hannu Krosing wrote:
> > The obvious solution would be to handle the table
> > `pg_largeobject_metadata` the same way as we currently handle
> > `pg_largeobject `by not doing anything with it in `pg
Looked like a bit illogical order on re-reading it so I want to make
clear that the pg_upgrade-like test showing 100min for 100 million LOs
is at the end of last message and the proposed solution is at the
beginning
On Tue, Apr 8, 2025 at 9:15 AM Hannu Krosing wrote:
>
> I was testing on version
I was testing on version 17
On Tue, Apr 8, 2025 at 6:52 AM Michael Paquier wrote:
>
> On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote:
> > What version are you testing? We did some work in that area in the
> > v17 cycle (a45c78e32).
>
> I am puzzled by the target version used here, as
On Mon, Apr 07, 2025 at 05:25:32PM -0400, Tom Lane wrote:
> What version are you testing? We did some work in that area in the
> v17 cycle (a45c78e32).
I am puzzled by the target version used here, as well. If there is
more that can be improved, v19 would be the version to consider for
future im
Hi Hackers
## The issue
I have now met a not insignificant number of cases where pg_upgrade
performance is really bad when the database has a large number of
Large Objects.
The average time to `pg_dump --binary-upgrade --format=custom ...` a
database and then `pg_restore ...` it back is 1 minute
On Mon, Apr 07, 2025 at 10:33:47PM +0200, Hannu Krosing wrote:
> The obvious solution would be to handle the table
> `pg_largeobject_metadata` the same way as we currently handle
> `pg_largeobject `by not doing anything with it in `pg_dump
> --binary-upgrade` and just handle the contents it like we
Hannu Krosing writes:
> I have now met a not insignificant number of cases where pg_upgrade
> performance is really bad when the database has a large number of
> Large Objects.
What version are you testing? We did some work in that area in the
v17 cycle (a45c78e32).
rega
48 matches
Mail list logo
