X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 08472D1D2EA for ; Thu, 8 Apr 2004 12:49:28 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 90962-07 for ; Thu, 8 Apr 2004 12:49:26 -0300 (ADT) Received: from hotmail.com (bay8-dav4.bay8.hotmail.com [64.4.26.108]) by svr1.postgresql.org (Postfix) with ESMTP id 324E6D1D283 for ; Thu, 8 Apr 2004 12:49:25 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 8 Apr 2004 08:49:24 -0700 Received: from 80.21.203.111 by bay8-dav4.bay8.hotmail.com with DAV; Thu, 08 Apr 2004 15:49:24 +0000 X-Originating-IP: [80.21.203.111] X-Originating-Email: [reina_ga@hotmail.com] X-Sender: reina_ga@hotmail.com From: "Tony and Bryn Reina" To: "Stephan Szabo" Cc: References: <272e4be7.0404080115.39f1cb36@posting.google.com> <20040408063838.Q93611@megazone.bigpanda.com> Subject: Re: Database storage bloat Date: Thu, 8 Apr 2004 17:49:26 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: X-OriginalArrivalTime: 08 Apr 2004 15:49:24.0401 (UTC) FILETIME=[10DB4A10:01C41D81] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/154 X-Sequence-Number: 13127 > Well, an important question is where is that space going? It'd be > interesting to give a breakup by the directories and then which files (and > using the contrib/oid2name to get which table/indexes/etc they are). > > At least 16MB of that is probably going into the transaction log (IIRC > that's the default size for the segments) in pg_xlog. > Thanks Stephan. That at least helps me narrow it down to my one problem table: segmentvalues. It is taking up almost 50 MB and its primary key is over 38MB. Nothing else even comes close. Here's the relevant output for oid2name in order of size. SIZE (KB) OID TABLENAME 48844 17296 = segmentvalues 38100 17298 = segmentvalues_pkey 1108 16642 = pg_proc_proname_args_nsp_index 852 17246 = neuralvalues 676 17248 = neuralvalues_pkey .. .. Tom asked to see the table schema. Here's the 3 relevant tables for the bloat: CREATE SEQUENCE FileIndicies_fileID_seq; CREATE TABLE FileIndicies ( fileID integer DEFAULT nextval('FileIndicies_fileID_seq') UNIQUE NOT NULL, --PK szFileName text NOT NULL, szFileType text, CreationDate date, CreationTime time, dtimestampresolution float4, dtimespan float4, szAppName text, szFileComment text, PRIMARY KEY (fileID), UNIQUE (szFileName, szFileType, CreationDate, CreationTime) ); CREATE SEQUENCE EntityFile_dbIndex_seq; CREATE TABLE EntityFile ( EntityID integer, --PK fileID integer REFERENCES FileIndicies (fileID), dbIndex integer DEFAULT nextval('EntityFile_dbIndex_seq') UNIQUE NOT NULL, PRIMARY KEY (fileID, EntityID) ); CREATE TABLE SegmentValues ( dbIndex integer REFERENCES EntityFile (dbIndex), dwunitid smallint, dwsampleindex smallint, dtimestamp float4, dvalue float4, PRIMARY KEY (dbIndex, dtimestamp, dwsampleindex, dwunitid) ); I suppose one thing the binary flat file may be doing is not including the time stamp in table SegmentValues. Since I know the sampling rate, I can just calculate the timestamp on the fly by the rate times the index (assuming no time offset). That would lose a float4 field, but would add back a smallint field to the table. Is there any further normalization that I'm missing? -Tony