<FONT face="Default Sans Serif, Verdana, Arial, Helvetica, sans-serif" size=2><DIV>I do mass inserts daily into PG. I drop the all indexes except my primary key and then use the COPY FROM command. This usually takes less than 30 seconds. I spend more time waiting for indexes to recreate.<DIV><br><br>Patrick Hatcher<br>Macys.Com<br><DIV> </DIV><FONT color=#990099>-----pgsql-performance-owner(at)postgresql(dot)org wrote: -----<BR><BR></FONT>To: pgsql-performance(at)postgresql(dot)org<BR>From: Christopher Browne <cbbrowne(at)acm(dot)org><BR>Sent by: pgsql-performance-owner(at)postgresql(dot)org<BR>Date: 2004-12-04 06:48AM<BR>Subject: Re: [PERFORM] Improve BULK insertion<BR><BR><font face="monospace" size=2>In the last exciting episode, grupos(at)carvalhaes(dot)net (Grupos) wrote:<BR>> Hi !<BR>><BR>> I need to insert 500.000 records on a table frequently. It´s a bulk<BR>> insertion from my applicatoin.<BR>> I am with a very poor performance. PostgreSQL insert very fast until<BR>> the tuple 200.000 and after it the insertion starts to be really slow.<BR>> I am seeing on the log and there is a lot of transaction logs,<BR>> something like :<BR>><BR>> 2004-12-04 11:08:59 LOG: recycled transaction log file "0000000600000012"<BR>> 2004-12-04 11:08:59 LOG: recycled transaction log file "0000000600000013"<BR>> 2004-12-04 11:08:59 LOG: recycled transaction log file "0000000600000011"<BR>> 2004-12-04 11:14:04 LOG: recycled transaction log file "0000000600000015"<BR>> 2004-12-04 11:14:04 LOG: recycled transaction log file "0000000600000014"<BR>> 2004-12-04 11:19:08 LOG: recycled transaction log file "0000000600000016"<BR>> 2004-12-04 11:19:08 LOG: recycled transaction log file "0000000600000017"<BR>> 2004-12-04 11:24:10 LOG: recycled transaction log file "0000000600000018"<BR><BR>It is entirely normal for there to be a lot of transaction log file<BR>recycling when bulk inserts are taking place; that goes through a lot<BR>of transaction logs.<BR><BR>> How can I configure PostgreSQL to have a better performance on this<BR>> bulk insertions ? I already increased the memory values.<BR><BR>Memory is, as likely as not, NOT the issue.<BR><BR>Two questions:<BR><BR> 1. How are you doing the inserts? Via INSERT statements? Or<BR> via COPY statements? What sort of transaction grouping<BR> is involved?<BR><BR> COPY is way faster than INSERT, and grouping plenty of updates<BR> into a single transaction is generally a "win."<BR><BR> 2. What is the schema like? Does the table have a foreign key<BR> constraint? Does it have a bunch of indices?<BR><BR> If there should eventually be lots of indices, it tends to be<BR> faster to create the table with none/minimal indices, and add<BR> indexes afterwards, as long as your "load" process can be trusted<BR> to not break "unique" constraints...<BR><BR> If there is some secondary table with a foreign key constraint,<BR> and _that_ table is growing, it is possible that a sequential<BR> scan is being used to search the secondary table where, if you<BR> did an ANALYZE on that table, an index scan would be preferred<BR> once it grew to larger size...<BR><BR>There isn't a particular reason for PostgreSQL to "hit a wall" upon<BR>seeing 200K records; I and coworkers routinely load database dumps<BR>that have millions of (sometimes pretty fat) records, and they don't<BR>"choke." That's true whether talking about loading things onto my<BR>(somewhat wimpy) desktop PC, or a SMP Xeon system with a small RAID<BR>array, or higher end stuff involving high end SMP and EMC disk arrays.<BR>The latter obviously being orders of magnitude faster than desktop<BR>equipment :-).<BR>-- <BR>(format nil "~S(at)~S" "cbbrowne" "acm.org")<BR><a href="http://www3.sympatico.ca/cbbrowne/unix.html" target=blank>http://www3.sympatico.ca/cbbrowne/unix.html</a><BR>Rules of the Evil Overlord #207. "Employees will have conjugal visit<BR>trailers which they may use provided they call in a replacement and<BR>sign out on the timesheet. Given this, anyone caught making out in a<BR>closet while leaving their station unmonitored will be shot."<BR><<a href="http://www.eviloverlord.com/" target=blank>http://www.eviloverlord.com/</a>><BR><BR>---------------------------(end of broadcast)---------------------------<BR>TIP 7: don't forget to increase your free space map settings<BR></font></DIV></DIV></FONT>