From JManni@FireEye.com Wed Nov  4 03:08:18 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 7C54263222F
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  3 Nov 2009 23:08: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 70523-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 03:08:09 +0000 (UTC)
X-Greylist: delayed 00:05:14.364418 by SQLgrey-1.7.6
Received: from fempexch.FireEye.com (mail2.FireEye.com [12.68.100.48])
	by mail.postgresql.org (Postfix) with ESMTP id 6F57A633847
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Nov 2009 23:08:09 -0400 (AST)
Received: from mail2.FireEye.com ([192.168.0.116]) by fempexch.FireEye.com
	with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 3 Nov 2009 19:02:52 -0800
Received: from mail2.FireEye.com ([fe80::c1f0:c46:3b66:3f6c]) by
	mail2.FireEye.com ([fe80::c1f0:c46:3b66:3f6c%11]) with mapi;
	Tue, 3 Nov 2009 19:02:52 -0800
From: Jay Manni <JManni@FireEye.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Date: Tue, 3 Nov 2009 19:02:50 -0800
Subject: High Frequency Inserts to Postgres Database vs Writing to a File
Thread-Topic: High Frequency Inserts to Postgres Database vs Writing to a File
Thread-Index: Acpc+0ubBi18HvW7SG6CVEDg9si6Ow==
Message-ID: <60B0F2124D07B942988329B5B7CA393D01E5B93FF4@mail2.FireEye.com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF4mail2FireEyec_"
MIME-Version: 1.0
X-OriginalArrivalTime: 04 Nov 2009 03:02:52.0303 (UTC)
	FILETIME=[4C7911F0:01CA5CFB]
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/74
X-Sequence-Number: 36242

--_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF4mail2FireEyec_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi:

I have an application wherein a process needs to read data from a stream an=
d 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 -=
 so it can be stored in a database or in a file. The only caveat is that th=
e 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 da=
tabase 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 straigh=
tforward thing to do. However I am concerned about the performance overhead=
 of writing several 1000 records a second to the database. The same databas=
e is being used simultaneously for other activities as well and I do not wa=
nt those to be adversely affected by this operation (especially the query t=
imes). The advantage of running complex queries to mine the data in various=
 different ways is very appealing but the performance concerns are making m=
e wonder if just using a flat file to store the data would be a better appr=
oach.

Anybody have any experience in high frequency writes to a postgres database=
?

- Jay

--_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF4mail2FireEyec_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spreadsheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
.org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
 xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/services/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/services/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPortal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"&#1;" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Consolas;
	panose-1:2 11 6 9 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:Consolas;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>Hi:<o:p></o:p></s=
pan></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>I have an applica=
tion
wherein a process needs to read data from a stream and store the records fo=
r
further analysis and reporting. The data in the stream is in the form of
variable length records with clearly defined fields &#8211; so it can be st=
ored
in a database or in a file. The only caveat is that the rate of records com=
ing
in the stream could be several 1000 records a second. <o:p></o:p></span></p=
>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>The design choice=
 I am
faced with currently is whether to use a postgres database or a flat file f=
or this
purpose. My application already maintains a postgres (8.3.4) database for o=
ther
reasons &#8211; 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 b=
y
this operation (especially the query times). The advantage of running compl=
ex
queries to mine the data in various different ways is very appealing but th=
e
performance concerns are making me wonder if just using a flat file to stor=
e
the data would be a better approach. <o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>Anybody have any
experience in high frequency writes to a postgres database?<o:p></o:p></spa=
n></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>- Jay<o:p></o:p><=
/span></p>

</div>

</body>

</html>

--_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF4mail2FireEyec_--



From JManni@FireEye.com Wed Nov  4 03:12:41 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 9B9206335BC
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  3 Nov 2009 23:12:41 -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 70871-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 03:12:32 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from fempexch.FireEye.com (mail2.FireEye.com [12.68.100.48])
	by mail.postgresql.org (Postfix) with ESMTP id BC52E63222F
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Nov 2009 23:12:32 -0400 (AST)
Received: from mail2.FireEye.com ([192.168.0.116]) by fempexch.FireEye.com
	with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 3 Nov 2009 19:12:31 -0800
Received: from mail2.FireEye.com ([fe80::c1f0:c46:3b66:3f6c]) by
	mail2.FireEye.com ([fe80::c1f0:c46:3b66:3f6c%11]) with mapi;
	Tue, 3 Nov 2009 19:12:31 -0800
From: Jay Manni <JManni@FireEye.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Date: Tue, 3 Nov 2009 19:12:29 -0800
Subject: High Frequency Inserts to Postgres Database vs Writing to a File
Thread-Topic: High Frequency Inserts to Postgres Database vs Writing to a File
Thread-Index: Acpc+0ubBi18HvW7SG6CVEDg9si6OwAAU87A
Message-ID: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
In-Reply-To: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF7mail2FireEyec_"
MIME-Version: 1.0
X-OriginalArrivalTime: 04 Nov 2009 03:12:31.0303 (UTC)
	FILETIME=[A5957570:01CA5CFC]
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/37
X-Sequence-Number: 36205

--_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF7mail2FireEyec_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi:

I have an application wherein a process needs to read data from a stream an=
d 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 -=
 so it can be stored in a database or in a file. The only caveat is that th=
e 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 da=
tabase 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 straigh=
tforward thing to do. However I am concerned about the performance overhead=
 of writing several 1000 records a second to the database. The same databas=
e is being used simultaneously for other activities as well and I do not wa=
nt those to be adversely affected by this operation (especially the query t=
imes). The advantage of running complex queries to mine the data in various=
 different ways is very appealing but the performance concerns are making m=
e wonder if just using a flat file to store the data would be a better appr=
oach.

Anybody have any experience in high frequency writes to a postgres database=
?

- Jay

--_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF7mail2FireEyec_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spreadsheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
.org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
 xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/services/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/services/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPortal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"&#1;" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Consolas;
	panose-1:2 11 6 9 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal;
	font-family:Consolas;
	color:windowtext;}
span.EmailStyle18
	{mso-style-type:personal-reply;
	font-family:Consolas;
	color:#1F497D;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:10.0pt;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>Hi:<o:p></o:p></s=
pan></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>I have an applica=
tion
wherein a process needs to read data from a stream and store the records fo=
r
further analysis and reporting. The data in the stream is in the form of
variable length records with clearly defined fields &#8211; so it can be st=
ored in a
database or in a file. The only caveat is that the rate of records coming i=
n
the stream could be several 1000 records a second. <o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>The design choice=
 I am
faced with currently is whether to use a postgres database or a flat file f=
or
this purpose. My application already maintains a postgres (8.3.4) database =
for
other reasons &#8211; so it seemed like the straightforward thing to do. Ho=
wever 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 b=
y
this operation (especially the query times). The advantage of running compl=
ex
queries to mine the data in various different ways is very appealing but th=
e
performance concerns are making me wonder if just using a flat file to stor=
e
the data would be a better approach. <o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>Anybody have any
experience in high frequency writes to a postgres database?<o:p></o:p></spa=
n></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'><o:p>&nbsp;</o:p>=
</span></p>

<p class=3DMsoNormal><span style=3D'font-family:Consolas'>- Jay<o:p></o:p><=
/span></p>

</div>

</body>

</html>

--_000_60B0F2124D07B942988329B5B7CA393D01E5B93FF7mail2FireEyec_--



From scott.marlowe@gmail.com Wed Nov  4 03:35:32 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 5AE686321E7
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  3 Nov 2009 23:35:32 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 63878-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 03:35:12 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-iw0-f186.google.com (mail-iw0-f186.google.com
	[209.85.223.186])
	by mail.postgresql.org (Postfix) with ESMTP id 891136335BC
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Nov 2009 23:35:21 -0400 (AST)
Received: by iwn16 with SMTP id 16so4497298iwn.29
	for <pgsql-performance@postgresql.org>;
	Tue, 03 Nov 2009 19:35:19 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=rBVJXSTTlsjKC0Msa+5sieYCWjs+tjulvk/3WEKoyBQ=;
	b=wtwL0hN0HA21WQBbn5n0p+r6TnKBDlan6sRGwTNfjzFkbuprxnUGk24Ometykjnjp+
	Ru/fSVksLqBTtfS4FQSC/S+ftr+evKjHxJtl0VSH2Vs5doUtXwMrcLoNjwdDbV8LfHUE
	XxStuEcyPSF8ViyCn2wyoFGCNaoerb7Zy2x0s=
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=kBu+faRDY5vsT7YkQ45MeEw4yz99i+Bi5XrSmV5LXlJw36US2NNf9LJPH76hWcVH87
	dtUwOW4TF4qhVsMq6VnKVs62fUR7b/W8kHLB9IteszgaDkbCyd3E/pDkuFhOgJfzSTpg
	YDLp881Til148tvcDR/153VWkpRvfinj79rMQ=
MIME-Version: 1.0
Received: by 10.231.5.23 with SMTP id 23mr2488765ibt.45.1257305719481;
 Tue, 03
	Nov 2009 19:35:19 -0800 (PST)
In-Reply-To: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
	<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
Date: Tue, 3 Nov 2009 20:35:19 -0700
Message-ID: <dcc563d10911031935q5edf8961ye7812c161ea6e762@mail.gmail.com>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
From: Scott Marlowe <scott.marlowe@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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.506 tagged_above=-10 required=5 tests=AWL=0.093,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/38
X-Sequence-Number: 36206

On Tue, Nov 3, 2009 at 8: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. 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

A common approach is to store them in flat files, then insert the flat
files at a later time so that if the db falls behind no data is lost.



From dsaracini@yahoo.com Wed Nov  4 03:42:18 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id D0DA963353A
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	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 <pgsql-performance-postgresql.org@mail.postgresql.org>;
	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 <pgsql-performance@postgresql.org>;
	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 <dsaracini@yahoo.com>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
To: Jay Manni <JManni@FireEye.com>,
	"pgsql-performance@postgresql.org" <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 <JManni@FireEye.com>=
=0ATo: "pgsql-performance@postgresql.org" <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

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:arial, helvetica, sans-serif;font-size:1=
0pt"><div>"<span class=3D"Apple-style-span" style=3D"font-family: Consolas;=
 font-size: 15px; ">could be several 1000 records a second."</span></div><d=
iv><font class=3D"Apple-style-span" face=3D"Consolas" size=3D"4"><span clas=
s=3D"Apple-style-span" style=3D"font-size: 15px;"><br></span></font></div><=
div><font class=3D"Apple-style-span" face=3D"Consolas" size=3D"4"><span cla=
ss=3D"Apple-style-span" style=3D"font-size: 15px;">So, are there periods wh=
en there are no/few records coming in? &nbsp;Do the records/data/files real=
ly need to be persisted? &nbsp;</span></font></div><div><font class=3D"Appl=
e-style-span" face=3D"Consolas" size=3D"4"><span class=3D"Apple-style-span"=
 style=3D"font-size: 15px;"><br></span></font></div><div><font class=3D"App=
le-style-span" face=3D"Consolas" size=3D"4"><span class=3D"Apple-style-span=
" style=3D"font-size: 15px;">The
 following statement makes me think you should go the flat file route:</spa=
n></font></div><div><font class=3D"Apple-style-span" face=3D"Consolas" size=
=3D"4"><span class=3D"Apple-style-span" style=3D"font-size: 15px;"><br></sp=
an></font></div><div><font class=3D"Apple-style-span" face=3D"Consolas" siz=
e=3D"4"><span class=3D"Apple-style-span" style=3D"font-size: 15px;">"The ad=
vantage of running complex queries to mine the data in various different wa=
ys is very appealing"</span></font></div><div><font class=3D"Apple-style-sp=
an" face=3D"Consolas" size=3D"4"><span class=3D"Apple-style-span" style=3D"=
font-size: 15px;"><br></span></font></div><div><font class=3D"Apple-style-s=
pan" face=3D"Consolas" size=3D"4"><span class=3D"Apple-style-span" style=3D=
"font-size: 15px;">Please don't be offended, but that sounds a little like =
feature creep. &nbsp;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.</span></f=
ont></div><div><font
 class=3D"Apple-style-span" face=3D"Consolas" size=3D"4"><span class=3D"App=
le-style-span" style=3D"font-size: 15px;"><br></span></font></div><div><fon=
t class=3D"Apple-style-span" face=3D"Consolas" size=3D"4"><span class=3D"Ap=
ple-style-span" style=3D"font-size: 15px;">I know it's not exactly what you=
 were looking for... &nbsp;Just food for thought.</span></font></div><div><=
font class=3D"Apple-style-span" face=3D"Consolas" size=3D"4"><span class=3D=
"Apple-style-span" style=3D"font-size: 15px;"><br></span></font></div><div>=
<font class=3D"Apple-style-span" face=3D"Consolas" size=3D"4"><span class=
=3D"Apple-style-span" style=3D"font-size: 15px;">Best of luck!</span></font=
></div><div><font class=3D"Apple-style-span" face=3D"Consolas" size=3D"4"><=
span class=3D"Apple-style-span" style=3D"font-size: 15px;"><br></span></fon=
t></div><div><font class=3D"Apple-style-span" face=3D"Consolas" size=3D"4">=
<span class=3D"Apple-style-span" style=3D"font-size: 15px;">David</span></f=
ont></div><div style=3D"font-family:arial, helvetica,
 sans-serif;font-size:10pt"><br><div style=3D"font-family:times new roman, =
new york, times, serif;font-size:12pt"><font size=3D"2" face=3D"Tahoma"><hr=
 size=3D"1"><b><span style=3D"font-weight: bold;">From:</span></b> Jay Mann=
i &lt;JManni@FireEye.com&gt;<br><b><span style=3D"font-weight: bold;">To:</=
span></b> "pgsql-performance@postgresql.org" &lt;pgsql-performance@postgres=
ql.org&gt;<br><b><span style=3D"font-weight: bold;">Sent:</span></b> Tue, N=
ovember 3, 2009 7:12:29 PM<br><b><span style=3D"font-weight: bold;">Subject=
:</span></b> [PERFORM] High Frequency Inserts to Postgres Database vs Writi=
ng to a File<br></font><br>=0A=0A=0A=0A =0A =0A<style>=0A<!--=0A =0A _filte=
red {font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;}=0A _filtered=
 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}=0A _filtered {font-fa=
mily:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;}=0A =0Ap.MsoNormal, li.MsoNorm=
al, div.MsoNormal=0A=09{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;f=
ont-family:"Calibri", "sans-serif";}=0Aa:link, span.MsoHyperlink=0A=09{colo=
r:blue;text-decoration:underline;}=0Aa:visited, span.MsoHyperlinkFollowed=
=0A=09{color:purple;text-decoration:underline;}=0Aspan.EmailStyle17=0A=09{f=
ont-family:Consolas;color:windowtext;}=0Aspan.EmailStyle18=0A=09{font-famil=
y:Consolas;color:#1F497D;}=0A.MsoChpDefault=0A=09{font-size:10.0pt;}=0A _fi=
ltered {margin:1.0in 1.0in 1.0in 1.0in;}=0Adiv.Section1=0A=09{}=0A-->=0A</s=
tyle>=0A=0A=0A=0A=0A=0A<div class=3D"Section1">=0A=0A<p class=3D"MsoNormal"=
><span style=3D"font-family:Consolas;">Hi:</span></p> =0A=0A<p class=3D"Mso=
Normal"><span style=3D"font-family:Consolas;"> &nbsp;</span></p> =0A=0A<p c=
lass=3D"MsoNormal"><span style=3D"font-family:Consolas;">I have an applicat=
ion=0Awherein a process needs to read data from a stream and store the reco=
rds for=0Afurther analysis and reporting. The data in the stream is in the =
form of=0Avariable length records with clearly defined fields =E2=80=93 so =
it can be stored in a=0Adatabase or in a file. The only caveat is that the =
rate of records coming in=0Athe stream could be several 1000 records a seco=
nd. </span></p> =0A=0A<p class=3D"MsoNormal"><span style=3D"font-family:Con=
solas;"> &nbsp;</span></p> =0A=0A<p class=3D"MsoNormal"><span style=3D"font=
-family:Consolas;">The design choice I am=0Afaced with currently is whether=
 to use a postgres database or a flat file for=0Athis purpose. My applicati=
on already maintains a postgres (8.3.4) database for=0Aother reasons =E2=80=
=93 so it seemed like the straightforward thing to do. However I am=0Aconce=
rned about the performance overhead of writing several 1000 records a=0Asec=
ond to the database. The same database is being used simultaneously for=0Ao=
ther activities as well and I do not want those to be adversely affected by=
=0Athis operation (especially the query times). The advantage of running co=
mplex=0Aqueries to mine the data in various different ways is very appealin=
g but the=0Aperformance concerns are making me wonder if just using a flat =
file to store=0Athe data would be a better approach. </span></p> =0A=0A<p c=
lass=3D"MsoNormal"><span style=3D"font-family:Consolas;"> &nbsp;</span></p>=
 =0A=0A<p class=3D"MsoNormal"><span style=3D"font-family:Consolas;">Anybody=
 have any=0Aexperience in high frequency writes to a postgres database?</sp=
an></p> =0A=0A<p class=3D"MsoNormal"><span style=3D"font-family:Consolas;">=
 &nbsp;</span></p> =0A=0A<p class=3D"MsoNormal"><span style=3D"font-family:=
Consolas;">- Jay</span></p> =0A=0A</div>=0A=0A=0A=0A=0A</div></div><div sty=
le=3D"position:fixed"></div>=0A=0A=0A<!-- cg2.c204.mail.gq1.yahoo.com compr=
essed/chunked Mon Nov  2 11:03:07 PST 2009 -->=0A</div></body></html>
--0-1442661790-1257306128=:35747--




From gsmith@2ndquadrant.com Wed Nov  4 04:48:09 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id B7E8D63311F
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 00:48:09 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 75187-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 04:47:58 +0000 (UTC)
X-Greylist: delayed 00:05:03.276547 by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.10])
	by mail.postgresql.org (Postfix) with ESMTP id E7AF863278C
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Nov 2009 00:47:58 -0400 (AST)
Received: from [192.168.1.3] (pool-96-244-14-10.bltmmd.fios.verizon.net
	[96.244.14.10])
	by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis)
	id 0MZwWl-1Mraei1aaF-00LCHn; Wed, 04 Nov 2009 05:42:52 +0100
Message-ID: <4AF10648.5080905@2ndquadrant.com>
Date: Tue, 03 Nov 2009 23:42:48 -0500
From: Greg Smith <gsmith@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
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
References: <alpine.GSO.2.01.0911032303250.2715@westnet.com>
In-Reply-To: <alpine.GSO.2.01.0911032303250.2715@westnet.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V01U2FsdGVkX19Kx6YEijYvZuY6XcLkMzvKmU7RBRZfs6dCIDw
	rjneJ7NP0JzA/BFKrt/GhNOrXIA144BxjLHN4ysS0UlUncIZf3
	wkuAydu+fNbFxTHeY5xpL3bjV8GKZKu
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/75
X-Sequence-Number: 36243

Jay Manni wrote:
> 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.
There's a few limits to be concerned about here, some physical, some 
related to your application design.  A few thousand records is possible 
with either the right software design or some hardware assist.  The wall 
where it gets increasingly difficult to keep up is closer to 10K/second, 
presuming your records aren't particularly wide.

Some background:  when you commit a record in PostgreSQL, by default 
that transaction doesn't complete until data has been physically written 
to disk.  If you take a typical 7200 RPM disk, that spins 120 
times/second, meaning that even under the best possible conditions there 
can only be 120 commits per second to physical disk.

However, that physical commit can contain more than one record.  Here 
are the common ways to increase the number of records you can insert per 
second:

1) Batch up inserts.  Turn off any auto-commit behavior in your client, 
insert a bunch of records, issue one COMMIT. 
Typical popular batch sizes are in the 100-1000 records/commit range.  
If individual records aren't very wide, you can easily get a huge 
speedup here.  Hard to estimate how much this will help in your case 
without knowing more about that width and the speed of your underlying 
disks; more on that below.

2) Have multiple clients committing at once.  Typically I see this give 
at most about a 5X speedup, so on a slow disk with single record commits 
you might hit 600/s instead of 120/s if you had 10 clients going at once.

3) Use a RAID controller with a battery-backed cache.  This will hold 
multiple disk commits in its cache and dump them onto disk in larger 
chunks transparently, with only a small risk of corruption if there's an 
extended power outage longer than the battery lasts.  Typically I'll see 
this increase commit rate to the 1000-10,000 commits/second range, again 
depending on underlying disk speed and row size.  This approach really 
reduces the worst-case behavior disks can get into, which is where you 
keep seeking between two spots writing small bits at each one.

4) Turn off synchronous_commit.  This lets you adjust the rate at which 
records get committed into larger chunks without touching your 
application or hardware.  It does introduce the possibility you might 
lose some records if there's a crash in the middle of loading or 
changing things.  Adjusting the commit period here upwards makes this 
case look similar to (1), you're basically committing in larger chunks 
but the app just doesn't know it.

Basically, (2) alone is probably not enough to reach 1,000 per second.  
But (1) or (3) is, as is (4) if you can take the potential data 
integrity issues if there's a crash.  If your batchs get bigger via any 
of these techniques, what should end up happening is that you push the 
bottleneck to somewhere else, like disk write or seek speed.  Which of 
those you'll run into depends on how interleaved these writes are with 
application reads and the total disk bandwidth.

To close, here's a quick example showing the sort of analysis you should 
be doing to better estimate here.  Imagine you're trying to write 10,000 
records/second.  Each record is 100 bytes wide.  That works out to be 
almost 1MB/s of steady disk writes.  In the real world, a single cheap 
disk can't do much better than this if those writes involve heavy 
seeking around the disk.  And that's happens in a database, because at a 
minimum you need to write to both the main database area and the 
write-ahead log.  If your records are 1,000 records wide instead, you 
might hit the upper limit of your disk seeking capability at only 
1,000/second.

Whereas if you have an app that's just writing to a file, you wouldn't 
necessarily expect that to regularly seek elsewhere.  That means it's 
much likely that you'd hit >10MB/s on writes rather than the 1-2MB/s 
worst-case behavior when seeking.  Of course, as you've already noted, 
you end up paying penalties on reporting instead if you do that.  The 
best you can do here is to try and measure your application and 
estimate/simulate larger volume, then see what happens if you apply one 
or more of these techniques.

--
Greg Smith        greg@2ndQuadrant.com        Baltimore, MD



From mmoncure@gmail.com Wed Nov  4 13:59:35 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id AFEBA63278C
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 09:59:35 -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 57312-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 13:59:27 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pw0-f44.google.com (mail-pw0-f44.google.com
	[209.85.160.44])
	by mail.postgresql.org (Postfix) with ESMTP id 6F02263275D
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Nov 2009 09:59:27 -0400 (AST)
Received: by pwj15 with SMTP id 15so157544pwj.3
	for <pgsql-performance@postgresql.org>;
	Wed, 04 Nov 2009 05:59:25 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=0zg3T+hIv3XoSjESDLgEwewErTVtekZQjhi4Q2SnRq4=;
	b=PES1ndFIO5WPxMZJuf7t6W1vT08UO9qHcPQz8CjHHYNB827lBGwbEmUXnsSVeInZZ8
	HboxljsSOqaD4ZtZ2LNNDbHE3MwyMIYv4C0M4Np9KFKtc+ciWE/vWFDyKvLryjLCsyrH
	FySY65XSV9uq0pkxxDI52EoaUuqYYdEc3lMoU=
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=o+HNXcpddcPrFBCNhtJKDs5bCaozSjyLxcFwNoA8WDVwy+RysaFXIli7Oe71uBRw7l
	24scIzKU2wV24xuswJWYxXwTy2A2MS0l0J7A1ZgutUe8dg/frAEVEV3gwnxHIsmZaO3e
	99Pr1fNRqjS6IVtDuqPYXrrryiKHI0tFAEU3c=
MIME-Version: 1.0
Received: by 10.114.69.13 with SMTP id r13mr2084760waa.16.1257343165589; Wed,
	04 Nov 2009 05:59:25 -0800 (PST)
In-Reply-To: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
	<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
Date: Wed, 4 Nov 2009 08:59:25 -0500
Message-ID: <b42b73150911040559w660d1ad2w38a014fbc34e877f@mail.gmail.com>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
From: Merlin Moncure <mmoncure@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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/42
X-Sequence-Number: 36210

n Tue, Nov 3, 2009 at 10: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. 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.

Postgres doing this is going to depend on primarily two things:
*) Your hardware
*) The mechanism you use to insert the data into the database

Postgres can handle multiple 1000 insert/sec but your hardware most
likely can't handle multiple 1000 transaction/sec if fsync is on.  You
definitely want to batch the insert into the database somehow, so that
something accumulates the data (could be a simple file), and flushes
it in to the database.   The 'flush' ideally should use copy but
multiple row insert is ok too.  Try to avoid inserting one row at a
time even if in a transaction.

If you are bulk inserting 1000+ records/sec all day long, make sure
you have provisioned enough storage for this (that's 86M records/day),
and you should immediately start thinking about partitioning and
rotating the log table (if you log to the database, partition/rotate
is basically already baked in anyways).

The effects on other users of the database are really hard to predict
-- it's going to depend on how much resources you have (cpu and
especially disk) to direct towards the loading and how the database is
being used.  I expect it shouldn't be too bad unless your dataase is
already i/o loaded.  The good news is testing this is relatively easy
you can simulate a load test and just run it during typical use and
see how it affects other users.  Standard o/s tools (iostat, top), and
database log with min_duration_statement are going to be a big help
here.   If you start seeing big leaps in iowait corresponding with
unexpectedly lagging queries in your app , you probably should think
about scrapping the idea.

merlin



From greg@2ndquadrant.com Wed Nov  4 14:33:59 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id C42DD633CA2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 10:33:59 -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 61925-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 14:33:51 +0000 (UTC)
X-Greylist: delayed 00:05:04.118893 by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.186])
	by mail.postgresql.org (Postfix) with ESMTP id 5753063278C
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Nov 2009 10:33:49 -0400 (AST)
Received: from [192.168.1.3] (pool-96-244-14-10.bltmmd.fios.verizon.net
	[96.244.14.10])
	by mrelayeu.kundenserver.de (node=mreu1) with ESMTP (Nemesis)
	id 0M0e7e-1MIevz05Ne-00uvSY; Wed, 04 Nov 2009 15:28:38 +0100
Message-ID: <4AF18F92.2060007@2ndquadrant.com>
Date: Wed, 04 Nov 2009 09:28:34 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Jay Manni <JManni@FireEye.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V01U2FsdGVkX1+ba2FTNyt3F8kTCvNwYev5spk36EKJ9r9W9AE
	aVEYY7qHrBh49dLbCLSKf18qDULiq51OoI7rVr8Ex5hlfF3RX9
	w//yG5JrRoTZZV3jg7HNL9+4fU+bO3Z
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/43
X-Sequence-Number: 36211

Jay Manni wrote:

> 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.


There's a few limits to be concerned about here, some physical, some
related to your application design. A few thousand records is possible
with either the right software design or some hardware assist. The wall
where it gets increasingly difficult to keep up is closer to 10K/second,
presuming your records aren't particularly wide.

Some background: when you commit a record in PostgreSQL, by default
that transaction doesn't complete until data has been physically written
to disk. If you take a typical 7200 RPM disk, that spins 120
times/second, meaning that even under the best possible conditions there
can only be 120 commits per second to physical disk.

However, that physical commit can contain more than one record. Here
are the common ways to increase the number of records you can insert per
second:

1) Batch up inserts. Turn off any auto-commit behavior in your client,
insert a bunch of records, issue one COMMIT.
Typical popular batch sizes are in the 100-1000 records/commit range.
If individual records aren't very wide, you can easily get a huge
speedup here. Hard to estimate how much this will help in your case
without knowing more about that width and the speed of your underlying
disks; more on that below.

2) Have multiple clients committing at once. Typically I see this give
at most about a 5X speedup, so on a slow disk with single record commits
you might hit 600/s instead of 120/s if you had 10 clients going at once.

3) Use a RAID controller with a battery-backed cache. This will hold
multiple disk commits in its cache and dump them onto disk in larger
chunks transparently, with only a small risk of corruption if there's an
extended power outage longer than the battery lasts. Typically I'll see
this increase commit rate to the 1000-10,000 commits/second range, again
depending on underlying disk speed and row size. This approach really
reduces the worst-case behavior disks can get into, which is where you
keep seeking between two spots writing small bits at each one.

4) Turn off synchronous_commit. This lets you adjust the rate at which
records get committed into larger chunks without touching your
application or hardware. It does introduce the possibility you might
lose some records if there's a crash in the middle of loading or
changing things. Adjusting the commit period here upwards makes this
case look similar to (1), you're basically committing in larger chunks
but the app just doesn't know it.

Basically, (2) alone is probably not enough to reach 1,000 per second.
But (1) or (3) is, as is (4) if you can take the potential data
integrity issues if there's a crash. If your batchs get bigger via any
of these techniques, what should end up happening is that you push the
bottleneck to somewhere else, like disk write or seek speed. Which of
those you'll run into depends on how interleaved these writes are with
application reads and the total disk bandwidth.

To close, here's a quick example showing the sort of analysis you should
be doing to better estimate here. Imagine you're trying to write 10,000
records/second. Each record is 100 bytes wide. That works out to be
almost 1MB/s of steady disk writes. In the real world, a single cheap
disk can't do much better than this if those writes involve heavy
seeking around the disk. And that's happens in a database, because at a
minimum you need to write to both the main database area and the
write-ahead log. If your records are 1,000 records wide instead, you
might hit the upper limit of your disk seeking capability at only
1,000/second.

Whereas if you have an app that's just writing to a file, you wouldn't
necessarily expect that to regularly seek elsewhere. That means it's
much likely that you'd hit >10MB/s on writes rather than the 1-2MB/s
worst-case behavior when seeking. Of course, as you've already noted,
you end up paying penalties on reporting instead if you do that. The
best you can do here is to try and measure your application and
estimate/simulate larger volume, then see what happens if you apply one
or more of these techniques.

--
Greg Smith greg@2ndQuadrant.com Baltimore, MD



From jeff.janes@gmail.com Wed Nov  4 16:39:58 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id E2F396327F0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 12:39:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 95522-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 16:39:47 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qy0-f186.google.com (mail-qy0-f186.google.com
	[209.85.221.186])
	by mail.postgresql.org (Postfix) with ESMTP id 2D471632F9B
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Nov 2009 12:39:48 -0400 (AST)
Received: by qyk16 with SMTP id 16so3240130qyk.15
	for <pgsql-performance@postgresql.org>;
	Wed, 04 Nov 2009 08:39:47 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=7FAN/MJzzIv3EJb65aoC4M9Mfxo/v2uqoGQ/UkSz3+4=;
	b=G28x1m47+0XitWnrDIgviMauXM7iWRpqj6VFDG2mw8kA3zZP7KdNNq2gpIfLiHmNzF
	b0xKg+2D+N+UdXhlFLHVUVNaS/jm0tknGccGrMRA5ZiiAR6HnwN5Sz3BMrPYq6wvDe0x
	zGo9O9pSvNDha6HVOIr12Q1g7OU47LbD8tWd8=
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=fFenJpakpz2vn2MMDT9w7XeBqtUaa/y+vGCpajHcHKGWMDefjie84cSXRs5Na0joj6
	W5GnUYxyPOh9k5laT9USIN2yYlTRLIS/OtDBmAYyMuE/92Fv9jAYfeHRoHGD1U4YSM0L
	xRUsMEPk/xFoJOrc3N+bUsIR6qQ+c2OcWlTHM=
MIME-Version: 1.0
Received: by 10.239.156.193 with SMTP id n1mr163722hbc.187.1257352786309;
 Wed,
	04 Nov 2009 08:39:46 -0800 (PST)
In-Reply-To: <60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
	<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
Date: Wed, 4 Nov 2009 08:39:46 -0800
Message-ID: <f67928030911040839n75c9f6ean4ef21ac457bb384e@mail.gmail.com>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
From: Jeff Janes <jeff.janes@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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.439 tagged_above=-10 required=5 tests=AWL=-0.155,
	BAYES_00=-2.599, SARE_MILLIONSOF=0.315
X-Spam-Level: 
X-Archive-Number: 200911/44
X-Sequence-Number: 36212

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 =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).

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



From fotographs@gmail.com Wed Nov  4 16:58:16 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id D1F4E633363
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 12:58:16 -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 79704-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 16:58:09 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pw0-f44.google.com (mail-pw0-f44.google.com
	[209.85.160.44])
	by mail.postgresql.org (Postfix) with ESMTP id D1CD8632F9B
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Nov 2009 12:58:08 -0400 (AST)
Received: by pwj15 with SMTP id 15so269206pwj.3
	for <pgsql-performance@postgresql.org>;
	Wed, 04 Nov 2009 08:58:07 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=Ryh/bpnC9xFw5sofG/hsKZWl/vvYxIqhnogzNhzOtks=;
	b=sMTXZr6X92OjyQ3YYhSYYh6Fa2c/H9HQliPokTSc2zow4eKaDl9mk64gAl9WmShE+8
	hB7FhUd6j9WGB48BBId+H1ItFf3hxJ7+cTCB+YwPtZTLEHT9Qb4/cCyktld13qsW6OSC
	0RFDYQe0xgIkv96jwaEwqZJVyzVGyBvmFJ6LY=
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
Received: by 10.140.170.17 with SMTP id s17mr98449rve.208.1257353887500; Wed,
	04 Nov 2009 08:58:07 -0800 (PST)
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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/46
X-Sequence-Number: 36214

> 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.



From craig@postnewspapers.com.au Thu Nov  5 01:14:27 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id D26C0634AC2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 21:14:27 -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 41771-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  5 Nov 2009 01:14:20 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by mail.postgresql.org (Postfix) with ESMTP id CC887635B04
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Nov 2009 21:12:23 -0400 (AST)
Received: from localhost (access [127.0.0.1])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id BF6A91E043F;
	Thu,  5 Nov 2009 09:12:19 +0800 (WST)
X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au
Received: from mail.postnewspapers.com.au ([127.0.0.1])
	by localhost (access.postnewspapers.com.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with LMTP id e29pX0NlWzmB; Thu,  5 Nov 2009 09:12:18 +0800 (WST)
Received: from [10.1.1.5] (124-169-36-119.dyn.iinet.net.au [124.169.36.119])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client CN "Craig Ringer",
	Issuer "POST Certificate Authority" (verified OK))
	by mail.postnewspapers.com.au (Postfix) with ESMTPSA id EB4601E043C;
	Thu,  5 Nov 2009 09:12:17 +0800 (WST)
Message-ID: <4AF22671.303@postnewspapers.com.au>
Date: Thu, 05 Nov 2009 09:12:17 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Merlin Moncure <mmoncure@gmail.com>
CC: Jay Manni <JManni@fireeye.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>	
	<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
	<b42b73150911040559w660d1ad2w38a014fbc34e877f@mail.gmail.com>
In-Reply-To: <b42b73150911040559w660d1ad2w38a014fbc34e877f@mail.gmail.com>
X-Enigmail-Version: 0.95.7
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/54
X-Sequence-Number: 36222

Merlin Moncure wrote:

> Postgres can handle multiple 1000 insert/sec but your hardware most
> likely can't handle multiple 1000 transaction/sec if fsync is on.

commit_delay or async commit should help a lot there.

http://www.postgresql.org/docs/8.3/static/wal-async-commit.html
http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html

Please do *not* turn fsync off unless you want to lose your data.

> If you are bulk inserting 1000+ records/sec all day long, make sure
> you have provisioned enough storage for this (that's 86M records/day),

plus any index storage, room for dead tuples if you ever issue UPDATEs, etc.

--
Craig Ringer



From JManni@FireEye.com Thu Nov  5 08:06:28 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 8A1FB632727
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  5 Nov 2009 04:06:28 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 53009-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  5 Nov 2009 08:06:14 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from fempexch.FireEye.com (mail2.FireEye.com [12.68.100.48])
	by mail.postgresql.org (Postfix) with ESMTP id C0260632931
	for <pgsql-performance@postgresql.org>;
	Thu,  5 Nov 2009 04:01:40 -0400 (AST)
Received: from mail2.FireEye.com ([192.168.0.116]) by fempexch.FireEye.com
	with Microsoft SMTPSVC(6.0.3790.1830);
	Thu, 5 Nov 2009 00:01:38 -0800
Received: from mail2.FireEye.com ([fe80::c1f0:c46:3b66:3f6c]) by
	mail2.FireEye.com ([fe80::c1f0:c46:3b66:3f6c%11]) with mapi;
	Thu, 5 Nov 2009 00:01:38 -0800
From: Jay Manni <JManni@FireEye.com>
To: Craig Ringer <craig@postnewspapers.com.au>, Merlin Moncure
	<mmoncure@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Date: Thu, 5 Nov 2009 00:01:36 -0800
Subject: Re: High Frequency Inserts to Postgres Database vs Writing 	to a File
Thread-Topic: [PERFORM] High Frequency Inserts to Postgres Database vs
	Writing 	to a File
Thread-Index: AcpdtQntgR4PUGh9SOCy7JmPTzBq+gAOPeDQ
Message-ID: <60B0F2124D07B942988329B5B7CA393D01E5B94187@mail2.FireEye.com>
References: <95652FF3B9EF3045AC4AE8FE5AF8671EED65CF6B@mail2.FireEye.com>
	<60B0F2124D07B942988329B5B7CA393D01E5B93FF7@mail2.FireEye.com>
	<b42b73150911040559w660d1ad2w38a014fbc34e877f@mail.gmail.com>
	<4AF22671.303@postnewspapers.com.au>
In-Reply-To: <4AF22671.303@postnewspapers.com.au>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginalArrivalTime: 05 Nov 2009 08:01:38.0890 (UTC)
	FILETIME=[33F6EAA0:01CA5DEE]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/59
X-Sequence-Number: 36227

Thanks to all for the responses. Based on all the recommendations, I am goi=
ng to try a batched commit approach; along with data purging policies so th=
at the data storage does not grow beyond certain thresholds.

- J

-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]=20
Sent: Wednesday, November 04, 2009 5:12 PM
To: Merlin Moncure
Cc: Jay Manni; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] High Frequency Inserts to Postgres Database vs Writi=
ng to a File

Merlin Moncure wrote:

> Postgres can handle multiple 1000 insert/sec but your hardware most
> likely can't handle multiple 1000 transaction/sec if fsync is on.

commit_delay or async commit should help a lot there.

http://www.postgresql.org/docs/8.3/static/wal-async-commit.html
http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html

Please do *not* turn fsync off unless you want to lose your data.

> If you are bulk inserting 1000+ records/sec all day long, make sure
> you have provisioned enough storage for this (that's 86M records/day),

plus any index storage, room for dead tuples if you ever issue UPDATEs, etc=
.

--
Craig Ringer

--=20
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.