DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=rKH0PoDo2afldVInowZ6hOsR9IQllyFMIx4EdWvMzM3IwOXdXdAUy5HzoL6DeeO6fJ
	vR+N7NJGmh6wS9suzcA8G1ya+fyUcLEBpfK76mFFy3RPL8IXK9XsKepVIXB4ga0pHZjx
	fjTEaT9hpbuHYHICWDzvEmD8hDXDkcbUfAo7M=
MIME-Version: 1.0
In-Reply-To: 
 <OFE0550409.E8DBE5B8-ON652576C6.0014526C-652576C6.001716AC@ibsplc.com>
References: 
 <OFE0550409.E8DBE5B8-ON652576C6.0014526C-652576C6.001716AC@ibsplc.com>
Date: Tue, 9 Feb 2010 22:49:24 -0700
Message-ID: <dcc563d11002092149n17d5d3b9l486bec231c5e2f63@mail.gmail.com>
Subject: Re: PostgreSQL - case studies
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>,
	pgsql-general@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Quick note, please stick to text formatted email for the mailing list,
it's the preferred format.

On Tue, Feb 9, 2010 at 9:09 PM, Jayadevan M
<Jayadevan.Maymala@ibsplc.com> wrote:
>
> Hello all,
> Apologies for the long mail.
> I work for a company that is provides solutions mostly on a Java/Oracle p=
latform. Recently we moved on of our products to PostgreSQL. The main reaso=
n was PostgreSQL's GIS capabilities and the inability of government departm=
ents (especially road/traffic) to spend a lot of money for such projects. T=
his product is used to record details about accidents and related analysis =
(type of road, when/why etc) with maps. Fortunately, even in India, an acci=
dent reporting application does not have to handle many tps :). =A0So, I ca=
n't say PostgreSQL's performance was really tested in this case.
> Later, I tested one screen of one of our products - load testing with Jme=
ter. We tried it with Oracle, DB2, PostgreSQL and Ingres, and PostgreSQL ea=
sily out-performed the rest. We tried a transaction mix with 20+ SELECTS, u=
pdate, delete and a few inserts.

Please note that benchmarking oracle (and a few other commercial dbs)
and then publishing those results without permission of oracle is
considered to be in breech of their contract.  Yeah, another wonderful
aspect of using Oracle.

That said, and as someone who is not an oracle licensee in any way,
this mimics my experience that postgresql is a match for oracle, db2,
and most other databases in the simple, single db on commodity
hardware scenario.

> After a really good experience with the database, I subscribed to all Pos=
tgreSQL groups (my previous experience is all-Oracle) and reading these mai=
ls, I realized that many organizations are using plan, 'not customized' =A0=
PostgreSQL for databases that handle critical applications. =A0Since there =
is no company trying to 'sell' PostgreSQL, many of us are not aware of such=
 cases.

Actually there are several companies that sell pgsql service, and some
that sell customized versions.  RedHat, Command Prompt, EnterpriseDB,
and so on.

> Could some of you please share some info on such scenarios- where you are=
 supporting/designing/developing databases that run into at least a few hun=
dred GBs of data (I know, that is small by todays' standards)?

There are other instances of folks on the list sharing this kind of
info you can find by searching the archives.  I've used pgsql for
about 10 years for anywhere from a few megabytes to hundreds of
gigabytes, and all kinds of applications.

Where I currently work we have a main data store for a web app that is
about 180Gigabytes and growing, running on three servers with slony
replication. We handle somewhere in the range of 10k to 20k queries
per minute (a mix of 90% or so reads to 10% writes).  Peak load can be
into the 30k or higher reqs / minute.

The two big servers that handle this load are dual quad core opteron
2.1GHz machines with 32Gig RAM and 16 15krpm SAS drives configured as
2 in RAID-1 for OS and pg_xlog, 2 hot spares, and 12 in a RAID-10 for
the main data.  HW Raid controller is the Areca 1680 which is mostly
stable, except for the occasional (once a year or so) hang problem
which has been described, and which Areca has assured me they are
working on.

Our total downtime due to database outages in the last year or so has
been 10 to 20 minutes, and that was due to a RAID card driver bug that
hits us about once every 300 to 400 days.  the majority of the down
time has been waiting for our hosting provider to hit the big red
switch and restart the main server.

Our other pgsql servers provide search facility, with a db size of
around 300Gig, and statistics at around ~1TB.

> I am sure PostgreSQL has matured a lot more from the days when these case=
 studies where posted. I went through the case studies at EnterpiseDB and s=
imilar vendors too. But those are customized PostgreSQL servers.

Not necessarily.  They sell support more than anything, and the
majority of customization is not for stability but for additional
features, such as mpp queries or replication etc.

The real issue you run into is that many people don't want to tip
their hand that they are using pgsql because it is a competitive
advantage.  It's inexpensive, capable, and relatively easy to use.  If
your competitor is convinced that Oracle or MSSQL server with $240k in
licensing each year is the best choice, and you're whipping them with
pgsql, the last thing you want is for them to figure that out and
switch.