Page 1 of 2

Interesting read: Why PostgreSQL instead of MySQL

Posted: Sat Jul 28, 2007 5:15 pm
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...)

Posted: Sat Jul 28, 2007 5:22 pm
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.

Posted: Sat Jul 28, 2007 5:33 pm
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.

Posted: Sat Jul 28, 2007 5:51 pm
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.

Posted: Sun Jul 29, 2007 8:02 am
by superdezign
I think a better article would be, "Why MSSQL sucks."

Posted: Sun Jul 29, 2007 9:01 am
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)

Posted: Sun Jul 29, 2007 9:05 am
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

Posted: Sun Jul 29, 2007 9:28 am
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 :)

Posted: Sun Jul 29, 2007 4:11 pm
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...

Posted: Sun Jul 29, 2007 6:44 pm
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.

Posted: Mon Jul 30, 2007 4:24 pm
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..

Posted: Mon Jul 30, 2007 5:24 pm
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.

Posted: Mon Jul 30, 2007 5:59 pm
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)

Posted: Mon Jul 30, 2007 6:18 pm
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.

Posted: Tue Jul 31, 2007 10:33 am
by ReverendDexter
superdezign wrote:PHP's growth has been pretty similar.
I never said I liked PHP, either :wink: