From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: tobias@nordicbet.com
Cc: junaili@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Help with rewriting query
Date: Mon, 13 Jun 2005 09:21:06 -0500
Message-ID: <s2ad5015.018@gwmta.wicourts.gov> (raw)
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 <tobias@nordicbet.com> 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 :-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, tobias@nordicbet.com, junaili@gmail.com
Subject: Re: Help with rewriting query
In-Reply-To: <s2ad5015.018@gwmta.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox