Hi, On 13 March 2012 15:11, François Beausoleil <francois@xxxxxxxxxxx> wrote: > 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. Yes, it will be faster to use COPY than plain INSERTs. We have similar situation -- up to 10k rows every 3 minutes and around 15 indexes on the table. Table is portioned and we do not update data. Check bloat query reports some bloat but it growing very slowly and there is new partition every month. > > 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. Drop all indexes, measure time to insert and collect iostat output. Create indexes, repeat the process and compare the results -- Ondrej Ivanic (ondrej.ivanic@xxxxxxxxx) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
