Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 7EB172E0118 for ; Wed, 28 Nov 2007 06:32:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 29981-09 for ; Wed, 28 Nov 2007 06:32:20 -0400 (AST) Received: from copgpuniv.digitalglobe.com (ew-fw-out.digitalglobe.com [205.166.175.2]) by postgresql.org (Postfix) with ESMTP id D267B2E0D55 for ; Wed, 28 Nov 2007 06:32:23 -0400 (AST) Received: from copgpuniv.digitalglobe.com (localhost.localdomain [127.0.0.1]) by copgpuniv.digitalglobe.com (PGP Universal) with ESMTP id 079C92A030E; Wed, 28 Nov 2007 03:32:22 -0700 (MST) Received: from comailgate.digitalglobe.com ([10.10.42.50]) by copgpuniv.digitalglobe.com (PGP Universal service); Wed, 28 Nov 2007 03:32:22 -0700 X-PGP-Universal: processed; by copgpuniv.digitalglobe.com on Wed, 28 Nov 2007 03:32:22 -0700 Received: from COMAIL01.digitalglobe.com ([10.10.42.61]) by comailgate.digitalglobe.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 28 Nov 2007 03:32:21 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C831A9.F50944E3" Subject: Re: Case studies Date: Wed, 28 Nov 2007 03:32:21 -0700 Message-ID: <8B319E5A30FF4A48BE7EEAAF609DB233015E2FC9@COMAIL01.digitalglobe.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Case studies Thread-Index: AcgxZxR7PYLdNjFuT2y4i2odjQHaIwAQTDwo References: <493343bd0711261934p43fe15f9q46aaae78c8b6ddd4@mail.gmail.com> <493343bd0711271832l7ddf96cx57a91bd082717a28@mail.gmail.com> From: "Gregory Williamson" To: =?iso-8859-1?Q?Elvis_Henr=EDquez?= , "Scott Marlowe" Cc: X-OriginalArrivalTime: 28 Nov 2007 10:32:21.0480 (UTC) FILETIME=[F54DA680:01C831A9] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/238 X-Sequence-Number: 27481 This is a multi-part message in MIME format. ------_=_NextPart_001_01C831A9.F50944E3 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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. The case studies you looked at all still valid, really, if you think = about it -- PostgreSQL has gotten *better* with successive releases, and = has also gotten faster. So fewer worries and more speed. If 7.4.x could = handle something it is almost certain that 8.2 can do so as well and = probably better. You might also take a quick look at the case studies for the PostGIS = extension at = . (The = GlobeXplorer stuff is us; DigitalGlobe is now using PostgreSQL as well.) We migrated from Informix 9.3 some years ago (mainly due to cost) and = haven't regretted it. Greg Williamson Senior DBA GlobeXplorer, a DigitalGlobe Company (some legal disclaimers snipped since this is a public mail ... please = excuse the top-posting but this is a lame reader and the post I am = responding to was top-posted so at least this is consistent) -----Original Message----- From: pgsql-admin-owner@postgresql.org on behalf of Elvis Henr=EDquez Sent: Tue 11/27/2007 7:32 PM To: Scott Marlowe Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Case studies =20 That's what I'm telling to them, but they want proven results in other companies, but I'll manage it. Thanks for your reply and information. Best regards. On Nov 27, 2007 2:12 AM, Scott Marlowe wrote: > That's actually pretty small. 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. > > This is a small database for either oracle or postgresql. Talk your > bosses into giving postgresql a try if you can. You should be able to > build a 20million test database in an afternoon or so, so it's not > like you'll be dedicating thousands of man hours to test it. > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ------_=_NextPart_001_01C831A9.F50944E3 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [ADMIN] Case studies

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.

The case studies you looked at all still valid, really, if you think = about it -- PostgreSQL has gotten *better* with successive releases, and = has also gotten faster. So fewer worries and more speed. If 7.4.x could = handle something it is almost certain that 8.2 can do so as well and = probably better.

You might also take a quick look at the case studies for the PostGIS = extension at <http:/= /postgis.refractions.net/documentation/casestudies/>. (The = GlobeXplorer stuff is us; DigitalGlobe is now using PostgreSQL as = well.)

We migrated from Informix 9.3 some years ago (mainly due to cost) and = haven't regretted it.

Greg Williamson
Senior DBA
GlobeXplorer, a DigitalGlobe Company

(some legal disclaimers snipped since this is a public mail ... please = excuse the top-posting but this is a lame reader and the post I am = responding to was top-posted so at least this is consistent)

-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Elvis = Henr=EDquez
Sent: Tue 11/27/2007 7:32 PM
To: Scott Marlowe
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Case studies

That's what I'm telling to them, but they want proven results in = other
companies, but I'll manage it.

Thanks for your reply and information.

Best regards.

On Nov 27, 2007 2:12 AM, Scott Marlowe <scott.marlowe@gmail.com> = wrote:
> That's actually pretty small.  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.
>
> This is a small database for either oracle or postgresql.  = Talk your
> bosses into giving postgresql a try if you can.  You should be = able to
> build a 20million test database in an afternoon or so, so it's = not
> like you'll be dedicating thousands of man hours to test it.
>

---------------------------(end of = broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your = joining column's datatypes do not
       match

------_=_NextPart_001_01C831A9.F50944E3--