pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tony and Bryn Reina <reina_ga@hotmail.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Database storage bloat
Date: Thu, 8 Apr 2004 17:49:26 +0200
Message-ID: <BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com> (raw)
References: <272e4be7.0404080115.39f1cb36@posting.google.com>
	<20040408063838.Q93611@megazone.bigpanda.com>

> 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









view thread (13+ messages)  latest in thread

Message-ID: <BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com>
Permalink:  ../BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com/
Also on:    postgresql.org/message-id/BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: reina_ga@hotmail.com, sszabo@megazone.bigpanda.com
  Subject: Re: Database storage bloat
  In-Reply-To: <BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com>
  References: <272e4be7.0404080115.39f1cb36@posting.google.com> <20040408063838.Q93611@megazone.bigpanda.com> <BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox