Re: [PERFORM] improvement suggestions for performance design
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 4 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] improvement suggestions for performance design
Hi. 2007/7/8, Thomas Finneid <[EMAIL PROTECTED]>: 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.) It's my third implementation, based on earlier work by Kris Jurka, a maintainer of the JDBC driver. (It is really quite short so it's easy to keep it clear.) I consider it mature enough to have accommodated it as part of an upcoming large application, but I'd really like to hear others' opinions. Documentation I should add one of these days, maybe even rewrite the javadoc. You can use COPY as is on the server side without the patch, but then you need to get the data as CSV or TSV files onto the database server machine, and use db superuser privileges to import it. My patch just adds the ability to feed data from client with normal user privileges. -- Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
