Thread: array behavior

If a field(attribute) in a type is declared to be an array. Then is it always the case that the array will be packed
intoa single tuple that resides in the database. There is the TOAST mechanism for oversized tuples but that is still
consideredto be a single tuple. Is there any circumstance in which an attribute which is an array will be broken up
intoindividual
 
tuples which are somehow associated with the main tuple. Such as if the array happens to have 5000 elements and the
tuplewill become quite large if these are packed within a single tuple and additionally it may have undesirable
performanceimpact if the queries are not even interested in seeing the array when fetching the object ?
 

thanks

On Fri, Dec 16, 2011 at 00:15, amit sehas <cun23@yahoo.com> wrote:
> There is the TOAST mechanism for oversized tuples but that is still
> considered to be a single tuple. Is there any circumstance in which an
> attribute which is an array will be broken up into individual
> tuples which are somehow associated with the main tuple.

I think you've misunderstood TOAST. That's exactly what TOAST does now.
TOAST works by storing individual *attributes* (values) in a separate
table -- not entire tuples. All smaller columns in a row are still stored
together with other rows, and only large fields are split out.

> and additionally it may have undesirable performance impact if the queries
> are not even interested in seeing the array when fetching the object ?

If your query doesn't touch the TOASTed field, it's not even read from the
TOAST table. However, large values that are smaller than the TOAST threshold
(2kB compressed) sometimes cause problems, since they're stored inline in
the heap, bloating the table size and thus increasing the time it takes to
scan it.

Regards,
Marti