Why is MySQL more popular than PostgreSQL?
There is much discussion of why MySQL is more widely adopted than PostgreSQL. The discussion I’ve heard is mostly among the PostgreSQL community members, who believe their favorite database server is better in many ways, and are sometimes puzzled why people would choose an inferior product.
There are also many comparison charts that show one server is better than the other in some ways. These don’t really seem to help people with this question, either!
I can’t answer for everyone, but I can put it in the form of a question: if I were to replace MySQL with PostgreSQL, what things do I rely on that would become painful or even force a totally different strategy? The answer turns out to be fairly simple for me: replication and upgrades.
Replication
Love it or hate it, MySQL’s built-in replication is absolutely key to much of what I do with MySQL. I can truthfully say that it has lots of problems and limitations. But I can also say this about it:
- It’s included by default with the server. PostgreSQL’s have historically not been included. (I think this is about to change, but I’m not sure.)
- It is conceptually very simple. You could call that a weakness and a limitation, but you could also say that it enables a tremendous amount of flexibility. I tend to hold with the latter view. PostgreSQL’s replication technologies have a very different complexity profile. That scares me.
- It is easy to set up (it takes just a couple of commands) and is easily scriptable. This is mostly due to its simplicity. I am happy because I know it inside and out.
- It is generally very low overhead. PostgreSQL’s main replication system is built on top of triggers and is said not to scale very well. (Disclaimer: this is only what people have told me; I haven’t battle-tested it. But I’m afraid of it.)
- There is only One Way To Do It. PostgreSQL has lots of different replication systems. That in itself is a pretty significant deterrent for me.
Regardless of the technical strengths and weaknesses of each database’s replication systems, it is my perception that MySQL’s ultimately lets me do incredibly flexible and useful things; in general it is Just Enough and has just the right combinations of qualities for lots of purposes. And each of its weaknesses is easily avoided or worked around, or just sidestepped — because MySQL replication’s simplicity and flexibility lets me easily choose a different approach.
In-Place Upgrades
MySQL’s files are extremely portable between versions, between operating systems, and even between platforms most of the time (unless you have a system that doesn’t use IEEE floating-point format, but who does these days?). That means an upgrade is dead simple.
This may not seem like a big deal, but I work with a lot of data. When you do that, you have to consider the alternatives: what if I couldn’t upgrade in-place?
That’s the current state of PostgreSQL. You have to dump and reload your data, and when you have a terabyte of data, that’s no fun. The workarounds usually involve replicating your data to another server, switching to the other server, upgrading, and switching back. But why should you have to have another server just to upgrade your data?
I see this as a significant — even critical — sticking point. It’s something I just don’t have to think about most of the time with MySQL
Are PostgreSQL’s other strengths enough?
Not for the systems I work on. These two problems seem extremely difficult for me to work around. I rely so heavily on MySQL’s replication and in-place upgrades that it feels too daunting to live without them.
What I’m trying to do here is give some psychological insight into what makes me feel happy with MySQL, and afraid of the thought of having to solve these problems with PostgreSQL. It may or may not apply broadly; my sense is that these are concerns for others as well, but I could be wrong.
If I were primarily a PostgreSQL user, I’m sure there would be similar feelings the other direction. This would explain why some people in the PostgreSQL camp seem to recoil away from MySQL. I’d be interested to hear why that is, too.
Fundamentally, the exact feature set is irrelevant.
I think the key to MySQL’s popularity has been its ease of use (time to get started with it). Yes PostgreSQL now has native Windows support, but it’s years later. By that time MySQL had reached critical mass, and thus the point of who is “better” becomes moot. At that stage you use MySQL because everybody else does, and the number of people having worked with it is huge.
This is, in many respects, very similar to the dominance of Windows. It may suck in many ways, but it has critical mass. The argument “Linux is better” bears no relevance to the average user. And even to advanced users like yourself, it does not matter for MySQL, as “better” is a subjective thing: it depends on what you need to do.
Even the argument that you’d use MySQL for simple stuff and PostgreSQL for advanced foo is fatally flawed, and simply proven wrong in the real world. Some then brand those who choose MySQL to be stupid and ignorant, and I reckon that merely shows those commenters’ own ignorance of how such things work ;-)
Try convincing an average Windows user to switch by saying “Linux is better” and perhaps showing them a booted Linux desktop. Or, perhaps not. Wrong approach and waste of time, as anyone who’s done (or just observed) this will attest…..
The two points you raised, easy-to-use replication, and in-place-upgrades, are as important as the ease-of-first-use issue that I described. Such things are the key, overall technical superiority really is not. That doesn’t make MySQL a bad product (not even after my Windows comparison ;-), it’s a great product where “great” is measured over many different aspects that matter in the real world.
Arjen Lentz
18 May 08 at 6:30 pm
I agree totally with what you’ve said. Replication is a key component for me, and especially the way mysql implements it. I too have been put off by the replication methods available to postgresql.
In one of my implementations, I have a single master server which contains databases for about a dozen web applications. I have a backup slave server that has a replication of all those databases, it is there in case the master server fails (and for offline backups), and is not used by any clients. I also have a slave database server for each web-app which only replicates the database associated for that app. All INSERTS and UPDATES occur on the master server, and all SELECTS occur on the apps slave server.
This was very easy to setup in mysql (relatively speaking), but when looking at how to implement the same thing in postgresql, I gave up after reading docs for several hours, and am still not sure if it is even possible.
Even for a dozen busy web apps, on hardware that is a generation or two behind current, the databases are running very fast. We can only just measure a lag between an INSERT going into the master server, and the data showing up in the dedicated slave. We can also barely measure cpu usage required for replication. It just seems to work like magic.
The only MAJOR problem we have had is when a developer forgets and updates data on the slave, but really thats our own fault because we haven’t locked down the access accounts to only do SELECT’s (which we keep meaning to do).
Clint Webb
18 May 08 at 11:54 pm
I’ll second many of Arjen’s points. For a specific example, my understanding of PostgreSQL back in 1998 was that no row could have more than 8k of data, which seemed to indicate it would not be a good choice for forum software, as most posts were often more than 8k.
Secondly, most hosting providers provide MySQL, and few PostgreSQL. Why is this? If it’s really as simple to set up and administer as its fans say it is, why aren’t hosting companies providing it by default? *Today* that answer would probably be down to the network effect – everyone else is offering it, and that’s what all the major web software is written for. However, that wasn’t the case 10 years ago. But PostgreSQL was (is?) not written with ease of administration in mind.
It seems to me that the majority of people using PostgreSQL (and likely developing it as well) are people who are in complete control of their dev environment from A-Z, and expect every other user of their software (if they ever write software to be distributed, which I bet most don’t) to have the same control.
It would also seem that for all the haranguing that PostgreSQL fans do, that some of them would do more to address the major hosting companies’ lack of PG support. Getting a few key players to support it in $5/month hosting would make it very easy for people to develop/test with it, as well as justify the time to target it during development.
Instead we continue to get people bitching about MySQL’s technical shortcomings (most of which get addressed year after year).
Michael Kimsal
19 May 08 at 12:17 am
> MySQL’s files are extremely portable between versions, between
> operating systems, and even between platforms most of the time
> (unless you have a system that doesn’t use IEEE floating-point format, but who does these days?).
Hmm, there is nothing in the documentation about platform portability. I was looking for the tips on 32bit -> 64bit migration on x86 but found nothing conclusive. Is your opinion based on the experience?
kmike
19 May 08 at 1:12 am
kmike, not a problem; just moving between 32 and 64 boxes, or even boxes with different endian-ness.
Only thing to look out for is float/double fields when moving to/from say a Sparc box since they don’t use IEEE format, and moving on-disk files of NDBCluster, it’s not endian-safe. But MyISAM and InnoDB are.
Arjen Lentz
19 May 08 at 1:21 am
Replication is not the reason, since MySQL was more popular than PostgreSQL way before MySQL hat any sort of replication features. As Arjen pointed out it was ease of use and with that I mean that MySQL was very unsnobby about things. Maybe too unsnobby for the good of its own users as MySQL blindly truncated too long data or stored invalid dates. But the PostgreSQL community has traditionally insisted on doing things right ™ and most people are either unable, not yet able or simply uninterested in doing things right ™.
Lukas
19 May 08 at 1:38 am
How could you not mention the name?
“PostgreSQL” is so ugly it makes me shiver. I will never use Postgres, no matter how much better than MySQL it becomes.
Andrew
19 May 08 at 1:54 am
I have recently been working with PostgreSQL after having used only MySQL in the past. I can’t say my experience with either is thorough, but, reading through Postgres’ manual, I got a profound sense of elegance; so far, my experience with Postgres has been consistent with that sense. MySQL, on the other hand, has so many caveats and inconsistencies to remember; but that does not diminish its usefulness.
To me, the Postgres versus MySQL debate seems like the *BSD versus Linux debate. One is less popular, and has a rigidly defined scope and mission, clearly defining what belongs and exactly how, sticking to standards and good practices, while the other has widespread support and a somewhat more loosely defined mission, along with less well-defined ideas of the right way to accomplish it.
Chris Daniel
19 May 08 at 2:26 am
I’ve deployed a number of applications on both databases over the last 10 years or so, and I think the difference in populararity stems from a social, rather than tehcnical source.
PostgreSQL came from academia, and the developers never sought adoption within the early Perl CGI developer community.
MySQL was the de-facto successor to mSQL, one of the first lightweight UNIX SQL servers. The target market was, from an early stage, quick deployment scenarios where relational integrity was less important than simplicity.
…and then came PHP, and the proliferation of quick-and-dirty applications written with basically no concern outside of expedience.
PostgreSQL is “serious” software, which benefits from a real DBA and careful tuning. Given that level of support, it can be every bit as scalable, upgradable, and agile as MySQL as a platform.
The Windows/UNIX comparison is quite apt, though. Once MySQL became even marginally more popular, few developers considered it worthwhile to write portable code.
Lennon
19 May 08 at 2:39 am
Well I know the in-place updates is being worked on.
From my experience at a web hosting company it’s a minor issue though. I never once upgraded a mysql install. Many of their customers are still running php4/mysql4. I believe the problem is only between major releases too, so between 8.2.7 and 8.3.0 but not between 8.3.0 and 8.3.1.
I can’t say anything about replication. I’m not that experienced.
I choose postgres for it’s major ansi-sql support. I like checks and constraints. mysql just doesn’t support these (enough there is foreign key constraint). If I recall correctly (can’t find a citation) postgresql is actually ansi sql-92 compliant. It might just be the only product that is.
The other reason some people chose mysql is speed. It’s faster than postgres (postgres-8.3 benchmarked ~twice as fast as 8.2). In many web apps I can see that constraints are not so needed. I think a lot of them are barely relational (I can’t prove this).
also if you stick to ansi sql code, the code you write for mysql should work for postgres as well (save very small few things) but such is not true in the inverse. Most products I know that support postgres support MySQL as well, this means they probably didn’t add support for the additional features of postgres. This means that mysql is faster and that there are zero gains to be made by using postgresql (unless you already have postgres running on the server).
Caleb Cushing (xenoterracide)
19 May 08 at 3:13 am
Personally, the reason I use MySQL as opposed to PostGres, is it’s ubiquity.
MySQL works with everything I’d like it to.
It’s been in the space competing with Microsoft, Oracle, Sybase and the like long enough that it is either explicitly supported by tool vendors or it has been adapted to do so (see ODBC, dotNet).
It works, I know it works and that’s all it really comes down to.
Bren
19 May 08 at 3:31 am
We had a developer deliver a project that required Postgres due to it’s objects. When I looked into it Postgres was far more a problem than MySQL to administer.
For one, many small projects attempting to resolve replication. Many small projects means none have yet succeeded and any could die at any moment.
Another, the need to dump and reload data between major revisions. Serious pain in the rear end.
Another, a totally different way of maintaining user accounts which I really didn’t have time to learn and document for internal management.
Another, the databases required vacuuming. We found out this after the system stopped.
James
19 May 08 at 4:39 am
I don’t think it is about the featureset per se.
People are usually lazy and stick to what they picked first (and by now, know already)
MySQL simply is much more ubiquitous. I think PostGRE should make campaigns to raisen it’s knowledge factor. It should ALSO target “dumb” users and Sqlite-users (because if they target sqlite users, these sqlite users are more likely to upgrade to postgre if postgre provides a lot of “added advantage”, tutorials, podcasts, screencasts etc..)
mark
19 May 08 at 4:52 am
Hello,
First, I’d like to invoke an issue with database functions:
PostgreSQL allows writing a function with ‘SETOF RECORD’ as return type. The equivalent process in Oracle database is to create a new TYPE, say basicType, where you declare fields and their corresponding db types (int, varchar, etc.), then create another TYPE, say basicTypeSet, which is a TABLE OF basicType then make your function’s return type as basicTypeSet.
This allows you to return data in an extensible way. No way to do it in MySQL, can anyone point to how is doing this possible in MySQL ?
Second, did anybody track any trace of RULES in MySQL db ?
digitalDNA
19 May 08 at 6:02 am
When you install mysql and type “mysql”, something happens. When you install PostgreSQL, you can try “postgresql”, “postgres”, even “pgsql”, and they’ll all say “command not found”, because it’s “psql”. When I was a total n00b, I tried to start out with postgres because I had heard it was “a real database”, but I wasn’t able to find psql in 10 seconds. So I got going with mysql instead, and in retrospect, it was the best choice.
It’s like how I learned Perl instead of Python back then, because only ‘man perl’ worked.
sapphirecat
19 May 08 at 12:46 pm
@sapphirecat how on earth did you learn unix? I want to delete a file, man delete. It brings up the manual on the sql delete command. I understand you but I’m not sure it’s really the best argument. It basically says you can’t be bothered to look up documentation.
ironically your arugment is completely null.
$man postgres
NAME
postgres – PostgreSQL database server
…
$postgres –version
postgres (PostgreSQL) 8.3.1
it seems the commands and man pages exist.
Caleb Cushing (xenoterracide)
19 May 08 at 1:00 pm
I changed from mysql to postgresql only on the basis of the greater reliability. If you were Bank of America and were forced to choose among the two db’s, which would you choose?
My background includes embedded development and financial applications too. So I’ve become a member of the cult of reliability. Craigslist runs fast on mysql but the company doesn’t die if an advertisement or two is lost. I’m not saying they are lost, I don’t know.
Also, postgresql is even freer than mysql, long-term. There is a sense of impending propriety in the mysql world that does not exist in the postgresql world.
On the other hand, I cannot say that postgresql is as easy to deal with.
Ricky
19 May 08 at 1:15 pm
As Lukas said, it’s not the replication, since more people used MySQL long before either had replication. It’s a bit of ease of use, and a lot of difference in the marketing strategy.
As for what PostgreSQL people shudder about with regard to MySQL, here’s my top two: (1) actions that indicate the database doesn’t care about your data, such as silently accepting bad data, defaulting to ignoring transactions (note: not even giving an error when you try to use them, but ignoring them!), etc.; (2) A company that engages in a campaign of intentionally distorting the meaning of the GPL in order to protect their profits.
Chris
19 May 08 at 1:58 pm
The last version of MySQL I worked with was 5.017, sometime ago. We completely ditched MySQL after struggling for months with bugs and lack of features “to be corrected on the next release”, namely:
- Replication of triggers and stored procedures: yah, it should be working fine these days, but I went across several 5.0 releases “with replication” that wouldn’t replicate these kind of objects.
- Replication robustness: if a server is down, we need to manually sync the database with the rest of the cluster. That may be ok if there is a sysadmin allways there, but if a server crashes, the data may not be replicating at all. Again, this probably is already fixed.
- Performance of stored procedures and triggers: not that important, but calling functions was slow – real slow.
- Decimal datatype changes: this was a real show-stopper – we had lots of rounding issues, and even bugs in calculations. Again, probably already fixed.
- Subpar performance on subselects: yeah I’m a subselect fan. This feature has been available since 4.1 but it seems the right way to do it on mysql is using joins. And don’t get me started on nested subselects.
Regarding the article, it is probably easier to setup a replication environment with mysql (specially if what is required is a multi-master or a simple master/slave environment), but the tools available with postgres are far more complete (and also more complicated to setup), and doesn’t limit to “trigger-based”.
Also, databases with big tables (both in field count and row number) would only benefit from postgres features – bigger row size, better indexing, bigger table size.
As for in-place upgrading, I guess you’re refering to upgrading between minor versions, this is also possible with postgresql (but of course not recommended). (Note: I haven’t tested between different architectures) If you’re talking about major version upgrades, you’re in for a world of hurt – probably will work between not-so-different versions, but if you’re using any of the juicy stuff of 5.0/5.1, it probably won’t, specially between versions with incompatible table partitioning schemes.
Replying to the comment about row size, the current row size on postgres is 1,6TB – a bit more than MySQL’s 4Gb (that’s more than enough for most applications anyway).
I believe the popularity of MySQL arises from 2 factors – it is very easy to use and to learn, and it is very easy to setup. It’s probably ok for some kind of applications, but I wouldn’t compare it with postgresql. Postgres is on a completely different level.
JP
19 May 08 at 2:31 pm
I worked on a postgresql project once. There is no way to dump a database and re-import it. The built-in dump methods cannot properly organize triggers so that when you re-import the dump file the tables are organized correctly.
That, plus, whenever I try to add postres support to any of my projects I get bummed-out by the lack of just running a script that says ‘drop table if exists X’. Of course you can just ignore the error returned, but tell me which tool actually *does* ignore the error? Please, I will use it religiously.
Apart from having to write your own tools to do ignore errors from “if exists” type drop tables, it’s so much trouble to setup. Why does it have any sort of Unix login integration? It feels so ’90s to have that, it’s like a mail server that can’t take mail for inboxes unless they have a valid login on the machine. Who wants that feature? Plus, way to not name your commands uniquely postgres… which command adds a new database user? useradd, or adduser? I forget which one alters my operating system, and which one allows someone to access the database server… hmmm…
Mark Kimsal
19 May 08 at 2:37 pm
JP, I’m fairly sure (someone correct me if I’m wrong) that you can’t move files between architectures even within the same PG version. I mean, you can’t just copy files from a 32-bit AMD processor to a AMD64 machine.
I don’t know if this ever would have been a big deal if I hadn’t become accustomed to doing it on MySQL.
On dumping and reloading: mysqldump doesn’t handle triggers right, either. But mk-parallel-dump does ;-) And MySQL doesn’t support any sort of deferred schema checking on views, so dumping and reloading views is a bit tricky. You have to do some sleight-of-hand with creating and dropping tables to prevent inter-dependencies from breaking.
Xaprb
19 May 08 at 2:45 pm
Sorry, my above comment was hastily written, postgresql tends to make me … type faster than usual.
Postgres is thought of as a “real” database servers. It’s used when an organization needs to collect data. MySQL is an “application” database server, it’s used by developers when they need to add database support to an application. DBAs vs developers, that’s where the distinction lies. It’s like writing apps for Windows vs Linux. VB was drop-dead simple to make apps with, and apps for Windows flourished. Technology follows developers, not DBAs.
Postgres needs to play catch-up in terms of usability and “out-of-the-box” experience.
Nobody uses it, partly because it was so bad historically. Only storing 8k max in a row… nobody could build “large” CMS type web apps back in the day, so they used mysql, now it’s a comfort level issue.
Focus on comfort level, abandon old ideas about what a “real” database “should” be, focus on Web apps, or desktop apps, or embeded DB support… but give up on the “enterprise”-ness of postgres. Nobody wants that, and if they need it, they’ll fork over the money for a closed source version because it makes them feel better.
Mark Kimsal
19 May 08 at 2:47 pm
A killer feature within MySQL is of course the simple replication. Didn’t see that in PostgreSQL. And I use it a lot.
In the future, MySQL will offer a lot of cool features through storage engines, think of kickfire (damn fast transaction processing with a special SQL Chip) or Primebase XT (some cool High Availability and SSD stuff in the pipeline, check the Conference & Expo Videos). MyISAM is kind of a toy thing for me but it has it’s uses, replacing it with the more advanced Maria Engine is still a good move. And then there is NDB. There is a lot more to imagine. So what will Postgres come up with? You simply can’t optimize an engine for every use case.
i wouldn’t consider MySQL and Postgres competitors really, because they both have their distinct user bases. Most MySQL users don’t need advanced features postgres offers, it’s just important to be aware of the alternatives. Usually you can’t simply switch to another RDBMS (only if you use basic SQL and don’t leverage any advanced features) so migrating existing applications is mostly out of question if there aren’t any real benefits. As for new deployments, platform choice depends on developer experience usually. You can build fine applications with both. Both have shortcomings to work around. As for MySQL, most of these shortcomings don’t matter in the web world, so why bother? Usually, when you scale large setup you will end up using stuff in a way it was not intended to be used anyways, I don’t think that’s how most Postgres Guys roll (the aforementioned “doing it right” attitude) ;). I’m more into “doing it good enough and have time to hit the bar afterwards”.
Nils
19 May 08 at 3:15 pm
@Mark Kimsal
Postgres has supported IF EXISTS since 8.2:
http://www.postgresql.org/docs/8.3/interactive/sql-droptable.html
@JP
They are working on this, and are at least looking as Postgres for hints.
http://s.petrunia.net/blog/?p=43
http://s.petrunia.net/blog/?p=40
@Xaprb
Its not just replication which is not simple. Setting up partitioned tables is a couple commands in MySQL (5.1), and bunch of triggers and constraints in Postgres. GAH!
Rob Wultsch
19 May 08 at 4:02 pm
@Nils
check this
http://www.postgresql.org/docs/8.3/interactive/high-availability.html
digitalDNA
19 May 08 at 4:04 pm
@Rob Wultsch
Yep, I know that. But my server is Mandriva 2007.1, barely a year old, and it doesn’t have 8.2. So there’s the reality, yes some features are now present, but it hasn’t disseminated to the masses fully yet. Not only would I have to upgrade, but I would have to make sure the recipients of my projects run on the very latest version of PG. I’m not saying it’s impossible, just a reality check.
Mark Kimsal
19 May 08 at 4:14 pm
@Mark Kimsal not at 8.2 yet?! wow… I would recommends skipping and going for 8.3.
@Rob Wultsch
http://momjian.us/cgi-bin/pgpatches_hold
there seems to be an auto partitioning patch on hold, it should make it into 8.4, that might do what you are referring too.
I prefer the Make a feature rich, stable, then make it easy to use. Linux followed this, Git follows this, postgres kinda seems to follow this, and so far, it’s worked out pretty well.
Caleb Cushing (xenoterracide)
19 May 08 at 4:27 pm
To everyone talking about current and future features, you’re sort of missing the point of the post. Why is MySQL more popular? It’s FAR more entrenched in the web app world, where dare I say a majority of software development happens (maybe I’m wrong, but certainly a lot happens there). In the late 90’s PostgreSQL completely missed the boat in terms of being accessible to the needs to web application developers. That is still haunting pg to this day, and continues to explain why MySQL is more popular than PostgreSQL. Installed base, inertia, ‘good enough’, etc. could have all been reasons for pg being more popular and people not trying MySQL (or firebird, or whatever) had they been more web-dev friendly in the early days. I say ‘they’, but it didn’t/doesn’t have a concerted company dedicated to its spreading, at least early on, so it will likely continue to remain a fringe player in the commodity database space.
Michael Kimsal
19 May 08 at 4:50 pm
Another thing that is done easily in MySQL but is more problematic in PostgreSQL are UPSIRTs. MySQL supports “ON DUPLICATE KEY UPDATE”, but no such construct exists in PostgreSQL. Many MySQL applications I’ve seen would have significant pain replacing O_D_K_U clauses with additional logic.
Justin Swanhart
19 May 08 at 6:07 pm
@xenoterracide: If you put it that way, your system is null. My experience definitely applies to Red Hat 7.0 and some point-in-time of Gentoo (possibly also antiquated, though). I can’t even remember how I found psql, but by the time I did, I was quite irritated. Couple that with the weird “default database is your login name” thing and you have a sufficient WTF/sec. factor as to avoid Postgres.
And I don’t care how much you call me lazy. It’s working out well for me these days. I used to waste lots of time on perfection, but now I have enough knowledge that I can focus on *doing* things, which is what pays.
P.S. All *nix tutorials cover rm. It would be hard not to learn about it.
sapphirecat
19 May 08 at 8:32 pm
You can turn your own examples around and make them pro-PG, e.g.,:
“There is only One Way To Do It. PostgreSQL has lots of different replication systems. That in itself is a pretty significant deterrent for me.” –> “There is only One Way To Do It. MySQL has lots of different table types. That in itself is a pretty significant deterrent for me.” As a PG user (and somebody who studied RDBMS theory in college), I’m not even sure what MySQL “table types” are for. It’s like “If you want transactions, pick A. If you want full-text-search, pick B. You can’t have both. We also have C and D, though we won’t tell you why you’d ever want these.” WTF? Do the MySQL idiots even know what an RDBMS is? Did they ever read the document spelled out by 3/5ths of their name?
Even if you’ve decided you want your RDBMS to be an RDBMS and set up MySQL to have transactions (and gone to the pain of finding and installing a third-party FTS engine, ugh), they don’t apply to structural transformations, like CREATE TABLE. I know SQL doesn’t require this, but it doesn’t require a lot of things that are insanely useful — like, oh, indexing! So whenever I run a migration (rails-speak) in MySQL, if anything goes wrong, I’m left with the database in an inconsistent state. The (rails) book literally says “make backups first, and be prepared to restore”. So if I switched a PG site to MySQL, I’d be able to upgrade the database server in-place without doing backups (yay!) … at the cost of needing to do backups almost every time I update my webpage (wha?). I do not see this is as a net gain.
P.S., thanks for inviting MySQL rants! Most people don’t appreciate it when you dump this crap on their blogs, and I’m thrilled to have this opportunity. :-)
tim
19 May 08 at 8:39 pm
Tim, the table types argument had definitely occurred to me. (Nowadays they’re called storage engines.) I just finished writing a book that has a lot of material about learning how to use various storage engines to their fullest.
Xaprb
19 May 08 at 9:27 pm
I just so happen to talk about this subject in my blog last week.
http://menendez.com/blog/mysql-dates-and-time-zones-postgres-and-mindshare/
Basically it boils down to Postgres being more than what most people need. Why change for something better which you haven’t used before when what you have now is good enough?
Ed Menendez
20 May 08 at 1:13 am
@everyone saying mysql is good enough. I’m curious what operating system do you use personally? because good enough is typically what I here from people who are arguing against Linux, Windows is good enough and does what I want. After I discovered linux, and spent 2 years learning it Windows eventually became not good enough, and mostly incapable of doing what I needed it to do.
This is also why I picked postgres over mysql. After learning some things I wanted to use, I found out that mysql couldn’t do them but postgres could.
however, this very example points to why mysql is more popular. 1.) the fact that it now has a bigger name makes it more popular 2.) it was easier in the beginning making it making it more popular 3.) it’s now perceived to be ‘good enough’ so why change, especially when learning that something new might be difficult or time consuming.
Caleb Cushing (xenoterracide)
20 May 08 at 6:03 am
MySQL has a reputation for being easier to use than PostgreSQL. I’m not sure if this is fair or not. PostgreSQL isn’t just a different kind of database, it’s program is architected entirely differently from MySQL. The concurrency model is different from MySQL, the administration is significantly more complex than an out-of-the-box MySQL setup, backup/recovery are different, etc.
Personally, I find MySQL fits my needs. I already know what the deficiencies are, and how to work around them. I’ve read the source and am comfortable making changes to it. I’m now as entrenched with MySQL as I was with Oracle when I was forced to make the jump to MySQL, cursing it all the way. I don’t know the particular difficulties of a large PostgreSQL deployment, so I can’t anticipate what might happen.
That being said, I plan on starting to use PostgreSQL more, particularly because I plan on porting some MySQL stored procedures to PG/SQL.
To xenoterracide: Where MySQL is concerned I am a Linux user, with CentOS being my favorite distro followed by OpenSuSE. I use OSX, XP and/or Vista on my personal machines. I’ve never encountered a production Windows MySQL database.
Justin Swanhart
20 May 08 at 1:20 pm
@justin Swanhart.
So you use Windows/OSX on the Desktop and Linux for Servers.
I have seen mysql on windows servers, but that’s another matter.
I was actually asking more about your choice of ‘Desktop OS’ than server.
My theory is that most of the people who defend their use of mysql by saying it’s good enough are using windows on their desktops ‘by choice’ (windows for gaming not included).
Caleb Cushing (xenoterracide)
20 May 08 at 2:03 pm
I don’t use Windows, period :-) I haven’t for many years. I use GNU/Linux. I know a lot of the people I’ve met through the MySQL community don’t use Windows on desktop machines either. (A lot of them do — but I’d say a higher-than-average number use GNU/Linux).
Xaprb
20 May 08 at 2:24 pm
I use Windows because I like to play games. I always keep a Virtualbox VM at hand to run my favorite open source operating systems.
Justin Swanhart
20 May 08 at 2:28 pm
Given we are talking about the past not current features.
I was working with an unix admin who was interested in learning the DBA ropes using either MySQL and PostgreSQL.
When we discovered that the DESC command didn’t exist in postgres guess which database he decided to use. Sure I could have written the SQL for him to do the same thing, but why. Simple metadata queries make supporting databases much easier.
People wonder why products are not adopted even though they are seen as being better. Sometimes as mentioned before in these comments it is down to that WTF moment when you first use a product.
I have played around and installed with plenty of OSS software and the assumed knowledge for some products is extraordinary. There is little wonder that widespread adoption is slow.
Have Fun
Paul M
21 May 08 at 2:54 am
@Paul M: I am honestly unsure how to respond to such an uninformed response.
When using the ‘psql’ client, you can simply type ‘\d ‘. It provides just as much useful output as the ‘DESC’ command, and is actually documented in the interactive help for PostgreSQL.
Lennon
21 May 08 at 3:11 am
For me it’s always been about access methods. MySQL has a nicely intuitive user account system. I can add a user easily, grant specific privileges, revoke them, grant rights globally or down to the table level.
Postgres probably isn’t as bad as I think it is. I do have to use it for certain things that our developers have built, but I always have to go back to the documentation to figure out how to add users.
Using the pg_hba.conf file to allow access seems counter intuitive to the point of being nearly arcane level knowledge. Would a normal end user know that you need to add a line to a text file to let a new user into the system?
Secondly, user accounts are weird and confusing to create and regulate. However, I will admit, this is likely my own lack of interest in learning how to do it on a routine basis.
Tim
21 May 08 at 9:49 am
I just wanted to how nice it is to read a well written discussion on the topic of MySQL and PostgreSQL. By flipping the question around and looking at what how you are using MySQL and then detailing how that same usage might be harder in PostgreSQL is a good approach. Then focus on specific issues. Also recognizing that this is specific to jobs you are working on, not everyone as a whole.
Improvements in PostgreSQL in the areas of replication and in place upgrades would be great to see. On the flip side there are still plenty of things in MySQL I’d like to see addressed as well. I’ll save that for another post :-)
Joseph Scott
22 May 08 at 12:28 pm
I’ve been using both for about the last eight years. I absolutely cannot understand where the “MySQL is easier to use/admin” mindset comes from except possibly from the perspective that there are more newbie oriented tutorials around for MySQL of the “how to install Apache, MySQL, PHP, and Mediawiki in 10 minutes” type that walk the reader through setting up the complete stack. PostgreSQL administration has always seemed pretty intuitive to me. ‘apt-get install postgresql-server’, ‘createuser foo’, ‘createdb bar’, ‘psql bar’. I have to interact with mysql on a regular basis and still always have to lookup the man pages to remember what the order of the commandline args are.
Adding up the total time spent admining the two systems (and considering that I use PostgreSQL much more heavily for development), I’d say that I spend a *lot* more time dealing with MySQL. Mostly doing ‘REPAIR TABLE’s on production systems that have mysteriously crapped themselves. PostgreSQL has never died on me or lost data, ever.
Then there was the MySQL 4.0 -> 4.1 upgrade where they changed the password hashing and, at least from our perspective, *everything broke*. Fun.
I don’t do replication though and I’ve never tried switching architectures. I’ve had to do the dump and import on upgrade with PostgreSQL twice in the last eight years (6 -> 7 and then 7 -> 8) and it wasn’t a big deal. I’ve had to do dump and import for MySQL to move to new table types.
MySQL was faster that Pg for read-heavy databases once upon a time. Even then, it wasn’t *that* much faster and Pg still performed better when there were a lot of writes mixed in. With today’s hardware, I haven’t seen a noticeable difference between them (though supposedly Pg has made even more gains on the benchmarks).
That’s from a sysadmin perspective. As a developer, I always use an ORM (SQLObject, SQLAlchemy or Django’s ORM) except for an occasional query that has to be hand tuned. So the difference between them is minimal. The main exceptions being that the Pg apps can use proper transactions and so tend to lose data less frequently and that MySQL’s fulltext search is horrid compared to Pg’s tsearch2. It’s a constant complaint from the users of every MySQL backed application we run. Plus with Pg, I don’t have to choose between full text search and transactions. MySQL’s 64KB row size limit has also bit me a few times. And we won’t even talk about unicode (though to be fair, Pg has only recently got it’s unicode support properly cleaned up).
anders
22 May 08 at 3:18 pm
Interesting: Bruce Momjian just posted a link to a talk on PG upgrades with this comment: “… highlighted the complexity of doing in-place Postgres upgrades between major versions.” The link is to a session given at PGCon this week, http://www.pgcon.org/2008/schedule/events/87.en.html
The description of the session says “Upgrade from old PostgreSQL version to the new is now possible only through data export and import. PG upgrade is a project which should allow to upgrade database on-fly or with minimal down time.
Database upgrade is not easy task. Session will show current situation and it will focus on each affected parts of PostgreSQL and its conversion process. Presentation will start with storage module. Difference between page versions will be described include a way how to convert page structures and tuple headers. Second very important part is system catalog. Each new version of postgreSQL usually has modified system catalog structure. Catalog contains very important data for postgreSQL run and its conversion is very complicated. Talk will show some possible ways. Data types on disk structure conversion is last big part of upgrade process which will be presented. It also include varlena encoding change.”
Xaprb
24 May 08 at 8:37 am
Very interesting Article including the comments.
A friend of mine works on some Java ORM tools which connects to a whole slew of DB’s and his take on the fastest open source DB is MaxDB aka SAP DB (http://en.wikipedia.org/wiki/MaxDB). He said it out performed MySql, PostgreSQL and the big boys like Oracle, IBM and M$ SQL. Okay this was over a year ago and it ran on normal servers not some super-grid clusters. This DB is meant to handle a high volume of transactions. It was based on a DB still used on mainframes at most of the big banks called ADABAS.
Another good DB to be considered is Firebird (http://en.wikipedia.org/wiki/Firebird_(database_server)) which became an open source fork of Borlands Interbase. I used it for a long while and it severed me well – The beautiful thing about it is that once it was set up and running, it required very little support or management – low cost of ownership.
I’ve always believed that each job requires the best tool and not every tool will work for every job.
Wavesailor
28 May 08 at 1:56 pm
Issue handling is where you’ll experience /the/ key difference.
If you have an issue with PostgreSQL, you post it on their mailing list, and get written off by arrogant and better-knowing developers. Agonizing!
If you have an issue with MySQL, post it to bugs.mysql.com, and you get a friendly, professional and timely response. Response not good enough? Post again, and the issue is escalated immediately to a developer who knows more about the specifics you care about. In the end, if there is a bug, it gets fixed, if there is a design issue that is working against you, helpful information on if/when MySQL will do something about the issue at hand is handed to you. Soothing…
So there you have it :-). A professional attitude (MySQL) vs professional arrogance (PostgreSQL) is the key difference.
(PS. I know this doesn’t apply to Mr. Schwartz, being as he is an über-über-über-professional and is actually able to get the attention of the PostgreSQL folks and even make them listen. It does apply to the rest of us, though, being only mere mortal single-über professionals.)
eniemenie
4 Jun 08 at 10:46 am
über is one of my favorite words, but I’m afraid I have no more clout than you do :-)
Xaprb
4 Jun 08 at 10:54 am
I’ve gotten pretty good response from the postgresql folks. I remember there was a Unicode-on-Windows issue a while back. They fixed it very quickly.
Ricky
6 Jun 08 at 2:48 am
But the reason that MySQL is generally more popular is that it was available and supported on Windows earlier than Postgresql. Lots and lots of people have been using Windows as a dev machine, even for Linux deployments.
Ricky
6 Jun 08 at 2:50 am
I work with both DBs and find both very easy to work with.
I can’t see any reason to think PG is harder to use. Maybe it was different years ago.
MySQL seems more targetted at newbies, and PG at more technical people, it’s true.
I don’t like the commercialism of MySQL – PG seems more open source and community based – so will probably go with PG. The latest version 8.3 seems to outperform mysql for my simple application on my hardware at least.
Apparently Skype runs on PG because PG scales much better than MySQL as you add more CPU cores.
Beniji
6 Oct 08 at 8:25 pm
In terms of performance of MySQL vs Postgres it now seems that Postgres is much faster for the traditional “read” loads that mysql used to be great at.
See page 15 of the following PDF for example (the PDF is an interesting read in itself) where the FreeBSD team found that PG was about 40% faster!
http://people.freebsd.org/~kris/scaling/7.0%20and%20beyond.pdf
Jackson
17 Oct 08 at 2:20 pm
I’m lucky that I started Web programming using PostgreSQL, I just don’t remember why.
I once made a big portal, and the client required the project to be in MySQL. Well, I tried to use all the advanced features I always used with PostgreSQL. What can I tell? The database goes down at least 2 times/week. On the other hand, all my PostgreSQL applications and websites NEVER went down.
I don’t know if I did something wrong with that MySQL architecture or what, but when I think in MySQL I feel like stopping developing forever, it feels like is somewhat taking my knowledge away, “use this hobbyist tool”.
Alfred R. Baudisch
19 Oct 08 at 6:27 pm
You know, I’m more convinced than ever that it’s how people FEEL in many cases. And there’s nothing wrong with that — I FEEL happier using Free Software, and that’s among my most important reasons to do so.
Xaprb
20 Oct 08 at 10:17 am
It all comes down to this:
MySQL is a way cuter name than PostgreSQL. I don’t even know how to pronounce PostgreSQL, and whenever I try to pronounce it I feel stupid and/or pretentious.
I’ve used both. Not an expert in either. But as far as I can tell, both are awesome. And I also like Firebird, which is underrated. However, it’s definitely true that MySQL has momentum behind it. More web hosts offer MySQL, for example.
They should change the name of PostgreSQL to FastSQL or RadSQL or NeatSQL or UltraSQL or KillerSQL or FireSQL or something like that. Oh! I’ve got it! SexSQL. Then do a marketing blitz to make people believe that SexSQL is already the world’s most popular open source database.
James T. Jirk
8 Dec 08 at 7:25 am
My experience:
– tried Wamp (Windows, apache, mysql, php, 2 mins to install): 2 mins later, I was inserting from php page… wow, I could insert invalid data without error!
– tried Wapp (windows, apache, PostgreSQL, phph, 3 min to install): 2 mins later, I was inserting from php page…wow, error message: value too long for type character varying(60).
– MySql throwed away 20 seconds later
At work I play with Sql Server 2K5, for me data consistency is in first place
Alberto Piva
10 Dec 08 at 8:03 pm
1) I think the main reason that MySQL is more popular is that it’s more friendly in a shared user environment for these users that want to run there Web-apps on it.
2) Most MySQL people I spoke to don’t have a background in SQL, but needed a way to store there data (not talking about relationa data) Like xenoterracide is mentioning is that most web-apps are not relational in design to start off with (this prooves this statement a bit).
But most of all 1) PG is much harder to configure in such a environment.
I have work with MySQL and PostgreSQL extensively but I do find PG much easer to user, specially when using something like PGAdmin III (phpmyadmin is just crappy to work with).
Ries
Ries van Twisk
12 Jan 09 at 10:52 am
I’m amazed at the number of comments I see from mySQL folks (including the original author!) whose reactions to PostgreSQL are based more on fear of what it *might be like* than on an actual review of the evidence. Please, people, give it a try and make up your own mind.
For myself, I switched from mySQL to PostgreSQL about 2 years ago and have been much happier since. I initially switched because I needed spatial features for a project involving geocoded data, and mySQL’s implementation of spatial features is so bad as to be unusable, whereas PostGIS rocks. Then I wound up doing a project in mySQL (not by my choice) where we needed stored procedures. That experience scarred me so much as to completely put me off mySQL — the (one) procedural language is, as far as I can tell, *broken* for anything non-trivial. (PostgreSQL, OTOH, offers you a choice of procedural languages, and the SQL-based one is much more powerful than mySQL’s.)
Also, mySQL makes you choose between speed and referential integrity. PostgreSQL doesn’t — generally speaking, you get both in the same storage engine.
I’ve heard that PostgreSQL clustering doesn’t work as well as mySQL’s, but I’ve never worked on a project that used clustered servers (not even the million-record mySQL DB…don’t get me started…), so that hasn’t been an issue for me.
About the only things I wish PostgreSQL would adopt from mySQL are easier setup and ON DUPLICATE KEY UPDATE.
Marnen Laibow-Koser
16 Jan 09 at 2:06 pm
Wow what a discussion ranging from “I’ve used both 10 years” and “I’ve used xyz 2 years ago”…
just to tell you lads, NOW…not in the past. Like Marnen mentioned, try PostgreSQL first.
and then there is a price, last time I checked MySqL Enterprise Silver cost 2 thousands $ per year/server whereas Postgre cost …well, it is free.
Sometimes the better are free (please don’t tell me there is a free MySql or I shoot you).
cowgaR
28 Jan 09 at 5:48 am
knowing what I known now if I have a choice I pick PG.
mySQL does some things that PG won’t that I sometimes wish we had, but mySQL just cut it for our use.
I could get the performance on mySQL that I can get on PG (after I have tuned it)
PG will use multiple indexes if warranted when planning and executing a query. (not sure if mySQL does at this point or not)
PG has never hosed my data.
PG is always free
dvlhntr
5 Nov 09 at 11:21 pm
I’ve used both Postgres and Mysql and unlike most developers love them both for very different reasons.
It’s comparing apples to oranges. MySQL aims to be simple. Postgres aims to be advanced. The one isn’t better than the other, it solely depends on what the requirements are.
Johandk
12 Dec 09 at 3:54 pm
from my experience,
Mysql is for smaller and least complex projects, while Postgres is for the other.
Mysql has horizontal but Postgres has steep learning curve, for the beginners.
santosh
7 Mar 10 at 10:25 pm
Often I hear MySQL is simpler then PostgreSQL, yet I never could understand why MySQL is simpler.
I have worked extensively with both and still I fail to see why people find MySQL simpler. I find them both as simple to use and I don’t see why MySQL is simpler.
If you look deep down my heart however, I feel that PostgreSQL is simpler to sue then mysql for the following reasons:
1) PG puts out clear error messages when something is wrong, MySQL doesn’t output anything or just try to make up something in comparison to PG. This makes PG easer to understand when teh DB shows values different then what was inputted
2) explain analyze shows much more detailed values in PG then MySQL’s explain making it easer to optimize.
3) pgAdmin rocks making PostgreSQL administration a beeze, I couldn’t find a good open source or free tool for MySQL doing the same, honestly phpmyadmin simply sucks…
4) PostgreSQL default access policy is simpel and secure while MySQL’s default adds root for one reason or the other to access all databases, making MySQL more hard to secure properly on default installations.
5) My personal experience is that MySQL documentation is more obscure then PostgreSQL’s version.
6) Creating solutions in PG is often easer then MySQL. For example if you need a transaction save DB with full text capabilities then MySQL fails to create this without using external tools, InnoDB and FTS doesn’t work, in PG that’s a breeze.
Often is said that PostgreSQL installation is a harder because of the initdb step, but honestly read the fine manual!!
Ries
rvt
8 Mar 10 at 12:21 am
MySQL is used by a lot of people who own a very small web sites and is not a developer, just a some kind of advanced user. In general it is comparison of apples and oranges. They should be compared in a commercial context.
John Ibn Smith
15 Mar 10 at 5:58 pm
I have worked with MySQL for the last 7 years and built a number of projects, but particularly in one finance application, when data increased over 4 years, I needed advanced features and the move to PostgreSQL has been inevitable.
Put simple, if you are handling small apps or apps that are not data-heavy, MySQL is for you and if you want to deal with a programmable database, PostgreSQL is the one for you!
Egesa Ronald Leonard
17 Apr 10 at 4:14 am