Re: BIG files

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: rabt(at)dim(dot)uchile(dot)cl
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: BIG files
Date: 2005-06-20 14:36:46
Message-ID: 6806.1119278206@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

rabt(at)dim(dot)uchile(dot)cl writes:
> Tom, this is my table definition including domains:

The problem appears to be that you have defined all the columns as NOT
NULL with defaults that are obviously NULL substitutes, eg

> CREATE DOMAIN f29.tipo_idcomuna
> AS int4
> DEFAULT 99999
> NOT NULL
> CONSTRAINT tipo_idcomuna_check CHECK ((VALUE <= 99999) AND (VALUE >= 0));

That means that every one of the 99 columns is actually present, with a
value, in every row. According to VACUUM FULL VERBOSE the actual
on-disk widths of your three sample rows range from 816 to 828 bytes,
which works out to an average column width of 8 or so bytes, which seems
reasonable.

Had you allowed the missing columns to go to NULL there would only be a
dozen or so actual values stored in each of these sample rows, so
(allowing for an extra 12 bytes for the nulls bitmap) the stored width
ought to be in the vicinity of 140-150 bytes. Which more than accounts
for the bloat you are seeing.

In short: use NULL the way it was intended to be used, that is, to
indicate missing values.

regards, tom lane

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Mace, Richard 2005-06-20 15:55:51
Previous Message sara simoes 2005-06-20 10:00:38 Data Migration from Access to Postgresql