
Can PostgreSQL pickup where MySQL left off? - ruffdev
http://news.cnet.com/8301-13846_3-20097433-62/can-postgresql-pickup-where-mysql-left-off/
======
pilif
When I started with PHP3 development in 1998, PostgreSQL was still called
Postgres95 and it had some severe limitations which were directly adverse to
the features needed for web development:

1) performance was very slow

2) There was an effective row size limit of 8K over the whole row which worked
against people who wrote forum software (big posts) or were (ugh) storing
binaries in the database.

3) Even today, PostgreSQL requires a bit more maintenance and configuration
until it works right, whereas MySQL ran out of the box. Back then, VACUUM
wasn't at all optional and still required an exclusive lock over the tables it
was working on.

4) There was a misconception that each Postgres user needed to be a Unix
system user (this might or might not be true. Probably isn't, but I and others
were certainly thinking that), so it was working against the usual cheap
virtual hosting.

5) The MySQL extension for PHP was much more advanced than the postgres one,
leading to people preferring MySQL which in the end lead to the virtual hosts
only installing MySQL at which point Postgres' fate was sealed.

6) MySQL was coined as a spiritual successor to mSQL (just look at the names),
which was very popular at the time as it was the first SQL-database for
"normal people". You either did flat-files or mSQL.

7) MySQL had Windows support from the beginning. Postgres95 was still using
cygwin (if you could get it to work). Back then, many people were using
Windows as their development environment and being able to run the whole stack
on your development machine does have some advantages.

By now, aside of the maintenance thing, Postgres has lost all these drawbacks,
but now it's too late as people are using what they know is working for them,
so they are just going with MySQL (or SQLite if they need more simplicity,
which is interesting in itself as MySQL for _ages_ provided an embedded
linkable and serverless variant which also never took on).

~~~
mgkimsal
#2 - even if you don't store binaries, just very long posts, it's a problem.

#4 - whether it's a misconception or not, the majority of tutorials and even
the default utilities make that assumption. Working around that is not
something as widely documented as the defaults.

Also, forum software is a big arena. Not being able to get accurate count()
values back hurt(s) postgresql. It's hurting people who now rely on MySQL's
innodb as well, but you always have the option of MyISAM for certain tables
that you want accurate count()s on for pagination. I've heard for years that
"it's so easy to do this in PostgreSQL, just write some triggers and stored
procedures... " yet... if it's "so easy", why not just bundle that in as
default functionality (or perhaps even just a script that would create
triggers for you) in postgresql?

I don't ever recall hearing about the "embedded linkable and serverless
variant" of MySQL, and have been doing PHP since 1996. Perhaps it was a
licensing issue? MySQL seemed to take off largely due to a rise in PHP, but
sqlite seems to have taken off more because of embeddability and the public
domain aspect of it first, well, before I ever saw PHP adoption of sqlite.

~~~
pilif
> I don't ever recall hearing about the "embedded linkable and serverless
> variant" of MySQL, and have been doing PHP since 1996. Perhaps it was a
> licensing issue?

<http://dev.mysql.com/doc/refman/5.1/en/libmysqld.html>

pretty much a licensing issue as all of MySQL is released under the GPL or a
proprietary license. This also includes the mysql client by the way, but they
made a license exception there that allowed linking against PHP (extensions
mysql and mysqli) and later there was a reimplementation of the on-the-wire
protocol inside a PHP extension (mysqlnd) released under the PHP license.

> Not being able to get accurate count() values back hurt(s) postgresql.

count() is totally accurate within the limits of MVCC. If you need it to be
100% accurate across statements, make your transaction SERIALIZABLE.

There's one thing about count(): count(*) under MySQL, if using MyISAM tables,
is optimized so it doesn't have to actually count and thus is much faster
than, say count(row), count(whatever) with a where clause or count([asterisk
as to not confuse the HN parser.]) in any other database.

But the moment you use InnoDB or any other database that supports
transactions, count(whatever) unfortunately requires counting in all cases.

~~~
mgkimsal
re: count() - I guess I should have also said "reasonably fast". Waiting 8
seconds for a count() isn't practical for many apps.

------
Zak
I wonder why MySQL became super-popular and Postgres didn't. I remember
reading for years about how Postgres had a bunch of stuff (e.g. transactions)
that MySQL didn't (until recently) and that anybody who knew anything about
databases would choose Postgres, yet people still chose MySQL.

What did MySQL have during the last decade that made it better for certain
applications than Postgres?

~~~
buro9
MySQL was simple.

You didn't have to deal with schema/tablespaces, or the chance that someone
used stored procs, or security (almost everyone just used the equivalent of a
root account).

MySQL just worked.

For the most part, with zero monitoring, it stayed working until the server
ran out of disk space or died.

MySQL was always available.

No matter which hosting company you looked at, no matter how little you wanted
to spend, MySQL was pre-installed and ready for use.

I never thought it was the better choice, but I understood why it dominated.
Once things like Wordpress, vBulletin, phpBB and other stuff was written only
for MySQL the choice was then made for you.

I've long been a fan of applications that give you a choice of DB, but it's
now unrealistic as many applications have designed schemas to cope with the
quirks and limitations of MySQL specifically. These would now be harder to
migrate than simply changing a database string, and most application devs
don't invest in such things when what they have works well enough and is all
anyone is asking for.

~~~
Zak
The second half of your explanation, which boils down to "it's everywhere"
makes sense now, but is only true because it was already in demand.

That it was faster (for certain applications), easier and more reliable does
make sense. I understand, for example why PHP got popular even though there
were other options available at the time that I believe were better from a
software engineering[0] perspective. Things get popular by being better at the
things the majority of users care about, even if they're awful at everything
else.

[0] Did I really just use that term? I can't think of a better one to describe
the issue I'm talking about.

~~~
sjs
For many people, including hosting admins, MySQL was the path of least
resistance. MySQL required less maintenance than Postgres back in the day.
It's pretty much exactly the same situation as with PHP. The tool created by
mere mortals is usable by mere mortals, so they use it. The tool created by
scholars is technically superior at core functions but is slightly more
difficult or cumbersome to use so is used by fewer mortals.

One thing people seem to forget is that Postgres has not been around longer
than MySQL, both were released to the public in 1995. Postgres skipped a few
version numbers so maybe that makes it seem older.

~~~
stonemetal
Counting public releases of non university projects sure, but at least
according to the wiki page it has been under active development since 1986,
with first release in 1989. Where Mysql started development in 1994.

------
mkup
Well, PostgreSQL is licensed under BSD, which makes closed-source, commercial
forks possible. That's why it is attractive for large businesses, like Apple.

GPL-licensed MySQL serves the opposite purpose: it is impossible to fork it
into commercial DB product, grow it as times goes by, and threaten Oracle. It
is nearly impossible to create commercial product for this market, because
there's a free product there: MySQL. MySQL is like a ditch around the Oracle
stronghold, a ditch to keep competitors far away. GPL is a perfect tool for
such tricks.

In contrast to GPL which suppresses commercial developments in it's market
segment, BSD encourages them.

For the same reason, Apple slowly gets rid of GCC in favour of clang. It is
perfectly OK from legal point of view to include BSD-licensed compiler into
commercial closed-source IDE, unlike GPL-licensed compiler.

~~~
sigzero
I actually think the license had very little to do with its popularity. The
fact that MySQL was a lot simpler than Pg made it popular.

------
jacques_chester
IMO the short answer is: no.

Most opensource software that uses MySQL is riddled with MySQLisms.

~~~
riledhel
Many forks exists to deal with this situation
<http://en.wikipedia.org/wiki/MySQL#Forks>

~~~
pornel
You don't need fork for that — just enable ANSI mode.

The problem is that MySQL-based software doesn't do that and relies on the
default "quirks" mode.

------
gierach
In my mind, replication has historically been a pretty big deciding factor in
favor of MySQL. PostgreSQL didn't support it natively until version 9.0 (which
still isn't part of most Linux distro repositories). You'd have to install
Slony or something similar. MySQL made master/slave or master/master
replication simple to configure and a breeze to maintain.

------
ruffdev
thanks for all these points. I am doing a presentation tomorrow on the same
and all these points will help!

------
pablospr
When you’re choosing a database, you’re making a long-term decision, because
changing your mind later is difficult and expensive. You want to get it right
the first time

Here's a comparison in a tabluar format [http://database-management-
systems.findthebest.com/compare/1...](http://database-management-
systems.findthebest.com/compare/16-30-43/H2-vs-MySQL-vs-PostgreSQL)

~~~
Halienja
that's a good comparison. We use MySQL and PostGre both in our organization
and the deciding factor is mainly cost.

~~~
masklinn
> PostGre

It has never been named "PostGre".

Its original name was Postgres, the current one is PostgreSQL with Postgres
the accepted nickname.

