Re: PostgreSQL, GnuCash

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?

Kaarel

Kaarel wrote:
> Would PostgreSQL be a good enough choise for GnuCash (or Quickbooks
> or the likes) type of program?

I believe GnuCash runs on PostgreSQL, so yes.

> What could be the potential drawbacks of using PostgreSQL (perhaps
> its big size)?

I don't think so.

> What would be a better database for that kind of job?

None. :-)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Quoth kaarel(at)future(dot)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

Oops! kaarel(at)future(dot)ee (Kaarel) was seen spray-painting on a wall:
> What could be the potential drawbacks of using PostgreSQL (perhaps its
> big size)?

"Big size" seems an unlikely thing to actually be true.

Contrary to popular misconceptions, MySQL is certainly _not_ smaller
than PostgreSQL. The source tarball for the "production release" of
the former is 13.2MB in size, whereas PostgreSQL 7.4.3 weighs in,
lately, at 12.1MB.

Measuring it as binary installs:

- Installing PostgreSQL on Debian requires adding a 9.8MB package.
- Installing MySQL on Debian requires adding several packages adding
up to (+ 8.7 0.2 1.0 0.6), or roughly 10.3MB.
- Installing FireBird2 requires (+ 2.2 0.7 2.4 1.8) or about 7.1MB.

(This according to my firewall box that has no databases running on
it...)

Seeing as how there's quite a bit more functionality in PostgreSQL,
you've got to wonder why MySQL is so bloated...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www.ntlug.org/~cbbrowne/spreadsheets.html
"Everyone likes flattery, and when you come to Royalty, you should lay
it on with a thick trowel." -- Benjamin Disraeli on Queen Victoria

Christopher Browne <cbbrowne(at)acm(dot)org> wrote in message news:<m3pt6ams2w(dot)fsf(at)wolfe(dot)cbbrowne(dot)com>...
>
> -> 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.
>

This is very true. For a financial applications in SQLite you would be
well advised to store all quantities as an integer number of cents
and shift the decimal point in the application itself. SQLite version 3.0
uses a 64-bit integer so overflow shouldn't be a problem.

On Aug 1, 2004, at 6:26 PM, Christopher Browne wrote:

> Measuring it as binary installs:
>
> - Installing PostgreSQL on Debian requires adding a 9.8MB package.
> - Installing MySQL on Debian requires adding several packages adding
> up to (+ 8.7 0.2 1.0 0.6), or roughly 10.3MB.
> - Installing FireBird2 requires (+ 2.2 0.7 2.4 1.8) or about 7.1MB.
>

I compiled PostgreSQL on Mac OS X with optimization -Os (for size) and
the resulting installation was less than 2 MB. I am looking to embed it
in applications to reduce administrative effort, so size matters in my
case.

At 08:01 AM 8/2/2004 -0400, Aaron Burghardt wrote:
>I compiled PostgreSQL on Mac OS X with optimization -Os (for size) and the
>resulting installation was less than 2 MB. I am looking to embed it in
>applications to reduce administrative effort, so size matters in my case.

2MB? That's smaller than some CPUs 2nd level caches. Hmmm. Wonder what's
the perfomance of -Os like compared to the others.

aburgh(at)mac(dot)com (Aaron Burghardt) wrote in message news:<A5CEF61A-E47B-11D8-A34D-000393B8BDAA(at)mac(dot)com>...
> On Aug 1, 2004, at 6:26 PM, Christopher Browne wrote:
>
> > Measuring it as binary installs:
> >
> > - Installing PostgreSQL on Debian requires adding a 9.8MB package.
> > - Installing MySQL on Debian requires adding several packages adding
> > up to (+ 8.7 0.2 1.0 0.6), or roughly 10.3MB.
> > - Installing FireBird2 requires (+ 2.2 0.7 2.4 1.8) or about 7.1MB.
> >
>
> I compiled PostgreSQL on Mac OS X with optimization -Os (for size) and
> the resulting installation was less than 2 MB. I am looking to embed it
> in applications to reduce administrative effort, so size matters in my
> case.
>

If size matters, SQLite weighs in at less than 250KB when compiled for
size.

On Mon, 2004-08-02 at 08:01, Aaron Burghardt wrote:
>
> On Aug 1, 2004, at 6:26 PM, Christopher Browne wrote:
>
> > Measuring it as binary installs:
> >
> > - Installing PostgreSQL on Debian requires adding a 9.8MB package.
> > - Installing MySQL on Debian requires adding several packages adding
> > up to (+ 8.7 0.2 1.0 0.6), or roughly 10.3MB.
> > - Installing FireBird2 requires (+ 2.2 0.7 2.4 1.8) or about 7.1MB.
> >
>
> I compiled PostgreSQL on Mac OS X with optimization -Os (for size) and
> the resulting installation was less than 2 MB. I am looking to embed it
> in applications to reduce administrative effort, so size matters in my
> case.

That sounds small, but you don't seem to be accounting for size used by
initdb and WAL to actually get a working system...

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL