Real-Time Geolocation Backends: PostGIS Spatial Indexes, FastAPI Streaming, and Docker Dispatch

An on-demand booking platform, the kind that connects a customer to the nearest available artisan, driver or courier, looks from the outside like a single feature: show me who is close and connect us. Inside it is two workloads with directly opposing requirements running against the same rows. One is a firehose of small writes, every active worker pushing a new coordinate every few seconds. The other is a latency-sensitive read that has to answer "who is within five kilometres, sorted by distance" while the writes are still landing.

Treating those two as one problem is what produces a system that is fast in a demo and unusable at a thousand concurrent workers. This note works through the design that keeps them separate: PostgreSQL with PostGIS as the durable spatial store, Redis as the volatile index of who is live right now, FastAPI as an asynchronous API that streams dispatch events instead of being polled, and Docker Compose holding the three together. The focus is on the specific places where the obvious implementation degrades: the index that stops being used the moment a filter is added, the location update that quietly disables PostgreSQL's cheapest update path, and the stream that a reverse proxy swallows.

Two workloads, one map

The first architectural decision is which store owns which question. PostGIS is a complete geometry engine: it does polygons, geofences, spatial joins, distance in true metres over a spheroid, and it survives a restart. Redis knows almost nothing about geography beyond points, but it answers a radius query against a purely in-memory structure and it does not care that the same key was overwritten four seconds ago.

Question	Store	Why
Where is worker 4471 right now?	Redis	Overwritten constantly, worthless after a minute, must never touch disk
Who is within 5 km and available?	Redis first, PostGIS as fallback	Hot path, called on every search
Is this address inside our service area?	PostGIS	Polygon containment, changes rarely
Which routes did this worker take last month?	PostGIS	Durable, analytical, joined against bookings
Who should be notified about booking 90210?	Redis Pub/Sub	Transient message, no history needed

The API layer is the only component that talks to both. Clients never reach the database directly, which matters more here than in a typical CRUD service: raw coordinates of identifiable people are the single most sensitive category of data this system holds, and every access to them has to pass through one place where authentication, rate limiting and audit logging are applied.

Schema: geometry, geography, and the column you actually index

PostGIS offers two spatial types and the choice between them decides how much arithmetic ends up in your queries. geometry treats coordinates as points on a flat Cartesian plane. With SRID 4326 that plane is longitude and latitude, so the units of every distance are degrees, not metres. geography treats the same coordinates as points on a spheroid and returns distances in metres.

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE artisans (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    available   BOOLEAN NOT NULL DEFAULT false,
    -- Canonical storage: longitude first, then latitude.
    location    GEOMETRY(Point, 4326) NOT NULL,
    -- Derived, so the two representations can never disagree.
    geog        GEOGRAPHY(Point, 4326)
                GENERATED ALWAYS AS (location::geography) STORED,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One R-tree per representation. Neither index serves the other type.
CREATE INDEX idx_artisans_geom ON artisans USING GIST (location);
CREATE INDEX idx_artisans_geog ON artisans USING GIST (geog);

The generated column is the part worth copying. Storing latitude and longitude twice by hand, once as geometry and once as geography, is a data integrity bug waiting to happen the first time an update forgets one of them. GENERATED ALWAYS AS ... STORED makes divergence impossible at the cost of a few bytes per row.

Working in geometry alone is possible but you inherit the unit conversion. A radius of $r$ metres around latitude $\varphi$ is not a circle in degree space, it is an ellipse whose axes differ by the cosine of the latitude:

At the equator a degree of longitude is about 111 km; in Baku, at roughly 40 degrees north, it is about 85 km. Hard-coding a single conversion factor produces a search radius that is correct in one city and wrong by a quarter in another, and it is the kind of error that never throws, it just returns slightly the wrong set of people. Use geography for anything user-facing and keep geometry for the internal work where the planar assumption is genuinely safe.

Choosing the spatial index

PostgreSQL offers three index families that PostGIS can use, and the received wisdom that "GiST is the spatial index" is a good default rather than a rule.

Index	Build cost	Size	Query behaviour	Fits
GiST (R-tree)	Moderate	Moderate	Handles overlap, containment and KNN ordering; predictable under skew	The default. Points, polygons, mixed workloads
SP-GiST (quad-tree)	Roughly a third of GiST	Around 80% of GiST	Competitive or better on uniformly spread, non-overlapping points	Large point-only tables with even distribution
BRIN	Near-instant	Kilobytes, not megabytes	Only useful when physical row order correlates with location	Append-only history tables clustered by time or region

The distinction that actually decides this is physical correlation. A BRIN index stores one bounding box per block range, so it is astonishingly small and equally astonishingly useless when consecutive rows are scattered across the map. On a live artisans table, where row order reflects nothing but signup sequence, a BRIN bounding box covers the entire service area and every query degrades to a sequential scan. On a location_history table appended in timestamp order and partitioned by day, the same index is close to free and genuinely effective.

SP-GiST is the interesting middle case. Its quad-tree partitioning assumes points do not overlap, which is exactly true for coordinates and exactly false for delivery zones. For a points-only table it builds faster and occupies less space than GiST; for anything with polygons, GiST remains the answer. The honest recommendation is to start on GiST, and only move if EXPLAIN (ANALYZE, BUFFERS) shows the index itself is the bottleneck rather than the heap fetches behind it.

Radius and nearest-neighbour queries

Two query shapes cover nearly every dispatch requirement. The radius search bounds the result set to a service area:

SELECT id,
       name,
       ST_Distance(geog, $1::geography) AS metres
FROM   artisans
WHERE  ST_DWithin(geog, $1::geography, $2)
ORDER  BY geog <-> $1::geography
LIMIT  $3;

ST_DWithin is doing two things in sequence, and understanding the order is what makes it fast. It first asks the GiST index for every row whose bounding box intersects the search envelope, which is a cheap index scan, then computes exact spheroidal distance only for those candidates. Writing the same condition as ST_Distance(geog, $1) <= $2 inverts that: the function has to run on every row before the comparison exists, so the index is never consulted and a table with a million rows takes seconds instead of milliseconds.

The nearest-neighbour form drops the radius entirely and relies on the <-> distance operator:

SELECT id, name
FROM   artisans
ORDER  BY location <-> ST_SetSRID(ST_MakePoint($1, $2), 4326)
LIMIT  5;

Index-assisted KNN only engages when one side of <-> is a constant or a bound parameter. Compare two columns and PostgreSQL falls back to sorting the whole table by computed distance. Verify this rather than assuming it: the plan must contain Index Scan using idx_artisans_geom, and a Sort node above a Seq Scan means the operator was evaluated the expensive way.

The filter that silently kills the index

The query the application actually needs is not the one above. It needs the nearest available artisans, and adding that predicate is where a fast query becomes a slow one:

SELECT id, name
FROM   artisans
WHERE  available
ORDER  BY location <-> $1::geometry
LIMIT  5;

PostgreSQL will use one index for this statement, not two. It either scans the B-tree on available and sorts the survivors by distance, or walks the GiST index in distance order and discards unavailable rows until it has five. When most workers are offline the second plan reads a large fraction of the index before the limit is satisfied, and it degrades exactly when the platform is quietest, which is precisely when nobody is watching the dashboards.

A partial index resolves it by encoding the predicate into the index itself:

CREATE INDEX idx_artisans_available_geom
    ON artisans USING GIST (location)
    WHERE available;

Now the index contains only the rows the query wants, distance ordering is index-assisted, and the index shrinks to the size of the active fleet rather than the registered one. The alternative, a multicolumn GIST (location, available), requires the btree_gist extension to index a boolean under GiST at all, and buys less: it still stores every row. Partial indexes are the better tool whenever the filter is a small, stable set of values.

The write path is the part that breaks

Everything above concerns reads. The write path is where a geolocation backend actually falls over, and the reason is a PostgreSQL optimisation that this workload disqualifies itself from.

Normally an UPDATE that leaves every indexed column untouched can be a Heap-Only Tuple update: the new row version is written into the same page and no index entry is created, so the cost is one page write. The moment an update touches an indexed column, HOT is off. A new tuple is written, an entry is added to every index on the table, and the old versions become dead rows that autovacuum has to reclaim.

A location update changes location. location is indexed. Every single position report therefore writes a heap tuple plus two GiST entries, and GiST entries are more expensive to insert than B-tree entries because the tree may need to be rebalanced. With 5,000 active workers reporting every four seconds, that is 1,250 index-writing updates per second against a table that is simultaneously serving the search queries, plus the dead tuples from all of them. Autovacuum falls behind, the table and its indexes bloat, and read latency climbs for a reason that never appears in the query plan.

There are three responses, and a production system uses all of them.

Keep the hot set out of PostgreSQL. Redis stores current positions in a sorted set keyed by geohash and answers radius queries without touching disk:

# GEOADD overwrites in place: one key per city, one member per worker.
await redis.geoadd("live:baku", (lon, lat, f"artisan:{artisan_id}"))

# GEOSEARCH superseded GEORADIUS in Redis 6.2; GEORADIUS still works but is
# deprecated and should not be used in new code.
nearby = await redis.geosearch(
    "live:baku",
    longitude=user_lon,
    latitude=user_lat,
    radius=5,
    unit="km",
    sort="ASC",
    count=20,
    withdist=True,
)

The encoding is a 52-bit geohash held as the sorted-set score, which bounds positional error at well under a metre, far below GPS noise. Redis offers no durability guarantee worth relying on here, which is the correct trade: a position that is four seconds old has no value worth persisting.

Write to PostGIS asynchronously and in batches. The durable record does not need to be current to the second. Buffer position reports and flush them periodically with a single multi-row statement rather than one round trip per report.

Separate current position from history. A narrow artisan_positions table holding one row per worker, with its own index and an aggressive autovacuum_vacuum_scale_factor of around 0.02, keeps the churn away from the wide artisans table that carries names, ratings and profile data. Historical tracks go into a partitioned table keyed by day, where each partition is written once, indexed with BRIN, and detached rather than deleted when the retention window passes.

The API surface

FastAPI on asyncpg, with the connection pool created once in the lifespan handler rather than per request. Two endpoints carry the entire read and write path.

import os
from contextlib import asynccontextmanager

import asyncpg
from fastapi import Depends, FastAPI
from pydantic import BaseModel, Field


@asynccontextmanager
async def lifespan(app: FastAPI):
    # One pool for the process. Creating a connection per request is the
    # single most common cause of "Postgres is slow" in async services.
    app.state.pool = await asyncpg.create_pool(
        dsn=os.environ["DATABASE_URL"],
        min_size=5,
        max_size=20,
        command_timeout=5,
    )
    yield
    await app.state.pool.close()


app = FastAPI(lifespan=lifespan)


class LocationUpdate(BaseModel):
    # Validation at the edge: a swapped lat/lon pair is the most common
    # client bug in this domain and it is silent without these bounds.
    lon: float = Field(ge=-180, le=180)
    lat: float = Field(ge=-90, le=90)
    accuracy_m: float | None = Field(default=None, ge=0)


@app.post("/location", status_code=204)
async def update_location(
    body: LocationUpdate,
    artisan_id: int = Depends(current_artisan_id),
):
    await redis.geoadd("live:baku", (body.lon, body.lat, f"artisan:{artisan_id}"))
    await position_buffer.put((artisan_id, body.lon, body.lat))


class Nearby(BaseModel):
    id: int
    name: str
    metres: float


@app.get("/search/nearby", response_model=list[Nearby])
async def search_nearby(
    lon: float, lat: float, radius_m: int = 5000, limit: int = 20,
):
    radius_m = min(radius_m, 25_000)   # Bound the work a caller can request.
    # The origin expression is repeated rather than hoisted into a CTE. It is
    # built from immutable functions over bound parameters, so the planner
    # folds it to a constant and the KNN index assist survives; a CTE column
    # reference would not qualify and the ORDER BY would fall back to a sort.
    sql = """
        SELECT id,
               name,
               ST_Distance(
                   geog, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography
               ) AS metres
        FROM   artisans
        WHERE  available
          AND  ST_DWithin(
                   geog, ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography, $3
               )
        ORDER  BY geog <-> ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography
        LIMIT  $4
    """
    async with app.state.pool.acquire() as conn:
        rows = await conn.fetch(sql, lon, lat, radius_m, limit)
    return [dict(r) for r in rows]

Two details that are easy to get wrong. asyncpg uses positional $1 placeholders, not the named :param style of SQLAlchemy or psycopg, and mixing the two conventions produces a syntax error at the database rather than in Python. And the radius is clamped server-side: without that ceiling any client can request a 10,000 km radius and turn a bounded index scan into a full table sort.

Streaming dispatch over SSE

Dispatch is inherently push-shaped. The rider is waiting for a driver to accept; polling that with a request every two seconds burns battery on the client and connections on the server to deliver nothing most of the time. Server-Sent Events fit the shape exactly: one long-lived HTTP response, server to client only, with automatic reconnection built into every browser.

import json

from fastapi import Request
from sse_starlette.sse import EventSourceResponse


@app.get("/stream/bookings/{booking_id}")
async def booking_stream(booking_id: int, request: Request):
    async def events():
        pubsub = redis.pubsub()
        await pubsub.subscribe(f"booking:{booking_id}")
        try:
            async for message in pubsub.listen():
                if message["type"] != "message":
                    continue
                # Disconnects are not always visible as exceptions; check.
                if await request.is_disconnected():
                    break
                payload = json.loads(message["data"])
                yield {
                    # The id lets a reconnecting client resume via
                    # Last-Event-ID instead of silently missing updates.
                    "id": str(payload["seq"]),
                    "event": payload["type"],
                    "data": json.dumps(payload),
                }
        finally:
            await pubsub.unsubscribe(f"booking:{booking_id}")
            await pubsub.close()

    return EventSourceResponse(events(), ping=15)

EventSourceResponse comes from sse-starlette, not from FastAPI itself. The ping keeps a comment frame flowing every fifteen seconds, which stops intermediate proxies from closing a connection they consider idle.

The client side is a browser primitive, no library required:

const source = new EventSource(`/api/stream/bookings/${bookingId}`);

source.addEventListener("driver_confirmed", (e) => {
  const { driver, eta_seconds } = JSON.parse(e.data);
  setDriver(driver);
  setEta(eta_seconds);
});

source.addEventListener("location_update", (e) => {
  const { lon, lat } = JSON.parse(e.data);
  marker.setLngLat([lon, lat]);
});

// EventSource reconnects on its own; close it when the view unmounts,
// otherwise every navigation leaks a connection.
return () => source.close();

Two operational constraints follow from the connection being long-lived. The first is buffering: Nginx accumulates a proxied response before forwarding it, so events arrive in a batch when the handler finally exits, which for an infinite generator is never. The fix is X-Accel-Buffering: no on the response, or proxy_buffering off in the location block, together with a proxy_read_timeout long enough to outlast the ping interval. This is the same failure mode that affects token streaming from a language model, described in the note on production RAG architecture.

The second is concurrency. Every subscribed client holds a worker slot for as long as they are watching. That is affordable on an async stack, where a slot is a coroutine, and immediately fatal on a synchronous one, where it is an OS thread. If a single blocking call sneaks into the event generator, it stalls the entire event loop and every other stream on that worker with it.

One booking, end to end

Redis appears twice in that flow doing two unrelated jobs: the geospatial index for the search, and the pub/sub bus that carries the booking request to whichever API worker happens to be holding that artisan's stream. The second job is what makes horizontal scaling possible at all. Without a shared bus, a booking can only be delivered if the accepting worker is the same process that owns the target's connection, which is true until the moment a second replica starts.

Running it: Docker Compose

services:
  db:
    image: postgis/postgis:17-3.5
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD_FILE: /run/secrets/db_password
      POSTGRES_DB: dispatch
    command:
      # Spatial indexes are only fast while they are in memory.
      - "postgres"
      - "-c"
      - "shared_buffers=1GB"
      - "-c"
      - "maintenance_work_mem=512MB"
      - "-c"
      - "random_page_cost=1.1"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U appuser -d dispatch"]
      interval: 10s
      retries: 5
    secrets:
      - db_password

  redis:
    image: redis:7-alpine
    command: ["redis-server", "--save", "", "--appendonly", "no"]

  api:
    build: ./backend
    environment:
      DATABASE_URL: postgresql://appuser@db/dispatch
      REDIS_URL: redis://redis:6379/0
    depends_on:
      db:
        condition: service_healthy
      redis:
        condition: service_started
    ports:
      - "8000:8000"

volumes:
  pgdata:

secrets:
  db_password:
    file: ./secrets/db_password.txt

Three settings there are load-bearing. random_page_cost=1.1 tells the planner it is running on SSD, and without it PostgreSQL systematically overestimates the cost of index scans and picks sequential scans for exactly the queries this system depends on. The Redis persistence flags are deliberate: this instance holds nothing worth writing to disk, and disabling both RDB snapshots and the append-only log removes the fork-and-write pauses that would otherwise stall the hot path. And condition: service_healthy rather than a bare depends_on is what prevents the API from starting against a Postgres that has accepted the TCP connection but not yet finished initialising PostGIS.

Operating it

Connection pooling. The asyncpg pool bounds connections per process; multiply that by your replica count before deciding it is safe. Past a few hundred backends, PostgreSQL spends more time context-switching than working, and PgBouncer in transaction mode is the standard answer. One caveat that costs an afternoon if you meet it cold: asyncpg prepares statements by default and transaction pooling breaks prepared statements, so statement_cache_size=0 is mandatory behind PgBouncer.

Rate limiting. The location endpoint is the one an attacker or a buggy client will hammer, and it is a write. Limit it per authenticated identity rather than per IP, since an entire city of mobile clients can share a carrier NAT address. A worker reporting more often than every two seconds is reporting GPS jitter, not movement.

Authentication and row-level security. Short-lived JWTs on every endpoint, validated in a dependency, and current_artisan_id taken from the token rather than the request body. Sending an identifier the caller controls in a location update lets any authenticated user move anyone. For multi-tenant deployments, PostgreSQL row-level security enforces the tenant boundary in the database rather than trusting every query to carry the right WHERE clause.

Retention. Location history is personal data, and an indefinitely retained track of where somebody was every four seconds is a liability far exceeding its analytical value. Daily partitions make expiry a DETACH PARTITION followed by a DROP TABLE, which is instant and reclaims the space immediately, rather than a DELETE that leaves the table bloated and autovacuum busy for hours.

Conclusion

The architecture that holds up is the one that stops pretending the read path and the write path are the same problem. PostGIS earns its place through correctness: true spheroidal distance, geofence containment, durable history, and index support that is genuinely fast when the queries are written so the planner can use it. Redis earns its place through indifference to durability, which is exactly the right property for data that is worthless four seconds later. SSE earns its place by replacing thousands of polling requests with one idle connection per waiting user.

What ties them together is a single boundary in the API layer where authentication, validation and rate limiting are applied once, and a set of database choices that are only correct when they are deliberate: geography for user-facing distances, a partial GiST index matching the filter the application actually sends, and a table layout that keeps the update churn away from the rows being searched.