Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 77D342E0099 for ; Tue, 27 Nov 2007 02:12:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 65124-06 for ; Tue, 27 Nov 2007 02:12:26 -0400 (AST) Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.188]) by postgresql.org (Postfix) with ESMTP id 4543C2E0092 for ; Tue, 27 Nov 2007 02:12:28 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id l15so793876rvb for ; Mon, 26 Nov 2007 22:12:27 -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:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=Hjv9iHy2wdnU/eCsN2c6S1V80qE/Ik/3XKyXwuHLF/g=; b=X8e19jCdkz7+hRObd2z0PiaamJahCb85yEOdO9fSGGOQL906VBK0dcGGwC26DyhcCtJ7kWQM6EfvuDlAT0bi9BnINDJB9ktxrWglQ7k+jYcAraXNCROUckXSfWgIghbf+Fc1Y07CKVdZEmKG31RbbVe/TEgczPdA0qwcJQvYNuU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=WvwzhmD5TA/dnwrwJgtGtDJ33gg7lAEfTHxuL9bRbk6u6TS3cFx4Xr9Btk/EiQqmgSH+32zEyobJ2iQnyvtgocLGCGw99dORKlV67pOxadbDCmplKaCbzjYtDZKE0WW5PECyUBTpFBqdUvkefiSKVVM7Y9Q3qASkJDo4eAIY+vE= Received: by 10.114.15.1 with SMTP id 1mr882429wao.1196143947137; Mon, 26 Nov 2007 22:12:27 -0800 (PST) Received: by 10.114.53.7 with HTTP; Mon, 26 Nov 2007 22:12:27 -0800 (PST) Message-ID: Date: Tue, 27 Nov 2007 00:12:27 -0600 From: "Scott Marlowe" To: "=?ISO-8859-1?Q?Elvis_Henr=EDquez?=" Subject: Re: Case studies Cc: pgsql-admin@postgresql.org In-Reply-To: <493343bd0711261934p43fe15f9q46aaae78c8b6ddd4@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <493343bd0711261934p43fe15f9q46aaae78c8b6ddd4@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/232 X-Sequence-Number: 27475 On Nov 26, 2007 9:34 PM, Elvis Henr=EDquez wrot= e: > Hello everybody. > > Rather than asking for a technical detail or writing about a problem, > I'm asking for up-to-date case studies involving PostgreSQL. > > The company where I'm actually working is migrating some apps; one > requires a Data Warehouse, and I'm proposing PostgreSQL, but they're > thinking of Oracle, as the system has one table (among others) with 20 > fields and more then 19 millions of records, and this exists for 2 > subcompanies, which are actually in different databases, but the > migration project implies joining the two subcompanies data. > > This amount of data is the result of 8 years of usage, and the data > growing rate has increased in the last two years. 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.