Re: Issues with \copy from file

On Sun, 18 Oct 2009, Scott Marlowe wrote:
> You can only write data then commit it so fast to one drive, and that
> speed is usually somewhere in the megabyte per second range. 450+150
> in 5 minutes is 120 Megs per second, that's pretty fast, but is likely
> the max speed of a modern super fast 15k rpm drive. If it's taking 20
> minutes then it's 30 Megs per second which is still really good if
> you're in the middle of a busy afternoon and the db has other things
> to do.

You're out by a factor of 60. That's minutes, not seconds.

More relevant is the fact that Postgres will normally log changes in the
WAL, effectively writing the data twice. As Euler said, the trick is to
tell Postgres that noone else will need to see the data, so it can skip
the WAL step:

> BEGIN;
> TRUNCATE TABLE foo;
> COPY foo FROM ...;
> COMMIT;

I see upward of 100MB/s over here when I do this.

Matthew

--
Patron: "I am looking for a globe of the earth."
Librarian: "We have a table-top model over here."
Patron: "No, that's not good enough. Don't you have a life-size?"
Librarian: (pause) "Yes, but it's in use right now."