[lug] MySQL vs PostgreSQL

[Jeremy Hinegardner]

On Fri, Aug 03, 2012 at 06:44:18PM -0400, Gordon Golding wrote:
> Somebody has a PostgreSQL system.
> They want to do an entire new large data warehouse.
> I used to work with Postgres, now with MySQL.
> It seems I just don't see Postgres mentioned that much any more.
> Would it be best to build the new data warehouse on MySQL?
> Any comments on one over the other?

I would absolutely recommend PostgreSQL over MySQL any day of the week.  The
query planner in Postgres is top notch and makes much smarter use of indexes
and PG allows a variety of types of indexes you may create.

We migrated from MySQL to Postgres over 4 years ago and have never looked back.
The supporting tools, the ability to easily spread data out over different
disks, have actual table spaces, be able to put indexes, tables and scratch
space on different spindles is aboslutely paramount in a data warehouse
environment.

Not to mention that not even once, since we switched, has PG failed, or
corrupted our data, ever. I can not say the same for my experience with MySQL.

Our Postgres database is approximately 2TiB in size spread across 40 logical
disks probably handling 1000+ queries per second, and currently has between 400
and 800 concurrent clients connected to it.

This is on a single Dell R710 Server with 64G of RAM.  It is connected to 3
MD1220 disk enclosures all with 15k rpm disks.

We use PG's hot standby capability and so have another identical piece of
hardware in place for standby.

I hope that helps,

-jeremy

-- 
========================================================================
 Jeremy Hinegardner                              jeremy at hinegardner.org