GeoPandas vs PostGIS Decision Matrix

Most routing arguments between an in-process library and a spatial database are settled by preference, and preference is wrong by an order of magnitude often enough to matter. A matrix built from row counts, index availability and the shape of the operation settles them from evidence instead. This guide gives that matrix and the measurement behind it — the concrete half of GeoPandas and PostGIS tool routing.

When to Use This Approach

Any system with both backends available needs an explicit rule, because the default in the absence of one is whichever the author is more comfortable with.

Implementation

The matrix takes three inputs and returns a route. Row count comes from table statistics; index availability comes from the catalogue; operation shape is a property of the plan step you already have.

def route(step, stats) -> str:
    rows = stats.row_estimate(step.source)
    indexed = stats.has_spatial_index(step.source, step.geom_column)

    if step.produces_aggregate and rows > SMALL:
        return "postgis"                       # the answer is tiny, the input is not
    if rows > LARGE and indexed:
        return "postgis"
    if rows > LARGE and not indexed:
        return "postgis_with_warning"          # still better than transferring it
    if step.op in AWKWARD_IN_SQL:
        return "geopandas"
    if rows <= SMALL:
        return "geopandas"
    return "split"

The split route is the one that earns the most and is written the least. It applies a spatial predicate in the database to reduce the population, then finishes the awkward part in process on what survives.

def run_split(step, conn):
    narrowed = read_postgis(
        "SELECT * FROM {t} WHERE ST_Intersects({g}, ST_GeomFromEWKB(%s))".format(
            t=step.source, g=step.geom_column),
        conn, params=(step.region.wkb,), geom_col=step.geom_column)
    return awkward_operation(narrowed)         # hundreds of rows, not millions

The thresholds SMALL and LARGE are the only numbers in this that must be measured rather than chosen. Everything else is structural and transfers between deployments; those two do not.

Validation & Testing

Measure the crossover once, properly, with a benchmark that runs both paths over the same inputs at several sizes. The result is two numbers and a graph nobody needs to look at twice.

SIZES = [100, 1_000, 10_000, 100_000, 1_000_000]

def measure_crossover(conn, op):
    for n in SIZES:
        pg = time_it(lambda: run_in_postgis(op, conn, limit=n))
        gp = time_it(lambda: run_in_geopandas(op, conn, limit=n))
        print(f"{n:>9,}  postgis {pg:6.2f}s  geopandas {gp:6.2f}s")

Then test the router itself against fixtures rather than against the database: given a step and a stats object, assert the route. That keeps the routing logic testable without a database and makes the threshold change a one-line diff with visible consequences.

Gotchas & Edge Cases

Row count as a proxy for cost. A thousand coastline polygons with tens of thousands of vertices each cost far more than a thousand building footprints. Where a table is known to hold complex geometry, a per-table multiplier is crude but maintainable; modelling vertex counts properly is better and rarely survives contact with a deadline.

An index that exists but is not used. A spatial index on a column the query transforms — a function applied to the geometry before comparison — is not used, and the planner will say so. Checking for the index in the catalogue is necessary and not sufficient; checking the plan is what confirms it.

Memory as a hard boundary. The in-process path fails absolutely rather than slowly when the frame does not fit, and the failure arrives after the transfer has already been paid for. Any route to the process should have a row ceiling above which it is simply not attempted, independent of the cost comparison.

Different answers from the two backends. Buffer results, validity definitions and floating-point handling differ slightly between GEOS versions and between the library and the server. For most questions this is noise; for a comparison against a previously stored result it is a discrepancy somebody will report, and recording which backend produced each answer is what makes it explicable.

The split route not being considered. Because the matrix reads as a binary choice, the split case tends to be skipped even when it is the best answer. Making it an explicit return value rather than an implementation detail is most of what gets it used.

Recording What the Router Decided

Every routing decision should be recorded with the estimate that produced it, the backend chosen and the measured duration. Three fields, one row, and they answer nearly every question anyone will later ask about the routing.

The immediate use is drift detection. Comparing the estimate against the measurement across a few thousand runs shows whether the thresholds still match the data, and systematic underestimation on one side is the clearest evidence that they do not. Without the record the only signal is a complaint about slowness, which arrives long after the drift began and points at the query rather than at the routing.

The second use is explaining a surprising answer. When two runs of the same question produced different durations, or slightly different results, the recorded backend is usually the explanation — and reconstructing it after the fact from logs that do not contain it is close to impossible. The record also makes the fallback path auditable: a request that ran on the second-choice backend because the first was unavailable is a different event from one routed there deliberately, and only the record distinguishes them.

Operating This Step Over Time

Re-measure after any material change to the data volume, the hardware or the database version. None of those raise an error when they invalidate a threshold; the system simply starts making worse decisions, at a rate proportional to how far things have moved.

Watch for operations that always route the same way regardless of input. That usually means the estimator has no useful signal for them, which is acceptable — but it should then be an explicit constant with a comment rather than a decision that appears to be dynamic and is not.

The third habit is checking that the split route is still being taken. It is the outcome most easily lost to a well-meaning simplification, because a router reduced to a binary choice still works and still returns reasonable answers — it just stops doing the thing that made the expensive cases fast. A counter on each route makes that regression visible in a way that no test of correctness will, since nothing about the answer changes when the split disappears.

Indexes deserve their own periodic check. An index dropped during a migration, or one made unusable by a change to how the column is queried, silently moves a whole class of work from the fast path to the slow one while the routing decision continues to assume it exists. Asserting index presence at startup, and comparing the catalogue against what the router expects, catches it in seconds rather than in a support conversation.

Frequently Asked Questions

Related

• Up to the parent topic: GeoPandas and PostGIS Tool Routing
• Routing LLM Calls to GeoPandas vs PostGIS Backends
• Related topic: Cost and Latency Budgets for Spatial Agents
• Related topic: Async vs Sync Geoprocessing Workflows