pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_bulkload slowing down
5+ messages / 4 participants
[nested] [flat]
* pg_bulkload slowing down
@ 2025-07-28 13:07 Sbob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 5+ messages in thread
From: Sbob @ 2025-07-28 13:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
All;
We are loading a series of files into a single table, each file has
approx 500million rows, the first file took ~ 1.25 hours, now on file
number 8 it has been running for more than 10 hours, there is only a PK
index on the table.
Thoughts?
Thanks in advance for any help
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_bulkload slowing down
@ 2025-07-28 13:17 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2025-07-28 13:17 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Jul 28, 2025 at 9:07 AM Sbob <sbob@quadratum-braccas.com> wrote:
> All;
>
>
> We are loading a series of files into a single table, each file has
> approx 500million rows, the first file took ~ 1.25 hours, now on file
> number 8 it has been running for more than 10 hours, there is only a PK
> index on the table.
>
Triggers? Foreign Keys?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_bulkload slowing down
@ 2025-07-28 13:23 Álvaro Herrera <alvherre@kurilemu.de>
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 1 reply; 5+ messages in thread
From: Álvaro Herrera @ 2025-07-28 13:23 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin@lists.postgresql.org
On 2025-Jul-28, Sbob wrote:
> All;
>
>
> We are loading a series of files into a single table, each file has approx
> 500million rows, the first file took ~ 1.25 hours, now on file number 8 it
> has been running for more than 10 hours, there is only a PK index on the
> table.
I bet it's trying to find unused OIDs for toast pointers -- there are
only 2^32-1 of those available in any one toast table, and by the eighth
file of 500 million rows, you'd run out.
You could workaround this by partitioning the table.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_bulkload slowing down
@ 2025-07-28 13:52 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2025-07-28 13:52 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Jul 28, 2025 at 9:23 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> On 2025-Jul-28, Sbob wrote:
>
> > All;
> >
> >
> > We are loading a series of files into a single table, each file has
> approx
> > 500million rows, the first file took ~ 1.25 hours, now on file number 8
> it
> > has been running for more than 10 hours, there is only a PK index on the
> > table.
>
> I bet it's trying to find unused OIDs for toast pointers -- there are
> only 2^32-1 of those available in any one toast table, and by the eighth
> file of 500 million rows, you'd run out.
>
That seems to imply you can only insert 4Bn rows into a table with attached
TOAST. Am I misunderstanding something?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_bulkload slowing down
@ 2025-07-28 15:12 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2025-07-28 15:12 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, 2025-07-28 at 09:52 -0400, Ron Johnson wrote:
> That seems to imply you can only insert 4Bn rows into a table with
> attached TOAST. Am I misunderstanding something?
Only if those rows actually contain column values big enough to
warrant storing them out of line.
And if each row had *two* such columns, you could only store
2^31 such rows.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-07-28 15:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-28 13:07 pg_bulkload slowing down Sbob <sbob@quadratum-braccas.com>
2025-07-28 13:17 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-28 13:23 ` Álvaro Herrera <alvherre@kurilemu.de>
2025-07-28 13:52 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-28 15:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox