devto 2026-06-25 원문 보기 ↗
Honestly, I thought building a spatial search would be easy. "It's 2026, databases have spatial indexes built in — how hard can it be?" I said to myself after three energy drinks at 2 AM. Three weeks later, I had a production system where a simple "find points within 1km" query took 8 seconds.
Eight seconds. On a dataset of only 10,000 points.
So here's the thing — I learned the hard way that "having spatial indexes" isn't the same as having fast spatial queries. If you're building anything with location-based search, stick around — this is everything I wish someone had told me before I started.
Spatial Memory lets users pin photos and notes to physical coordinates. When you open the app, it needs to show everything interesting within walking distance. The query sounds simple:
Give me all points within 1 kilometer of (latitude, longitude)
Sort them by distance
Return the closest 20 results
That's it. That's all I wanted. My first implementation? Straightforward with PostGIS:
SELECT id, lat, lng, name,
ST_Distance(geom, ST_SetSRID(ST_MakePoint($1, $2), 4326)) AS distance
FROM points
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint($1, $2), 4326), 1000)
ORDER BY distance
LIMIT 20;
Looks right, right? I created a GIST index on the geom column, added the query, called it a day.
Then I tested it.
Query time: 7.8 seconds.
I checked the index — yes, it was there. I checked the statistics — everything looked fine. 10,000 rows shouldn't take 8 seconds. What gives?
Here's mistake number one: I was using 4326 (WGS84) which is great for storing coordinates, but ST_DWithin with distances in meters gets complicated because 4326 uses degrees, not meters.
PostGIS does have geography type that handles meters correctly, but guess what? The query planner doesn't always use GIST indexes efficiently with geography when you have larger datasets. Or maybe I was doing it wrong — honestly, after three days of debugging, I stopped caring why and just looked for a better way.
The fix that cut my query time from 8 seconds to 800ms? Transform to a projected coordinate system that uses meters:
-- Before (slow): SRID 4326, degrees
ST_DWithin(geom, ST_SetSRID(ST_MakePoint($1, $2), 4326), 0.009) -- degrees ≈ 1km
-- After (faster): Transform to Web Mercator (meters)
ST_DWithin(
geom_mercator,
ST_Transform(ST_SetSRID(ST_MakePoint($1, $2), 4326), 3857),
1000 -- actual meters, no guessing
)
I added a second column geom_mercator that's already transformed, created a new GIST index on it, and boom — 10x faster immediately.
// In Go with GORM
type Point struct {
ID uint `json:"id"`
Lat float64 `json:"lat"`
Lng float64 `json:"lng"`
Geom geog.GeoJSON `gorm:"column:geom;srid:4326"`
GeomMercator geometry.Polygon `gorm:"column:geom_mercator;srid:3857"`
}
// Auto-update geom_mercator when lat/lng changes
func (p *Point) BeforeSave(*gorm.DB) error {
// Transform from 4326 to 3857 for fast queries
p.GeomMercator = geometry.NewPoint(geometry.Coordinate{X: p.Lng, Y: p.Lat}, 3857)
return nil
}
Lesson #1: Store your data in 4326 (everyone expects WGS84), but query in a projected CRS that uses actual meters. Your query planner will thank you.
Even with 800ms, that's still too slow for mobile. Users open the app, they expect results instantly. 800ms feels laggy.
But think about it — most spatial queries hit the same popular areas over and over. Tourist attractions, city centers, university campuses. Why go to PostGIS every time when the results don't change that often?
Enter Redis GEO.
I added a simple two-level caching strategy:
Here's what that looks like in code:
import (
"context"
"strconv"
"github.com/go-redis/redis/v8"
)
func (s *SpatialService) SearchNearby(ctx context.Context, lat, lng float64, radiusKm float64, limit int) ([]*Point, error) {
radiusMeters := radiusKm * 1000
// Step 1: Get candidate IDs from Redis GEO first
// This is almost always <1ms
ids, err := s.redis.GeoRadius(ctx, "spatial:points", lng, lat, &redis.GeoRadiusQuery{
Radius: radiusMeters,
Unit: "m",
WithCoord: false,
WithDist: false,
WithGeoHash: false,
Count: limit * 2, // Get extra for sorting
Sort: "ASC",
}).Result()
if err != nil {
// Fallback to full PostGIS query if Redis down
return s.fullPostGISSearch(lat, lng, radiusKm, limit)
}
// Convert results to numeric IDs
pointIDs := make([]uint, len(ids))
for i, res := range ids {
id, _ := strconv.ParseUint(res.Name, 10, 32)
pointIDs[i] = uint(id)
}
// Step 2: Only query the specific IDs we need from Postgres
// Instead of scanning the whole table, this is just a few index lookups
var points []*Point
err = s.db.Where("id IN ?", pointIDs).Find(&points).Error
// Sort by distance (Redis already did this, but just to be safe)
// ... sorting code ...
return points, nil
}
// When inserting a new point, add it to Redis too
func (s *SpatialService) InsertPoint(ctx context.Context, p *Point) error {
err := s.db.Create(p).Error
if err != nil {
return err
}
// Add to Redis GEO
_, err = s.redis.GeoAdd(ctx, "spatial:points", &redis.GeoLocation{
Longitude: p.Lng,
Latitude: p.Lat,
Name: strconv.FormatUint(uint64(p.ID), 10),
}).Result()
return err
}
The result? Average query time dropped from 800ms to 30ms. That's another 27x improvement. Combined with the first fix, that's ~260x faster than my original query.
Wait — but what about memory? Redis is in-memory after all. Let's do the math:
That's nothing. Even on the cheapest VPS, this fits in memory easily.
Lesson #2: PostGIS is powerful, but for simple radius searches, Redis GEO gives you almost instant results for almost no memory cost. Use it as a filter before hitting the database.
Wait a minute — why was the original query so slow even with a GIST index?
I dug into the query plan with EXPLAIN ANALYZE and found it:
Seq Scan on points ... (cost=0.00..1234.56 rows=10 width=... actual time=7000.00..7800.00)
It was doing a sequential scan! Not using the index at all. Why? Because with the wrong SRID and distance units, the query planner thought the search would hit most of the table, so a sequential scan was cheaper than random I/O.
Even after fixing the SRID, if you're searching a large area (like 10km radius in a dense city), the query planner might still choose a sequential scan because it expects many rows. That's still slower than it needs to be.
What fixed this? Two things:
Cluster your data using the spatial index: CLUSTER points USING idx_points_geom_mercator; This physically orders points that are near each other on disk. Fewer random reads = faster queries.
Set reasonable limits and analyze regularly: VACUUM ANALYZE points; Postgres query planning depends on good statistics. After you add a bunch of points, update those stats.
If you always limit to 20 results, tell Postgres that: LIMIT 20 helps the query planner choose an index scan because it can stop searching once it has enough results.
After clustering, I got another 2-3x speedup on cold cache queries. Not bad for a one-liner.
Lesson #3: Always check EXPLAIN ANALYZE. The index exists doesn't mean it's being used.
Honestly, I've been running this setup in production for three months now. Let me be straight with you — this isn't the "latest and greatest" with some fancy new spatial database. It's PostGIS + Redis, two tools you probably already know. Does it actually work?
CLUSTER rewrites the whole table, it locks it. You can't do it live on a busy production table. You need to schedule it during maintenance windows. For my side project, that's fine. For a busy app, you need to plan accordingly.If I were starting over today, knowing what I know now, I'd still choose PostGIS + Redis GEO. But I'd do a few things differently:
Start with the simplest thing that works: I overcomplicated it with fancy PostGIS features at the beginning. I should have started with Redis GEO + Postgres primary key lookups and added complexity only when I needed it.
Use smaller radii by default: Most users aren't searching for everything within 10km. They want nearby spots. 1km default gives you fewer candidates, faster queries, and more relevant results anyway.
Don't worry about precision: For consumer apps, 1-2 meters error doesn't matter. Users are on foot, they can't tell the difference anyway. Redis GEO is more than accurate enough.
Backup your Redis GEO: If your Redis data gets wiped, you can rebuild it from Postgres. Just iterate through all points and re-add them. Write that script early. I learned this the hard way when I accidentally flushed my Redis cache while debugging.
Building Spatial Memory has taught me that most of the time, you don't need fancy new technology. You just need to combine two mature tools the right way.
PostGIS is amazing for complex spatial work. But for the common case — "find everything within X km of me" — you don't need all that power. Let Redis do what it's good at (fast in-memory lookups), let Postgres do what it's good at (storing your data and handling complex queries when you need them).
I went from 8 seconds to 20ms with two simple changes. That's the power of knowing your tools, not just using the newest tool.
So that's my story. Eight seconds to 20ms, three weeks of debugging, a lot of coffee, and a lot of lessons learned.
If you're building something with location search:
EXPLAIN ANALYZE — your index might not be usedIt's not rocket science, but it's the kind of thing no tutorial really tells you. They just show you the working query and call it done. They don't tell you about the 8 seconds.
What about you — have you built anything with spatial search? What's your setup? Did you go all-in on PostGIS, or use Elasticsearch, or something else entirely? I'd love to hear about what's worked for you in the comments below.
The code for this project is open source on GitHub if you want to dig in.