Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D0DA963353A for ; Tue, 3 Nov 2009 23:42:18 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75280-02 for ; Wed, 4 Nov 2009 03:42:10 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from n4.bullet.mail.ac4.yahoo.com (n4.bullet.mail.ac4.yahoo.com [76.13.13.28]) by mail.postgresql.org (Postfix) with SMTP id B944063222F for ; Tue, 3 Nov 2009 23:42:10 -0400 (AST) Received: from [76.13.13.25] by n4.bullet.mail.ac4.yahoo.com with NNFMP; 04 Nov 2009 03:42:09 -0000 Received: from [67.195.9.81] by t4.bullet.mail.ac4.yahoo.com with NNFMP; 04 Nov 2009 03:42:08 -0000 Received: from [98.137.27.209] by t1.bullet.mail.gq1.yahoo.com with NNFMP; 04 Nov 2009 03:42:08 -0000 Received: from [127.0.0.1] by omp119.mail.gq1.yahoo.com with NNFMP; 04 Nov 2009 03:42:08 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 768324.1217.bm@omp119.mail.gq1.yahoo.com Received: (qmail 38005 invoked by uid 60001); 4 Nov 2009 03:42:08 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=uH1+R54ESznWQUEgLctlUZBFqhypwwXjGMwI2HU7TccLYLQiZIOuWrd3IZMO+87wpNQEPsndj/g/uHAaO10WmrXUww5jRLCq0/v2E/sR+LceBvh4WWiokNMt7KDw/nQqscLhz1rnYTuHGFgwNFqFhPBNuwNySVTXH5hk/7DPWaQ=; Message-ID: <582494.35747.qm@web180304.mail.gq1.yahoo.com> X-YMail-OSG: NQpIiakVM1mqW9Z5H8Lgjb1GdMguRj2lENFislA7dzrqvv8Ej3vBFcStJiWbMFRUqYwdZSBjShEx3BgKAv9D2rvjJi21jlvLBlzgz7Fw_dVYPdnJQgQkuQqBx1kP3z3tKcr8iKG6RiYNCqMuIj6DKAEDcJLlUCuX98Uyc4BhwsRJ1NzfZif_xsjqnVKt3XZOtPsmKuTpkmg2cadNgcIcbc_cPbo6hqgANUkyhs1daB7kF2zAkTu_HsohcEDgrUkDjoUtZa.CLiMnVdx4c1F5c4A4ZFqCvwqrL9K.3Lth.vPv.RuCawDxdbLMTTagdQlgEeCK3ZcdgBJYaV0ZyZbZiJSYtyVf1CJdb2MShN_uKKJmbz379r7OLKnK Received: from [67.170.81.155] by web180304.mail.gq1.yahoo.com via HTTP; Tue, 03 Nov 2009 19:42:08 PST X-Mailer: YahooMailRC/211.6 YahooMailWebService/0.7.347.3 References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com> <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com> Date: Tue, 3 Nov 2009 19:42:08 -0800 (PST) From: David Saracini Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File To: Jay Manni , "pgsql-performance@postgresql.org" In-Reply-To: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1442661790-1257306128=:35747" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/39 X-Sequence-Number: 36207 --0-1442661790-1257306128=:35747 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable "could be several 1000 records a second."=0A=0ASo, are there periods when t= here are no/few records coming in? Do the records/data/files really need t= o be persisted? =0A=0AThe following statement makes me think you should go= the flat file route:=0A=0A"The advantage of running complex queries to min= e the data in various different ways is very appealing"=0A=0APlease don't b= e offended, but that sounds a little like feature creep. I've found that i= t's best to keep it simple and don't do a bunch of work now for what might = be requested in the future.=0A=0AI know it's not exactly what you were look= ing for... Just food for thought.=0A=0ABest of luck!=0A=0ADavid=0A=0A=0A= =0A________________________________=0AFrom: Jay Manni = =0ATo: "pgsql-performance@postgresql.org" =0ASent: Tue, November 3, 2009 7:12:29 PM=0ASubject: [PERFORM] High Freque= ncy Inserts to Postgres Database vs Writing to a File=0A=0A =0AHi:=0A =0AI = have an application=0Awherein a process needs to read data from a stream an= d store the records for=0Afurther analysis and reporting. The data in the s= tream is in the form of=0Avariable length records with clearly defined fiel= ds =E2=80=93 so it can be stored in a=0Adatabase or in a file. The only cav= eat is that the rate of records coming in=0Athe stream could be several 100= 0 records a second. =0A =0AThe design choice I am=0Afaced with currently is= whether to use a postgres database or a flat file for=0Athis purpose. My a= pplication already maintains a postgres (8.3.4) database for=0Aother reason= s =E2=80=93 so it seemed like the straightforward thing to do. However I am= =0Aconcerned about the performance overhead of writing several 1000 records= a=0Asecond to the database. The same database is being used simultaneously= for=0Aother activities as well and I do not want those to be adversely aff= ected by=0Athis operation (especially the query times). The advantage of ru= nning complex=0Aqueries to mine the data in various different ways is very = appealing but the=0Aperformance concerns are making me wonder if just using= a flat file to store=0Athe data would be a better approach. =0A =0AAnybody= have any=0Aexperience in high frequency writes to a postgres database?=0A = =0A- Jay --0-1442661790-1257306128=:35747 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
"could be several 1000 records a second."

<= div>So, are there periods wh= en there are no/few records coming in?  Do the records/data/files real= ly need to be persisted?  

The following statement makes me think you should go the flat file route:

"The ad= vantage of running complex queries to mine the data in various different wa= ys 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.
<= font class=3D"Apple-style-span" face=3D"Consolas" size=3D"4">
= Best of luck!
<= span class=3D"Apple-style-span" style=3D"font-size: 15px;">
= David

From: Jay Mann= i <JManni@FireEye.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgres= ql.org>
Sent: Tue, N= ovember 3, 2009 7:12:29 PM
Subject= : [PERFORM] High Frequency Inserts to Postgres Database vs Writi= ng to a File

=0A=0A=0A=0A =0A =0A