You are not logged in.

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

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

PostGresSQL vs MySQL

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*


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

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

afu
Member
From: Tuscalooser, Alabummer
Registered: 2004-02-19
Posts: 155

Re: PostGresSQL vs MySQL

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.

Offline

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

oscar
Member
From: Kiruna, Sweden
Registered: 2004-08-13
Posts: 457

Re: PostGresSQL vs MySQL

I say sqlite smile


To err is human... to really foul up requires the root password.

Offline

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

cereal
Member
From: Switzerland
Registered: 2004-08-29
Posts: 29
Website

Re: PostGresSQL vs MySQL

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 wink

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?


"I watched a snail crawl along the edge of a straight razor. That's my dream. That's my nightmare." - (Kurtz, Apocalypse Now)

A few PKGBUILDs

Offline

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

apeiro
Daddy
From: Victoria, BC, Canada
Registered: 2002-08-12
Posts: 771
Website

Re: PostGresSQL vs MySQL

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.

Offline

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

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: PostGresSQL vs MySQL

*rubs hands together*


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

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

darkcoder
Member
From: A bar near you
Registered: 2004-09-10
Posts: 310

Re: PostGresSQL vs MySQL

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.

Offline

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

oscar
Member
From: Kiruna, Sweden
Registered: 2004-08-13
Posts: 457

Re: PostGresSQL vs MySQL

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


To err is human... to really foul up requires the root password.

Offline

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

sarah31
Member
From: Middle of Canada
Registered: 2002-08-20
Posts: 2,975
Website

Re: PostGresSQL vs MySQL

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.


AKA uknowme

I am not your friend

Offline

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

i3839
Member
Registered: 2004-02-04
Posts: 1,185

Re: PostGresSQL vs MySQL

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.

Offline

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

abhaysahai
Member
Registered: 2005-07-12
Posts: 51

Re: PostGresSQL vs MySQL

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

Offline

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

mmccaskill
Member
From: NC
Registered: 2005-02-21
Posts: 163

Re: PostGresSQL vs MySQL

MySQL 5 is supposed to really be kickass.

Offline

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

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: PostGresSQL vs MySQL

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


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

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

LB06
Member
From: The Netherlands
Registered: 2003-10-29
Posts: 435

Re: PostGresSQL vs MySQL

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.

Offline

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

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: PostGresSQL vs MySQL

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. wink They must know something about something. lol


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

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

sash
Member
Registered: 2005-10-16
Posts: 155

Re: PostGresSQL vs MySQL

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.

Offline

Board footer

Powered by FluxBB