Postgres VS MySQL 1

[JCrou82]

i have knowledge of MySQL but have only done small applications using MySQL databases. For a dynamic, e-commerce website, should i go with mysql or postgres?

Generally what i am asking is:
what are the differences between postgres and mysql?
which is easier to work with, has support and is faster or more stable?
Basically the pros and cons and differences between MySQL and Postgres.

PS - I have also read the faq on postgres but i just wanted a general concensus.

 

[sleipnir214]

Take a look at faq699-1751 ______________________________________________________________________
TANSTAAFL!

 

[rycamor]

>what are the differences between postgres and mysql?

See this table, from MySQL's own documentation, showing features in PostgreSQL, and when they are planned for MySQL:

PostgreSQL              |   MySQL version  
------------------------|---------------
Subselects              |  4.1  
Foreign keys            |  4.1 (3.23 with InnoDB)  
Views                   |  5.0  
Stored procedures       |  5.0  
Triggers                |  5.0  
Unions                  |  4.0  
Full join               |  4.1  
Constraints             |  4.1 or 5.0  
Cursors                 |  4.1 or 5.0  
R-trees                 |  4.1 (for MyISAM tables)  
Inherited tables        |  Not planned  
Extensible type system  |  Not planned

> which is easier to work with, has support and is faster or more stable?

They are both quite stable. MySQL of course has a shorter learning curve, because it lacks many features that you have to learn with other DBMS's.

> Basically the pros and cons and differences between MySQL and Postgres.

They are in FAQ699-1751, but here is the nutshell version:

MySQL:
- easily deployed, runs on Windows also
- quick learning curve (mainly because it lacks many SQL features)
- flexible (database structure can be altered more easily)
- fast performance for read-intensive sites
- lacks data integrity and logical manipulation features needed in complex applications

PostgreSQL:
- includes almost all logical manipulation features available in larger commercial DBMS's, supports internal programming with stored procedures
- Advanced abilities, such as user-defined types, etc...
- fast performance (when tuned properly)
- scaleable, especially for concurrent read/write ability
- ANSI standard SQL--ports much more easily to larger commercial DBMS's such as Oracle.
- table design changes on an active database can be problematic (better support is under way)
- slightly more complicated to install (not much)
- runs only on Unix platforms (A native Windows port is under way)
-------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

 

[MrTom]

i recently tried moving to Postgres but found the documentation to be severly lacking. so even tho all these features exist, it took me ages to figure out how to use them and even tho in the end i figured out what i needed, i felt that when i hit more complex problems down the line i'd have no hope of resolving them (i am only a db novice but was working with a mssql expert and it took us ages).
if you want to learn a 'proper' db then i guess posgres might be good but mssql (if u have the money) would be better.
postgres needs better books (the oreilly one is useless) and more online tutorials (hint, hint about using triggers, stored procs, views......

 

[JCrou82]

Thank You rycamor, that was very good breakdown. That helped me alot.

MrTom your comments also helped in shaping my decision.

Still more feedback from others would be appreciated so that I can make a sound decision.

Your Help is Appreciated

 

[rycamor]

Was that the "Practical PostgreSQL" book? I was not too impressed with that. A better choice is "PostgreSQL Developers Handbook", or Bruce Momjian's book "Postgresql: Introduction and Concepts".

Now, using a MSSQL expert to help you with a PostgreSQL database was your first mistake. An Oracle expert would have no trouble understanding PostgreSQL, because it is very similar to Oracle. IMHO, Microsoft uses somewhat "dumbed down" syntax in many places (autonumber instead of sequences, etc...)

The sad thing is, Postgres does have good resources, but they are not "marketed" right. For example, have you been to

http://techdocs.postgresql.org/?

This website has everything you just mentioned and more: 2 online books, many tutorials (including some nice Flash beginner tutorials), links to 3rd-party websites with example code, pages of 3rd-party applications and add-ons. It is a treasure trove of pgsql-related info, but unless someone tells you about it, you have to virtually "stumble across" it to find it.

Also, there are several years worthy of indexed PostgreSQL mail lists on every topic at

http://archives.postgresql.org.

This archive is better than any documentation I have found in any other software, period. Many of the core developers answer questions personally (try that with Microsoft).

-------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

 

[sleipnir214]

PostgreSQL is a more advanced RDBMS than MySQL -- about the only place where MySQL is better than PostgreSQL is its permission system. But because MySQL is less advanced, it is easier to set up, particularly in its default configuration.

You need to take a long look at what you need from your database server. Analyze whether you need PostgreSQL's more advanced functions. If you don't, MySQL's lighter learning curve may be of use to you.

Take a look at your programming language requirements. For example, PHP supports both MySQL and PostgreSQL -- but it's support for MySQL is much more tightly bound into its system. Thus available PHP code samples tend to be written for MySQL. It's an easy but non-trivial matter to convert the code yourself, but it's something to look at.

I've used both PostgreSQL and MySQL. To be honest, of all the features that PostgreSQL has that MySQL does not, the only ones I miss are stored procedures, views, and subselects. The rest of it them I rarely use or can work around on the programming-language side of things.

And I wholeheartedly agree with rycamor that getting an MSSQL guy involved is not the best strategy. They generally don't know enough about the theory of what is going on inside the server to cross their experience to another software base, and they get lost when they don't have a GUI to manipulate. ______________________________________________________________________
TANSTAAFL!

 

[rycamor]

Oh, and speaking of GUIs, PostgreSQL has a great GUI front-end you can run on windows:

http://pgadmin.postgresql.org.

The latest version is an alpha release, but already quite stable, and has some fairly sophisticated capability. -------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

 

[JCrou82]

Thank you for your feedback, but i don't know who mentioned anything about MSSQL. I was asking about the differences between MySQL and Postgres. But thanks for the feed back so far.

 

[JCrou82]

Please omit my previous post. Thank you