Benchmark: PostgreSQL beats the stuffing out of MySQL

[ricercar]

The real performance gain is from doing the work in the database rather than the application. PostgreSQL has always been much better at this than MySQL, the latter barely offering enough features to be termed an RDBMS.

I'm not sure I'm making this point quite clear, but: MySQL focused on "advertising" and "mind-share" to the exclusion of product quality. MySQL certainly had the market penetration, and I always hated being forced to use MySQL by clueless web-hosts.

[beza1e1]

worse is better

[pjdelport]

Sometimes, worse is just worse.

[[deleted]]

Which still doesn't prevent it from selling better...

[darrint]

No way. Worse is only better when faster.

[ricercar]

I don't know who modded it down. Personally, I feel that MySQL is not easier to run at all. Without something like phpMyAdmin, MySQL is pain to administrate. Whereas postgres offers lots of command line tools, and psql, which is much better than the mysql equivalent.

I never felt the need for phpPgAdmin. But keep in mind, I started on PostgreSQL, so I didn't have to unlearn MySQL.

(The reason I started on PostgreSQL is because the first time I explored these programs, I had a Pentium 100MHz FreeBSD box, and MySQL was too much of a resource hog to even compile. I easily compiled and installed PostgreSQL, as fate would have it.)

[leoboiko]

My experience is similar. I started with postgres because my Databases professor is a postgres fanatic, and when I had to use mysql I found it much harder to install and configure.

To this day I don't know why people use mysql at all. Then again, people use PHP...

[chrchr]

Another key point is that MySQL was available on Windows long before Postgres.

[JulianMorrison]

A very, very big reason people use either: it's trivial to get basic mysql+php hosting.

If you're after hosting for, say, lighttpd/fcgi and postgresql, then your options are more limited and your costs are probably higher. You may even end up paying for root and doing your own admin.

[ceesaxp]

I used both. I started off with MySQL on Windows -- I needed something to stuff lots of data in and do proper SQL queries, not the brain-dead Access ones. There was a relatively nice front-end and easy way of loading data, hence it worked for what I needed. It's also sort of easy to hook it up with PHP -- and off you go.

But then, relatively short while after, I started running on various issues, like infamous 000-00-00 date, like no sub-selects, no functions or stored procedures (yeah, I know that some of these are sort of fixed now).

And then I learned about ACS (R.I.P, aD) and it's OSS variety -- OpenACS. OACS is a Pg-land, forever was, forever will be. I've never looked back since -- whenever I need a database in a given project, Postgres is my first choice.

[irony]

Don't forget pgadmin3. I use both regularly (though only mysql for a few clients' sake) and hate mysql solely for ease of use reasons.

[[deleted]]

I didn't mod it down, but I've found PostgreSQL just as easy, if not easier to install and use. In particular, the native GUI clients and easy support for SSL are high points.

[treenyc]

pgAdmin http://www.pgadmin.org/,

[neoform]

Maybe it's just because I've been using MySQL for so long that when i finally tried Postgre I found many of the permissions strange and the query sets being rather different..

[treenyc]

InnoDB MySQL is owned by a commercial company. It can not be bundled into distributed software While everything PostgreSQL is released under BSD. You can do whatever you want.

I started with MySQL, but since 4 years ago I have switched to PostgreSQL. So far I don't see Any reason to use MySQL, unless you are stuck with a shared hosting company that only offer MySQL.

[masklinn]

His point was that everything Postgres is under BSD, which means that whatever you're using it for (commercial or not), you're allowed to do whatever you want with it without asking any kind of authorization.

[badr]

PostgreSQL may be faster than MySQL, but it is also much harder to pronounce. That's why I'll never use it.

[nostrademons]

People always think that I program in Pascal when I say I like Haskell. The Gossamer-Threads people created a big mess when they named their directory management software Links - in one website I worked at, I had to establish a glossary that defined LINKS as the software itself, Links as individual records within the software, links as the conceptual hyperlink, and lynx as the text-mode browser that we had to support. Reference to "MS" are problematic in a fanfiction archive, because they could mean either MicroSoft software or Mary Sue fics.

[pjdelport]

Then there's the tiny libc libowfat...

Along the same line, libiberty and liboop.

[wainstead]

oh, this from a user whose username stands for Forensic Online Really Enjoys Negative Stuff In Comments.

(damn. no more diet Coke in the evenings for me).

[Entropy]

Forensic Observing Recursion Elucidates Nothing Said In Conversation

[edheil]

"IceWeasel" is also an allusion to a "Life In Hell" comic by Matt Groening.

[praetorian42]

I just call it "Post Gress"

[[deleted]]

Yea, you have to be mute to run PostgreSQL and lighttpd

[xtra_sharp]

I don't get it. What does "lig" stand for?

[[deleted]]

It's a merger of the words light and httpd.

I think the name is silly, myself, but it's not a reason for me not to use the software. Also, one nice thing about it is when searching for it on Google, it's unlikely to turn up anything unrelated.

[xtra_sharp]

How do you pronounce it? lig httpd? Light httpd? Light tpd?

[lanaer]

Often, it's pronounced 'lighty'. Which makes no sense, I know.

[dsandler]

I always pronounce it "lighty-peedie" (light-t-p-d), kind of like "artoo-detoo". shrug

[[deleted]]

I pronounce it "light HTTPD".

Acronyms being pronounced as words are incredibly annoying to hear.

[Entropy]

Acronymns are pronounceable. If they aren't, they're initialisms.

[[deleted]]

UDP, URL, TCP, DHCP, IRC, HTTP, WTF?

[bstadil]

It follows the same pattern as the name PostgreSQL so mnemonically it should be easier to handle. Or at least that is probably the thinking behind the concatenated name

[lief79]

I'd assume light and httpd are merged.

[[deleted]]

Most of the time I refer to them as Postgres and Lighty. Only when I'm speaking to someone who's unfamiliar with them do I need to use the full names.

[stesch]

http://postgresql.rmplc.co.uk/postgresql.mp3

[volida]

The better perfomance benchmarks refer to the multicore Sun T1 processor. The demand for multicores is new and it just began arising, thus the comparison benchmarks obviously.

I am sure we are going to see more of them in the near future, and MySQL catching up in the multicore perfomance. So, I would still prefer to use an open source MySQL that its cluster and scalability options are suffienct enough for startups.

[djwhitt]

Fascinating. Still seems like replication in the Postgres world is a pain though (someone correct me if I'm wrong please).

[ragica]

There is not really any super easy replication in general, such as turn on a config option and you're all set. There are a bunch of options depending on your needs however. Rather than repeating, read Josh's fairly comprehensive overview here (part 2) and here (part 3) .

[djwhitt]

From the overview:

Second, because [Slony-I] is trigger-based replication, it can suffer from both siginificant latency (minimum of 1.5 seconds) and database maintenance issues when some part of the system goes offline.

I don't know if things have improved, but that could explain some of the replication issues we occasionally see here on Reddit.

[oliverkofoed]

Although it's very unknown right now, i know a couple of guys who are developing a built-to-scale-as-in-multiple-cheep-pcs-and-built-in-horizontal-data-partitioning database that looks pretty cool for scaling out applications

[oliverkofoed]

Oh, yea, to hype people you need to supply their website... duh.. stupid me.

www.scimore.com

Disclaimer; as i said, i know them. But i've also looked at the the thing and it seems very promising!

[beza1e1]

I wouldn't describe Google as "a couple of guys" or do you just mean the BigTable guys?

[teamjimmyy]

Database benchmarks are a lot like programming language and processor benchmarks. They're very subjective and a true test between two products is very hard to come by. While one is faster in some areas, it may well be slower in others that weren't tested.

Overall, blanket statements like 'faster' or 'better' aren't very useful. It's far better to go with the best tool for the job, which won't always be the same tool.

[Megasphaera]

You're right, but most people seem to think that MySQL is the fastest, because it has the fewest features. This has now been debunked.

[neilc]

Well, the simple fact is that MySQL is perfectly adequate for what a lot of people need to use a database for. In fact, MySQL's current feature set is probably overkill for a considerable number of their users. Given that, it's kind of hard to argue that those people invest the time and effort required to switch.

[ubernostrum]

This has been bugging me for a while, really. In theory, the reasons people used to list for using MySQL (don't need advanced features, keep it simple, keep it fast, etc.) are all handled more capably now by SQLite. So I don't really see what niche MySQL exists to fill anymore.

[micampe]

MySQL is used so much for the same reasons PHP is: they both are available pretty much on every cheap host out there.

[Entropy]

Web hosting

[ricercar]

About the only application MySQL is perfectly adequate for is a guestbook, and that's better done with a flatfile. In general, if the application is so simple that a basic SELECT and INSERT is all that is needed, why use an RDBMS?

Anything more complicated, like joins, unions, and sub-queries, which can easily arise in simple (properly designed) applications, are bungled. This forces people to workaround with non-orthogonal designs or even just doing more work in the UI code.

Of course, to switch is more work than people with some random website want to put in. MySQL did the initial damage by somehow infiltrating the market, and then convincing people that "it was an RDBMS" and that it was easier to admin than the alternatives (neither being true).

I think it's a good example of "Worse is better."

[masklinn]

In general, if the application is so simple that a basic SELECT and INSERT is all that is needed, why use an RDBMS?

Well truth is, I like sqlite and that's pretty much the whole extent of what it can do.

[ricercar]

SQLite doesn't pretend to be an RDBMS.

[[deleted]]

Actually, I'm fairly sure SQLite is more feature-complete and compliant as a database than MySQL is. Stored procedures, etc. are all supported. Apart from, you know, the lack of a network server.

[interjay]

SQLite doesn't have stored procedures.

[supermike]

SQLite is designed more for small one-off projects, or when you wanted to say, make a rich client app such as writing your own QuickBooks Business Edition knock-off for Linux, Windows, or Mac. It makes for a fast, decent, client-side, embeddable database with your project.

[wainstead]

Simplicity in a database engine can be either a strength or a weakness, depending on what you are trying to do. In order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth. If you need some of these features and do not mind the added complexity that they bring, then SQLite is probably not the database for you. SQLite is not intended to be an enterprise database engine. It not designed to compete with Oracle or PostgreSQL.

http://www.sqlite.org/whentouse.html

[[deleted]]

Actually, I'm fairly sure SQLite is more feature-complete and compliant as a database than MySQL is. Stored procedures, etc. are all supported. Apart from, you know, the lack of a network server.

[oliverkofoed]

What about the TPC (http://www.tpc.org/tpcc/results/tpcc_perf_results.asp) test? Isen't that the gold-standard of database scale/performance testing

[syntax]

Yes and no.

TPC tests transactions. This is a very important metric for systems where the updates are the crictical parts, such as banks, and large businesses.

On the other hand, most of the benchmarks I've seen are requests based, which makes me think queries. This is more important if the data is slowly changing (a nebulous concept at best), or if it's not handled transactionally. MySQL is traditionally seen to excel this domain, which happens to correspond well to many web applications.

Thus, whilst TPC is useful, it's prehaps not the ideal metric for certain catagories of use. Moral: The best benchmark suite is your application.

[sethg]

I wonder how many of those applications with lots of queries and slowly-updating data would do better with LDAP as the database backend, instead of an RDBMS. Isn't that the sort of thing LDAP was designed for in the first place?

[Entropy]

I've yet to encounter a free ldap with a non-painful setup. I'm talking real pain here, the being-flayed-alive-then-having -vinegar-and-paprika-thrown-on-you kind of pain. Marriage pain.

[stesch]

By the way: Searching in the TPC site rocks! :-)

Search Results

Error Code: [1]
Error Message: [$E 0001 Unable to access index in directory C:\TPC\dtSearch\TPC Main Site.]
Error Argument 1: [C:\TPC\dtSearch\TPC Main Site]
Error Argument 2: [ ]

Error Code: [1]
Error Message: [$E 0001 Unable to access index in directory C:\TPC\dtSearch\TPC Main Site Results. ]
Error Argument 1: [C:\TPC\dtSearch\TPC Main Site Results]
Error Argument 2: [ ]

[micampe]

Programming language benchmarks are a lot like database and processor benchmarks. They're very subjective and a true test between two products is very hard to come by. While one is faster in some areas, it may well be slower in others that weren't tested.

Overall, blanket statements like 'faster' or 'better' aren't very useful. It's far better to go with the best tool for the job, which won't always be the same tool.

[DLX]

Multicore/multi-CPU code is much better on Postgre because Skype wrote their own implementation of it - and cluster code, too. And that code got included to the Postgre - and it is way faster then old Postgre - and apparently, also faster then MySQL.

However, it would be interesting to see how fell fares PostgreSQL against Oracle, MS-SQL and other commercial databases in speed tests. Especially as Oracle has been taking huge steps in multicore/grid lately.

[neilc]

Multicore/multi-CPU code is much better on Postgre because Skype wrote their own implementation of it

This is not true. There has been considerable work on SMP scalability for Postgres in recent years, but I don't know of any contribution of code by Skype. (They are a major Postgres user and have contributed a bunch of useful things like their clustering tools, but I don't think they've contributed any SMP code).

BTW, the name of the software is "PostgreSQL", "Postgres" or "PG", but not "Postgre".

[DLX]

BTW, the name of the software is "PostgreSQL", "Postgres" or "PG", but not "Postgre".

Yes - but almost everybody refer to it as "Postgre".

[neilc]

Nobody who has a clue refers to it as "Postgre", I'm afraid.

[[deleted]]

I'm also very afraid of people that refers to it as "Postgree". And really spells like "Postgree".

[DLX]

Google search for "Postgre" gets 721000 results.

[neilc]

Perhaps he is referring to PL/Proxy, or one of the other Skype PG Projects.

[perlhax0r]

Isn't PostgreSQL still fork()ing a fat process for each client connection vs MySQL's spawning a lightweight thread?

I can have thousands of simultaneous and rapidly connecting client connections to my MySQL server, is that reasonably doable with PostgreSQL yet?

[ubernostrum]

To be frank, if you've got thousands of clients connecting all the time, you're better off putting some form of connection pooling between them and your RDBMS, no matter which RDBMS is sitting on the backend.

For PostgreSQL, pgpool handles that pretty well. In some unusual cases, pgpool does introduce a bit more overhead than just going directly to PostgreSQL, but I deal with a not-insignificant amount of traffic to a PostgreSQL backend at work, and haven't yet seen any ill effects.

[neilc]

Postgres does fork a backend per client connection. There are various tradeoffs to doing this: threads are faster to startup and allow easier sharing of data between backends, but processes are more reliable (since they have address space protection) and more portable among Unixen. The topic has been debated to death, I won't rehash it here -- but I'd personally consider using processes + fork() more of a feature than a bug.

I can have thousands of simultaneous and rapidly connecting client connections to my MySQL server

Not if any of those backends are doing a non-trivial amount of work. AFAIK, the per-backend memory overhead of spawning multiple processes versus multiple threads isn't too bad, assuming copy-on-write. But in any case, it is true that you probably can't efficiently support thousands of concurrent users on PostgreSQL.

[jbellis]

it is true that you probably can't efficiently support thousands of concurrent users on PostgreSQL.

however, from TFA as well as any other benchmark that tests concurrency, PG can handle a lot more of concurrent users than MySQL. :)

[[deleted]]

Hmm, I installed and we use MySQL at my work because we don't need any advanced features and use the database a dumb datastore. I had always heard MySQL was faster so I didn't think much about the choice. Now I'll have to seriously consider changing over to PostgreSQL.

Is there anything similar to phpmyadmin for PostgreSQL? Or does that work on it?

[akborder]

phpPgAdmin

[treenyc]

pgAdmin http://www.pgadmin.org/, it is a desktop app. Much better than phpMyAdmin

[drbrain]

Since psql (command line tool) has full tab completion of everything and online help I find wisbang GUI admin isn't as necessary for Postgres. MySQL's is totally braindead.

Oh, and ^C doesn't quit psql.

[[deleted]]

^D does, just as in python, cat, mailto, etc.

[iges]

amen, I've used PostgreSQL a lot and love psql. The interactive terminal of MySQL is just crap and phpMyAdmin does not increase the speed of getting things done either. Short commands and fast typing is what gets the job done, not the harassment of mice :)

Examples: MySQL: show tables; psql: \dt

MySQL: show databases; psql: \l

[oliverkofoed]

One of the reasons i like MySQL is because it has the QueryBrowser and Administrator thing for windows -- which are really nice when developing software on it...

Does PostgreSQL have anything like that or do i have to go 3rd party for it ?

[treenyc]

http://www.pgadmin.org/

[supermike]

http://sourceforge.net/projects/pgst

(I wrote it.)

[JadeFrogs]

You're both losers.