Jan wrote:
> I need a database capable of storing at least 12 million records per
> table, mostly used for batch queries. Basically an invoice database.
> Some tables could potentially store 100 million records.
>
It does not sound like your performance requirements are very demanding.
Either database should make short work of that, although be careful of
MySQL's planner, which is not always as good with more complex queries.
Of course I assume here that you're not doing table scans constantly. If
the 100 million rows needs to be read for each query, it will depend on
your I/O speed, not your database software.
> mySQL5 contains many of the features or PostgreSQL, and I doubt that I
> need all these features. Are there any spefic benefits in query
> performance or reliability of going with PostgreSQL?
>
> Secondary need is a database where 200 users will need to perform
> lookups, probably using Windows PC's. Most likely only a handful will
> perform lookups simultanously.
>
If the database server is on windows, consider that PostgreSQL 8.0 is
the first release to support windows, and I might recommend 8.1, which
is in late beta stages. If it's only the users that are on windows, that
doesn't make much difference, the client libraries for either database
are well-established on windows.
Generally speaking, many reports indicate that PostgreSQL performs
better for simultaneous reading and writing by many users. With just a
handful doing read-only, it probably doesn't make much difference.
Honestly, I think either database is capable. Personally, I trust
PostgreSQL more on several fronts:
(1) It does what you think it's doing.
(2) It notices and prevents errors from becoming problems (i.e., it will
not allow bad data to be inserted).
(3) I trust its reliability and backup systems more.
(4) Not nearly as many traps/gotchas.
Regards,
Jeff Davis