DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=OtaAtc8HaSr4Z52SdnSKcAZKBy21knmDvjaa8oTl2Imre+x79hN1fUheMg5Us8iAx/
	4A1owudiexQ2KiEb86Ruou9IMiezlmOHz084JqFTq8uGe7Dy1MpZ3dPjrG30SSFiEaXH
	3J33/xsXfIYV1P46xOgoczs5FT6+pazxr/E+Q=
MIME-Version: 1.0
In-Reply-To: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
	<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
Date: Wed, 4 Nov 2009 08:58:07 -0800
Message-ID: <f2fd819a0911040858w103c90cbi63d2fda3b814707f@mail.gmail.com>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
From: Anj Adu <fotographs@gmail.com>
To: Jay Manni <JManni@fireeye.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

> I have an application wherein a process needs to read data from a stream =
and
> store the records for further analysis and reporting. The data in the str=
eam
> is in the form of variable length records with clearly defined fields =96=
 so
> it can be stored in a database or in a file. The only caveat is that the
> rate of records coming in the stream could be several 1000 records a seco=
nd.
> The design choice I am faced with currently is whether to use a postgres
> database or a flat file for this purpose. My application already maintain=
s a
> postgres (8.3.4) database for other reasons =96 so it seemed like the
> straightforward thing to do. However I am concerned about the performance
> overhead of writing several 1000 records a second to the database. The sa=
me
> database is being used simultaneously for other activities as well and I =
do
> not want those to be adversely affected by this operation (especially the
> query times). The advantage of running complex queries to mine the data i=
n
> various different ways is very appealing but the performance concerns are
> making me wonder if just using a flat file to store the data would be a
> better approach.
>
>
>
> Anybody have any experience in high frequency writes to a postgres databa=
se?


As mentioned earlier in this thread,,make sure your hardware can
scale. You may hit a "monolithic hardware" wall and may have to
distribute your data across multiple boxes and have your application
manage the distribution and access. A RAID 10 storage
architecture(since fast writes are critical) with a mulitple core box
(preferably 8) having fast scsi disks (15K rpm) may be a good starting
point.

We have a similar requirement and we scale by distributing the data
across multiple boxes. This is key.

If you need to run complex queries..plan on aggregation strategies
(processes that aggregate and optimize the data storage to facilitate
faster access).

Partitioning is key. You will need to purge old data at some point.
Without partitions..you will run into trouble with the time taken to
delete old data as well as availability of disk space.

These are just guidelines for a big warehouse style database.