Re: reducing NUMERIC size for 9.1

On Jul 15, 2010, at 11:58 AM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> On 10 July 2010 00:58, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> EnterpriseDB asked me to develop the attached patch to reduce the
>> on-disk size of numeric and to submit it for inclusion in PG 9.1.
>> After searching the archives, I found a possible design for this by
>> Tom Lane based on an earlier proposal by Simon Riggs.
>
> Hi Robert,
>
> I'm reviewing this patch for the commitfest, and so far everything in
> the patch looks good. Compile and regression tests worked fine.
>
> However, I was trying to find a simple way to verify that it really
> was reducing the on-disk size of compact numeric values and didn't get
> the results I was expecting.
>
> I dropped one thousand numerics with value zero into a table and
> checked the on-disk size of the relation with your patch and on a
> stock 8.4 instance. In both cases the result was exactly the same.
>
> Shouldn't the table be smaller with your patch? Or is there something
> wrong with my test?
>
> CREATE TEMP TABLE numeric_short (a numeric);
>
> INSERT INTO numeric_short (a)
> SELECT 0::numeric FROM generate_series(1, 1000) i;

Well, on that test, you'll save only 2000 bytes, which is less than a full block, so there's no guarantee the difference would be noticeable at the relation level. Scale it up by a factor of 10 and the difference should be measurable.

You might also look at testing with pg_column_size().

...Robert