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?
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