From: Craig Ringer <ringerc@ringerc.id.au>
To: pgsql-performance@postgresql.org
Subject: Re: Heavy contgnous load
Date: Wed, 19 Oct 2011 08:56:43 +0800
Message-ID: <4E9E204B.2040608@ringerc.id.au> (raw)
In-Reply-To: <1318939765661-4913425.post@n5.nabble.com>
References: <1318939765661-4913425.post@n5.nabble.com>
On 10/18/2011 08:09 PM, kzsolt wrote:
> What is important think for this task I do not need any tranasction. So the
> COMMIT and ROLLBACK feature is useless.
> The question is how I minimize the rollback activity to free resoureces?
Actually, you do need transactions, because they're what prevents your
database from being corrupted or left in a half-updated state if/when
the database server loses power, crashes, etc.
Presumably when you say "rollback activity" you mean the overheads
involved in supporting transactional, atomic updates? If so, there isn't
much you can do in an INSERT-only database except try to have as few
indexes as possible and do your inserts inside transactions in batches,
rather than one-by-one as individual statements.
Consider logging to a flat file to accumulate data, then COPYing data in
batches into PostgreSQL.
An alternative would be to write your data to an unlogged table
(PostgreSQL 9.1+ only) then `INSERT INTO ... SELECT ...' it into the
main table(s) in batches. Unlogged tables avoid most of the overheads of
the write-ahead log crash safety, but they do that by NOT BEING CRASH
SAFE. If your server, or the PostgreSQL process, crashes then unlogged
tables will be ERASED. If you can afford to lose a little data in this
case, you can use unlogged tables as a staging area.
--
Craig Ringer
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
Subject: Re: Heavy contgnous load
In-Reply-To: <4E9E204B.2040608@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