Kalle Hallivuori wrote:
> COPY is plentitudes faster than INSERT:
> http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
>
> If you can't just push the data straight into the final table with
> COPY, push it into a temporary table that you go through with the
> database procedure.
>
> Shameless plug: If you use Java and miss COPY functionality in the
> driver, it's available at
>
> http://kato.iki.fi/sw/db/postgresql/jdbc/copy/
>
> I was able to practically nullify time spent inserting with that.
Interresting, I will definately have a look at it.
What is the maturity level of the code at this point? and what is
potentially missing to bring it up to production quality? (stability is
of the utmost importance in my usage scenario.)
>
>> Well, it has been tested and showed to make postgres perform much
>> better, ie. 100 000 inserts separated between 4 threads performed much
>> faster than with a single thread alone.
>
> Sounds interesting. The results should still end up written into the
> same table, so are you sure this didn't end up using the same time at
> server end - would that even matter to you?
yes it would matter, because a number of clients are waiting to read the
data before the next batch of data is inserted. (in essence every 6
seconds 40000 attributes must be written, and after that write 8-16
clients read most of that data based on query criteria.and this is just
today, in the future, 5-10 years, it might be as high as 2-300 000
attributes per 3 seconds.
thomas