Configures the storage mode for geometry and geography columns, keeping a fixed number of bytes in-row and moving the rest off-row. Use this function when tables with large spatial objects cause the query optimizer to skip spatial indexes and fall back to sequential scans.
Syntax
bool ST_SetTypeStorage(cstring typeName, cstring storageStrategy, integer size);Parameters
| Parameter | Type | Description |
|---|---|---|
typeName |
cstring | The spatial data type to configure. Valid values: geometry, geography. |
storageStrategy |
cstring | The PostgreSQL TOAST storage strategy to apply. The only supported value is main. The main strategy allows compression and keeps data in-row when possible, falling back to off-row storage only as a last resort. |
size |
integer | The number of bytes to store in-row. Set to 0 to keep main mode without changing the in-row threshold. |
Description
PostgreSQL uses TOAST (The Oversized-Attribute Storage Technique) to handle column values that exceed the page size. By default, spatial data uses the extended strategy, which compresses and moves large values off-row. This causes a problem when a table contains few rows but large geometry objects: the main table appears small to the query optimizer, so it skips the spatial index and does a sequential scan — but the sequential scan must read all off-row TOAST pages, causing unexpectedly high disk I/O.
The main strategy addresses this by keeping a fixed number of bytes in-row. For spatial types, those in-row bytes hold the bounding box metadata that spatial operators like && need to filter rows. For normal query and analysis workloads, PolarDB needs to read only the in-row metadata, which reduces disk I/O. The optimizer can then evaluate bounding box conditions without fetching full geometry values from the TOAST table, and spatial indexes are used as expected.
Recommended size values
The recommended values correspond to the bounding box metadata size for each geometry dimension. Spatial operators read this in-row data to filter rows without fetching the full geometry.
| Data type | Dimension | Recommended value |
|---|---|---|
| geometry | 2d(x,y) | 24 |
| 3d(x,y,z) and 3dm(x,y,m) | 32 | |
| 4d(x,y,z,m) | 40 | |
| geography | 2d(x,y) | 32 |
Example
Set the storage strategy for geometry to main with 41 bytes stored in-row:
Select ST_SetTypeStorage('geometry','main', 41);
st_settypestorage
-------------------
t
(1 row)The function returns t (true) on success.