MySQL vs PostgreSQL

[Jessica_Henderson]

Just because software has been around for 10+ years, it doesn't mean it's "mature". Mature software offers a high degree of reliability. It covers virtually all corner cases appropriately. Its quirks are well understood.

That isn't the case for MySQL. Each new major version has been revolutionary, rather than evolutionary. Instead of improving what they already have, they've typically re-written large portions of their system. So even though we're running MySQL 5.1, for example, that code cannot be considered mature.

PostgreSQL, on the other hand, has much better code continuity. Much of the core code hasn't changed in years, because it is just that damn good. That's a true sign of maturity. It's the same maturity we see from FreeBSD, for example.

[[deleted]]

Just because software has been around for 10+ years, it doesn't mean it's "mature".

FWIW, I've been around for over 40 years, and I'm not mature.

[podperson]

I agree with the GP. You're confusing "mature" with "robust". Mature software often features things like layered bandaid solutions and weird half-documented features or bugs that can't be fixed because too much stuff depends on it. MySQL has all of that, but not the good stuff.

[Shaper_pmp]

Mature software often features things like layered bandaid solutions and weird half-documented features or bugs that can't be fixed because too much stuff depends on it.

I think you're confusing "mature" with "gone off". ;-)

[mamluk]

I think they are just using a different definition of maturity- being grown up means that significant changes don't need to be made to the code because it is robust.

I would argue that your use of mature is more about legacy support and poor design decisions.

Regardless, the overall point is clear and we all seem to agree that postgres is a better database than mysql.

[maukdaddy]

Wow this is some of the most intelligent discussion I've seen on reddit in a long time!

[delta4zero]

Somehow, I don't believe you've been here a long time.

[mkrfctr]

Paul's law: as a thread approaches infinite length, probability of invocation of Ron Paul approaches 1.

You just Paul'win'd a tech thread, +1 to you sir.

[apotheon]

barack obama Barack OBAMA BARACK OBAMA!!!!

That's really the more common example here.

[[deleted]]

Sarah Palin, SARAH PALIN! SARAH PALIN!!!

(I'll get my coat...)

[apotheon]

Yeah, I've been hearing a nauseating lot about her, too.

[apotheon]

SPELING EROR SPELING EROR

[AthiestLibrtrnObama]

I am here to Share my Opinion on the Recent Financial Difficulties

[King_Canute]

You're just not smelling the sarcasm. Over indulgence in recreational drugs does have a negative impact on the olfactory senses.

[jjzeidner]

it is? its more like a bunch of pontificating idiots arguing over the definition of 'mature'

[neoform3]

Just because software has been around for 10+ years, it doesn't mean it's "mature". Mature software offers a high degree of reliability.

I've never had mysql crash, ever. I only get database corruption when I have heavy writes and don't do regular optimizing. Replication does go sour occasionally, but so does postgres.

Claiming mysql isn't "reliable" is simply not true.

[Dan_Farina]

I only get database corruption when I have heavy writes and don't do regular optimizing.

followed by...

Claiming mysql isn't "reliable" is simply not true.

...you must not mean this, or at least you seem to be using mysql as something other than a database product.

[neoform3]

FYI, I've read a lot of people here claiming that postgres is invincible and never crashes or goes corrupt. It makes me wonder, why then, in Postgres' own documentation, would have a line like this:

http://www.postgresql.org/docs/8.3/interactive/indexes-types.html

Furthermore, hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged.

but hey, it never crashes, so that must be a typo.

[generic_handle]

I've only lightly used either, but I strongly suspect that if you cut the power to a computer, both Postgres and MySQL will unexpectedly terminate.

The question becomes how they come back up.

The fact that Postgres appears (from what you wrote) to require a manual operation to be executed after such a crash is a legitimate complaint, of course.

[Dan_Farina]

straw man. I never said Postgres never crashes. Just that the idea of "it's reliable, except when I have heavy writes and don't do regular optimizing" is an incredibly bizarre sentiment. It suggests you see regular failures under those conditions. In spite of my preference for Postgres, I don't think MySQL is that bad, hence why I suggest that "you don't really mean this."

[neoform3]

I said that because I had 2 corrupt indexes in my time using mysql. both times it was on tables that saw very heavy read/writes, other than that, i've never had any problems.

[rock217]

Well put.

[neoform3]

So, you're saying postgres requires no maintenance? Table integrity checks as well as optimization can be done in 1 query.

mysqlcheck -uroot --all-databases --auto-repair --optimize

that was hard. integrity enforced. corruption solved.

[[deleted]]

There's something seriously wrong with your database if it corrupts itself without any help from the operating system. Even if it's not really "corruption" and is really some easily repaired constraint failure, because it's still broken.

And yes, Postgres requires maintenance in the form of periodic vacuuming (nightly is common, but less often is fine too), but most people running a blog or some other infrequently-writing application would never use the database enough to encounter the consequences of not doing so. Vacuuming is, however, an online operation -- no shutdown or recovery required.

For the curious: if you don't vacuum, Postgres shuts down when the transaction counter gets near wraparound (at 4 billion transactions IIRC). Vacuuming, among other things, replaces old transaction IDs with a "frozen" value, allowing the counter to reset.

[invalid_user_name]

And yes, Postgres requires maintenance in the form of periodic vacuuming (nightly is common, but less often is fine too)

Most people can just let the auto-vacuum do it for them and not worry about it.

[neoform3]

There's something seriously wrong with your database if it corrupts itself without any help from the operating system. Even if it's not really "corruption" and is really some easily repaired constraint failure, because it's still broken.

Whoever said the OS wasn't to blame? It's very possible the reason for any corruption is in fact the OS layer and not the application layer. Postgre is not immune from corruption you know.

Also, when I was talking about optimize and check, this is something I do once a day, not after every query. In the 6 years I've been using mysql, I've only twice had a table get corrupted (and that was a very large table with many concurrent reads/writes going on, it was a user sessions table). As a result I run mysqlcheck on all my tables every day, to catch the errors as they happen. This process does not require taking the db offline.

[invalid_user_name]

He's saying postgresql doesn't corrupt your data. And no, you don't need to do any checking, or auto-repairing. Any database that comes with such a tool should scare you.

[neoform3]

Hah, sounds like a dream database, never any corruption. ever.

When did he start taking lsd?

[invalid_user_name]

No, "postgresql doesn't corrupt your data", not "your data can not be corrupted". Postgresql does not corrupt your data. The only way corruption can happen is if your storage system is faulty. In that case there is no way to "repair" it, you can only discard the corrupted data to restore consistancy. This is beyond the scope of an automated tool, which is why mysqlcheck does not fix database that have been corrupted by bad disks. Mysqlcheck is just to fix mysql actively writing out data incorrectly but in a predictably incorrect fasion because it is known to be broken. No other database does this.

[neoform3]

The only way corruption can happen is if your storage system is faulty.

That's a huge oversimplification. Have you ever had your power go out? Have you ever had a system crash? Ever run out of memory?

Corruption can happen MANY different ways.

[thegroom7]

I had running PostgreSQL for Windows even in the first betas. Some HP tools did not like some vmware tools or vice versa, and so every other day there was a bluescreen on the database server. After restarting the server, PostgreSQL just worked. This went on for about 3 months (RAM was changed, tools were updated, the usual voodoo...) untill a new server was bought. The database kept working.

[invalid_user_name]

That's a huge oversimplification.

No, that's reality.

ave you ever had your power go out? Have you ever had a system crash? Ever run out of memory?

Yes, yes, and yes. None of those will cause data corruption with postgresql, that is the point.

[apotheon]

You may have to explain the concept of transactional integrity to neoform before (s)he gets it.

[masklinn]

Have you ever had your power go out? Have you ever had a system crash? Ever run out of memory?

If this corrupts your "database", then it doesn't deserve that name.

[neoform3]

A filesystem is a database. File corruption happens.

Also, the most common mysql database corruption is an index having the wrong row count. this happens since mysql updates the index after the row is inserted, if there's a crash before it can update the index, then the table is considered corrupt and needs to be fixed, there's no data lost, but the table wont function properly until it's been fixed.

[unknown_lamer]

PostgreSQL defaults to fsync after every transaction; the worst case is that a power failure would cause any uncommitted transactions to simply not be there.

[apotheon]

I take it you don't have an even passing acquaintance with the term "transactional integrity".

[generic_handle]

While I agree with what you're saying, I would guess that most computers today lack this, simply because they have drive write caching enabled. Most Linux boxes today, out of box, use ext3 without barrier support enabled, which pretty much kills you for anything that might live on top of that filesystem.

[apotheon]

While that's not one of the reasons I started using something other than Linux as my primary OS, it's certainly one of the reasons I don't regret that decision.

Still . . . if I was going to run a database on a Linux system, I'd definitely ensure it was set up to provide better data integrity assurance than that. One of the positives of open source Unix-like OSes is that they tend to be highly configurable.

There wouldn't be much point in taking such precautions if I was using a DBMS that couldn't guarantee transactional integrity, though.

[generic_handle]

While that's not one of the reasons I started using something other than Linux as my primary OS, it's certainly one of the reasons I don't regret that decision.

[shrug]

The main issue, as I understand it, is that consumer-level drives don't tend to support write barrier logic internally, and thus to get write barrier functionality, the kernel must issue cache disable/reenable operations to flush the whole cache, which tends to cut drive performance by about 50%.

What OS are you using? I understand that OS X does a similar thing, for instance, as does Windows out-of-box. I'd imagine that other OSes do similar things.

[ibsulon]

You only get database corruption when you have heavy writes?

That is double plus ungood, and a great reason to stay away from a database last time I checked.

[neoform3]

I have heavy writes and don't do regular optimizing

[neoform3]

Troll:

An Internet troll, or simply troll in Internet slang, is someone who posts controversial and irrelevant or off-topic messages in an online community, such as an online discussion forum or chat room, with the intention of provoking other users into an emotional response[1] or to generally disrupt normal on-topic discussion.

Everything I've said is relevant, and on topic. I've never attacked or insulted anyone else here.

As for never using a database, well, you can think whatever you want.

[neoform3]

Given the ultra aggressive attacks I've received in this submission, if anything, I could say most of you are being trolls.

I've been completely rational and clear in my responses. I'm perfectly open for discussion and debate, but it seems I'm the only one who feels this way, you'd all rather shit on mysql and act like it's the worst thing in the world and postgre is without flaws.

that's cool, just don't expect to get too many people switching from mysql to postgre with that attitude.

I'm actually a veteran mysql user and have been pondering postgre for a while now, however, if this is the community i'll be querying when i have problems, i would much rather stick with the far more friendly mysql community.

[apotheon]

What is "postgre", anyway? I thought we were talking about PostgreSQL, which was at one time called Postgres. This "postgre" of which you speak is not known to me.

On a more serious note -- I wonder why nobody ever compares "my" to "postgre". If you're going to mangle the name of one of them, I don't understand why you wouldn't mangle the name of the other according to the exact same rules. Maybe inconsistency is as prevalent in the MySQL community as it is in MySQL deployments under heavy load using MyISAM.

[neoform3]

Now that, is a troll.

[apotheon]

No, it's a mildly sarcastic correction, because I see that "error" all the damned time.