The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Databases: MySQL and PostgreSQL

I have two databases to choose from for a "proof of concept" application: MySql or PostgreSQL (these were the options presented to me).  The OS is Suse Linux Enterprise Server 10.  I have no experience with PostgreSQL, a wee bit with MySQL. 

My question here is NOT "which is better", because that has been discussed to death here. I've searched the previous posts and read many of the now - closed topics.

When Googling this topic, I found this comparison, but noted that it was slightly dated:

http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html

My question to those that know both is: Do you think the points on the above page are still accurate, or has the releases since its publication fundamentally changed the comparison?

Much thanks
OneMist8k
Monday, June 11, 2007
 
 
I skimmed through them, but I thought that they were reasonably thorough. The only thing that I thought should be on the list is MySQL's habit of silently failing when the actual data does not meet the expected parameters.
Please note I'm talking in the past tense; this guy was good enough to mention features he knew was coming down the pike, I'd hoped he'd answer that criticism too.

In other words, I don't know if MySQL still flubs mismatched data types.
TheDavid
Monday, June 11, 2007
 
 
For a proof of concept thing, either will probably suffice. The only difference that'll affect development really is between the stored proc environment and which you want to learn if you're doing that.

Personally I prefer PG, but I know plenty of people who prefer TSQL. The PG stored procs are more portable to Oracle, the TSQL ones more portable to SQLServer - so it may make sense to find out what the eventual target is in the name of code reuse.

Long term... we're having issues with MySQL's clustering and replication. It's all very fragile and manual and not as robust as we really need. Quite honestly, I've never used PG in that environment so I don't /actually/ know if it's better...
Katie Lucas
Tuesday, June 12, 2007
 
 
I've been working with MySQL stored procs for about a year, and I have only two major complaints:

1)The inability to raise application level exceptions from my sprocs. I have worked around this by creating a framework for error codes, and code lookups - I raise the error by dropping a table that does not exist, with the error code number as the table name.

2) No named parameters - so you need to keep track of the order of your parameters.
I still code in Delphi
Tuesday, June 12, 2007
 
 
SQL compliance: PG still very compliant, MySQL much less so, although you can
add some flags when starting the server to make some parts of it a little more so.

Platforms: Both run very well on all platforms, including native Window versions.

Speed: MySQL's MyISAM tables are much faster but are not ACID safe and should not be used.
MySQL slows down on complex queries and concurrent access. Postgres still has a slower
connection time.

Stability: PG wins, hands down. MySQL has a table repair tool, PG does not, for a reason.

Data integrity: Same as written. You shouldn't use MyISAM, so that warning does not apply.

Special features: Same as written, although PG also has hot backup.

Security: pretty much as written.

Locking: MVCC rocks. Don't settle for less.

Large objects: PG suports LOs completely now, as well as bytea.

Alter table: PG now supports full alter table options.

Language support: Both basically need work

This is a very old list. You might want to google to find something more recent. Personally
I'm uneasy with MySQL as not only are their major features (views, triggers, subselects, etc.)
relatively new, but they are ditching InnoDB and switching to a new "table handler" written
from scratch. Which means years of shaking out bugs.
Michael Smith Send private email
Tuesday, June 12, 2007
 
 
Thanks for the info, Michael.  Yes, the list is a little old, but not terribly so.  I searched for something newer, but everything was even older.
OneMist8k
Tuesday, June 12, 2007
 
 
I wouldn't spend much time reading over the original review.

The article is not comparing MySQL to Postgres 8.x.  Version * came with MASSIVE improvements as seen in the review below.

http://tweakers.net/reviews/657

Also, a very detailed functionality comparison can be found at:

http://dcdbappl1.cern.ch:8080/dcdb/archive/ttraczyk/db_compare/db_compare.html
Tim
Wednesday, June 13, 2007
 
 
Tim, that second URL is even older than the original article - it compares Postgres 7.0 and Oracle 8! The tweakers.net URL was very recent and very interesting, however, so thanks for that.
Michael Smith
Wednesday, June 13, 2007
 
 
One of the things that people have trouble with when they first start using PostgreSQL is the "out of the box" performance.  By default PostgreSQL is tuned for a "general" workload on modest hardware.  If you get into PostgreSQL you'll want to check out an article I wrote a few years ago on tuning it which can be found at http://www.revsys.com/writings/postgresql-performance.html

Hope it helps!
Frank Wiles Send private email
Wednesday, June 13, 2007
 
 
@Frank - Thanks for the link to your page.  I'm leaning towards PostgreSQL, and if we use it, this will be helpful.

May I make a suggestion?  Put a date on your article, and reference what version of PostgreSQL you are referencing.  There is a lot of stuff on the 'net about db's, some of it rather aged.  I assume your article is recent from the copyright on the bottom of the page, but it would be good for the reader to know for sure.
OneMist8k
Wednesday, June 13, 2007
 
 
As software developers, we tend to look for the "best" possible solution in terms of it's technical capacity. The PostgreSQL/MySQL debate is all about this, in terms of SQL compliance, scalability, reliability, etc.

However, there's something very important to keep in mind that may impact your decision. Way more people are using MySQL than PostgreSQL, SQLite, and Firebird combined. If your plan is to hire more people, you may want to factor in the popularity of the technology.

Good luck.
Robby Slaughter Send private email
Friday, June 15, 2007
 
 
@Robby - Availability of skills is certainly a consideration. However, that is pretty low on the list.  This is a proof of concept application.  The production DB could be anything, though the DB used in the proof of concept would certainly have an edge when it comes to production deployment considerations.

There are always trade-offs with any choice.  Which DBMS to use may vary as the requirements vary.  "Which is the best DBMS overall" out of the application context is a  flame baited question for sure.  My original question was designed make sure the facts about the features were up to date, good and bad for both.

Have a great Friday!!!
OneMist8k
Friday, June 15, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz