[postgis-users] Insert performance issues

[Gerry Creager N5JXS]

So: I'm doing a whole slew of inserts (OK, COPY's) on a frequent basis, 
say, every 6 minutes.  I can lock the table, take down the index, do the 
COPY, unlock the table, reindex.  Doesn't take _too_ long 'til I get to 
the new index.  I've got to be able to snag those data in almost real 
(read: "as inserted") time for display on my Mesonet site.

I'm concerned that your solution, while we've found it to be the 
fastest, too, will adversely affect our SELECT performance and plotting.

Thanks,
Gerry

Charlton Purvis wrote:
> Sorry to have joined the thread so late.  Good chance that I've missed
> the crux of the messages.
> 
> But I read through the thread, and your initial questioned seemed to be
> centered around insert performance.  And I am intimate w/ that beast.
> 
> I'd HIGHLY recommend that you use the pgsql COPY command instead of lots
> of inserts.  It reduced my insert time exponentially from something like
> 12 hours to 5 minutes for about 5 million records.  Kind of crazy, eh?
> 
> That is, if you can lock the table you're updating w/o effecting your
> production system.  Also, removing all indices on the table before
> copying helps, too.  Then rebuild and analyze them once your table is
> populated.
> 
> Charlton
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
Gerry Creager -- gerry.creager at tamu.edu
Network Engineering -- AATLT, Texas A&M University	
Cell: 979.229.5301 Office: 979.458.4020 FAX: 979.847.8578
Page: 979.228.0173
Office: 903A Eller Bldg, TAMU, College Station, TX 77843