noukuReal-time asset tracking is a write-heavy spatial workload — every position update is an insert or update against a spatial index that has to stay queryable for proximity and containment lookups happening concurrently. The recurring mistakes: using GEOMETRY with lat/lon degrees when the queries actually need GEOGRAPHY semantics (or vice versa, paying for spherical math nobody needed), running ST_Distance in a WHERE clause instead of ST_DWithin, and letting index bloat from high-frequency position updates go unmanaged.
The Shape of the Workload
Asset tracking — fleet vehicles, containers, field equipment — produces a write pattern spatial systems aren't naturally optimized for: many small, frequent updates to individual point geometries, interleaved with read queries that need current results, not stale ones. This is different from the workload PostGIS's indexing was originally optimized around (mostly-static geometries, occasional bulk updates). The tuning that matters here is specific to that mismatch.
GEOMETRY vs. GEOGRAPHY: Pick Based on the Query, Not the Data
PostGIS gives you two spatial types for point data: GEOMETRY, which treats coordinates as points on a flat Cartesian plane, and GEOGRAPHY, which treats them as points on a sphere and does distance/area calculations accordingly.
The mistake goes both directions. Using GEOMETRY with raw lat/lon values and then computing distances as if they were planar coordinates produces distance figures that are wrong, and the error grows with latitude and distance — fine near the equator over short distances, increasingly wrong as either grows. The fix isn't necessarily "switch to GEOGRAPHY" — it's often "project into an appropriate planar SRID" (a coordinate system suited to the operating region) if the workload is geographically constrained, because GEOMETRY operations are cheaper and its indexing behavior is more predictable than GEOGRAPHY's.
Conversely, defaulting everything to GEOGRAPHY because it's "more correct" pays for spherical distance calculations on every query, even ones — like a simple bounding-box containment check for a dashboard viewport — that don't need that precision. The type choice should follow from what the queries actually need: precise real-world distance and area (GEOGRAPHY, or GEOMETRY reprojected to a suitable SRID), versus fast approximate containment and proximity checks where planar math is an acceptable simplification.
ST_DWithin, Not ST_Distance in a WHERE Clause
The most common query-shape mistake in proximity search: filtering with WHERE ST_Distance(point, target) < radius. This computes the exact distance for every row before the comparison happens, which means it can't use the spatial index to prune candidates — the index exists to avoid computing distance for rows that are obviously out of range, and a raw ST_Distance predicate defeats that.
ST_DWithin(point, target, radius) is the index-aware equivalent — it lets the planner use the GiST index to first narrow to rows whose bounding boxes could possibly satisfy the distance constraint, then does the more expensive exact check only on that narrowed set. For asset tracking specifically — "which vehicles are within 500 meters of this depot right now" — this is the difference between a query that scales with the size of the candidate set near the target versus one that scales with total table size.
GiST Indexing and Write-Heavy Update Patterns
PostGIS spatial indexes are built on GiST (Generalized Search Tree). GiST handles updates by inserting/deleting index entries, same as any B-tree-adjacent structure, and high-frequency position updates on tracked assets generate a correspondingly high rate of index churn.
Two practical consequences follow directly from that:
- Index bloat. Frequent updates on a table with a spatial index leave dead tuples behind (standard PostgreSQL MVCC behavior — an update is a delete-plus-insert at the row level), and the spatial index bloats along with the table. Autovacuum needs to be tuned aggressively enough for the update rate — the default thresholds are set for far less update-heavy tables than a live position feed. Under-tuned autovacuum on a tracking table is a common cause of gradual query degradation that looks like "PostGIS doesn't scale" but is actually "vacuum isn't keeping up."
- Write amplification from index maintenance. Every position update maintains the spatial index synchronously as part of the write. If the table has additional indexes that aren't load-bearing for the actual query patterns (an index added speculatively, or one inherited from an earlier schema iteration), each one adds write cost to every position update, for a workload where writes are already the dominant traffic.
The mitigation isn't avoiding indexing — it's making sure the index set is exactly what the queries need, and that vacuum settings match the write rate rather than PostgreSQL's general-purpose defaults.
KNN Search: Let the <-> Operator Do the Work
"Nearest N assets to this point" is a common tracking query, and it's easy to write inefficiently as an ORDER BY ST_Distance(...) LIMIT N — which, like the ST_DWithin case, computes exact distance for every row before sorting and truncating.
PostGIS's <-> distance operator is designed specifically for K-nearest-neighbor queries against a GiST index — ORDER BY point <-> target LIMIT N lets the planner use the index to walk candidates in approximate distance order and stop once it has enough, instead of computing and sorting the full distance set. For a "nearest 10 vehicles" query against a large fleet table, this is the difference between an index-assisted lookup and a full-table distance computation.
Partitioning Historical Positions Out of the Hot Path
A pattern specific to tracking systems: the table accumulates position history, but the queries that need to be fast — proximity, containment, current-state lookups — only care about recent positions. If historical data stays in the same table as live positions, every index (spatial or otherwise) carries the weight of data the hot-path queries never touch, and vacuum has more table to work through on every pass.
Partitioning by time (current position table separate from historical archive, or time-range partitions with the active partition kept small) keeps the spatial index that matters for real-time queries sized to the live working set rather than the full historical record. This is a schema decision, not a query optimization, but it's frequently the highest-leverage fix available — every other optimization in this post operates on a table that's smaller and less bloated as a direct result.
Tune for the Workload You Actually Have
None of this is exotic PostGIS knowledge — GiST indexing, ST_DWithin, the <-> KNN operator are documented, standard tooling. The mistakes are almost always about workload shape: treating a write-heavy, latency-sensitive tracking table like a mostly-static GIS dataset, and applying general-purpose defaults (autovacuum thresholds, index sets, distance predicates) to a query pattern that needs them tuned specifically for high-frequency spatial writes competing with real-time reads.