Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 8A1FB632727 for ; 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 ; 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 ; 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 To: Craig Ringer , Merlin Moncure CC: "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> <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.