X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 448A45282A for ; Mon, 13 Jun 2005 11:21:31 -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 32211-05 for ; Mon, 13 Jun 2005 14:21:22 +0000 (GMT) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.91]) by svr1.postgresql.org (Postfix) with ESMTP id D966D52819 for ; Mon, 13 Jun 2005 11:21:21 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 13 Jun 2005 09:21:25 -0500 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.2 Date: Mon, 13 Jun 2005 09:21:06 -0500 From: "Kevin Grittner" To: Cc: , Subject: Re: Help with rewriting query Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.052 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/288 X-Sequence-Number: 12925 I've done a lot of work with a bookkeeping system where we have such redundancy built in. The auditors, however, need to be able to generate lists of the financial transaction detail to support balances. These reports are among the most demanding in the system. I shudder to think how unacceptable performance would be without the redundancy. Also, due to multiple media failures, and backup process problems (on another database product), a large database was badly mangled. The redundancies allowed us to reconstruct much data, and to at least identify what was missing for the rest. There is, of course, some cost for the redundancy. Up front, someone needs to code routines to maintain it. It needs to be checked against the underlying detail periodically, to prevent "drift". And there is a cost, usually pretty minimal, for the software to do the work. I strongly recommend that some form of trigger (either native to the database or, if portability is an issue, within a middle tier framework) do the work of maintaining the redundant data. If you rely on application code to maintain it, you can expect that sooner or later it will get missed. >>> Tobias Brox 06/11/05 4:59 AM >>> [ Reminds me about the way the precursor software of our product was made, whenever it was needed to check the balance of a customer, it was needed to scan the whole transaction table and sum up all transactions. This operation eventually took 3-4 seconds before we released the new software, and the customers balance was supposed to show up at several web pages :-) By now we have the updated balance both in the customer table and as "post_balance" in the transaction table. Sometimes redundancy is good. Much easier to solve inconsistency problems as well :-)