News for hackers. Stuff that matters.
Re:PostgreSQL Rising by rickmb on Friday July 20, 2012 @03:10AM (#4269779)
The primary target audience for hacker-to-hacker Postgres evangelism is MySQL users. Because let's face it, the choice for DBs like Oracle is usually made upstairs, and for very different reasons.

So why do Postgres advocates insist on dissing MySQL with false and misleading arguments? The usual target is some default settings, when obviously there are three kinds of MySQL users: the ones that actually have a reason to want less strictness, the ones that know how and when to change the settings, and the ones that just don't give a fuck.

You're not going to convince the latter until you are #1, and you're insulting the intelligence of the first two, whilst also making them question whether the arguments in favor of Postgres are actually true, since you're not being particularly honest in your criticism of MySQL.

And you're wondering why Postgres doesn't get any more love?

Most MySQL users are well aware that Postgres is technically a better DB. They mostly (right or wrong) feel MySQL serves their needs sufficiently, and are not particularly attracted to an open source community with such a douchy attitude.

If even Oracle doesn't scare people of sufficiently to jump ship and switch to a better, free and open database, you've got a serious image problem. Stop blaming it on other people's "stupidity".

Re:PostgreSQL Rising by newhouseb on Friday July 20, 2012 @05:09AM (#4270047)
I'm not sure where in the article the author insults the _users_ of MySQL as stupid, but rather insults some decisions made by MySQL as ill-advised.

Incidentally, I was not aware of issues around default settings with MySQL when I switched our infrastructure to Postgres - the primary reason we switched was transactional and much more efficient DDL changes. I was at a company previously that I felt was seriously hampered from innovating due to strong resistance against schema changes by the DBAs and want to do my best to prevent a similar culture where I work.

Before I decided to switch I benchmarked schema changes between Postgres and MySQL and found Postgres to be quite impressive, see the gist of results here: https://gist.github.com/1620133. If you (or anyone else) can point out why this benchmark is false or misleading, I'm all ears.

Re:PostgreSQL Rising by jeltz on Friday July 20, 2012 @06:01AM (#4270120)
Your benchmarks especially demonstrate the fact that PostgreSQL tries to do as little work as possible when adding a column or dropping a column (or in recent versions of PostgreSQL when changing the type of a column). If you do not have a default value for the new column PostgreSQL only has to update the table definition which is almost instant.

Another interesting part about your benchmarks are that they throw serious doubts on the popular myth that count(*) is slow in PostgreSQL. And your benchmarks were made before PostgreSQL 9.2 which will add index-only scans.

Re:PostgreSQL Rising by mgkimsal on Friday July 20, 2012 @06:55AM (#4270215)
"... throw serious doubts on the popular myth that count() is slow in PostgreSQL."

Whew! Glad that was only a myth! I'll remind myself of that whenever my code takes > 10 seconds to return the count() from a table. "Just a myth - this isn't really happening". I'll try repeating that to myself while waiting for the results - probably should only take 10-15 repeats of the phrase before the row returns, right?

Whoah - just did a check on a real table with a whopping 2 million records - postgresql 9 just blazed through that in < 7 seconds to give me

select count(*) from student; count --------- 2032609 (1 row)

w00t! 7 seconds! I'm not even sure how that myth got started, let alone why people still believe that hogwash.

Re:PostgreSQL Rising by lusr on Friday July 20, 2012 @07:44AM (#4270311)
http://wiki.postgresql.org/wiki/Slow_Counting

Are there DBMSs where this doesn't happen?

I just did a SELECT COUNT(*) on a table here in our QA environment; 20.5 seconds to count 42385875 records from one table and 68.8 seconds to count 191906711 records from another table (Oracle 10g 64-bit).

Re:PostgreSQL Rising by mgkimsal on Friday July 20, 2012 @10:56AM (#4271048)
"The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense."

When operating on a table, my understanding is that pg will select a version and operate on that version. If other versions are being worked on in transactions - that's a different story. Why can't metadata about the number of rows be assigned with the table version data, so after every operation, you'd know what the number of rows was at at that moment in time?

Re:PostgreSQL Rising by jeltz on Friday July 20, 2012 @11:39AM (#4271249)
PostgreSQL does not track any versions at the table level, instead it tracks the versions for every row. This means two queries can modify different parts of the table concurrently without any lock contention.[1]

In PostgreSQL every row has two numbers. The transaction ID it was insert in and the transaction ID it was deleted in. An update is an insert plus a delete.[2] When running a select in PostgreSQL you just traverse the table and for each row check these two numbers to know if you are allowed to see the row.

The details above are PostgreSQL specific but most other databases have the same problem with there being no way to know the exact count without actually counting the rows.

Footnotes:

1. There is contention currently in PostgreSQL when writing the database journal (used for crash recovery and replication).

2. There are some optimization which are done here. For example HOT to avoid index updates.

Re:PostgreSQL Rising by jeltz on Friday July 20, 2012 @07:38AM (#4270296)
I was unclear the part that is a myth is that they are slow in _PostgreSQL_. COUNT( ) is actually slow in general on databases with high concurrency requirements. Because you have to sacrifice something to make COUNT( ) fast.

If you had read his benchmark you would have seen COUNT( ) was slow in both databases (three times as slow in MySQL).

Re:PostgreSQL Rising by lobster_johnson on Friday July 20, 2012 @12:28PM (#4271534)
You can do a fast, conditionless count that ignores current transaction state and therefore risks being slightly out of date, but usually good enough for most use cases:

    select n_live_tup
    from pg_stat_user_tables
    where relname = 'mytable'
Re:PostgreSQL Rising by erichocean on Sunday July 22, 2012 @08:58PM (#4279062)
Way more people should be using this.
Re:PostgreSQL Rising by darnaut on Saturday July 21, 2012 @03:14AM (#4274229)
> If you (or anyone else) can point out why this benchmark is false or misleading, I'm all ears.

You might want to check out the "Online DDL for InnoDB Tables" section of the manual, it should help with understanding the results. http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.htm...

Re:PostgreSQL Rising by yread on Friday July 20, 2012 @03:35AM (#4269825)
> usual target is some default settings

for me it would be missing common table expressions, weak implementation of functions and SPs (recursion) and horrible .NET connector (connections dying, int can get returned as byte[]). And I'm not even mentioning the gazillions of advanced features that pgsql (and Oracle or SQL Server) has that mysql doesn't.

Re:PostgreSQL Rising by adrianhoward on Friday July 20, 2012 @04:10PM (#4272668)
My personal all time mysql fave - values that are both null and not-null

http://use.perl.org/use.perl.org/_Adrian/journal/32627.html

Re:PostgreSQL Rising by epo on Friday July 20, 2012 @04:39AM (#4269993)
Typical fanboy bluster. What's dishonest about the criticism, be specific? What you really mean to say is that most MySQL users use it because it is there and take it as it has been configured for them. MySQL is the storage platform for people who do not know about databases and probably only use it because some blogging product requires them to.
Re:PostgreSQL Rising by smackay on Friday July 20, 2012 @04:58AM (#4270028)
You just made his point and rather succinctly too.

MySQL has a low barrier to entry with some reasonable tools that make it easy to get up and running with a minimum of fuss. For a large swathe of applications it does the job just fine. There's little point in trying to pretend otherwise. Where PostgeSQL really fits in is when the next step is needed, either because the app has outgrown what MySQL can offer or there are some use (edge) cases where PostgreSQL does a better job. Address these areas competently and the world will beat a path to your door.

Re:PostgreSQL Rising by taligent on Friday July 20, 2012 @05:31AM (#4270083)
> MySQL is the storage platform for people who do not know about databases.

Exactly. I mean seriously what do Facebook, Twitter, Yelp, LinkedIn, Flickr etc know about storing lots of data.

Re:PostgreSQL Rising by harshreality on Friday July 20, 2012 @11:46AM (#4271282)
Even when a company like Google or Facebook uses "mysql", it's not stock Oracle MySQL -- which is what people are going to download and use when they hear that MySQL is good enough because major companies use it. This is an unfortunate situation caused by most distributions still favoring stock mysql over percona, and Oracle refusing to incorporate patches even when they're plenty of evidence that people who know what they're doing want those patches.

It seems like Oracle is more concerned about trying to keep mysql less well suited for major deployments, so it can upsell its flagship product. Or else their development process simply doesn't properly accommodate community assistance. Either way, as long as their resistance to community-aided development continues, mysql and oracle/mysql disparagement will continue, not because people hate Oracle, but because Oracle's management of mysql makes it less than ideal for those who simply want a good open source database.

Which is the point of the OP. Anyone who is open to looking at options and who wants a good open source database should look at PostgreSQL.

I doubt any of those example companies you listed even use stock Percona. Facebook for sure has extensive modifications beyond even the publicly available distributions like Percona and MariaDB.

Re:PostgreSQL Rising by spudlyo on Friday July 20, 2012 @05:39PM (#4273014)
Up until very recently Twitter was still using stock MySQL 5.0.45 (which I believe is from 2007) nearly everywhere in production. The notion that MySQL requires a highly patched version with a world class development team to maintain is a myth. Facebooks patches are made public and are often incorporated into MariaDB and/or Percona forks. Oracle usually goes their own way on implementing changes from the community.

Source: http://tinyurl.com/7adp6fl (PDF)

Re:PostgreSQL Rising by gaius on Friday July 20, 2012 @07:13AM (#4270246)
If FB or twitter miss a status update, it's no big deal. If you bank misses your salary going in, it is a HUGE deal. Use the right tool for the job.
Re:PostgreSQL Rising by darnaut on Saturday July 21, 2012 @03:36AM (#4274266)
Re:PostgreSQL Rising by taligent on Friday July 20, 2012 @10:08AM (#4270856)
And what about Amazon, Craigslist, eBay, Etsy, Google, Groupon, Ticketmaster, Yahoo etc.

You think they are equally going to be okay with losing data ? Or do you want to try again.

Re:PostgreSQL Rising by gaius on Friday July 20, 2012 @10:15AM (#4270878)
Amazon runs its real-money handling on MySQL? Link please.
Re:PostgreSQL Rising by taligent on Friday July 20, 2012 @10:30AM (#4270947)
Nice shifting of the goalpost there.

Let's add some more companies: 37Signals, DHL, Dropbox, Evernote, UPS, Kayak, LastMinute, Orbitz, Continental, Mint, Quora, Tumblr, Techcrunch, Slashdot, NYT, NBC, Reuters, Wotif, Zappos, Wikipedia, Youtube.

You still think any of these companies would tolerate loss of ANY data for ANY reason ?

Re:PostgreSQL Rising by gaius on Friday July 20, 2012 @10:43AM (#4271004)
Not at all. Like I say above, right tool for the job. I don't doubt that all those companies use MySQL for something, indeed probably every large company in the world has at least one instance of it somewhere.

For example, taking the first company on your list that actually deals with real stuff in the real world rather than just running a website, DHL, here I see http://www.oracle.com/us/corporate/press/037809 - they're an Oracle shop. I could continue but I think I've proved my point.

Re:PostgreSQL Rising by gaius on Saturday July 21, 2012 @06:14AM (#4274454)
PS the cost of not losing "ANY data for ANY reason" is effectively infinite. You make a call on what data loss would cost your business then you decide how likely each scenario is and that gives you your budget. Datacentre disruptions I have been personally involved with include workmen digging up fibre (several times actually), deliberate vandalism of cable (during May Day riots), fire in the generator room (a couple of times), large truck losing control and driving through the walls, flooding, and several more. No one of these scenarios was sufficient by itself to hurt us because we had mitigated them. But 2 happening together, then yeah, there is a significant possibility that some data loss would occur (e.g. replication to datacentre B is disrupted, then datacentre A is catastrophically lost). The cost of protecting against that however is prohibitive.

So before you get all starry-eyed about these amazing companies and their amazing MySQL installations, understand that you are starting from a false premise. Oracle - for a cost - provides some powerful capabilities (e.g. stretch-RAC[1]). Is it worth the money? Well, it comes back to what your data is worth. This incidentally is why some organizations cling to paper forms - it's cheaper to re-key the data than build out the infrastructure to do it all online...

[1] Tho' if you think you need this, what you probably really need is DB/2 and Sysplex

Re:PostgreSQL Rising by jfaucett on Friday July 20, 2012 @04:06AM (#4269905)
I couldn't agree with you more. The statements made in this article seem pretty derisive towards MySQL, a system I like for its flexability, easy integration, and power. Sure, it might not be the most pure sql implementation, it lets you bend the rules for data integrity (maybe not best practice but I still like it) and there are some quirks but calling MySQL stupid doesn't make me a Postgres fan.
Re:PostgreSQL Rising by sangnoir on Friday July 20, 2012 @04:25AM (#4269950)
I see this stuff on here all the time, except it's usually $LANGUAGE vs PHP, and it's usually condoned/encouraged.
Re:PostgreSQL Rising by crististm on Friday July 20, 2012 @10:29AM (#4270939)
I, for example, have been hit these days by this: http://bugs.mysql.com/bug.php?id=26339

I wonder why this bug is still open today after 5 years.

Re:PostgreSQL Rising by justincormack on Friday July 20, 2012 @03:41AM (#4269841)
It is a lot easier to convince someone who wants to use Oracle to use Postgres than to use Mysql. There is serious commercial support available, and it is built for reliability.
Re:PostgreSQL Rising by einhverfr on Friday July 20, 2012 @05:11AM (#4270051)
Not just that. PostgreSQL is a lot more like Oracle than it is like MySQL. Both are development platforms in a box. Both are built for many applications to run off the same database (this is a use case that MySQL really sucks at btw-- MySQL is ideal for one app per database deployments). Both let you write stored procedures in some variation of PL/SQL and Java.

Oracle on the other hand has a setting for everything. A good Oracle DBA can spend all his time fiddling with Oracle settings to get the best possible performance. On the other hand, a good Oracle DBA can spend all his time fiddling with settings to get that little bit of extra performance gain. PostgreSQL is simpler.

PostgreSQL on the other hand lets you write your stored procedures in any language. On the other hand, you can spend all your time writing and debugging stored procedures written in brainfuck.....( and in case you want to do that, download the handler at https://github.com/mikejs/pl-bf and go get started!)

There are a few other differences as well. In general, I think Oracle is better for some things, PostgreSQL for others, but they have more overlap than either does with MySQL.

Re:PostgreSQL Rising by jeltz on Friday July 20, 2012 @03:49AM (#4269869)
And there is also a company dedicated to providing a migration path from Oracle to PostgreSQL: EntrepriseDB. I have not used their products but they employ several of the core contributors to the PostgreSQL project which have contributed to many important features.
Re:PostgreSQL Rising by AaronBBrown on Friday July 20, 2012 @04:48AM (#4270009)
Are you suggesting that there isn't "serious commercial support" available for MySQL? Oracle, Percona, SkySQL, and a number of other businesses provide high quality support for MySQL installations.
Re:PostgreSQL Rising by arethuza on Friday July 20, 2012 @06:59AM (#4270224)
I suspect the main reason for organisations running Oracle (and MS SQL Server) is because you want to run 3rd party applications.
Re:PostgreSQL Rising by ibejoeb on Friday July 20, 2012 @09:48AM (#4270751)
That's one reason. The reason why they need it for third-party applications is because that's what developers targeted, for two reasons:

1. It was the only game in town when the application was designed.

2. It was head-and-shoulders above everyone else. More, better, faster. Most people think you just throw SQL-99 at it, but then you're wasting your money. Look at their analytical languages, for example.

Re:PostgreSQL Rising by arethuza on Friday July 20, 2012 @09:51AM (#4270766)
Of course, Oracle and Microsoft are also major vendors of applications that run on their database servers. In fact, the application revenue for both is probably much higher than database server revenue.