Comparing MySQL and PostgreSQL 2

Mr. Jax writes "6 years ago Mr Poet submitted the story Comparing MySQL and PostgreSQL. Since then both databases have evolved to wherever they are today. Are the points raised 6 years ago still valid? What has changed? Are there other things to consider since then (e.g. licensing)?" This is certainly a valid question since both databases have had to evolve with the times. Have these applications been specialized to fit a particular niche market or are they both still strong competitors? What does the horizon look like for the development of these programs, especially considering the recent MySQL partnership with SCO?

Comments

Get off it ScuttleMonkey

[Anonymous Coward]

SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company.

Re:Get off it ScuttleMonkey

[Saeed al-Sahaf]

No, it's like MySQL _sold_ them something.

Well, MySQL AB is a for-profit company, they sell things to people. And, last time I looked into it, SCO wasn't gassing people or mowing down rain forrest, or something. Sure, they are obnoxious, but the truth is, so are many commercial companies we deal with every day.

But does it even matter? The jokes on SCO, they paid to use something in a product very few companies will buy.

More fun than dragging out this, would be guessing what non-issue Slashdot will toss up on their front page next in an attempt to stir shit and boost their dieing readership?

Yawn....

[truckaxle]

This is a prefect flame fest topic - great scheduling for a vacation day. I will venture to guess the posts will be well into the 1000's Now what does it matter if Mysql partnered up with SCO. SCO as a O/S provider is history may a well extract a scraps of meat from the bones.

Another question

[Eightyford]

Sure it's slashdot and we all love free software, but how do these two compare with oracle, sql server and other non-free db's?

Re:Another question

[Dogtanian]

Oracle has the 'non benchmarking' clause that prevents you from doing that.

Then arrange to have the benchmarking done in a country which won't uphold anti-competitive bullshit clauses (and when Oracle protest that the license lets them sue the guy in the jurisdiction of Buttfuck, Illinois, will tell them where they can stick their extradition request).

Although I reckon such a case (brought by Oracle) might still get thrown out in a US court, I wouldn't bet my life savings on that, and the US legal system means you're unlikely to get fees paid if Oracle lose (does this *ever* happen in the US?); a great way for the large company to effectively win by attrition if the benchmarkers don't have that much money.

Re:Another question

[Stinking Pig]

SQL Server gets a lot of flack on /., I'd be interested to know why? I've worked a fair amount with it, Oracle 8 and 9, Postgres, and a little bit of MySQL. I've also done extensive benchmark testing of SQL/Oracle/Postgres handling the same load on the same hardware (shh, don't tell Oracle :).

My experience leads me to beleive the following things:
1) MS-SQL is a high quality database that is ridiculously easy to set up, tune, and maintain. It is also very expensive.
2) Postgres is a high quality database that is ridiculously easy to set up and maintain, but fairly difficult to tune. However, its performance is just as good as SQL server as long as you stay away from nested loops(*). It is also fairly inexpensive (free license, but increased TCO).
3) Oracle is a pig, and it requires a professional, certified swineherd. If you spend an amazing amount of money on licenses, gear, and certified DBAs you will presumably get good performance; I however was never able to get it past 60% of the performance of MS-SQL or Postgres.

(*) Nested loops are like candy to SQL server, and I've heard this is the same for Sybase (understandably). Deep sets of nested loops will kick the other databases I've tested in the teeth. Given an instruction with several nested loops and 16 million rows of data, I got results from SQL server in 5 minutes, results from Oracle 9 in an hour, and results from Postgres in 18 hours. This was a year ago and Postgres has changed, so it might be better now. Does MySQL handle them well?

Re:Another question

[huiac]

Please, don't take away my stored procedures. They give me:

    - Complex column & table constraints;
    - Rapid retrieval of rows satisfying particular conditions, by indexing on a complex expression;
    - Programmable actions on insert/update/delete (triggers)
    - Updateable views

huiac at internode.on.net

Re:Another question

[einhverfr]

Admittedly, this has a lot ot do with my style - I'm old school enough that I write my logic in C, C++ or Perl and use the database purely for storing and retrieving data. DBMS vendors (and some database researchers, to be fair) would like coders to do program purely with database packages. I've always though this a supremely boneheaded idea - I trust database designers to design databases, but not progamming langauges thank you. However, if that approach appeals, then you probably need a lot more features than I do.

The best approach IMO is somewhere in the middle. I think both extremes are boneheaded.

Your RDBMS is there to do three things:
1) Store data
2) Maintain data (i.e. triggers, check constraints, etc)
3) Present your data (views, possible some stored procedures, etc).

I generally try to avoid programming directly against stored procedures. If necessary, I will write a view and update rules/triggers (depending on the RDBMS) to make this appear like a table.

In general your data model logic should be entirely contained within and enforced by the database (Date's Central Rule). This does not mean that application function should be a stored procedure. Far from it. Instead, by doing things this way, you can do a fair bit of heavy lifting in the RDBMS where appropriate, and have your application logic in the application where it belongs.

For example, sending email from the database backend is not really the best solution to any problem I am aware of in large part because this would happen outside of transactional control. Instead, use a trigger to place an item in a queue that an external program can use to send it. This way if your transaction rolls back after the email is sent.... ;-)

People on both extremes simply don't understand the features and limitations of the transactional and relational models.

Re:Another question

[Aceticon]

I've worked for several years both creating programs inside the database and on a server layer outside it (and also just about every other layer).

I have to agree with grassbeetle above.

Software architecture-wise:
- You can't make a scalable architecture if you put everything in one single place (in this case the database).
- You will be hard-pressed to create a failure tolerant architecture if you stuff everything in a single point of failure.
- Databases are NOT application servers. They are designed with data storage and retrieval in mind, not reliable execution of complex business logic. Amongst other things databases do not make available in an easy and/or reliable way some of the standard application server functionality.
- All external components of the application (for example UIs) have to connect to the database. You're now stuck to using the connection protocols from the chosen database. This might cause all sort of problems with security, firewalls, use of asychronous messaging, availability of adaptors in the platform you are deploying your applications to, etc...
- Spliting your application accross several servers or in a multi-tiered geographical distribution is much harder.
- All coders have to have a good knowledge on how to work with the specific database you are using.
- Programing inside databases is not standartized. Different databases and indeed different versions of the same database have sometimes different versions of the same language or different libraries available. The language/libraries have not been so throughly used/tested/examined by a big user comunity (while for example standard C/Java/etc libraries have been thouroughly debugged in billions of man-hours of use). This means more library bugs and a lack of third party tools for software design and development inside the database.
- Facilities such as version control, source control, etc are either not available or difficult to use in a reliable manner.
- Availability of compatible 3rd party libraries or application modules is very, very restricted by comparison to NOT having your server side logic all inside the database.
- Forget about moving databases in the future. Also, simple migrating to a newer version of the database can be a nightmare.

Software design-wise, the design of the software will be strongly constrained by the internal structure of the database:
- Information flows will mostly have to be database-like information flows
- A true object oriented structure is pretty much impossible. At the most you can do weakly connected islands with an objecte oriented structure. If the database language you have to use is procedural forget about OO design.
- Server-side initiated connections to outside entities, thread control, ditributed transactions and other more advanced functionalities are pretty much impossible.
- Usage/integration with 3rd party libraries or application modules is very hard or even impossible.

Software programming-wise, and from my experience (mostly Oracle):
- The language sucks.
- The application libraries (not the DBA ones) suck big time.

Simply put, a software architect that puts all server-side logic inside the database is with this single choice removing almost all his other architecture options and creating/fortifying vendor lock-in of the application to the database itself and 3rd party tools and also of the development team itself by means of the knowledge experience they have/will gain with said database and said 3rd party tools.

Such a person should IMHO either be demoted to a place were he/she can't cause any damage or fired outright.

Re:Popularity

[mellon]

MySQL is like Microsoft. It's not entirely compatible with the standard, but everybody is using it, so if you want to use their software, you have to use it too. I have a copy of PostgreSQL and a copy of MySQL on my server, because Wikipedia doesn't work with PostgreSQL. I presume this is because the developers started working with MySQL back in the bad old days when it was _really_ incompatible, and their code now contains dependencies on MySQL.

I don't really know what to say about all of this - these incompatibilities are really frustrating as an end-user of this software, but I understand that it's hard to make things work with both MySQL and PostgreSQL, and resources are limited. What frustrates me is that these incompatibilities create a form of lock-in - once you've based your app on MySQL, you are stuck with it.

I suspect that if you were to start now, and to use the SQL spec rather than the MySQL documentation as a reference while doing your development, you would wind up with something that was a lot more portable, so this isn't actually an argument against using MySQL. It's more an argument towards sticking to standards when using whatever db you choose, so that when the time comes to use a different DB backend, you aren't faced with a monumental refactoring job.

Re:popularity

[Jerry]

He didn't seem to be at all bothered that this the main argument people give for using windows.

That's probably because those features are part of PostgreSQL and is the main argument for why people believe that PostgreSQL is overtaking MySQL. Also the fact the PostgreSQL can run PL/SQL with only some modifications, and visa-versa.

I used to like MySQL

[caluml]

The DBAs I worked with always told me "Postgres is better". But I tried it a good few years ago, couldn't install it, it didn't "just work", and I was not that good with Linux at the time, so I just moved on to the next thing - MySQL.
MySQL was good enough, and all the stuff that hardened DBAs said to me - "It doesn't do transactions", or "It handles NULLs wierdly", etc, just didn't apply.

But when I tried to do a query like this: SELECT * FROM foo where bar NOT IN (SELECT blib from wheee) - MySQL advised me that it "didn't do" "NOT IN" queries. I tried to work around it, but after trying all the JOINs I could, it just didn't seem like something that I could get round. (I wasted quite a long time trying to work around this, and although I'm sure that some really top DBAs out there can do it, I couldn't.)

So, mysqldump > mysql.dump, and then restore into Postgres. :%s/mysql_/pg_/g in all my PHP files. Change mysql_error to pg_last_error, and fiddle with pg_num_rows, and it all worked. Moreover, one huge query that took 25 seconds to complete in MySQL (lots of JOINS and nastiness) took about 1 second in Postgres.
I've never looked back. MySQL is now just coming to fill in all the gaps it's missing - but just go with Postgres. It's rather good.
No mention of SQL servers can go without the Gotchas: Mysql and Postgres. The worst MySQL is probably that it modifies data as you insert it without throwing an error. Yuk.

Re:Popularity

[TheMMaster]

I hate to burst your bubble here, but ALL dbms's are slightly different from eachother, this is largly because the SQL spec leaves room for these kind of things.
It's not like there is a HUGE difference, as long as no dbms specific procedural language was used (think plsql (oracle)) it's pretty trivial to port an application from one dbms to the other
The biggest differences are usually pretty subtile and indeed rather frustrating, but by no means hard to solve.
The biggest and most 'incompatible' difference between postgres and mysql is the autoincrement field really, the rest is just small fish to fix.

Not exactly ...

[khasim]

If I go to the store and buy a copy of MSOffice, that's one thing.

If I get a site license from Microsoft, that's something else.

If Bill Gates and I do a press release about our new partnership, that's an entirely different thing.

SCO and MySQL AB did the press release thing. That's not the same as SCO buying a license to distribute.

Re:Not exactly ...

[Quarters]

Press releases have no bearing whatsoever on the level of business relationships. Press releases happen if someone (or some-company) has enough money (~$300US) to do a wire release and has something to say. They're just an advertising medium. Nothing more, nothing less.

I could do a press release about how I just bought a tube of toothpaste at the local Kroger. The wire service(s) would happily take my money and put the story on their distribution network(s). Big whoop.

Mysql is very isp friendly

[Billly Gates]

The one good thing I have to say about mysql is that its multi-user friendly for hundreds of accounts.

For a mom and pop ISP with only 3 or 4 employees this is significant. Is it feature filled? No

Its just included in the default user account which is difficult if not impossible with posgresql unless you manually install it for each account.

Users on the web dont need something heavy unless they are a commercial website. Also there are a ton of php and perl scripts and tools for users to use.

This is why msql is so popular. Its what ISP's prefer.

Message bearer

[burnin1965]

True, but then again it also depends on who is bearing the news as well.

Considering you are nobody your press release wont really mean much. And I would also go so far as to say The SCO Group are nobody as well and their press releases don't mean much.

However, if Kroger made a big deal about your toothpaste purchase in the news section of their website it may actual be something to consider.

When SCO made their press release I didn't pay much attention because I have become very skeptical of any messages that come out of their organization. But it is disconcerting when MySQL considers the partnership to be news worthy as well:

http://www.mysql.com/news-and-events/news/article_ 948.html

burnin

Re:Popularity

[kryonD]

"I hate to burst your bubble here, but ALL dbms's are slightly different from eachother, this is largly because the SQL spec leaves room for these kind of things."

I hate to burst your bubble, but the differences have nothing to do with a poorly designed spec. They have everything to do with companies either being too lazy, or too stubborn to adjust to and adhere to the specs. Even M$, who created the ODBC standard fails to adhere 100% to it in both Access and MS SQL.

In their defense however, DBMS's have often evolved far faster than the specs could keep up resulting in dozens of different ways to do something the specs didn't originally cover. However, my forgiveness ends right where the spec catches up and then the dbms developers fail to add compatibility to their product. The fear that compatibility will leave room open for customers to migrate to a competing product is exactly why the USA is about 2 to 3 years behind in technology right now. Go to NTT Docomo's website and look at their newest line of phones and you will note two very distinct trends:

#1 The lowest model blows away all US phones.

#2 They all share the same baseline standard design...which forces them to compete in the non-standard areas...which rewards the consumer with a consistant design and interface and innovation.

Wake me up when companies (or even OSS project managers) in the US stop screwing their customers with proprietary interfaces designed to lock in their customer base. I really don't measure a product's worth by the number of users it has....otherwise I'd still be using IE.

Just compared MySQL 4.0.12 vs PG 8.0.3

[adturner]

Short story, mysql.com's interpretation of the GPL is frankly a lot more strict then mine or my reading of the FSF's FAQ on the GPL. If it wasn't for that, I'd still be using MySQL for my company's application since I'm more familar with it.

Anyways, PostgreSQL IMHO has some things going for it:
- More features like triggers, stored procs, schemas, subselects, etc then the current stable version of MySQL supports. About the only thing I find myself using are subselects which are just a nice to have.
- Attempts to be "safer" with your data via WAL, etc. Good for unreliable environments.
- Tends to follow the SQL standards closer then MySQL
- Is BSD licenced so you don't have to worry about licensing issues.
- #postgresql on freenode is great. The people there are intelligent, knowledgeable and friendly if you're not an *sshole. They've helped me a lot.

The problems I have with PostgreSQL is that:
- INSERT is very slow (about 3x slower compared to MySQL/InnoDB) for my dataset. The "answer" is to use the COPY command or disable your indexes/FK's which is f*cking lame since you loose all your relational integrity. I was willing to trade off performance for disaster prevention (system crash, power failure, etc) by disabling WAL, but you can't actually do that.
- The OSS tools available aren't as good for postgres as they are for MySQL. I've yet to find anything as nice or complete as phpmyadmin for Pg or something that supports schema's for ER Diagrams. Frankly, I'm sick and tired of designing my DB in vim.
- Having to run vacuum all the time to help the query optimizer figure things out. Why this doesn't happen automagically in the background without me having to worry about it is beyond me.
- In general, I find the documentation on mysql.com superior to on postgresql.org, but #postgresql more then makes up for it.

Frankly, all the technical "problems" in MySQL or Pg can be worked around if you're willing to think out side of the box.

Re:Haw haw

[schon]

Truncating varchars is handy for webforms... you don't want to reject the data..

Yes, I do. That's why I set the length in the first place. If I wanted to truncate the data, I'd tell the DB to do that.

if you REALLY need the logic to reject things like blank inputs in web forms then you should be doing that in your application logic anyway

This is the problem with MySQL's cheerleaders - they believe that the app designer should re-invent the wheel, rather than expecting the DB to do the stuff that it's supposed to do.

Why on earth should I have to write extra code to check each input field, when I should just be able to send the results to the DB, and return the error message to the client if it fails?

just trying to relay the idea that in the context that mysql is usually used, these small quirks don't have a large impact.

The only reason that people believe that they don't have a large impact is because they don't actually understand the *reasons* for the correct behaviour. The attitude is "well, I'm a programmer, so I'll just program around the problems", rather than expecting the DB to handle it (like it's supposed to.)

Re:Haw haw

[slamb]

but usually the data isn't important enough (like a slashdot post) to really care if a couple words get chopped off by accident

That is exactly the MySQL attitude. The problem is that my data tend to be important, or I wouldn't be putting them in a RDBMS. I'd rather the database assume the usual, safe thing - data should not silently truncated. If I want to throw away my data, I'll do it explicitly.

And filling in NOT NULLS is also handy for the same reason.... if you REALLY need the logic to reject things like blank inputs in web forms then you should be doing that in your application logic anyway....

What I REALLY need is for the database to pay attention to the constraints I specify. If I didn't care if these fields were blank, I wouldn't have said "NOT NULL".

I am by no means sticking up for non-conformity... just trying to relay the idea that in the context that mysql is usually used, these small quirks don't have a large impact.

That is not the context in which mysql.com is claiming their database is usually used. They claim they have a real database.

PostgreSQL SCO "relationship"

[tangledweb]

So what exactly is the difference between the MySQL-SCO relationship and the PostgreSQL-SCO realtionship that were announced at about the same time?

MySQL has only one commercial vendor, who helpfully call themselves MySQL AB, so even Slashdot readers can understand what they sell. So SCO made a deal with them to compile and test a certified MySQL binary for SCO.

PostgreSQL has had a number of failed commercial vendors over the years, but one current one is EnterpriseDB. Maybe not having the word PostgreSQL in the company name confused slashdot readers who think Walmart sell Wals?

eWeek report it as the same deal. "SCO has added open source database vendors MySQL and EnterpriseDB to its partner list, said SCO President and CEO Darl McBride"

What is the difference?

Oh, I forgot. This is slashdot where MySQL is evil because they charge for some things and where we all sit around and pretend that MySQL does not have transactions and that PostgreSQL vacuum is a good thing.

Yay for Postgres/Perl. Boo for MySQL/PHP. Can I have mod points now?

Re:It really doesn't matter.

[musicmaker]

An classic example of a MySQL Idiot. You put things in the database so that they are _atomic_. You want things to either compmletely succeed or completely fail. Most application layers cannot do this successfully, and you end up with corrupt data.

And go ahead, tell my that having corupt data doesn't matter. Then I'll throw sarbanes-oxley at you and laugh.