X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id AB297CB4E7 for ; Fri, 11 Jul 2003 03:14:12 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 64616-01 for ; Fri, 11 Jul 2003 00:14:01 -0300 (ADT) Received: from ethereal-realms.org (thebe.ethereal-realms.org [142.179.222.205]) by svr1.postgresql.org (Postfix) with ESMTP id 3BD45CF408 for ; Thu, 10 Jul 2003 21:23:07 -0300 (ADT) Received: from ethereal-realms.org (ip14.ethereal-realms.COM [192.168.1.14]) by ethereal-realms.org (8.12.6/8.12.6) with ESMTP id h6B0NBUc010670; Thu, 10 Jul 2003 18:23:13 -0600 (MDT) Message-ID: <3F0E036F.7080108@ethereal-realms.org> Date: Thu, 10 Jul 2003 18:23:11 -0600 From: Martin Foster Organization: Ethereal Realms User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Shridhar Daithankar" , PostgreSQL Performance Subject: Re: [NOVICE] Extreme high load averages References: <3F0D5550.21678.4E122CF@localhost> <3F0D5EEC.29862.506A9AE@localhost> In-Reply-To: <3F0D5EEC.29862.506A9AE@localhost> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/169 X-Sequence-Number: 2462 Shridhar Daithankar wrote: > On 10 Jul 2003 at 0:43, Martin Foster wrote: > >>As for creating a new table, that in itself is a nice idea. But it >>would cause issues for people currently in the realm. Their posts >>would essentially dissapear from site and cause more confusion then its >>worth. > > > No they won't. Say you have a base table and your current post table is child > of that. You can query on base table and get rows from child table. That way > all the data would always be there. > > While inserting posts, you would insert in child table. While qeurying you > would query on base table. That way things will be optimal. > > >>Inheritance would work, but the database would essentially just grow and >>grow and grow right? > > > Right. But there are two advantages. > > 1. It will always contain valid posts. No dead tuples. > 2. You can work in chuncks of data. Each child table can be dealt with > separately without affecting other child tables, whereas in case of a single > large table, entire site is affected.. > > Deleting 100K posts from 101K rows table is vastly different than deleting 10K > posts from 2M rows table. Later one would unnecessary starve the table with > dead tuples and IO whereas in former case you can do create table as select > from and drop the original.. > > HTH > > Bye > Shridhar While your idea is sound, I can easily report that this is as bad or even worse then removing thousands of rows at any given point in time. Trying to remove a child table, will pretty much guarantee a complete and total deadlock in the database. While it's waiting for a lock, it's locking out authenticating users but allows existing connections to go through. And considering this goes on for tens of minutes and people keep piling on requests to the server, this quickly disintegrates into one hell of a mess. I.E. requires a cold boot to get this thing up again. Perhaps it is more efficient, but until I can remove archived tables entirely, I do not exactly see a compelling reason to use inheritance. Also, some questions are not answered from documentation. Such as are indexes carried forth, if you call the parent table, or do you have to re-create them all manually. And what happens to the primary key constraints that no longer show up. Thanks for the tip though. Just wish it worked better then it does. Martin Foster Creator/Designer Ethereal Realms martin@ethereal-realms.org