Opened 10 years ago

Last modified 2 months ago

#2862 new enhancement

Batch topology loading mode

Reported by: remic Owned by: strk
Priority: medium Milestone: PostGIS 3.6.0
Component: topology Version: master
Keywords: performance Cc:

Description (last modified by strk)

Data loading into Postgis Topology is insufferably slow. This is due to a conceptual limit of loading objects into topology one by one. Such behaviour is very good for limited changes, but very bad for massive creation.

We need a specific process to deactivate checkings, then load everything the batch way

Change History (14)

comment:1 by strk, 8 years ago

+1 for functions to add/drop constraints/foreign_keys

comment:2 by strk, 8 years ago

Description: modified (diff)
Summary: PostGis Topology performance improvements : bathc loading modePostGis Topology performance improvements : batch loading mode

comment:3 by strk, 8 years ago

Milestone: PostGIS Future

comment:4 by strk, 8 years ago

FYI: I've been working on a function to "polygonize" correctly linked edges. Doesn't go all the way there (edge linking still has to be performed one by one) but may speedup the process significatively. I guess the interface for batch-loading would be the ISO ST_CreateTopoGeo function: http://postgis.net/docs/ST_CreateTopoGeo.html

The recent polygonization work (registerFaces) can be found here: https://git.osgeo.org/gogs/strk/postgis/src/batch-topo-all-in-ram

It exposes a TopoGeo_addLinestringNoFace (one by one) and a registerFaces (all in ram)

comment:5 by strk, 8 years ago

Another idea: make foreign keys deferrable (right now they are built as non deferrable).

comment:6 by strk, 8 years ago

For the record: latest version of the batch-topo branch is https://git.osgeo.org/gogs/strk/postgis/src/batch-topo

comment:7 by robe, 7 years ago

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

comment:8 by strk, 3 years ago

Keywords: performance added

comment:9 by strk, 10 months ago

For the record the latest work in this direction is here: https://git.osgeo.org/gitea/postgis/postgis/pulls/28

comment:10 by strk, 10 months ago

The ST_CreateTopoGeo batch-loading speedup entered the codebase and was tracked via #5670

What is still pending is a way to batch-load to a non-empty topology

comment:11 by strk, 10 months ago

Milestone: PostGIS Fund MePostGIS 3.5.0
Summary: PostGis Topology performance improvements : batch loading modeBatch topology loading mode
Version: 2.1.xmaster

comment:13 by strk, 9 months ago

And here's a PR to add/drop topology constraints: https://git.osgeo.org/gitea/postgis/postgis/pulls/176

comment:14 by robe, 2 months ago

Milestone: PostGIS 3.5.0PostGIS 3.6.0
Note: See TracTickets for help on using tickets.