X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 63DCD329EEF for ; Wed, 6 Oct 2004 22:36:17 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57880-02 for ; Wed, 6 Oct 2004 21:36:09 +0000 (GMT) Received: from vsmtp1.tin.it (vsmtp1.tin.it [212.216.176.141]) by svr1.postgresql.org (Postfix) with ESMTP id BB8EA329EBD for ; Wed, 6 Oct 2004 22:36:07 +0100 (BST) Received: from angus.tin.it (82.53.57.193) by vsmtp1.tin.it (7.0.027) id 4162DC020009E4F5 for pgsql-performance@postgresql.org; Wed, 6 Oct 2004 23:36:07 +0200 Message-Id: <6.1.2.0.2.20041006230239.0201bd40@box.tin.it> X-Sender: angusgb@box.tin.it (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 6.1.2.0 Date: Wed, 06 Oct 2004 23:36:05 +0200 To: pgsql-performance@postgresql.org From: Gabriele Bartolini Subject: Data warehousing requirements Mime-Version: 1.0 Content-Type: multipart/mixed; x-avg-checked=avg-ok-13F04943; boundary="=======395821=======" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_RFCI X-Spam-Level: X-Archive-Number: 200410/71 X-Sequence-Number: 8547 --=======395821======= Content-Type: text/plain; x-avg-checked=avg-ok-13F04943; charset=us-ascii; format=flowed Content-Transfer-Encoding: 8bit Hi guys, I just discussed about my problem on IRC. I am building a Web usage mining system based on Linux, PostgreSQL and C++ made up of an OLTP database which feeds several and multi-purpose data warehouses about users' behaviour on HTTP servers. I modelled every warehouse using the star schema, with a fact table and then 'n' dimension tables linked using a surrogate ID. Discussing with the guys of the chat, I came up with these conclusions, regarding the warehouse's performance: 1) don't use referential integrity in the facts table 2) use INTEGER and avoid SMALLINT and NUMERIC types for dimensions' IDs 3) use an index for every dimension's ID in the fact table As far as administration is concerned: run VACUUM ANALYSE daily and VACUUM FULL periodically. Is there anything else I should keep in mind? Also, I was looking for advice regarding hardware requirements for a data warehouse system that needs to satisfy online queries. I have indeed no idea at the moment. I can only predict 4 million about records a month in the fact table, does it make sense or not? is it too much? Data needs to be easily backed up and eventually replicated. Having this in mind, what hardware architecture should I look for? How many hard disks do I need, what kind and what RAID solution do you suggest me to adopt (5 or 10 - I think)? Thank you so much, -Gabriele -- Gabriele Bartolini: Web Programmer, ht://Dig & IWA/HWG Member, ht://Check maintainer Current Location: Prato, Toscana, Italia angusgb@tin.it | http://www.prato.linux.it/~gbartolini | ICQ#129221447 > "Leave every hope, ye who enter!", Dante Alighieri, Divine Comedy, The Inferno --=======395821======= Content-Type: text/plain; charset=us-ascii; x-avg=cert; x-avg-checked=avg-ok-13F04943 Content-Disposition: inline --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.773 / Virus Database: 520 - Release Date: 05/10/2004 --=======395821=======--