Re: [GENERAL] View vs Constantly Updated Table
How often are you using the bank balance value? I have no data on this as of yet, but it obviously needs to be correct for when the user looks at it. The opposite argument, is how long does the computation take? The computation is simple, however the amount of data that goes into it grows as there are more and more transactions added. This is why I was thinking of storing the balance in a table sort of as a cache to avoid spinning through all pas transaction to get the current balance. and how quickly do you need it? as fast as possible of course :) Also, if you would be taking any action with the value returned by the balance calculation, remember to lock any tables necessary to ensure the balance doesn't change between the time you compute it and the time you act on it. I'll keep this in mind. never done a lot of manual locking before, but I can see where this is going to be needed. Its possible to take row locks and not complete table locks correct? (manual chapter 13.3) I'm thinking if I use a single table to hold all users cached balances then I would not want to lock the entire table just to retrieve and act on one users balance. thanks for the input On Dec 15, 2008, at 11:27 AM, Adam Rich wrote: Truncated... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] View vs Constantly Updated Table
It is generally better to save the balance. The general rule in accounting systems design is what can be printed, should be explicitly on disk. for an invoice: value before tax, tax percentage, value after tax, total before tax, total after tax, etc, should all be saved explicitly. An account should have a balance. Every operation should have balance before operation, value, balance after operation. You should never update an operation. This way when business rules change all previous documents are stored in consistent state. Greetings Marcin Mańk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] View vs Constantly Updated Table
if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is updated for each transaction? so in the end select balance from view or select balance from table ? What are the pros cons ? Thanks Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat inline: kmail.png -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] View vs Constantly Updated Table
On 15/12/2008 16:14, Ketema Harris wrote: if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is updated for each transaction? so in the end select balance from view or select balance from table ? It would depend on how much calculation is involved in calculating the balance. If you had to query tens of millions of rows to get the balance, I'd imagine you'd do better to have a trigger updating the balance every time a row is inserted into the account ledger table. If there's only a small number of rows to be queried, then it's easier and probably more robust to do the calculation in a view or a function. I've done this with ledger containing about 500 rows with no noticeable delay (on my laptop). Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] View vs Constantly Updated Table
if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is updated for each transaction? so in the end select balance from view or select balance from table ? What are the pros cons ? How often are you using the bank balance value? If you're updating it for every transaction, you would be doing a lot of work computing values that may rarely if ever get used. That's an argument for the view route, since the computation only happens when necessary. The opposite argument, is how long does the computation take, and how quickly do you need it? The pre-computed value would obviously be much faster than waiting for it to be computed on the fly. Other things to keep in mind... you might want to make the balance calculation a separate function rather than building it into the table, unless it's used on *every* query. Also, if you would be taking any action with the value returned by the balance calculation, remember to lock any tables necessary to ensure the balance doesn't change between the time you compute it and the time you act on it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] View vs Constantly Updated Table
On Dec 15, 11:25 am, r...@iol.ie (Raymond O'Donnell) wrote: On 15/12/2008 16:14, Ketema Harris wrote: if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is updated for each transaction? so in the end select balance from view or select balance from table ? It would depend on how much calculation is involved in calculating the balance. If you had to query tens of millions of rows to get the balance, I'd imagine you'd do better to have a trigger updating the balance every time a row is inserted into the account ledger table. If there's only a small number of rows to be queried, then it's easier and probably more robust to do the calculation in a view or a function. I've done this with ledger containing about 500 rows with no noticeable delay (on my laptop). Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals:http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general OK. I will go with the updated table for now, as I am expecting thousands of records generated per day. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
