When adding millions of rows at once, getting out of disk space errors

From: Mike Christensen <imaudi(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: When adding millions of rows at once, getting out of disk space errors
Date: 2009-02-18 20:39:50
Message-ID: 499C7216.9000804@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all -

I'm doing some perf testing and need huge amounts of data. So I have a
program that is adding data to a few tables ranging from 500,000 to 15M
rows. The program is just a simply C# program that blasts data into the
DB, but after about 3M rows or so I get an errror:

ERROR: could not extend relation 1663/41130/41177: No space left on device
HINT: Check free disk space.

If I do a full VACUUM on the table being inserted into, the error goes
away but it comes back very quickly. Obviously, I wouldn't want this
happening in a production environment.

I've noticed some auto-vacuum settings as well (I just checked the box
and left all the defaults) but that doesn't seem to help too much.
What's the recommended setup in a production environment for tables
where tons of data will be inserted?

It seems to me there's some sort of "max table size" before you have to
allocate more space on the disk, however I can't seem to find where
these settings are and how to allow millions of rows to be inserted into
a table without having to vacuum every few million rows..

Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-02-18 20:54:00 Re: When adding millions of rows at once, getting out of disk space errors
Previous Message Tom Lane 2009-02-18 20:36:13 Re: Appending \o output instead of overwriting the output file