PostGIS Performance Tuning: From 2s to 10ms for Vietnamese Spatial Queries (Gogoduk Case Study)

When building map services such as reverse geocoding (finding nearby places from coordinates) or administrative boundary determination (point-in-polygon checks) for Vietnamese geography, database performance is paramount. With millions of Points of Interest (POIs) and highly detailed boundary polygons, an unoptimized spatial query can quickly saturate database CPU and degrade response times from milliseconds to seconds.

In this post, we will walk through a real-world case study from Gogoduk—a developer-focused Vietnam Map API service. You will learn how to resolve implicit casting bugs that bypass indexes, optimize spatial data storage with Geography columns, utilize Partial GiST Indexes, and apply topology-preserving polygon simplification to achieve sub-10ms database response times.

The Challenge of High-Performance Geospatial Queries

Gogoduk's Map API service handles two primary types of spatial lookups:

1. Reverse Geocoding: Given a latitude and longitude, find the closest address/POI within a 100-meter radius.
2. Administrative Boundary Determination: Given a coordinate, identify which Ward, District, and Province it falls into to enrich addresses or calculate shipping zones.

Our database stack consists of PostgreSQL with the PostGIS spatial extension.

The Classic Mistake: Runtime Casting on Geometry Columns

Initially, the table storing POI details (geocode_locations) was designed using a standard geom column of type GEOMETRY(Point, 4326), representing geographic coordinates in degrees.

However, the reverse geocoding requirement was to search within a radius specified in meters (e.g., 100m). To resolve the mismatch in units, the query was originally written by casting the geometry column to geography at query execution time:

SELECT place_id, address
FROM geocode_locations
WHERE ST_DWithin(geom::geography, ST_MakePoint(106.70266, 10.77555)::geography, 100)
ORDER BY ST_Distance(geom::geography, ST_MakePoint(106.70266, 10.77555)::geography) ASC
LIMIT 1;

Why was this query slow?

Running EXPLAIN ANALYZE revealed that PostgreSQL was performing a Sequential Scan (scanning every row in the table) instead of using the spatial GiST index created on geom.

The culprit was the runtime cast geom::geography. Because the database engine had to convert every coordinate row in the table to geography before evaluation, it could not utilize the prebuilt index on the raw geom geometry values. As Gogoduk's POI dataset scaled to millions of rows, this query grew to take over 2 seconds, exhausting database connection pools and spiking CPU utilization.

The Solution: Migrating to Native Geography

To resolve the index bypass, the optimal solution was to change the schema to store coordinates using native GEOGRAPHY columns.

The PostGIS GEOGRAPHY type is specifically designed for calculations on a round-earth spheroidal model. Spatial functions like ST_DWithin running on geography columns automatically operate in meters, eliminating the need for casting.

Here are the concrete migration steps implemented in Gogoduk (drawn from Migrations 000015 and 000016):

Step 1: Add the Native Geography Column and Backfill Data

-- Add the geog column of type GEOGRAPHY
ALTER TABLE geocode_locations ADD COLUMN IF NOT EXISTS geog GEOGRAPHY(Point, 4326);

-- Backfill geography from existing geometry
UPDATE geocode_locations
SET geog = geom::geography
WHERE geog IS NULL;

Step 2: Create a GiST Index on the New Geography Column

CREATE INDEX IF NOT EXISTS idx_geocode_geog ON geocode_locations USING GIST(geog);

Step 3: Drop the Redundant Geometry Column to Save Space

Since the geography column completely replaces the geometry query logic, we clean up the unused schema and indexes:

-- Drop the unused geometry index
DROP INDEX IF EXISTS idx_geocode_geom;

-- Drop the geom column
ALTER TABLE geocode_locations DROP COLUMN IF EXISTS geom;

Dropping the redundant geom column saved approximately 32 bytes per row. Across tens of millions of POI rows, this saved hundreds of megabytes in both disk storage and RAM. This memory savings allowed PostgreSQL to keep more indexes cached in memory, yielding higher cache hit rates.

The Optimized Spatial Query:

SELECT place_id, address
FROM geocode_locations
WHERE ST_DWithin(geog, ST_MakePoint(106.70266, 10.77555)::geography, 100)
ORDER BY geog <-> ST_MakePoint(106.70266, 10.77555)::geography ASC
LIMIT 1;

(Note: The <-> operator performs a K-Nearest Neighbors (KNN) search, prompting PostgreSQL to use the GiST index to quickly sort results by spatial proximity).

After this migration, query latency dropped from 2,000ms to just 6ms!

Reducing Index Size with Partial GiST Indexes

For point-in-polygon lookups, we evaluate coordinate inputs against boundary polygons in the vn_admin_boundaries table. This table stores everything from country boundaries down to wards and communes.

In our production API, geocoding lookups usually only require checking province boundaries (admin_level = 4) and district boundaries (admin_level = 8) to assign regions and calculate logistics zones.

Instead of building a massive GiST index indexing every ward and hamlet polygon, Gogoduk leverages Partial Indexes to index only the necessary administrative tiers:

-- Spatial index for Provinces/Cities only
CREATE INDEX IF NOT EXISTS idx_vn_admin_level4_geom
    ON vn_admin_boundaries USING GIST(geometry) WHERE admin_level = 4;

-- Spatial index for Districts only
CREATE INDEX IF NOT EXISTS idx_vn_admin_level8_geom
    ON vn_admin_boundaries USING GIST(geometry) WHERE admin_level = 8;

Advantages of Partial Spatial Indexes:

1. Tiny Memory Footprint: The index only tracks rows that meet the WHERE condition, shrinking index size by 70-80% compared to a full-table spatial index.
2. Faster Execution Plans: When the query parser detects an admin_level = 4 filter, the planner immediately uses the small idx_vn_admin_level4_geom index, completely bypassing millions of unrelated smaller polygons.

Polygon Simplification via ST_SimplifyPreserveTopology

Another performance challenge is returning administrative boundaries as GeoJSON overlays to map components in client web apps. Vietnam's coastline and administrative boundaries are highly detailed, containing tens of thousands of coordinate vertices. Sending raw geometries can easily inflate API payloads to 5-10MB per request, causing client UI stuttering and high network latency.

To optimize the API payload, Gogoduk uses ST_SimplifyPreserveTopology:

SELECT name, ST_AsGeoJSON(ST_SimplifyPreserveTopology(geometry, 0.001)) AS boundary_geojson
FROM vn_admin_boundaries
WHERE admin_level = 4;

Why ST_SimplifyPreserveTopology instead of ST_Simplify?

• Standard ST_Simplify uses the Ramer-Douglas-Peucker algorithm to remove redundant points, but it can accidentally create self-intersecting polygons or drop small islands/holes, leading to invalid shapes.
• ST_SimplifyPreserveTopology guarantees that the topological relationships remain valid (no overlapping boundaries, no holes, and no gaps between adjacent polygons). Setting a tolerance value of 0.001 (degrees) reduces over 90% of the vertices, dropping GeoJSON sizes from 5MB to less than 150KB with virtually no visible difference on the rendering canvas.

Summary and Key Takeaways

Optimizing Vietnam geocoding APIs down to sub-10ms response times at Gogoduk highlighted three key rules for PostGIS developers:

1. Keep Data Types and Operators Aligned: Avoid casting columns in spatial queries at all costs. Store coordinates in GEOGRAPHY if you need metric calculations, and GEOMETRY for flat grid calculations.
2. Leverage Partial Indexes: For hierarchical geospatial datasets, partial spatial indexes keep memory footprints low and index scan times extremely fast.
3. Simplify Geometries Before Transfer: Always smooth out complex boundary polygons using ST_SimplifyPreserveTopology before sending them to client apps to save bandwidth and client CPU cycles.

If you are developing logistics, delivery, or location-based services, applying these PostGIS optimizations will keep your databases responsive. To learn more about full-text optimization for searching addresses, check out our guide on Optimizing PostgreSQL Full-Text Search for Vietnamese Address Autocomplete.