Thread: PostgreSQL vs mySQL, any performance difference for large queries?

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.

I would ask you to ask the reverse question, why would you use MySQL when it still doesn't contain all the features of postgresql, has a bad query optimizer, a poor track record on scalability and will silenty truncate/accept invalid data, invalidating ACID, not only that you have to pay for it.

Why would you use MySQL?

Alex

Alex Turner wrote:
> I would ask you to ask the reverse question, why would you use MySQL when it
> still doesn't contain all the features of postgresql, has a bad query
> optimizer, a poor track record on scalability and will silenty
> truncate/accept invalid data, invalidating ACID, not only that you have to
> pay for it.
>

I didn't see anything in his question indicating that he'd need a
commercial license. It sounded to me like he could freely use either
database, and the question is, which one?

Regards,
    Jeff Davis

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

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.

On Mon, 2005-10-24 at 17:19, Scott Marlowe wrote:

> But PostgreSQL won't mangle your data to make it fit without even a
> notice, like MySQL will.

Note, in all fairness, MySQL 5.0.12 now does throw a warning when
mangling my data.  Why the client doesn't display it is beyond me.  Why
it's not an error is also beyond me.  But it does throw a warning, so I
need to amend my last statement up there.

For what it may be worth, executing the same commands into MySQL
5.0.15-nt-max (Win XP Pro) the following it received:

mysql> create table test (i1 int);
Query OK, 0 rows affected (0.41 sec)

mysql> insert into test values (123913284723498723423);
ERROR 1264 (22003): Out of range value adjusted for column (i1) at row 1

mysql> select * from test;
Empty set (0.03 sec)


Finally an improvement!

============================================================
-----Original Message-----
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.

Cool.  Does it still error out after issueing:

set sql_mode='MYSQL323';

???

Just wondering if bounds checking is still optional but is now op-out
instead of opt-in, or if it's something that you can no longer turn off.

The whole idea of correct behaviour being an option is pretty bad, but
hopefully at least the defaults are for proper behaviour, or are heading
that way.

On Tue, 2005-10-25 at 13:52, Wes Williams wrote:
> For what it may be worth, executing the same commands into MySQL
> 5.0.15-nt-max (Win XP Pro) the following it received:
>
> mysql> create table test (i1 int);
> Query OK, 0 rows affected (0.41 sec)
>
> mysql> insert into test values (123913284723498723423);
> ERROR 1264 (22003): Out of range value adjusted for column (i1) at row 1
>
> mysql> select * from test;
> Empty set (0.03 sec)
>
>
> Finally an improvement!
>
> ============================================================
> -----Original Message-----
> 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.
>

set sql_mode='MYSQL323';
Query OK, 0 rows affected (0.00 sec)

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Scott Marlowe
Sent: Tuesday, October 25, 2005 3:24 PM
To: Wes Williams
Cc: 'Jan'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL vs mySQL, any performance difference
for


Cool.  Does it still error out after issueing:

set sql_mode='MYSQL323';

???

Just wondering if bounds checking is still optional but is now op-out
instead of opt-in, or if it's something that you can no longer turn off.

The whole idea of correct behaviour being an option is pretty bad, but
hopefully at least the defaults are for proper behaviour, or are heading
that way.

On Tue, 2005-10-25 at 13:52, Wes Williams wrote:
> For what it may be worth, executing the same commands into MySQL
> 5.0.15-nt-max (Win XP Pro) the following it received:
>
> mysql> create table test (i1 int);
> Query OK, 0 rows affected (0.41 sec)
>
> mysql> insert into test values (123913284723498723423);
> ERROR 1264 (22003): Out of range value adjusted for column (i1) at row 1
>
> mysql> select * from test;
> Empty set (0.03 sec)
>
>
> Finally an improvement!
>
> ============================================================
> -----Original Message-----
> 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.
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Tue, Oct 25, 2005 at 02:24:11PM -0500, Scott Marlowe wrote:
> Cool.  Does it still error out after issueing:
>
> set sql_mode='MYSQL323';
>
> ???
>
> Just wondering if bounds checking is still optional but is now op-out
> instead of opt-in, or if it's something that you can no longer turn off.

Actually it still appears to be opt-in in stock builds.  I have a
5.0.15 server built from source and sql_mode is empty by default,
so I still get the bogus behavior.

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.15-log |
+------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create table test (i1 int);
Query OK, 0 rows affected (0.92 sec)

mysql> insert into test values (123913284723498723423);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from test;
+------------+
| i1         |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

mysql> set sql_mode='MYSQL323';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (123913284723498723423);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from test;
+------------+
| i1         |
+------------+
| 2147483647 |
| 2147483647 |
+------------+
2 rows in set (0.01 sec)

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (123913284723498723423);
ERROR 1264 (22003): Out of range value adjusted for column 'i1' at row 1

--
Michael Fuhr