Re: PostgreSQL, GnuCash

Quoth kaarel@future.ee (Kaarel):
> Would PostgreSQL be a good enough choise for GnuCash (or Quickbooks
> or the likes) type of program?  What could be the potential
> drawbacks of using PostgreSQL (perhaps its big size)?  What would be
> a better database for that kind of job?

The main plausible drawbacks to using PostgreSQL are that:

 a) It introduces some "system administration burdens," if you're
    not careful.

 b) It runs as a separate server process, which has some performance
    costs in comparison with "embedded" database systems like
    Berkeley-DB or SQLite.

If PostgreSQL seems to be somehow "too expensive," then you have
essentially two choices: Berkeley-DB and SQLite.

MySQL is _not_ smaller, and does _not_ introduce any less in the way
of "sysadmin burdens," so it doesn't provide meaningfully better
answers for those issues.

What PostgreSQL "buys you" that none of the other three database
systems mentioned is the capability to have the database strongly
enforce Way Lots of aspects of data integrity.  Comparing...

 -> If you try to store an invalid date, PostgreSQL will reject it.
 -> In contrast, the other 3 DBs do no meaningful validation of
    input.

For a financial application, you want a fixed-point decimal numeric
type so that you can be confident that it is calculating values
correctly.

 -> PostgreSQL provides NUMERIC(SIZE,DECIMALS) that deals with this
    nicely, and which never imposes floating point round-off errors on
    you.

 -> Berkeley-DB has no way to express data types; data is merely
    a payload, so you'll implement whatever type you choose,
    and if you're working in C or C++, that probably won't be a
    BCD-like numeric type.

 -> SQLite does not impose any data type constraints, and stores
    non-integer values as floating point values, which will not
    calculate correct values for financial transactions.

sqlite> create table accounts (name text, balance numeric(10,2));
sqlite> insert into accounts values ('chris', 27.50);
sqlite> insert into accounts values ('dave', '28.751');
sqlite> insert into accounts values ('brad', '29');
sqlite> insert into accounts values ('doug', '29.99999');
sqlite> select * from accounts;
chris|27.50
dave|28.751
brad|29
doug|29.99999
sqlite> select sum(balance) from accounts;
115.25099

 -> MySQL does appear to have a "numeric" type that can store
    rows correctly, but it then breaks if you ask it to do aggregates,
    as it collects them into a floating point variable.  Oops.

I'm quite prepared to trust PostgreSQL with financial numbers; none of
the other options are at all acceptable for that purpose.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www.ntlug.org/~cbbrowne/postgresql.html
"If you spend more  on coffee than  on  IT security,  then you  will be
hacked." -- Richard Clarke