2012/3/12 François Beausoleil <franc...@teksol.info>: > Hi all, > > When using COPY FROM STDIN to stream thousands of rows (20k and more hourly), > what happens with indices? Are they updated only once after the operation, or > are they updated once per row? Note that I'm not replacing the table's data: > I'm appending to what's already there. I suspect batching writes will be > faster than writing each individual row using an INSERT 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 what I'm working with. Following > vmstat, the server is spending 30 to 40% of it's time in iowait. I don't have > measurements as to what files are touched, and I'd welcome suggestions to > measure the time PostgreSQL actually spends writing indices 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
