Re: Array columns vs normalized table

Hello

2010/3/2 Lee Hachadoorian <lee(dot)hachadoorian(at)gmail(dot)com>:
> I work with state labor data which is reported to us in the form
>
>        industry, year, quarter1, quarter2, quarter3, quarter4
>
> where each quarter represents an employment count. Obviously, this can
> be normalized to
>
>        industry, year, quarter, employment
>
> Can anyone comment on, or point to me to an article or discussion
> regarding, why one would use an array column instead of normalizing
> the data? That is, would there be any benefit to storing it as

for very large timeseries you can use arrays. The storage can be more effective.

sample

industry year q1 q2 q3 q4
1, 2001, 10,11,12,13
1, 2002, 14, 15,16,17

can be transformed to tuple (industry: 1, start_year: 2002, data:
10,11,12,13,14,15,16,17, ..... )

your model isn't more safer then normalised tables and I think is
better prefer normalized tables.

the queries to arrays are difficult - so usually data from arrays are
dynamically unpacked to tables. But time series stored as array take
significantly less space on disc. Look on
http://www.postgresql.org/docs/8.3/static/storage-page-layout.html -
you safe repeated row headers.

Regards
Pavel Stehule

I hope so nobody uses this technique. It cannot substitute normalizated tables.

>
>        industry int, year smallint, employment int[ ]

if

>
> where the last column would be a four element array with data for the
> four quarters.
>
> Thanks,
> --Lee
>
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>