Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group