

Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007 - davidw
http://www.postgresql.org/docs/techdocs.83

======
e1ven
I've always been a fan of PostgreSQL-

We've seen the pain that mysql rebuilds can cause, with companies being down
for hours. I've seen this in companies I've worked at, and on public companies
such as wikipedia.

We've had good luck with psql internally. The speed isn't as much of an issue
as some people had predicted, once we eliminated row counts. We're doing most
of our processing on the server side, rather than using stored procedures, so
we may have a different use case than most.

Slony should be _fine_ for replication for most people. There's also the
heftier Mammoth replication, but that's for-pay.

We've talked with Command Prompt (commandprompt.com) a few times, and they've
been great at supporting pgsql- They have top notch pepole on staff, who know
the details of slony setup, or tweaking db files. I believe they employ two
slony devs on staff.

The only real annoyance I've found with postgresql is capitalization. they're
being faithful to the SQL standard, but they require that all column names are
lower-case, unless you put each part in quotes.

select * from "Product"."Table", where "Field", etc.

It'd be great if there were a way to allow more permissive names throughout.

In all though, I'd highly recommend it.

-Colin

~~~
neilc
WRT capitalization, lower-case identifiers are not actually required in
PostgreSQL. The case-folding rules are:

    
    
      1. unquoted identifiers are folded to lower-case
      2. double-quoted identifiers have their case preserved
    

This applies to all SQL queries, whether DDL or otherwise. Therefore, you can
freely use mixed-case or upper-case identifiers, as long as you _consistently_
either don't double-quote them, or always use the same case when you double-
quote them.

So it is perfectly feasible to use non-lower-case identifier names, you just
need to be aware of the rules. In practice, most Postgres users just use lower
case identifiers all the time, but that's mostly a matter of style I think.

(FWIW, Postgres' case-folding behavior is actually one of the few areas where
it is _not_ conformant with the SQL standard.)

~~~
e1ven
Wow, thanks for the info. I'm glad to learn when I've got the wrong idea- I
must have misread their forum posts on why they did it that way.

When we tried using mixed case without quotes it always converted it to
lowercase internally- It reminds me a bit of OS X's file system handling, in
how it worked- It accepted Mixed case, it just didn't care ;)

I'll look more into it, thanks. -Colin

------
nickb
The reason why we went with MySQL even though Postgres performed better was
the greater availability of MySQL expertise on the team. Three people on the
team were experienced in MySQL and each had a bag of tricks and none of us
knew Postgres past the basics. Also, people at the hosting place had a ton of
MySQL experience and they would jump in and help when needed.

Consider many different things when choosing DB. Don't just consider raw
performance.

------
chaostheory
I definitely agree that in most cases Postgres is still superior to MySQL
(feature maturity and variety, reliability, ...). There's just one problem:
(to my knowledge) there is currently no such thing as an open source clustered
version of postgres. (There is a non-free clustered version, but it's not
mature and its cost is prohibitive to small businesses.)

MySQL, even with its problems, is good enough. Moreover it accounts for future
growth with MySQL Cluster. While MySQL Cluster is not yet mature (though this
may change soon), it's there, it's open, and it's why I chose MySQL for my
startup, even though I'm a Postgres veteran.

The problem with Postgres (and most databases without clustering) is when you
need more resources your only option is to buy a bigger box to replace your
current one. Eventually you'll have to buy specialized and super pricey
machines...

With clustering you can add x-number of cheap commodity boxes to the existing
set to meet your current needs.

~~~
epi0Bauqu
By non-free clustered version, are you referring to Slony
(<http://slony.info/>) ? I believe you can use it for free, and I have known
people who use it successfully in production.

~~~
zach
That's how the Reddits did it.

~~~
davidw
They use Postgres? I hadn't seen that before.

~~~
dood
From 11 months ago: [<http://programming.reddit.com/info/jl2a/comments/cjpfw>]

" We use postgresql + slony, and are fairly pleased. Most of the time reddit
slows down, it's db related, but so far there has always been a way to work
through it. That being said, I feel we have to spend far too much time
worrying about the database, and that's probably the situation with any
relational db.

Presently, we have a db master that replicated to one machine, and that second
machine replicates to a couple others. Most of the reddit queries are handled
by the master and the first slave. The other slaves are used for backups and
slow queries (e.g. stats and recommendations).

Replication and query load-balancing helped out a lot with the database load,
but it introduced a new host of problems. Replication lag, in particular. This
occurs when a db write is committed to the master, but the next read hits a
slave before the write has propagated. "

------
cstejerean
Postgres 8.2.4 has some significant performance improvements even over 8.1.8.
I have been amazed at the performance gains in some slow parts of the
application simply from upgrading to the latest version of Postgres. On the
other side I'm very disappointed by the lack of real clustering. Slony
addresses some needs but to me it feels like a hack. I'm starting to
experiment with breaking up the data across multiple database servers. Sooner
or later when the number of writes is high enough clustering breaks down due
to the size of the replication traffic.

------
davidw
I think both databases have their merits, but the thing that makes me turn up
my nose is seeing so many MySql installations done wrong. These are the people
who didn't consider things carefully and choose, but just heard that Mysql was
faster, installed the default version of that, and now handle people's _money_
with no transactions, and no referential integrity.

------
sanj
Replication options?

Hot spares?

~~~
epi0Bauqu
<http://slony.info/>

