Postgres Topology and logical partitioning

[Lars Aksel Opsahl]

Last year, when working with big data that resulted in 80 million faces, we had to do a lot of divide and conquer to get through. First, we had to break topologies into many thousand small topologies that we could work on in parallel and then merge into bigger and bigger topologies.

However, a single topology of 80 million faces does not work well for our usage, so we split the temporary topology into around 20 smaller topologies based on a content-based grid. These 20 topologies are spatially not connected.

It would be nice if these 20 topologies could work similarly to table partitioning in PostgreSQL by using some kind of new topology logical partitioning.

[robe]

If I am understanding this need correctly, you would like for some situations the 20 topologies to be treated as one topology and then in other occassions be treated as separate topologies.

All this would require them to have IDs that don't overlap with each other, correct? Which would mean we would need face ids to be bigints.

They could use the same sequencer, or they could use separate sequencers that start at different numbering to ensure they don't overlap. Probably better for separate sequencer.

and there would be a new thing, called a "Spatial Grid" that denotes the boundaries of each topology and the sequence ranges of each.

You would build on the existing table partitioning in PostgreSQL, which I think would be not too hard since all topologies have the same structure anyway and use the ids as a partitioning key.

If you filter for a location in the grid, it would tell you you only need to touch topologies 1 and 2 so there would be a prefilter that filters only for say faces that are within the range of topologies 1 and 2.

That would force the system to ignore the other 18 topologies you have.