Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1FF2C9F9F08 for ; Sun, 10 Dec 2006 19:52:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 06785-09 for ; Sun, 10 Dec 2006 19:52:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from ipmail02.adl2.internode.on.net (ipmail02.adl2.internode.on.net [203.16.214.141]) by postgresql.org (Postfix) with ESMTP id 60C2C9F9C35 for ; Sun, 10 Dec 2006 19:52:15 -0400 (AST) Received: from ppp247-71.static.internode.on.net (HELO [192.168.8.200]) ([203.122.247.71]) by ipmail02.adl2.internode.on.net with ESMTP; 11 Dec 2006 10:22:09 +1030 X-IronPort-AV: i="4.09,518,1157293800"; d="scan'208"; a="58308979:sNHT6974105215" Message-ID: <457C9DA8.1000403@007Marketing.com> Date: Mon, 11 Dec 2006 10:22:08 +1030 From: Shane Ambler User-Agent: Thunderbird 1.5.0.8 (Macintosh/20061025) MIME-Version: 1.0 To: Daniel van Ham Colchete CC: pgsql-performance@postgresql.org Subject: Re: New to PostgreSQL, performance considerations References: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com> In-Reply-To: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.989 tagged_above=0 required=5 tests=AWL, BAYES_40, BIZ_TLD, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200612/191 X-Sequence-Number: 22247 Daniel van Ham Colchete wrote: > although I've worked with databases for more than 7 years now, I'm > petty new to PostgreSQL. Same here. > I need a db that can handle something like 500 operations/sec > continuously. It's something like 250 writes/sec and 250 reads/sec. My > databases uses indexes. Taken from an email to the admin list about a week ago - Stats about the system: Postgres 8.1.4 db size: 200+ GB Inheritance is used extremely heavily, so in figuring out what could cause a create to hang, it may be of interest to know that there are: 101,745 tables 314,821 indexes 1,569 views The last averages taken on the number of writes per hour on this database: ~3 million (this stat is a few weeks old) Machine info: OS: Solaris 10 Sunfire X4100 XL 2x AMD Opteron Model 275 dual core procs 8GB of ram > Each table would have to handle 5 million rows/day. So I'm thinking > about creating different tables (clusters?) to different days to make > queries return faster. Am I right or there is no problem in having a > 150 million (one month) rows on a table? Sounds to me that a month might be on the large size for real fast response times - I would think of seperating weekly rather than daily. Start with http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html then the next chapter explains using that to partition data into different tables dependant on specified criteria. You may be interested in tsearch2 which is in the contrib dir and adds full text indexing. -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz