PG11 to PG14 Migration Slowness

From: Vigneshk Kvignesh <krrvignesh2(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: PG11 to PG14 Migration Slowness
Date: 2023-01-12 09:15:41
Message-ID: CABQmCUksqRi2fwzSmDhx3gcdncCZt=5BPFZ6Xb+q02ykGPXcsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I'm migrating our existing PG instances from PG11.4 to PG14.3. I
have around 5 Million Tables in a single database. When migrating using
pg_upgrade, its taking 3 hours for the process to complete. I'm not sure if
its the intended behaviour or we're missing something here.
Most of the tables (90%) in 5 Million are foreign tables. On analysis
found that most of the time is spent in pg_dump (~2.3 hours). In pg_dump
getTableAttrs(), dumpTable() functions take the most time, approx 1 hour
each since we're processing table by table. Also, there are no columns with
default values, which if present might take some time. We're using PG14's
pg_upgrade binary for the process.
Since we have all these tables in one database, parallelism doesn't
have any effect here. Can we make binary upgrade for a single database run
in parallel ?
Kindly advise us if we have missed anything here and possible
solutions for this problem.
So we're not sure on what we missed here.
Have added more info on the process below.

No. of Tables: 5 Million
Time Taken: 3 Hours
Command Used: $PG14_UPGRADE -Uroot -b $PG11_DIR/bin -B $PG14_DIR/bin -d
$PG11_DIR/data -D $PG14_DIR/data -k -r -j32
Version: PG11.4 to PG14.3
Environment: CentOS machine (32 cores(Intel), 128GB RAM)

Thanks and Regards,
Vignesh K.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gurjeet Singh 2023-01-12 09:16:11 Named Operators
Previous Message Peter Eisentraut 2023-01-12 09:14:11 Re: Add BufFileRead variants with short read and EOF detection