Interesting read: Why PostgreSQL instead of MySQL
Moderator: General Moderators
Interesting read: Why PostgreSQL instead of MySQL
Earlier today i found out about http://developer.postgresql.org/index.p ... d_of_MySQL (Admitted, the URL may make you want to objectivitiy...)
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
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.
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.
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.
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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.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)
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
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
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.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.
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
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
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.
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
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.
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)
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)
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
PHP's growth has been pretty similar.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
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA