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