[postgis-users] postgis type size

[Paul Ramsey]

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 at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users