pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: reina_ga@hotmail.com
To: pgsql-admin@postgresql.org
Subject: Re: Database storage bloat -- FOLLOWUP
Date: 11 Apr 2004 13:57:40 -0700
Message-ID: <272e4be7.0404111257.555ad4a7@posting.google.com> (raw)
References: <272e4be7.0404080115.39f1cb36@posting.google.com>
	<20040408063838.Q93611@megazone.bigpanda.com>
	<BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com>
	<15732.1081446563@sss.pgh.pa.us>
	<BAY8-DAV64SOlbpH71y00020ce6@hotmail.com>

I thought I'd make a followup to the question of storage bloat. I
tried approaching the problem by living a little loosely with database
normalization and use arrays instead of flattening everything out in
the tables.

So my original table,

CREATE TABLE original (
dbIndex integer,
index1 smallint,
index2 smallint,
index3 smallint,
dvalue real
):

becomes

CREATE TABLE newtable (
dbIndex integer,
dvalue   real[][][]
);

Now I'd expect to get better storage here just because I'm dropping 3
smallint fields (so 2 bytes x 3 for each value, or a 6 fold decrease).

However, in actuality I'm seeing a 30 fold decrease:

original table = 48 MB
newtable       = 1.6 MB !!!!!

If I'm understanding the data directory correctly, the array tuples
are being toasted (which I think must compress them). The actual table
in the newtable format is only 8K and the pg_toast that goes with this
table is 1.6MB.

So I'm very pleased with the storage here. Plus, reads are faster than
the original design. All in all, I think I've found a good solution
for my kind of data.

Now I know that the use of arrays is considered taboo in the books,
but in my case the data being stored actually do fall into an array
(matrix)  structure naturally (it's a timebased recording so there's a
sample for each time point and each X,Y position -- a natural 3D
matrix).

What are the possible downsides that I may be missing?



Message-ID: <272e4be7.0404111257.555ad4a7@posting.google.com>
Permalink:  ../272e4be7.0404111257.555ad4a7@posting.google.com/
Also on:    postgresql.org/message-id/272e4be7.0404111257.555ad4a7@posting.google.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
  Subject: Re: Database storage bloat -- FOLLOWUP
  In-Reply-To: <272e4be7.0404111257.555ad4a7@posting.google.com>
  References: <272e4be7.0404080115.39f1cb36@posting.google.com> <20040408063838.Q93611@megazone.bigpanda.com> <BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com> <15732.1081446563@sss.pgh.pa.us> <BAY8-DAV64SOlbpH71y00020ce6@hotmail.com> <272e4be7.0404111257.555ad4a7@posting.google.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