Message-ID: <4E9F9924.7020707@ringerc.id.au>
Date: Thu, 20 Oct 2011 11:44:36 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20110930 Thunderbird/7.0.1
MIME-Version: 1.0
To: kzsolt <kzsoltkzsolt@freemail.hu>
CC: pgsql-performance@postgresql.org
Subject: Re: Heavy contgnous load
References: <1318939765661-4913425.post@n5.nabble.com>
	<4E9E204B.2040608@ringerc.id.au>
	<1319050535000-4919006.post@n5.nabble.com>
In-Reply-To: <1319050535000-4919006.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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