Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E2F396327F0 for ; 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 ; 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 ; Wed, 4 Nov 2009 12:39:48 -0400 (AST) Received: by qyk16 with SMTP id 16so3240130qyk.15 for ; 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: Subject: Re: High Frequency Inserts to Postgres Database vs Writing to a File From: Jeff Janes To: Jay Manni Cc: "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 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