PostGresSQL vs MySQL

cactus · 2004-11-07 02:32:27

Archers,

What is your experience. I have been using MySQL for a long time, and have had a few friends "giving" their advice on the matter. It seems many of them think postgres is far supirior.

I mainly use the database for simple storage of data for web apps (php and ruby). I don't think I really need stored procedures (pl/sql), don't use indexing very much. The main things I need are SQL conformity (doh!), atomic commits/transaction, etc. Just the normal stuff you would think of using a db for.

Both seem to have nice web front ends, from the ubiquitous phpmyadmin to phppgadmin.

Ok. Opinions..?

*sprays flame retardent on the topic before hand*

afu · 2004-11-07 02:54:23

I don't have a huge amount of experience with either, but I prefer Postgresql. It seems to be more similar to large commercial DBMS's I have worked with.

oscar · 2004-11-08 12:16:55

I say sqlite

cereal · 2004-11-09 09:15:34

I'm not very experienced either, but I think for your needs, it doesn't really matter, which one to use. DB-Gurus tend to call mySQL "nothing more than an interface to the filesystem", ignore Postgres and use Oracle

If you want to run a mission-critical DB-Server, I'd stick with Postgres, because of the Transaction-Support, which may be quite important in such cases.
A nice feature in any case are the subselects in Postgres. MySQL doesn't support this yet, so this may be kind of a "killer-criteria"...

But don't forget: never change a running system. If you run mySQL right now and you are happy with it, then why change?

apeiro · 2004-11-09 23:24:55

cereal wrote:

If you want to run a mission-critical DB-Server, I'd stick with Postgres, because of the Transaction-Support, which may be quite important in such cases.
A nice feature in any case are the subselects in Postgres. MySQL doesn't support this yet, so this may be kind of a "killer-criteria"...

Actually, I believe mysql 4.1.x has support for both now.

http://www.mysql.com/products/mysql wrote:

Transactions
Using the InnoDB or Berkeley DB (BDB) storage engines, the MySQL database server supports transactions. The InnoDB storage engine also supports foreign key constraints.
Expanded support for subqueries
Subqueries allow you to use the result of one query as a component of a larger query. The MySQL server already supports some forms of this technique, such as INSERT INTO ... SELECT ..., and this support will be expanded in version 4.1 to include nested SELECT queries, which is one of the most-requested features from our users.

cactus · 2004-11-10 02:17:44

*rubs hands together*

darkcoder · 2004-11-30 22:33:40

I'm no expert, and only have experience with MySQL. But it really depends on the software you'll be using. If the software support both, maybe you should go with PostgreSQL since it have some advanced features not available in MySQL. In the old days MySQL was known for its speed, but don't know which is faster, or use less resources right now.

oscar · 2004-12-09 17:02:14

I've changed my mind - I say mysql.
why?
it's swedish

sarah31 · 2004-12-09 17:10:26

i have never used postgres. i tried to get it working once but couldn't and i could not find any decent docs. i find mysql satisfactory and it is widely support by alot of software unlike postgres. at least in my limited experience.

mysql's online nmanual is very comprehensive so any user should be quite good with it in a short time.

i3839 · 2004-12-09 17:55:32

From a programming API's point of view, Postgresql has a richer API, as it supports things the Mysql one doesn't.

From a user's point of view I have no idea, never used them.

abhaysahai · 2005-10-21 10:33:56

postgres is the way to go.
Have used and left mysql.
Mysql was good during my learning days.

mmccaskill · 2005-10-21 13:16:29

MySQL 5 is supposed to really be kickass.

cactus · 2005-10-21 19:12:41

I have also now moved from mysql to postgres. I find postgres to be...simply awesome!

LB06 · 2005-10-21 22:21:13

MySQL is said to be superior when it comes to many small queries, while postgres is particulary good at large queries. Personally, I do not notice any significant difference in performance between postgreSQL 8.x and MySQL 4.x.x.

Support for views, safe transactions, subqueries, foreign keys, etc will no longer be an advantage of postgres, as MySQL 5.x (and 4.1 also, to some extent) will also support these features.

The greatest advantage that postgres has over MySQL, is its license and its independance, I think. For example, the most popular and advanced storage engine in MySQL has just been bought out by Oralce, which might become somewhat problematic for mysql in the future.

For mission critical stuff, I would choose for postgres, because they are probably the most mature when it comes to the aforementioned features.

For other stuff, I'm not sure. If you want to run it on a shared host, mysql is probably the only option. Also, mysql might just prove to be faster for you and provide all the features you need. But then again, mysql's dependence on innoDB, for example, might prove to be a huge problem.

cactus · 2005-10-22 18:50:59

I have read that mysql performance vs postgres performance as far as query speed..has closed the gap quite a bit. I would say that the difference is likely negligible at this point (although that is based soley on firsthand experience from my peers. I have no statistical information to back that up).

For me though, the biggest issue is with how mysql handles failures and edge cases.
If you attempt to add a 200 char data element to a column set as 100 chars, in mysql...only the first 100 chars are added. the rest are discarded. This 'silent data munging' is something i am no longer comfortable with.

I choose to go with a *perhaps* slower database engine (postgres) that places a higher importance on maintaining data consistency, than a *perhaps* faster database engine that places more empasis on raw query speed.

Lisence issues are concerns as well. With the recent fuss over the, arguably blown out of proportion, deal that mysql has entered into with SCO, I felt it was a good time to migrate my own development efforts on a platform that I felt more comfortable supporting.

Hopefully, for the rest of the community's sake, mysql continues to get better. It is the predominantly supported hosting db out there.

Redhat did choose postgres to base their 'redhat database' product on though. They must know something about something. lol

sash · 2005-11-24 16:30:11

I use the PostGis extention for PostgreSQL to store geospatial entities and do geospatial operations/queries.

I find the Postgre envronment much more pleasant to work with, and query results seem quicker. One thing that is strange though--Postgre's storage on disk occupies much more space than Mysql.

Arch Linux

#1 2004-11-07 02:32:27

PostGresSQL vs MySQL

#2 2004-11-07 02:54:23

Re: PostGresSQL vs MySQL

#3 2004-11-08 12:16:55

Re: PostGresSQL vs MySQL

#4 2004-11-09 09:15:34

Re: PostGresSQL vs MySQL

#5 2004-11-09 23:24:55

Re: PostGresSQL vs MySQL

#6 2004-11-10 02:17:44

Re: PostGresSQL vs MySQL

#7 2004-11-30 22:33:40

Re: PostGresSQL vs MySQL

#8 2004-12-09 17:02:14

Re: PostGresSQL vs MySQL

#9 2004-12-09 17:10:26

Re: PostGresSQL vs MySQL

#10 2004-12-09 17:55:32

Re: PostGresSQL vs MySQL

#11 2005-10-21 10:33:56

Re: PostGresSQL vs MySQL

#12 2005-10-21 13:16:29

Re: PostGresSQL vs MySQL

#13 2005-10-21 19:12:41

Re: PostGresSQL vs MySQL

#14 2005-10-21 22:21:13

Re: PostGresSQL vs MySQL

#15 2005-10-22 18:50:59

Re: PostGresSQL vs MySQL

#16 2005-11-24 16:30:11

Re: PostGresSQL vs MySQL

Board footer