This is one of those questions that, when asked, invites all kinds of questions about your overall approach. Just because you can store massive geometries in PostGIS doesn’t mean you should. But it’s good to know the limits of your technologies, and since the PostGIS documentation doesn’t get into this, it seems worthy of some exploration.

Without getting too deep into the design of PostGIS, we might expect the size to be constrained by two limits:

• the maximum value of a 32-bit signed integer (2,147,483,647), which is used in the PostGIS serialization format to indicate the number of points in a linear structure (a line or polygon ring), or the number of geometries in a GeometryCollection.
• the maximum size of a field in PostgreSQL, which for current versions is 1 GB.

It turns out that PostGIS geometries are limited by the 1 GB limit. PostGIS stores all coordinates at double precision, so we need 16 bytes of storage space per vertex for a 2D geometries (24 bytes for a 3D geometry) in addition to the overhead of the geometry’s headers. If we assume the headers take up no space, then a geometry should be able to store 1073741824 bytes / 16 bytes = 67,108,864 vertices. (Yes, 1 GB = 1073741824 bytes).

But there are practical limits to be hit before this. For example, you might think you could use a query like this to make a LineString with 40 million points:

1
2
SELECT ST_MakeLine(geom) FROM
(SELECT ST_MakePoint(0, generate_series(1, 40e6)) AS geom) sq;

But this comes back with an unfriendly message:

ERROR: array size exceeds the maximum allowed (1073741823).

Why is this? Well, the implementation of ST_MakeLine builds up an array of points and then passes them into a function that generates the LineString. This means that we have to be able to hold the coordinates and headers for each of those 40 million points until we construct the final geometry.

For point geometries, the header overhead is actually 50%:

• 4 bytes to tell Postgres how large the PostGIS object is
• 4 bytes to store the SRID and various flags
• 4 bytes to store the geometry type (Point)
• 4 bytes to tell us how many points are in our geometry
• 16 bytes for the coordinates themselves

So in this application, we’re limited to constructing a geometry of about 33.5 million vertices. We can approach the theoretical limit by generating two 33.5 million-point LineStrings and unioning them:

1
2
3
4
5
6
SELECT
  pg_size_pretty(ST_MemSize(ST_Union(
    (SELECT ST_MakeLine(geom) FROM (SELECT ST_MakePoint(0, generate_series(1, 33.5e6)) AS geom) sq), 
    (SELECT ST_MakeLine(geom) FROM (SELECT ST_MakePoint(1, generate_series(1, 33.5e6)) AS geom) sq)
))::bigint)
-- 1022 MB

So although you can create geometries with up to 67 million vertices, some functions are going to bail well before this limit is reached.