Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9FFD12E0175 for ; Mon, 10 Dec 2007 23:49:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 23285-03 for ; Mon, 10 Dec 2007 23:49:48 -0400 (AST) Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.241]) by postgresql.org (Postfix) with ESMTP id BEAE12E00C4 for ; Mon, 10 Dec 2007 23:49:45 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c36so597649ana for ; Mon, 10 Dec 2007 19:49:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=7V4TjIOZB8zgoRQCXK3R/lRtL3mjHK0aI9MYq8wVUNk=; b=Wvf6FG3wYvVsEHPOE0ShQ6dUk2PQRXBotde8OKP2jxOlmJcfz7dgO5MddpEa+UYFvI/K9DeYmq3RlyYwtrIaw6VBnZcnjeku2qdyZnvUGk4ou7HjQ5qQ613l34Uuk0qLTz0tUvaQUZf+EWjEL740k1M0BpaDDJrRcCwKUQ5D71o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=iTiTzDFSw0MGRi+xW5HdL1Kr9wO18Cqsfs5/lJPwoHyBFx5HCr6G7yXhHQ1k7RO3yHH0i8XsxCzor/gdOkFuNA2rAXiokvlJ4i8lkonAkIcjexfs7+PljN0lPv2i8GzB/2GrVyuR8lVVEDjOv0lJfnLjNK3kux7l46bQvqcER0E= Received: by 10.100.201.16 with SMTP id y16mr15218555anf.1197344978470; Mon, 10 Dec 2007 19:49:38 -0800 (PST) Received: by 10.90.79.20 with HTTP; Mon, 10 Dec 2007 19:49:38 -0800 (PST) Message-ID: <493343bd0712101949j7ecbd06fic43d1b4b5a51607c@mail.gmail.com> Date: Tue, 11 Dec 2007 23:19:38 +1930 From: "=?ISO-8859-1?Q?Elvis_Henr=EDquez?=" To: pgsql-admin@postgresql.org Subject: Re: Case studies In-Reply-To: <8B319E5A30FF4A48BE7EEAAF609DB233015E2FC9@COMAIL01.digitalglobe.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19908_13454788.1197344978460" References: <493343bd0711261934p43fe15f9q46aaae78c8b6ddd4@mail.gmail.com> <493343bd0711271832l7ddf96cx57a91bd082717a28@mail.gmail.com> <8B319E5A30FF4A48BE7EEAAF609DB233015E2FC9@COMAIL01.digitalglobe.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.415 tagged_above=0 required=5 tests=AWL=-0.805, BAYES_50=0.001, FUZZY_AMBIEN=1.026, HTML_MESSAGE=0.001, INVALID_DATE_TZ_ABSURD=2.191, WHOIS_NETSOLPR=0.001 X-Spam-Level: ** X-Archive-Number: 200712/44 X-Sequence-Number: 27561 ------=_Part_19908_13454788.1197344978460 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Good evening everybody!!! Here is an extract of the mail I sent to the project coordinators with the information gathered thanks to the help offered by all the mailing lists I asked, this is, you; I wanted to post it so future people looking the same information can find it all in one place: (a few information is in Spanish, as I'm from Venezuela, where the project is being developed. I left it as it is an extract of the mail; but you can check the URLs I quote) ############## ## Mail Begin ## ############## Entre algunos Casos de Estudio exitosos de implementaci=F3n de PostgreSQL como Data Warehouse, te puedo mencionar los siguientes (cito las partes resaltantes y coloco el enlace donde se puede observar el documento completo): 1) El mejor de todos los Casos de Estudio revisados, que incluye hasta aspectos t=E9cnicos de implementaci=F3n, est=E1 disponible un PDF de 3.7 Mb Sugiero que lean dicho documento. "Oracle features we need Partitioning Statistics and Aggregations rank over partition, lead, lag, etc. Large selects (100GB) Autonomous transactions Replication from Oracle (to Oracle)" Todos esos aspectos los consiguieron con PostgreSQL http://www.lethargy.org/~jesus/archives/66-Big-Bad-PostgreSQL.html 2) "Greenplum is the supplier of Bizgres MPP, a massively parallel implementation of the PostgreSQL open source database system. Frontier Airlines, the second largest carrier at Denver International Airport, uses Bizgres for competitive flight pricing. Bizgres, like PostgreSQL, is an open source code project. Greenplum sponsors the Bizgres project and sells technical support and services on top of it." ... "The appliance can be stacked with 10, 40, or 100 terabytes of disk" ... http://www.informationweek.com/showArticle.jhtml;jsessionid=3DKQDGEIMWGP3D0= QSNDLRCKH0CJUNN2JVN?articleID=3D191600197&queryText=3Dpostgresql 3) ... "This talk was on converting a really large (over 3 terabytes, largest table is 1.8 billion rows) data warehouse database from Oracle 8i to PostgreSQL" ... "The reason for choosing Postgres over MySQL was that Postgres has a much longer history with the kind of advanced features he needed. They needed (and were able to hack PostgreSQL to get) the following features: 1. Data partitioning (spreading tables over multiple drives) 2. Large selects (50-million-row return sets, over 100GB of data) 2. Incremental COMMITs for really, really long queries 3. Replication" http://www.sitepoint.com/blogs/2006/08/05/oscon-2006-big-bad-postgresql/ 4) 4.1) "FWIW, we've got a billing database with several moderate tables, one is 48 million rows with about 75k per day; and some much larger tables that are effectively partitioned by date so that some client software (Excel, etc.) doesn't choke on them. And we've got some spatial tables that dwarf the billing stuff." 4.2) "> Where I work we have a data warehouse > of similar design (a few large tables, a few small lookup tables). It > has 86,840,447 rows and takes up 44 Gigs of space. It sits on a > single CPU box with a 4 disk RAID-10 and runs queries covering a few > minutes to a few days worth of monitoring data. Sequential scanning > the whole main table takes 621 seconds or so (10+ minutes). We add > 150 to 200k rows a day to it. > > Selecting a days's worth of data takes ~ 350ms. A week's worth takes > 2 to 10 seconds depending on how much is cached." http://archives.postgresql.org/pgsql-admin/2007-11/msg00237.php 5) 5.1) "GlobeXplorer serves terrabytes of imagery to clients around the world using PostGIS as their production database server. In 2004, GlobeXplorer migrated from Informix to PostGIS, and now they are serving over a million requests a day with PostGIS." 5.2) "The national mapping agency of France manages over 100 million topographic features in PostGIS/PostgreSQL and provides read/write access to over 100 field researchers around the country." http://postgis.refractions.net/documentation/casestudies/ 6) Afilias, Ltd. - A Database for the Internet "The primary technical factors for using PostgreSQL are its support of SQL standard and multi-version concurrency control (MVCC). MVCC helps ensure that each database transaction sees a consistent view of the database while ensuring that high transaction volumes are supported. Additionally, PostgreSQL supports over 65,000 GB of data in a single table, which is more than adequate for the needs if .ORG or .INFO database management support. .INFO and .ORG each use PostgreSQL version 7.2= " http://www.netezza.com/products/prodlit.cfm Nota: en el enlace anterior existen varios documentos en Formato de Documento Port=E1til (PDF) que incluyen a su vez varios casos de estudio en diferentes =E1reas, como E-Business, Gobierno, Venta, Telecomunicaciones. y otros. 7) En el site de PostgreSQL aparecen varios Casos de Estudio de implementaciones exitosas de PostgreSQL, pero los mismos no indican cantidades de registros o tama=F1o de bases de datos; pero tambi=E9n sirven como soporte. Estos casos de estudio est=E1n basados en versiones anteriores de PostgreSQL (7.2) , pero como me coment=F3 un DBA en uno de los foros de PostgreSQL: Si la versi=F3n 7.2 pudo manejar esos caso, con la gran cantidad de mejoras que ha tenido PostgreSQL en las =FAltimas versiones (sobre todo en la 8.2), se conseguir=E1n mejores resultados. 8) En Wikipedia, en la acepci=F3n de PostgreSQL, se hacen referencias a usuarios del gestor. Textualmente: "Prominent users * Afilias, domain registries for .org, .info and others.[10] * Sony Online multiplayer online games.[11] * BASF, shopping platform for their agribusiness portal.[12] * hi5.com social networking portal.[13] * Skype VoIP application, central business databases.[14]" http://en.wikipedia.org/wiki/PostgreSQL#Prominent_users En la acepci=F3n en Wikipedia en Espa=F1ol aparecen m=E1s: " Usuarios Destacados * .org, .info, .mobi y .aero registros de dominios por Afilias [1] * La American Chemical Society * BASF * IMDB * Skype * TiVo * Penny Arcade * Sony Online [2] * U.S. Departamento de Trabajo * USPS * VeriSign * Wisconsin Circuit Court Access con 6 * 180GB DBs replicados en tiempo real. * OpenACS y .LRN" http://es.wikipedia.org/wiki/PostgreSQL#Usuarios_Destacados Adicional a los Casos de Estudio antes mencionados, est=E1n las pruebas de rendimiento (Benchmarks). De Wikipedia: "Many informal performance studies of PostgreSQL have been done[7] but the first industry-standard and peer-validated benchmark was completed in June 2007 using the Sun Java Systems Application Server 9.0 Platform Edition, UltraSPARC T1 based Sun Fire server and Postgres 8.2[8]. This result of 778.14 SPECjAppServer2004 JOPS@Standard compares favourably with the 874 JOPS@Standard with Oracle 10 on an Itanium based HP-UX [7] In August 2007, Sun submitted an improved benchmark score of 813.73 SPECjAppServer2004 JOPS@Standard. With the system under test at a reduced price, the price/performance improved from $US 84.98/JOPS to $US 70.57/JOPS. [9]" http://en.wikipedia.org/wiki/PostgreSQL#Benchmarks ############# ## Mail Ends ## ############# I hope the above information be helpful for somebody else. Based in the previous information, I let you know that PostgreSQL was approved for the development of the Data Warehouse!!! :-D, but, anyway, the main application will be developed in Oracle. :-( Again, thank you very much to everybody for your help, collaboration, comments y giving time in achieving what we have today: "The world's most advanced open source database". Cheers and good night. ------=_Part_19908_13454788.1197344978460 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Good evening everybody!!!

Here is an extract of the mail I sent = to the project coordinators with the information gathered thanks to the hel= p offered by all the mailing lists I asked, this is, you; I wanted to post = it so future people looking the same information can find it all in one pla= ce:

(a few information is in Spanish, as I'm from Venezuela, where = the project is being developed. I left it as it is an extract of the mail; = but you can check the URLs I quote)

##############
## Mail Begin = ##
##############

Entre algunos Casos de Estudio exitosos de implem= entaci=F3n de PostgreSQL
como Data Warehouse, te puedo mencionar los sig= uientes (cito las partes
resaltantes y coloco el enlace donde se puede o= bservar el documento
completo):

1)
El mejor de todos los Casos de Estudio revisado= s, que incluye hasta
aspectos t=E9cnicos de implementaci=F3n, est=E1 dis= ponible un PDF de 3.7 Mb

Sugiero que lean dicho documento.

&q= uot;Oracle features we need
Partitioning
Statistics and Aggregations
rank over partition, lea= d, lag, etc.
Large selects (100GB)
Autonomous transactions
Replica= tion from Oracle (to Oracle)"

Todos esos aspectos los consiguie= ron con PostgreSQL

http://www.lethargy.org/~jesus/archives/66-Big-Bad-PostgreSQL.h= tml


2)
"Greenplum is the supplier of Bizgres MPP, a = massively parallel
implementation of the PostgreSQL open source database system. Frontier<= br>Airlines, the second largest carrier at Denver International Airport,uses Bizgres for competitive flight pricing. Bizgres, like PostgreSQL,
is an open source code project. Greenplum sponsors the Bizgres project<= br>and sells technical support and services on top of it."
...
&= quot;The appliance can be stacked with 10, 40, or 100 terabytes of disk&quo= t;
...

http://www.informationweek.com/showArticle.jhtml;jsess= ionid=3DKQDGEIMWGP3D0QSNDLRCKH0CJUNN2JVN?articleID=3D191600197&queryTex= t=3Dpostgresql

3)

...
"This talk was on converting a really lar= ge (over 3 terabytes, largest
table is 1.8 billion rows) data warehouse = database from Oracle 8i to
PostgreSQL"
...
"The reason f= or choosing Postgres over MySQL was that Postgres has a
much longer history with the kind of advanced features he needed. They<= br>needed (and were able to hack PostgreSQL to get) the following features:=
1. Data partitioning (spreading tables over multiple drives)
2. Larg= e selects (50-million-row return sets, over 100GB of data)
2. Incremental COMMITs for really, really long queries
3. Replicatio= n"

http://www.sitepoint.com/blogs/2006/08/05/oscon-20= 06-big-bad-postgresql/

4)

4.1)
"FWIW, we've got a billing database = with several moderate tables, one is
48 million rows with about 75k per = day; and some much larger tables that
are effectively partitioned by dat= e so that some client software (Excel,
etc.) doesn't choke on them. And we've got some spatial tables = that
dwarf the billing stuff."

4.2)
"> Where I wo= rk we have a data warehouse
> of similar design (a few large tables, = a few small lookup tables).  It
> has 86,840,447 rows and takes up 44 Gigs of space.  It sits o= n a
> single CPU box with a 4 disk RAID-10 and runs queries covering = a few
> minutes to a few days worth of monitoring data.  Sequent= ial scanning
> the whole main table takes 621 seconds or so (10+ minutes).  = We add
> 150 to 200k rows a day to it.
>
> Selecting a da= ys's worth of data takes ~ 350ms.  A week's worth takes
>= ; 2 to 10 seconds depending on how much is cached."

http://archives.postgresql.org/pgsql-admin/2007-11/msg00237.php

5)

5.1)
"GlobeXplorer serves terrabytes of imagery = to clients around the world
using PostGIS as their production database server. In 2004, GlobeXplore= r
migrated from Informix to PostGIS, and now they are serving over a
= million requests a day with PostGIS."

5.2)
"The nationa= l mapping agency of France manages over 100 million
topographic features in PostGIS/PostgreSQL and provides read/write
a= ccess to over 100 field researchers around the country."

http://post= gis.refractions.net/documentation/casestudies/

6)
Afilias, Ltd. - A Database for the Internet
"The = primary technical factors for using PostgreSQL are its support of
SQL st= andard and multi-version concurrency control (MVCC). MVCC helps
ensure t= hat each database transaction sees a consistent view of the
database while ensuring that high transaction volumes are supported.Additionally, PostgreSQL supports over 65,000 GB of data in a single
ta= ble, which is more than adequate for the needs if .ORG or .INFO
database= management support. .INFO and .ORG each use PostgreSQL version=20 7.2"

ht= tp://www.netezza.com/products/prodlit.cfm

Nota: en el enlace ant= erior existen varios documentos en Formato de
Documento Port=E1til (PDF)= que incluyen a su vez varios casos de estudio
en diferentes =E1reas, como E-Business, Gobierno, Venta,
Telecomunic= aciones. y otros.

7)
En el site de PostgreSQL aparecen varios Cas= os de Estudio de
implementaciones exitosas de PostgreSQL, pero los mismo= s no indican
cantidades de registros o tama=F1o de bases de datos; pero tambi=E9n si= rven
como soporte.
Estos casos de estudio est=E1n basados en versione= s anteriores de
PostgreSQL (7.2) , pero como me coment=F3 un DBA en uno = de los foros de
PostgreSQL: Si la versi=F3n 7.2 pudo manejar esos caso, con la gran
= cantidad de mejoras que ha tenido PostgreSQL en las =FAltimas versiones
= (sobre todo en la 8.2), se conseguir=E1n mejores resultados.

8)
E= n Wikipedia, en la acepci=F3n de PostgreSQL, se hacen referencias a
usuarios del gestor.

Textualmente:

"Prominent users<= br>
   * Afilias, domain registries for .org, .info and others= .[10]
   * Sony Online multiplayer online games.[11]
 =   * BASF, shopping platform for their agribusiness portal.[12]
   * hi5.com social networking= portal.[13]
   * Skype VoIP application, central business dat= abases.[14]"

http://en.wikipedia.org/wiki/PostgreSQL#Prominent_users

En la acepci=F3n en Wikipedia en Espa=F1ol aparecen m=E1s:
<= br>" Usuarios Destacados

   * .org, .info, .mobi y .a= ero registros de dominios por Afilias [1]
   * La American Che= mical Society
   * BASF
   * IMDB
   * Skype
   * TiVo
&= nbsp;  * Penny Arcade
   * Sony Online [2]
  = ; * U.S. Departamento de Trabajo
   * USPS
   * V= eriSign
   * Wisconsin Circuit Court Access con 6 * 180GB DBs = replicados en
tiempo real.
   * OpenACS y .LRN"

http://es.wikip= edia.org/wiki/PostgreSQL#Usuarios_Destacados





Adi= cional a los Casos de Estudio antes mencionados, est=E1n las pruebas de
rendimiento (Benchmarks).

De Wikipedia:

"Many inform= al performance studies of PostgreSQL have been done[7] but
the first ind= ustry-standard and peer-validated benchmark was completed
in June 2007 u= sing the Sun Java Systems Application Server=20 9.0 Platform
Edition, UltraSPARC T1 based Sun Fire server and Postgres 8= .2[8]. This
result of 778.14 SPECjAppServer2004 JOPS@Standard compares f= avourably
with the 874 JOPS@Standard with Oracle 10 on an Itanium based = HP-UX [7]

In August 2007, Sun submitted an improved benchmark score of 813.73=
SPECjAppServer2004 JOPS@Standard. With the system under test at a
re= duced price, the price/performance improved from $US 84.98/JOPS to $US
70.57/JOPS. [9]"

http://en.wikipedia.org/wiki/PostgreSQL#Benchmarks
=
#############
## Mail Ends ##
#############

I hope the abo= ve information be helpful for somebody else.

Based in the previous information, I let you know that PostgreSQL w= as approved for the development of the Data Warehouse!!! :-D, but, anyway, = the main application will be developed in Oracle. :-(

Again, thank y= ou very much to everybody for your help, collaboration, comments y giving t= ime in achieving what we have today: "The world's most advanced op= en source database".

Cheers and good night.


------=_Part_19908_13454788.1197344978460--