Re: 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(at)postgresql(dot)org on behalf of Elvis Henríquez
Sent: Tue 11/27/2007 7:32 PM
To: Scott Marlowe
Cc: pgsql-admin(at)postgresql(dot)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(dot)marlowe(at)gmail(dot)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