Re: COPY and indices?

2012/3/12 François Beausoleil <francois@teksol.info>:
> Hi all,
>
> When using COPY FROM STDIN to stream thousands of rows (20k and more hourly), what happens with indices? Are they
updatedonly once after the operation, or are they updated once per row? Note that I'm not replacing the table's data:
I'mappending to what's already there. I suspect batching writes will be faster than writing each individual row using
anINSERT statement. 
>
> Currently, I can sustain 30-40 writes per second on a Rackspace VPS. I know it's not the ideal solution, but that's
whatI'm working with. Following vmstat, the server is spending 30 to 40% of it's time in iowait. I don't have
measurementsas to what files are touched, and I'd welcome suggestions to measure the time PostgreSQL actually spends
writingindices vs data. 

you're almost certainly blocking on fsync.  A real quick'n'dirty way
to confirm this (although it wont be as fast as COPY) would be to wrap
your inserts in a transaction.  VMs tend to have really horrible
storage latency which can hurt postgres performance.  Another option
would be to relax your commit policy (for example by flipping
synchronous_commit) if that fits within your safety requirements.

merlin