List partitioning in PostgreSQL/PostGIS is a declarative partitioning method that divides a large table into several sub-tables, according to the discrete values of a column (e.g. by region, type, year, etc.).
This is particularly useful in GIS when you need to manage large layers (e.g., a table with millions of geographic objects divided by country, or by type of area).
What is list partitioning?
It’s a technique that :
- Creates a main table (the parent table),
- And several partitions (daughter tables), each containing rows corresponding to a specific column value.
GIS examples:
- Partition a building table by type (residential, industrial, public…)
- Partition a zone table by commune or island (Mauritius, Rodrigues, etc.)
- Partition a roads table according to road type (freeway, road, greenway, etc.).
Benefits
- Faster queries if WHERE clause filters on type
- Fewer write locks
- Easier maintenance (partitions can be archived or detached)
- Can be combined with vacuum management, local indexes, partition-specific triggers, etc.
Limitations
- Partition column must be well chosen (discrete values, not too many)
- A row can only go into one partition
- Constraints and indexes must be defined for each partition (as of PostgreSQL 13, some can be inherited)
What about different geometry types (point, line, polygon)?
In PostGIS, you can use partitioned tables to store different types of geometry in separate partitions. PostgreSQL supports list partitioning (LIST PARTITIONING), which is well suited to separating geographic features according to their type (point, line, polygon).
1. Creating the main table
The main table is a partitioned table based on geometry type.
CREATE TABLE formation.objets_geographiques ( id SERIAL, nom TEXT NOT NULL, geom GEOMETRY NOT NULL, type_geom TEXT NOT NULL, PRIMARY KEY (type_geom, id) -- Ajout de type_geom dans la clé primaire) PARTITION BY LIST (type_geom);Here, the type_geom column is used as a partition key.
2. Creating partitions for each geometry type
Specific partitions are created for each type of geometry (POINT, LINESTRING, POLYGON).
CREATE TABLE formation.objets_points
PARTITION OF formation.objets_geographiques
FOR VALUES IN (‘POINT’);
CREATE TABLE formation.objets_lignes
PARTITION OF formation.objets_geographiques
FOR VALUES IN (‘LINESTRING’);
CREATE TABLE formation.objets_polygones
PARTITION OF formation.objets_geographiques
FOR VALUES IN (‘POLYGON’);
3. Adding a geometry type constraint
To ensure that each partition contains only the correct geometry type, we add a CHECK constraint.
ALTER TABLE formation.objets_points ADD CONSTRAINT chk_geom_point
CHECK (GeometryType(geom) = ‘POINT’);
ALTER TABLE formation.objets_lignes ADD CONSTRAINT chk_geom_linestring
CHECK (GeometryType(geom) = ‘LINESTRING’);
ALTER TABLE formation.objets_polygones ADD CONSTRAINT chk_geom_polygon
CHECK (GeometryType(geom) = ‘POLYGON’);
4. Data insertion
Data will be automatically redirected to the correct partition according to the type_geom value.
INSERT INTO formation.objets_geographiques (nom, geom, type_geom)
VALUES
(‘École’, ST_GeomFromText(‘POINT(57.5 -20.2)’, 4326), ‘POINT’),
(‘Route côtière’, ST_GeomFromText(‘LINESTRING(57.5 -20.2, 57.6 -20.3)’, 4326), ‘LINESTRING’),
(‘Parc national’, ST_GeomFromText(‘POLYGON((57.5 -20.2, 57.6 -20.3, 57.7 -20.1, 57.5 -20.2))’, 4326), ‘POLYGON’);
5. Creating indexes on partitions
GIST or SP-GiST indexes can be created on each partition to improve the performance of spatial queries.
CREATE INDEX idx_geom_points ON formation.objets_points USING GIST (geom);
CREATE INDEX idx_geom_lignes ON formation.objets_lignes USING GIST (geom);
CREATE INDEX idx_geom_polygones ON formation.objets_polygones USING GIST (geom);
Benefits of partitioning in PostGIS
- Improves spatial query performance by reducing the volume of data analyzed.
- Facilitates the management and organization of geographic objects.
- Simplifies maintenance (e.g. deleting a specific partition without impacting others).
6. Query to retrieve a specific type
If you only want to retrieve polygons, PostgreSQL directly queries the right partition.
SELECT * FROM formation.objets_geographiques WHERE type_geom = ‘POLYGON’;
This improves query efficiency by avoiding unnecessary data scanning.
Conclusion
Partitioning by geometry type in PostGIS enables efficient management of spatial data, while optimizing query and storage performance.
