From: David Saracini <dsaracini@yahoo.com>
To: Jay Manni <JManni@FireEye.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
Date: Tue, 3 Nov 2009 19:42:08 -0800 (PST)
Message-ID: <582494.35747.qm@web180304.mail.gq1.yahoo.com> (raw)
In-Reply-To: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
"could be several 1000 records a second."
So, are there periods when there are no/few records coming in? Do the records/data/files really need to be persisted?
The following statement makes me think you should go the flat file route:
"The advantage of running complex queries to mine the data in various different ways is very appealing"
Please don't be offended, but that sounds a little like feature creep. I've found that it's best to keep it simple and don't do a bunch of work now for what might be requested in the future.
I know it's not exactly what you were looking for... Just food for thought.
Best of luck!
David
________________________________
From: Jay Manni <JManni@FireEye.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Sent: Tue, November 3, 2009 7:12:29 PM
Subject: [PERFORM] High Frequency Inserts to Postgres Database vs Writing to a File
Hi:
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 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). The advantage of running complex
queries to mine the data in 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 database?
- Jay
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: dsaracini@yahoo.com, JManni@FireEye.com
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
In-Reply-To: <582494.35747.qm@web180304.mail.gq1.yahoo.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