PostgreSQL or MySQL ?

#11

On 30 Apr, 14:31, jag <indieh... (AT) gmail (DOT) com> wrote:

Quote:

On Apr 28, 8:08 am, Lennart <erik.lennart.jons... (AT) gmail (DOT) com> wrote:

jag wrote:
Hiya,

I'm planning to build an enterprise-level php web app which will be
used to manage a large amount of users and which will get a lot of
hits.

so, which database setup should i use?

got 2 choices,

PostgreSQL or MySQL

have used MySQL before but heard good stuff about PostgreSQL.

I'm under the impression that MySQL have been improved in v5 (not sure
though). Earlier version was a nightmare, no views, no subselects, no
referential integrity, operators with a completely different meaning
than in other dbms, ...

PostgreSQL was (is ?) much more standard compliant. If you ever decide
to change dbms, it will be easier with postgres (still a lot of work though)

Here are two references:

http://sql-info.de/postgresql/postgr...://sql-info.de...

Finally, the major players in the field (DB2, Oracle, MsSQL) all have
free to use versions (under som restrictions) these days. I'm only
familiar with DB2:

http://www-306.ibm.com/software/data/db2/express/

Eventhough you clearly stated mysql/postgres as your alternatives, it
might be worthwhile to have at look at these 3

/Lennart

thanks, i'm pretty familiar with MSSql and Oracle, it's just that for
this project the inital budget for buying software is $0, once i've
got a workable solution i can start getting money to move hardware.

http://www-306.ibm.com/software/data/db2/express/

dosent cost you anything. I have no relation with IBM and not trying
to sell you anything. Just pointing out that there is other free of
charge dbms out there.

/Lennart

[...]

#12

On Apr 30, 12:10 pm, David Segall <d... (AT) address (DOT) invalid> wrote:

Quote:

Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

[]
For development
and test purposes you can download the full Enterprise version of
Oracle
http://www.oracle.com/technology/software/products/database/oracle10g...>.
They will even send you a CD and I received an unsolicited phone call
from tech support asking if I needed any help. I don't use an Oracle
database but I find my free membership of OTN
http://www.oracle.com/technology/index.html> valuable because of the
wealth of technical information they provide.

Yes OTN is a great resource. And you are right about Express edition.
I was just grabbed the first license quote I could find.

Ed

#13

In article <46538721.7090404 (AT) superlativ (DOT) dk>, =?ISO-8859-1?Q?Nis_J=F8rgensen?= <nis (AT) superlativ (DOT) dk> wrote:

Quote:

jag skrev:
Hiya,

I'm planning to build an enterprise-level php web app which will be
used to manage a large amount of users and which will get a lot of
hits.

so, which database setup should i use?

got 2 choices,

PostgreSQL or MySQL

have used MySQL before but heard good stuff about PostgreSQL.

I have used both. Love Postgres. Hate MySQL.

People may tell you that Mysql has better performance. I believe a lot
of this comes from people doing single-table selects or simple joins
from ISAM tables. I have often been writing queries in Postgres that I
could never have done in MySQL - so when I was using MySQL, I would have
to code them procedurally on the webserver.

The documentation for Postgres is excellent. They have separate copies
online for the different versions, and clearly indicate the changes and
any deviations from the SQL standards.

The documentation for MySQL covers all versions at once. This means you
cannot just look at the syntax diagrams and assume that they apply to
the version you are running.

Postgres supports transactions, subselects, foreign keys, constraints,
updatable views (via RULES or TRIGGERS) and stored procedures/functions
in several languages - all in the standard configuration.

MySQL ... may support some of these out of the box, and most of them
with a little tweaking. Often they are implemented with gotcha's - "you
can't use a subselect there", "foreign keys only work with INNODB
tables" etc.

Postgres gives you meaningful error messages most of the time. MySQL
mostly just says "You have an error in your SQL syntax;"

And, IMNSHO, if you don't have time/money for a trained DBA - Postgres
beats Oracle as well, when it comes to maintenance AND performance.
Simply because it works out of the box, and the documentation is easy to
read and follow.

Enough evangelism, have a nice day

Nis

I agree with Nis. Both will work. I prefer Postgres for the same reasons as

Nis. One thing he didn't mention is that Postgres is much closer to the SQL
standard. The MySQL license is said to be more restrictive, but I have not
looked at it personally.

Eric

#14

On 23.05.2007 02:13 Nis Jørgensen wrote:

Quote:

I have used both. Love Postgres. Hate MySQL.

People may tell you that Mysql has better performance. I believe a lot
of this comes from people doing single-table selects or simple joins
from ISAM tables. I have often been writing queries in Postgres that I
could never have done in MySQL - so when I was using MySQL, I would have
to code them procedurally on the webserver.

The documentation for Postgres is excellent. They have separate copies
online for the different versions, and clearly indicate the changes and
any deviations from the SQL standards.

The documentation for MySQL covers all versions at once. This means you
cannot just look at the syntax diagrams and assume that they apply to
the version you are running.

Postgres supports transactions, subselects, foreign keys, constraints,
updatable views (via RULES or TRIGGERS) and stored procedures/functions
in several languages - all in the standard configuration.

MySQL ... may support some of these out of the box, and most of them
with a little tweaking. Often they are implemented with gotcha's - "you
can't use a subselect there", "foreign keys only work with INNODB
tables" etc.

Postgres gives you meaningful error messages most of the time. MySQL
mostly just says "You have an error in your SQL syntax;"

And, IMNSHO, if you don't have time/money for a trained DBA - Postgres
beats Oracle as well, when it comes to maintenance AND performance.
Simply because it works out of the box, and the documentation is easy to
read and follow.

I completely agree with the above.

Thomas

#15

On Apr 27, 1:16 pm, jag <indieh... (AT) gmail (DOT) com> wrote:

Quote:

Hiya,

I'm planning to build an enterprise-level php web app which will be
used to manage a large amount of users and which will get a lot of
hits.

so, which database setup should i use?

got 2 choices,
...

Actually you have far more choices than two. Have you ruled out
Firebird, Ingres, DB2, MaxDB?

--T

#16

On May 22, 5:13 pm, Nis Jørgensen <n... (AT) superlativ (DOT) dk> wrote:

Quote:

I have used both. Love Postgres. Hate MySQL.

I'm not going to argue that PostgreSQL isn't superior technology,
implemented some features long before MySQL, etc. I do agree with
that, and your points are well taken. But a few of your statements
below are not true.

Quote:

The documentation for MySQL covers all versions at once. This means you
cannot just look at the syntax diagrams and assume that they apply to
the version you are running.

This is the case for MySQL now too. They do still have doc for
3.23/4.0/4.1 lumped together, but now the 5.0 and 5.1 doc are
presented separately.

http://dev.mysql.com/doc/refman/4.1/en/index.html
http://dev.mysql.com/doc/refman/5.0/en/index.html
http://dev.mysql.com/doc/refman/5.1/en/index.html

In the 3.23/4.0/4.1 doc, it's true that the syntax diagrams cover only
the 4.1 syntax, but if you read the text of the docs, there are
numerous places where they clarify the exact version a feature was
introduced.

Quote:

Postgres supports transactions, subselects, foreign keys, constraints,
updatable views (via RULES or TRIGGERS) and stored procedures/functions
in several languages - all in the standard configuration.

MySQL 5.0 supports these features (except updatable views) in its
standard configuration. That is, the default storage engine in recent
versions is InnoDB, which supports the features you mention.

Subqueries have been supported since MySQL 4.1, the production release
of which was October 2004. MySQL 4.0 and earlier versions don't
support subqueries, that's true. But those earlier versions are now
so old that MySQL has discontinued support for them. How many years
does a feature need to be in the product before it is considered to be
a standard, supported feature?

By the same argument I could cite PostgreSQL's lack of support for
Windows. :-)
(To be clear, I'm referring to the fact that at one time in the
distant past, PostgreSQL did not offer a Windows binary, but of course
today they do.)

Quote:

Postgres gives you meaningful error messages most of the time. MySQL
mostly just says "You have an error in your SQL syntax;"

Yes, that is part of the MySQL error message... and it's followed by
the context in the SQL statement where the error occurred.

I don't work for MySQL, but I feel like sometimes people don't give
that product a fair chance. You should judge it based on its current
version, not versions that are 3+ years out of date.

Regards,
Bill Karwin

#17

On May 25, 10:38 am, Nis Jørgensen <n... (AT) superlativ (DOT) dk> wrote:

Quote:

http://dev.mysql.com/doc/refman/4.1/en/index.html

True. I am really wondering: Did 4.0 not have documentation? Why is that
documentation no longer available separately online?

Why don't you take a look at it? 3.23/4.0/4.1 are documented in one
lump -- even though only "4.1" appears in the URL above.

Regarding performance, on my current project we run a few hundred unit
tests against multiple database brands on a regular basis, including
MySQL and PostgreSQL. In my MySQL instance, InnoDB is the default
storage engine (the "slower" storage engine), and MySQL is still
noticably better on performance than PostgreSQL and any of the other
databases we support. Though I admit that being unit tests, with
dozens of setUp/tearDown operations, what I'm measuring is totally not
representative of typical RDBMS usage. So all I can conclude from
this is that MySQL is very quick when connecting and creating a half-
dozen tiny tables.

Quote:

- Not being able to update multiple rows in one UPDATE statement, if the
intermediate state of the table violates a constraint. Example: UPDATE
mytable SET order = 2 - order WHERE order in (1,2);

Yes, that's a problem. Actually, I'm not certain they still suffer
from that in the most recent releases. Have to check to be sure. Ah
- they have UPDATE IGNORE now, which at least doesn't abort the UPDATE
operation, but it still doesn't apply the update as you would expect;
MySQL does not support deferred constraint checks.

Quote:

- Not being able to use a subquery on the same table in an UPDATE
statement. Example: UPDATE mytable set order = 1+ (SELECT Max(order)
FROM mytable) where id = 123;

And more generally, reading and writing the same table in one
statement causes problems. The same applies to INSERT and DELETE.

Quote:

- Not being able to rename a field with a FOREIGN KEY constraint on it
(no error message until trying to manipulate the data).

And related, any ALTER command incurs a complete table restructure.

Quote:

- FOREIGN KEY constraints being silently ignored if the table is MyISAM.

This is documented that MyISAM doesn't enforce or even record such
constraints. But I agree -- if it doesn't work in that storage
engine, I wish it didn't silently accept the constraint declaration.
That's like false advertising.

Quote:

Actually, I believe this feature (lack of Windows support) has been
crucial in achieveing the current level of quality of postgresql. I
strongly recommend it for any Open Source project aiming for excellence.

That's one thing that MySQL did do several years earlier than
PostgreSQL, and I believe it gave them "critical mass" with respect to
name recognition and market share. PostgreSQL probably will never
recover from that.

I agree that MySQL is behind PostgreSQL when it comes to technical
features. That's not a surprise to anyone who knows both products,
and I wasn't arguing anything different.

Still, MySQL supports *both* CREATE...IF NOT EXISTS and DROP...IF
EXISTS! Not a really sophisticated feature, but awfully handy. I
discovered PostgreSQL only has the latter, and only added it in 8.2.
Kind of doesn't make any sense to have one and not the other, does
it? ;-)

Regards,
Bill Karwin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

PostgreSQL or MySQL ?

comp.databases comp.databases

Re: PostgreSQL or MySQL ? - 04-30-2007 , 12:04 PM

Re: PostgreSQL or MySQL ? - 04-30-2007 , 01:58 PM

Re: PostgreSQL or MySQL ? - 05-22-2007 , 09:57 PM

Re: PostgreSQL or MySQL ? - 05-23-2007 , 01:51 AM

Re: PostgreSQL or MySQL ? - YOU WIN! BEST TOPIC TROLL OF MAY - 05-24-2007 , 09:24 PM

Re: PostgreSQL or MySQL ? - 05-25-2007 , 10:10 AM

Re: PostgreSQL or MySQL ? - 05-30-2007 , 12:48 AM