Estimating storage cost of geometry columns

This page measures what geometry actually costs to store: a per-vertex estimate you can apply before loading anything, an ST_MemSize accounting of what landed, the compression and TOAST effects that make the on-disk number differ from both, and a monthly snapshot model that turns growth into a forecast.

When to use this approach

• Before ingesting a new source. A vendor’s “1.2 million features” says nothing about size; the same count can be 200 MB or 40 GB depending on vertex density.
• When storage is growing and row counts are not. That is a geometry problem by definition, and the accounting here locates it.
• When deciding what a mart may carry. Geometry duplicated across five marts costs five times, which is the argument for keys-plus-lookup described in serving spatial data to consumers.

Prerequisites

• PostGIS with ST_MemSize and ST_NPoints, or an equivalent size function on your engine.
• Access to table-size metadata: pg_total_relation_size on PostGIS, INFORMATION_SCHEMA.TABLE_STORAGE on BigQuery, TABLE_STORAGE_METRICS on Snowflake.
• A schema for snapshot history, as set up in spatial observability and cost control.
• Rough vertex counts for the source, obtainable from a sample rather than the whole file.

Step-by-step instructions

1. Estimate before you load

PostGIS stores a geometry as a small header plus one coordinate pair per vertex, each ordinate a double. The arithmetic is simple enough to do on a napkin and accurate enough to plan with.

bytes ≈ header (about 8–12) + 16 × vertices          (2D)
bytes ≈ header + 24 × vertices                       (3D, Z)

-- Estimate from a sample of the source before committing to a load
select
    count(*)                                   as sample_features,
    round(avg(st_npoints(geom)))               as avg_vertices,
    round(avg(st_memsize(geom)))               as avg_bytes,
    round(avg(st_memsize(geom)) * 1200000 / 1024.0 / 1024 / 1024, 2) as projected_gb_for_1_2m
from {{ ref('stg_sample_features') }}

Verify the estimate against the formula — if avg_bytes is far from 12 + 16 × avg_vertices, the geometry is likely 3D or a collection type, both of which change the arithmetic.

2. Account for what actually landed

ST_MemSize reports the uncompressed in-memory size, which is the right number for reasoning about geometry but not the number on the disk. Measure both and keep the ratio.

-- models/ops/int_geometry_storage.sql
{{ config(materialized = 'table') }}

with per_table as (
    select
        'mart_zones'                                  as table_name,
        count(*)                                      as feature_count,
        sum(st_memsize(geom))                         as geometry_bytes_uncompressed,
        sum(st_npoints(geom))                         as total_vertices,
        max(st_npoints(geom))                         as max_vertices
    from {{ ref('mart_zones') }}
)

select
    p.*,
    pg_total_relation_size('analytics.mart_zones')                       as on_disk_bytes,
    round((p.geometry_bytes_uncompressed::numeric
           / nullif(pg_total_relation_size('analytics.mart_zones'), 0)), 3) as geometry_share_of_table
from per_table p

Verify the ratio is plausible — on PostGIS with default settings, expect the on-disk figure to be somewhere between half and twice the uncompressed geometry, depending on how well the coordinates compress and how much index and row overhead the table carries.

3. Understand where the number moves

Three effects make the on-disk size differ from the arithmetic, and knowing which one is acting tells you which lever to pull.

The last row is the actionable one. Reducing stored precision to what the use case needs — 11 cm rather than a fraction of a micron — reduces entropy in the coordinate stream, and the compressor then does the rest. This is separate from simplification, which removes vertices; precision reduction keeps every vertex and shortens each one.

-- Measure the compression effect of precision reduction on your own data
select
    pg_size_pretty(sum(st_memsize(geom))::bigint)                                  as raw,
    pg_size_pretty(sum(st_memsize(st_reduceprecision(geom, 0.000001)))::bigint)    as reduced
from {{ ref('mart_zones') }};

4. Snapshot monthly and forecast

-- models/ops/snap_geometry_storage.sql
{{ config(materialized = 'incremental', unique_key = ['snapshot_month', 'table_name']) }}

select
    date_trunc('month', current_date)::date as snapshot_month,
    table_name,
    feature_count,
    geometry_bytes_uncompressed,
    on_disk_bytes,
    total_vertices
from {{ ref('int_geometry_storage') }}
{% if is_incremental() %}
where date_trunc('month', current_date)::date
      not in (select snapshot_month from {{ this }})
{% endif %}

-- analyses/storage_forecast.sql
select
    table_name,
    max(on_disk_bytes) filter (where snapshot_month = (select max(snapshot_month) from {{ ref('snap_geometry_storage') }}))
        as current_bytes,
    round(avg(monthly_growth), 3) as avg_monthly_growth_ratio,
    round(max(on_disk_bytes) * power(avg(monthly_growth), 12) / 1024^4, 2) as projected_tb_in_12_months
from (
    select
        table_name, snapshot_month, on_disk_bytes,
        on_disk_bytes::numeric / nullif(lag(on_disk_bytes) over (
            partition by table_name order by snapshot_month), 0) as monthly_growth
    from {{ ref('snap_geometry_storage') }}
) g
group by table_name
order by projected_tb_in_12_months desc

Verify the forecast against the last twelve months before trusting it forward: a table that grew steadily is forecastable, one that jumped on a single reload is not, and the difference is visible in the growth ratios.

5. Set a budget and test it

models:
  - name: int_geometry_storage
    columns:
      - name: geometry_share_of_table
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 0.9

-- tests/assert_storage_budget.sql
select table_name, on_disk_bytes
from {{ ref('int_geometry_storage') }}
where on_disk_bytes > {{ var('table_storage_budget_bytes') }}

A budget test is worth more than a dashboard because it makes the conversation happen at the right time: when a model first exceeds the agreed size, rather than at the quarterly cost review.

Configuration reference

Gotchas & edge cases

• ST_MemSize is not disk size. It ignores compression, TOAST overhead, indexes and dead tuples. Use it to compare geometry against geometry, and table-size functions to compare against a bill.
• Bloat masquerades as growth. A table that is updated frequently accumulates dead tuples; measure after a vacuum before concluding the data grew.
• 3D geometry costs 50 per cent more per vertex and is often carried accidentally, because the source had a Z ordinate nobody uses. Check with ST_NDims and drop it in staging if so.
• Collections multiply headers. A MULTIPOLYGON of 200 small parts carries 200 sub-geometry headers as well as its vertices; splitting or dissolving can reduce size materially.
• Copies are the real cost. One geometry column carried through five marts costs five times over, and the fix is architectural rather than an encoding trick.

FAQ

Related

• Spatial Observability & Cost Control — where storage sits among the other signals.
• Handling Large Geospatial Datasets — what to change once the numbers say the data is too big.
• Simplifying Geometries for Map Payloads — reducing vertices rather than precision.

Up: Part of Spatial Observability & Cost Control.