pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Craig Ringer <ringerc@ringerc.id.au>
To: kzsolt <kzsoltkzsolt@freemail.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Heavy contgnous load
Date: Thu, 20 Oct 2011 11:44:36 +0800
Message-ID: <4E9F9924.7020707@ringerc.id.au> (raw)
In-Reply-To: <1319050535000-4919006.post@n5.nabble.com>
References: <1318939765661-4913425.post@n5.nabble.com>
	<4E9E204B.2040608@ringerc.id.au>
	<1319050535000-4919006.post@n5.nabble.com>

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



view thread (11+ messages)  latest in thread

Message-ID: <4E9F9924.7020707@ringerc.id.au>
Permalink:  ../4E9F9924.7020707@ringerc.id.au/
Also on:    postgresql.org/message-id/4E9F9924.7020707@ringerc.id.au

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ringerc@ringerc.id.au, kzsoltkzsolt@freemail.hu
  Subject: Re: Heavy contgnous load
  In-Reply-To: <4E9F9924.7020707@ringerc.id.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox