On 10/20/2011 02:55 AM, kzsolt wrote:
"try to have as few indexes as possible and do your inserts inside transactions in batches, rather than one-by-one as individual statements. " That is the main problem. I use now few index as possible. Unfortunately the one-by-one INSERT is nature of or system. To join (batch) inserts is require spacial cache with inactivity timeout. But this timout are make more trouble for our application. The flat file has same problem.
Well, then you'll have to use an unlogged table (9.1 or newer only) to insert into, then periodically copy rows from the unlogged table into the main table using something like PgAgent to schedule the copy.
An unlogged table is a tiny bit more like MySQL's MyISAM tables in that it doesn't have any crash recovery features. It still supports transactions, of course, and you won't find any way to remove transaction support in PostgreSQL. One of the reasons MySQL has historically had so many bizarre behaviours, like (by default) writing invalid data as NULL, inserting zeroes for invalid dates, etc is because MyISAM can't roll back transactions when it discovers a problem partway through, so it has to finish the job badly rather than error out and leave the job half-completed.
If you really need absolutely maximum insert performance, you should just use a flat file or a different database system. Relational databases like PostgreSQL are designed for reliable concurrency, crash safety, fast querying, and data integrity, and they provide those at the cost of slower data insertion among other things.
-- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
