From: Jeff Janes <jeff.janes@gmail.com>
To: Jay Manni <JManni@fireeye.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
Date: Wed, 4 Nov 2009 08:39:46 -0800
Message-ID: <f67928030911040839n75c9f6ean4ef21ac457bb384e@mail.gmail.com> (raw)
In-Reply-To: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
On Tue, Nov 3, 2009 at 7:12 PM, Jay Manni <JManni@fireeye.com> wrote:
> Hi:
>
>
>
> I have an application wherein a process needs to read data from a stream and
> store the records for further analysis and reporting.
Where is the stream coming from? What happens if the process reading
the stream fails but the one generating the stream keeps going?
> The data in the stream
> is in the form of variable length records with clearly defined fields – 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 second.
>
> 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 maintains a
> postgres (8.3.4) database for other reasons – 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 same
> 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).
I would not use the database, but just a flat file. You can always load it
to a database later as long as you keep the files around, if a
compelling reason arises.
> The advantage of running complex queries to mine the data in
> various different ways is very appealing
Do you have concrete plans to do this, or just vague notions?
Even if the loading of 1000s of records per second doesn't adversely
impact the performance of other things going on in the server, surely
doing complex queries on hundreds of millions of records will. How
long to you plan on storing the records in the database, and how to
delete them out? Do you already know what indexes, if any, should be
on the table?
Jeff
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: jeff.janes@gmail.com, JManni@fireeye.com
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
In-Reply-To: <f67928030911040839n75c9f6ean4ef21ac457bb384e@mail.gmail.com>
* 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