Interesting read: Why PostgreSQL instead of MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Interesting read: Why PostgreSQL instead of MySQL

Post by timvw »

Earlier today i found out about http://developer.postgresql.org/index.p ... d_of_MySQL (Admitted, the URL may make you want to objectivitiy...)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

If I was was on the fence I would choose MySQL because of it's widespread usage and it's what I am used to using.
Last edited by Benjamin on Sun Jul 29, 2007 1:04 am, edited 1 time in total.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Not much there in that Wiki. It's more of a "We think you should use Postgres because in our OPINION it is faster. We can't really prove it because benchmarks are not easy to do.".

Plus, they make out like a programmer would use table locking for EVERYTHING, if they needed it, and that is plain silly. If you need to perform table locking it should only be done immediately BEFORE you perform an update/delete/insert query and immediately unlocked after the query executes.

The page is more of a "Rah! Rah! Use Postgres instead of Mysql" advertisement than anything informative or useful for determining which is better.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

Now I have postgre and mysql, and to me that doesn't really mention any of the benefits of postgre over mysql.. banging on about "defaults" and the speed of a "count(*)" really doesn't have any bearing on anybody who would seriously concider using postgre surely? I mean if a dev has got to the stage where they are looking for something ultra robust then mentioning that mysql only has a default of 8mb key_buffer is kind of rediculous when most decent size db/servers have this set to 512m and way beyond..

I can see uses for postgre's increment's that run completely seperate to a table, thus allowing an auto_inc over say 5 tables, useful for doing merge tables etc.. this said though, I've got sites with 2000 queries a second being executed, and mysql holds up fine (after a bit of tweaking to get write/read locks working okay..)

I'd like to see an article which mentions a few advantages from a schema design standpoint rather than a server/performance standpoint though.. anybody can bung mysql on a quadcore 32gb ram machine and be happy with the performance.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

I think a better article would be, "Why MSSQL sucks."
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

super, I'd be more interested in "what is better than mysql".. whats your personal choice? (best of disregarding all microsoft products for this one I feel.. even though DTS on SQL server is a kind of bonus point)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

nathanr wrote:super, I'd be more interested in "what is better than mysql".. whats your personal choice? (best of disregarding all microsoft products for this one I feel.. even though DTS on SQL server is a kind of bonus point)
MySQL, hands-down. I've only dabbled in the others just to ensure that I wasn't missing out, and I've found some of the alternative syntax to be unfavorable, specifically in MSSQL. I feel that as a PHP programmer, the simplicity of the queries would be the deciding factor for me as opposed to the performance, as I must write and edit these queries myself. :P
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

Yep I agree completely I'm really not *too* bothered about performance, as we all know query optimisation and schema design is the overriding factor here, you can scale mysql up to ndb cluster if you need it, and personally I've found it to be the most stable.

All my *gripes* are over the limitations placed upon schema design, and the db engine's. MyISAM is fantastic, the lack of transaction-safeness really annoys me though. The INSERT locks are also most annoying on the classic table design especially whn you get over 1500 queries a second.

In my mind the flat file format of table's really isn't effiecient or substantial enough for the more advanced needs we are seeing more and more, as a "clients" needs grow then suddenly you hit limits. for example a "row of columns" just doesn't cut it, lets say all users have 10 columns they need, then each user also needs to have a seperate set of 5 columns strapped tot he end, that stores different values for each user, and the columns are all differen for every user, only way to do this generally speaking is to bolt on 5 text columns so yuo can cater for everything, highly inefficient. Next major gripe is linking tables together, lets say I need to link 1 row in table a to 3 rows in table b.. there isn't really a viable efficient method in place in (any?) db that I know of. especially when you concider that often you need to link 2 tables to another 2 tables.. here link tables don't cut it at all and there just isn't a viable solution that I'm aware of. see viewtopic.php?p=404617#404617 for my current gripe

For 95% of uses though, mysql cuts it perfect, and is my preffered choice for use with php aswell :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Untill MySQL5 one couldn't really talk about 'data integrity'...eg: inserting '12345' into a char(4) or '99-99-9999' into a date column was (silently crippled) and accepted...
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

nathanr wrote:for example a "row of columns" just doesn't cut it, lets say all users have 10 columns they need, then each user also needs to have a seperate set of 5 columns strapped tot he end, that stores different values for each user, and the columns are all differen for every user, only way to do this generally speaking is to bolt on 5 text columns so yuo can cater for everything, highly inefficient.
I've found that since I use PHP, I can always just strap on a serialized object for miscellaneous data and put it all into one text column of an appropriate size.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

I'd assume that have an extra "objects" table, linked by id to the main table would keep it all semi-efficient aswell.. something grates on me still about storing a 3byte string in the same column as a 65k chunk though..
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Being trained initially on Oracle, I've always felt that Postgres was a better product, but I've never really had any hard data to prefer it over MySQL.

My general thought (pure semi-educated opinion, don't quote me) is that Postgres was designed from the ground up to have the features that it has. MySQL, on the other hand, was initially designed to be a very light, feature-sparse database built for speed and simplicity. Now, after 5 main versions, it has a bunch of features that have been added on.

I think MySQL was great for what it was, but I think it's developed into something it wasn't and still tries to claim to be what it originally was. Also in that time it has become tremendously popular. Which is incredibly frustrating if you're used to coding things in the backend (i.e. triggers and stored procedures), and you have to "stop that bad habit" of making your code "unportable", becuase MySQL doesn't have the features you want... well, at least not the version that the client has :roll:

Bottom line: I use MySQL because "everyone else does", not becuase I have any evidence that it's better. Now, this certainly isn't to say that I think MySQL is bad, I just don't think it's as good as Postgres.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

Agreed - I downloaded and installed postgres a couple of years ago, immediately thought it had far more potential than mysql, and can see it being far more robust.. then got consumed in the project of the moment, faster to do it in mysql which I already know, so stuck to it. I've been playing around with postgres again over the past few days and I think it's great, but everybody else uses mysql so i do...

As for the no solid data as to which is better, I don't think there is any, but when you concider that postgres can do more complicated "things" with greater ease than mysql, then it doesn't matter about data on runnign identical queries - it matters more that postgres can *do* this and mysql can't

storing arrays + function created indexes really rock, an index on chars 2-4 or colA and 5-8 of colB that only covers half the rows a table for instance.. try that in mysql!

*convincing myself to move into postgres fulltime and convince every other dev I work with to do the same.. think I'll manage? (concidering I'm a contractor lolol)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

ReverendDexter wrote:MySQL, on the other hand, was initially designed to be a very light, feature-sparse database built for speed and simplicity. Now, after 5 main versions, it has a bunch of features that have been added on.

I think MySQL was great for what it was, but I think it's developed into something it wasn't and still tries to claim to be what it originally was. Also in that time it has become tremendously popular. Which is incredibly frustrating if you're used to coding things in the backend (i.e. triggers and stored procedures), and you have to "stop that bad habit" of making your code "unportable", becuase MySQL doesn't have the features you want... well, at least not the version that the client has
PHP's growth has been pretty similar.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

superdezign wrote:PHP's growth has been pretty similar.
I never said I liked PHP, either :wink:
Post Reply