[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] postgis type size
From:       Paul Ramsey <pramsey () cleverelephant ! ca>
Date:       2022-04-11 15:14:19
Message-ID: 8735100B-6D62-4BEB-8FC9-3AF5C2AEE416 () cleverelephant ! ca
[Download RAW message or body]

Geometry is a variable sized type (aka "varlena") like strings so there is not a \
fixed size. You can see the storage size of any given geometry with st_memsize().

Geometry is 8-byte aligned and internally packed already so there's nothing further \
to be gained by juggling its position in the table definition. Just put it at the \
front with all the other aligned types.

When geometries get big enough, they are off loaded into the TOAST tables and the \
space in the main tuple is occupied by a pointer to the TOAST records. See the TOAST \
documentation for PostgreSQL for more information if you are curious.

P

> On Apr 11, 2022, at 6:53 AM, Jian He <hejian.mark@gmail.com> wrote:
> 
> select pg_column_size(st_geomfromtext('multipoint(-1 1,0 0,2 3)')); --104.
> 
> select pg_column_size( st_geomfromtext('point(-1 1)')); --return 32.
> 
> select st_geomfromtext('multipoint zm(-1 1 3 4 , 0 0 1 2, 2 3 1 2)');
> select pg_column_size(st_geomfromtext('multipoint zm(-1 1 3 4 , 0 0 1 2, 2 3 1 \
> 2)')); --return 168. 
> select st_geomfromtext('multipoint z(-1 1 3, 0 0 1, 2 3 1)');
> 
> select pg_column_size(st_geomfromtext('multipoint z(-1 1 3, 0 0 1, 2 3 1)')); \
> --return 136. from the above query result, seems in postgis the column size is \
> quite large.  
> In postgresql(outside postgis),  there is  a data type paddling. If less than 8 \
> byte then it will padding.  What about postgis data type, is there also a padding \
> thing?  
> So if pg_column_size return the correct result of data type size, then 8 kb per \
> page cannot store much column. Generally how many columns can i store? 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]