Re: double precision[] storage space questions

Francisco Olarte wrote
> Hi Greg:
>
> On Fri, Jun 12, 2015 at 4:08 PM, greg <

> gregory.jevardat@

> > wrote:
>> I cannot find any documentation on the space taken by a double precision
>> array. And the few tests I did surprise me.
>>
>> Here are a few tries I did to understand
>> select pg_column_size(1.1::double precision)              return 8    ---
>> as
>> expected
>> select pg_column_size('{}'::double precision[])           return 16  ---
>> ok
>> maybe an array header
>> select pg_column_size('{1.111}'::double precision[])   return 32  --- I
>> expected 16+ sizeof(double) = 24
>>
>> select
>> pg_column_size('{1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,10.0}'::double
>> precision[])  return 104 --- I'am lost because I expected 10*16 + 16 =
>> 176.
>> It is neither 16+10*8 (96)
>
> That's not a very good set of tests, look at mine ( slightly edited for
> size );
>
> apc=# select pg_column_size('{}'::double precision[]);
>              16
> apc=# select pg_column_size('{1.0}'::double precision[]);
>              32
> apc=# select pg_column_size('{1.0,2.0}'::double precision[]);
>              40
> apc=# select pg_column_size('{1.0,2.0,3.0}'::double precision[]);
>              48
> apc=# select pg_column_size('{1.0,2.0,3.0,4.0}'::double precision[]);
>              56
>
> Here I already expect 8*n+24, so the data point for 10
>
> apc=# select
> pg_column_size('{1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,10.0}'::double
> precision[]);
>             104
>
> Does not surprise me.
>
>> So what is happening behind the scene, I did not found any documentation.
>> Since the queries are done in memory I suppose no compression is going
>> on.
>> Furthermore select
>> pg_column_size('{1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0}'::double
>> precision[]) return 104 as well. So I discard compression.
>
> You're hunting for wild things, as said in some other places, headers,
> special case for dimensionless arrays, it's a classic thing in
> databases.
>
>> The whole point is that in the application I work on, we store double
>> arrays
>> as bytea (using some serialization before storing the data).
>> I was very surprised to see that the storage of an array of double take
>> more
>> space using double precision[] than serializing it and storing it into a
>> bytea.
>
> Not too much, just 20 bytes more per column, unless you play
> compression tricks. Unless you have lots of small columns, I doubt
> serializing/deserializing it is worth the hassle. Postgres does not
> always use the most compact form for storage. In fact I would be
> greatly surprised that any database stores an array ( which can be
> multidimensional, I do not know if other databases have single
> dimensional array types ) in a more compact way than an specialized
> serialization format for one dimensional double arrays.
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (

> pgsql-general@

> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Thanks a lot for this crystal clear explanation. Indeed my tests were bad, I
should have find it by myself ... So there is 24 extra bytes per column.
Given the average length of my arrays it is not sure that the byte storage
worth it.


Greg



--
View this message in context:
http://postgresql.nabble.com/double-precision-storage-space-questions-tp5853581p5853626.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.