X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 39E7A9FA1A1 for ; Mon, 18 Sep 2006 22:11:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 89765-08 for ; Tue, 19 Sep 2006 01:11:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.Mi8.com (d01gw01.mi8.com [63.240.6.47]) by postgresql.org (Postfix) with ESMTP id DBB769FA25B for ; Mon, 18 Sep 2006 22:11:15 -0300 (ADT) Received: from 172.16.1.25 by mail.Mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D1)); Mon, 18 Sep 2006 21:11:05 -0400 X-Server-Uuid: 241911D6-425B-44B9-A073-E3FE0F8FC774 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by D01HOST03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 18 Sep 2006 21:10:16 -0400 Received: from 63.80.24.130 ([63.80.24.130]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.118]) with Microsoft Exchange Server HTTP-DAV ; Tue, 19 Sep 2006 01:10:16 +0000 User-Agent: Microsoft-Entourage/11.2.5.060620 Date: Mon, 18 Sep 2006 18:10:13 -0700 Subject: Re: Large tables (was: RAID 0 not as fast as expected) From: "Luke Lonergan" To: "Bucky Jordan" , "Spiegelberg, Greg" , "Joshua Drake" cc: "Craig A. James" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: Large tables (was: RAID 0 not as fast as expected) Thread-Index: AcbYe+tsOENR02gVRlOrAK5w1z9kiQASCCsQAAZdXxMABS1nMAA+A5p1AFCcHOAAFul1Dw== In-Reply-To: <78ED28FACE63744386D68D8A9D1CF5D42099C7@MAIL.corp.lumeta.com> MIME-Version: 1.0 X-OriginalArrivalTime: 19 Sep 2006 01:10:16.0848 (UTC) FILETIME=[5E29F100:01C6DB88] X-WSS-ID: 69119C2232S6753424-03-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200609/240 X-Sequence-Number: 20926 Bucky, On 9/18/06 7:37 AM, "Bucky Jordan" wrote: > My question is at what point do I have to get fancy with those big > tables? From your presentation, it looks like PG can handle 1.2 billion > records or so as long as you write intelligent queries. (And normal PG > should be able to handle that, correct?) PG has limitations that will confront you at sizes beyond about a couple hundred GB of table size, as will Oracle and others. You should be careful to implement very good disk hardware and leverage Postgres 8.1 partitioning and indexes intelligently as you go beyond 100GB per instance. Also be sure to set the random_page_cost parameter in postgresql.conf to 100 or even higher when you use indexes, as the actual seek rate for random access ranges between 50 and 300 for modern disk hardware. If this parameter is left at the default of 4, indexes will often be used inappropriately. > Also, does anyone know if/when any of the MPP stuff will be ported to > Postgres, or is the plan to keep that separate? The plan is to keep that separate for now, though we're contributing technology like partitioning, faster sorting, bitmap index, adaptive nested loop, and hybrid hash aggregation to make big databases work better in Postgres. - Luke