Building a Sub-50ms Geolocation API with Spring Boot & PostGIS

Location-based queries are deceptively hard to get fast. A naïve "find me vendors within 2km" query on a table with 50,000 rows will bring a Postgres instance to its knees. Here's exactly how we solved this for StreetBite, achieving a stable sub-50ms average response time.

Most developers start with the Haversine formula in SQL — calculating the great-circle distance between two points for every row in the table. This is an O(n) full-table scan. With 50,000 vendor records, every search query touches every row. The result is a 400-600ms query time that only gets worse as the dataset grows.

PostGIS extends PostgreSQL with first-class geographic types. By storing vendor coordinates as a `geography(POINT, 4326)` column rather than two separate float columns, we unlock access to spatial operators and — critically — spatial indexes.

ALTER TABLE vendors

UPDATE vendors SET location = ST_SetSRID( ST_MakePoint(longitude, latitude), 4326 ); ```

A GIST (Generalized Search Tree) index on our geography column allows Postgres to prune the search space dramatically — instead of scanning 50,000 rows, the spatial index narrows the candidate set to the few hundred vendors that could plausibly be within the search radius before performing the exact distance calculation.

CREATE INDEX idx_vendors_location
ON vendors USING GIST (location);

After adding this index, our query time for a typical "vendors within 2km" search dropped from 480ms to 18ms on a cold cache.

We used a native JPQL query in our Spring Boot repository to leverage the PostGIS `ST_DWithin` operator, which is index-aware (unlike `ST_Distance` in a WHERE clause):

@Query(value = """
  SELECT v.* FROM vendors v
  WHERE ST_DWithin(
    v.location::geography,
    ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography,
    :radiusMeters
  )
  ORDER BY ST_Distance(
    v.location::geography,
    ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography
  )
  LIMIT :limit
  """, nativeQuery = true)
List<Vendor> findNearby(
  double lat, double lon,
  double radiusMeters, int limit
);

With PostGIS geography types, a GIST spatial index, and ST_DWithin, our average API response time for proximity searches sits at 38ms (P50) and 72ms (P99). We serve this from a single $15/month RDS db.t3.micro instance.

Spatial indexing is not optional for production geolocation APIs. It's table stakes.