On Mon, 2005-10-24 at 15:37, 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.
>
> 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 you are handling invoices, I could not recommend MySQL, as it is too
free and easy with your data to trust with them. By design, the default
installation will let you do things like: (This is with MySQL 5.0.12 on
my workstation, by the way...)
mysql> create table test (i1 int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test values (123913284723498723423);
Query OK, 1 row affected, 2 warnings (0.07 sec)
mysql> select * from test;
+------------+
| i1 |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)
And other fun things.
If you're handling money with your database, you should choose anything
EXCEPT mysql. It's a good storage db for content management, and
problem ticket tracking, and even bug tracking, but when it comes to
getting the math right, it's still got a ways to go.
Plus, as queries get more complex, it gets slower and slower.
I would recommend firebird or PostgreSQL, as better alternatives.
12 million rows, by the way, is nothing for either PostgreSQL or MySQL,
if they're properly set up on good, fast hardware.
Spend some money on a RAID controller with battery backed cache, lost of
memory for your server, and a good backup device, and you'll find almost
any halfway decent db engine can handle the load.
But PostgreSQL won't mangle your data to make it fit without even a
notice, like MySQL will.