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
30 matches
Mail list logo
