X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 83306D83C7 for ; Thu, 1 Sep 2005 13:38:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64180-01 for ; Thu, 1 Sep 2005 16:38:14 +0000 (GMT) Received: from mail.mi8.com (d01gw04.mi8.com [63.240.6.44]) by svr1.postgresql.org (Postfix) with ESMTP id 9FD4CD7D01 for ; Thu, 1 Sep 2005 13:38:12 -0300 (ADT) Received: from 172.16.1.118 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D4)); Thu, 01 Sep 2005 12:37:56 -0400 X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.174]) by d01smtp03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 1 Sep 2005 12:37:55 -0400 Received: from 67.103.45.218 ([67.103.45.218]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.104]) with Microsoft Exchange Server HTTP-DAV ; Thu, 1 Sep 2005 12:37:55 -0500 User-Agent: Microsoft-Entourage/11.1.0.040913 Date: Thu, 01 Sep 2005 09:37:53 -0700 Subject: Re: Need for speed 3 From: "Luke Lonergan" To: "Ulrich Wisser" , pgsql-performance@postgresql.org Cc: "Nicholas E. Wakefield" , "Barry Klawans" , "Daria Hutchinson" Message-ID: In-Reply-To: <43170151.90408@relevanttraffic.se> MIME-Version: 1.0 X-OriginalArrivalTime: 01 Sep 2005 16:37:55.0800 (UTC) FILETIME=[8145C580:01C5AF13] X-WSS-ID: 6F09F1EE3P01057575-02-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.575 required=5 tests=[AWL=-0.006, FORGED_RCVD_HELO=0.05, RCVD_NUMERIC_HELO=1.531] X-Spam-Level: * X-Archive-Number: 200509/8 X-Sequence-Number: 14316 Ulrich, On 9/1/05 6:25 AM, "Ulrich Wisser" wrote: > My application basically imports Apache log files into a Postgres > database. Every row in the log file gets imported in one of three (raw > data) tables. My columns are exactly as in the log file. The import is > run approx. every five minutes. We import about two million rows a month. Bizgres Clickstream does this job using an ETL (extract transform and load) process to transform the weblogs into an optimized schema for reporting. > After every import the data from the current day is deleted from the > reporting table and recalculated from the raw data table. This is something the optimized ETL in Bizgres Clickstream also does well. > What do you think of this approach? Are there better ways to do it? Is > there some literature you recommend reading? I recommend the Bizgres Clickstream docs, you can get it from Bizgres CVS, and there will shortly be a live html link on the website. Bizgres is free - it also improves COPY performance by almost 2x, among other enhancements. - Luke