Geospatial and Time-Series Data Questions

Direct answer

All three turn a latitude/longitude into a cell ID so "who is near this rider" becomes "who is in these cells". Geohash cuts the latitude/longitude rectangle in half again and again and interleaves the bits into a base-32 string (each character packs 5 bits, using digits and lowercase letters); it is simple and works as a prefix in any sorted key-value store (a database that keeps its keys in sorted order, so a shared prefix or a range of keys can be fetched in one query), but its cells are rectangles whose size and shape change with latitude, and nearby points can share no prefix. S2 projects the Earth onto a cube and subdivides each face as a quadtree (a tree where each square node splits into four smaller quadrant children, repeated at each level), giving 64-bit IDs ordered along a Hilbert curve with roughly square cells and no special cases at the poles or the 180 degree meridian. H3 tiles the sphere with hexagons whose six neighbours are all the same distance away, which makes rings of neighbours, smoothing (averaging a noisy value, such as demand, across nearby cells so a single cell's spike doesn't dominate) and zone-based pricing clean, at the price of imperfect parent/child nesting and 12 pentagons per resolution (a sphere cannot be tiled with hexagons alone; H3 starts from an icosahedron, a 20-triangle-faced solid, and one pentagon appears near each of its 12 vertices at every resolution, however many times the faces are subdivided). For a real-time ride-matching index I would pick H3 (or S2) over geohash, keeping geohash where the storage layer only offers string or prefix range scans.

Terms used

• Cell: one tile of the grid; every point on Earth falls into exactly one cell at a given resolution.
• Resolution / level / precision: how finely the grid is cut. Higher means smaller cells.
• Space-filling curve: a path that visits every cell once, so 2-D cells can be stored in a 1-D sorted order. Geohash uses a Z-order curve, also called a Morton curve after its inventor; S2 uses a Hilbert curve, which has fewer long jumps.
• Neighbour expansion: finding the cells around a given cell so a radius query does not miss a driver just across a cell edge.
• Antimeridian: the 180 degree longitude line, where longitude jumps from +180 to -180.
• RegionCoverer: an S2 library function that, given a shape such as a circle, returns a small set of cells (of mixed sizes) whose union covers it.

How each supports spatial queries

Neighbour expansion in practice. A rider's own cell is never enough, because a rider near a cell edge has drivers a few metres away in the next cell. Geohash: choose a precision whose cell is at least as large as the search radius in both directions, then query the cell and its 8 neighbours. S2: ask a region coverer for, say, at most 8 cells that cover the circle; it mixes big cells in the middle with small ones at the edge. H3: pick a resolution (resolution 9 hexagons average 0.105 km² with 201 m edges) and take grid_disk(k), where k is the radius divided by the centre-to-centre spacing between adjacent hexagons (edge length x sqrt(3), about 348 m for resolution 9's 201 m edge), rounded up. In every case the cells only produce candidates; an exact distance check then filters them.

Trade-offs on the four axes

Query granularity. Geohash sizes jump by a factor of 32 per character (alternately 4x8 and 8x4 splits), so you often choose between a cell that is too big and one that is too small. S2 levels change area by 4x and H3 resolutions by about 7x, both finer steps. Geohash cells also stretch: at 60 degrees latitude a cell is half as wide in metres as at the equator. S2 cell areas at one level vary by about 2x (level 12 spans 3.04 to 6.38 km²); H3 hexagons vary less and are more uniform in shape, which matters when cells are used as pricing or supply zones.

Edge cases (antimeridian and poles). Geohash is defined on the lat/lon rectangle, so a cell at longitude 179.999 and one at -179.999 are neighbours on Earth but have unrelated strings; neighbour code must wrap longitude explicitly. Near the poles geohash cells become thin slivers and the top row has no northern neighbour. S2 and H3 are built on the sphere (cube and icosahedron projections), so neither the antimeridian nor the poles need special cases. H3's special case is different: the 12 pentagons have 5 neighbours, and a few grid operations (for example grid distance between cells) can return an error when the path crosses a pentagon's distorted area, so code must handle that error. They sit mostly in oceans, so ride matching rarely meets one, but "rarely" is not "never".

Index size. Geohash at 7 characters is 7 bytes as text (often stored as an 8-byte integer); S2 and H3 IDs are 8-byte integers. For 1M online drivers the ID column is about 8 MB either way. The real memory is the index structure: a hash map from cell to a set of driver IDs, sized by drivers, not by the encoding.

Update cost for moving drivers. Computing a cell ID is a small constant amount of work for all three. The expensive part is moving the driver between cell sets, which happens only on a boundary crossing. Estimate: for a vehicle crossing a convex cell along a random straight line, the average path length inside it is about pi x area / perimeter. This is a standard result from geometric probability (the same relationship used to compute a particle's mean free path through a convex container): average over every straight line that crosses the cell, in every direction and every offset, and the mean chord length works out to pi times the cell's area divided by its perimeter. For an H3 resolution 9 hexagon with a 200.8 m edge that is about 273 m; for a geohash precision 7 cell at San Francisco's latitude (about 153 m x 121 m) it is about 106 m. At 10 m/s (36 km/h), 100,000 moving drivers cause about 100,000 x 10 / 273, or 3,700 index moves per second with H3, versus 100,000 x 10 / 106, or 9,400 with geohash. This is an estimate that assumes straight-line motion; with location pings every 4 seconds the raw ping rate is 25,000 per second either way, and the index writes are the fraction of pings that change cell.

When to choose which

• Geohash: the store only supports string keys and prefix or range scans (Redis, an in-memory data store, has GEO commands that use a 52-bit geohash as the score in a sorted set, a data structure that keeps its members ordered by a score so a range of them can be fetched quickly; Elasticsearch has geohash grid aggregations), traffic is regional and away from the antimeridian, and simplicity wins.
• S2: you need exact hierarchy and covering of arbitrary regions (a polygonal airport zone or city boundary) with integer range scans, for example in a sorted key-value store.
• H3: matching, surge pricing (raising the price shown to riders in a zone when demand there outstrips available drivers) and supply/demand analytics on equal-sized, equal-neighbour zones; ring searches (grid_disk) for "expand the search if no driver answers"; aggregation by zone in dashboards.

For a new ride-matching system I would use H3 resolution 9 for the live driver index and for pricing zones, run a grid_disk expansion (k = 1, then 2, 3) until enough candidates appear, and filter candidates by road travel-time estimate, not by straight distance.

Pitfalls

• Treating a shared prefix as "nearby" (or a different prefix as "far"): both are wrong at cell edges.
• Forgetting that H3 parents do not exactly contain their children, then summing child counts into a parent and expecting exact totals.
• Picking the resolution by habit instead of from the search radius and driver density: too fine means many cells per query, too coarse means many false candidates.

Direct answer

Store two shapes per listing: the true footprint as a polygon or multipolygon (for large properties such as ranches, campuses or multi-parcel estates) and a representative point that is guaranteed to lie inside that footprint. Keep both in PostGIS in plain longitude/latitude (SRID 4326), index each with GiST, and precompute the listing's neighbourhood ID at write time. Radius search runs against the point for speed and optionally refines against the footprint; neighbourhood aggregation becomes a plain GROUP BY on an integer; map rendering is served as vector tiles (a compact tile format explained below, not a picture) with zoom-dependent pre-aggregation so low zoom levels never draw millions of individual pins.

What to store

Key terms first.

• SRID (spatial reference identifier) names the coordinate system a shape is expressed in. 4326 is WGS 84 (World Geodetic System 1984) longitude/latitude, what GPS and most APIs produce. 3857 is Web Mercator, the flat projection used by web map tiles.
• Geometry vs geography in PostGIS: geometry does flat-plane math (fast, many functions, distances in coordinate units); geography does math on the Earth's surface (distances in metres, fewer functions).
• A spatial index (GiST, Generalized Search Tree, in PostGIS) indexes each shape's bounding box so "what is near here" skips most of the table.

A separate neighborhoods(neighborhood_id, name, boundary geometry(MultiPolygon, 4326)) table, also GiST-indexed, is the source of truth for boundaries.

Why store in 4326 and not 3857. 4326 is lossless relative to the input and is what every upstream feed uses. Web Mercator distorts distance badly away from the equator (scale grows with 1/cos(latitude), about 1.3x at 40 degrees latitude and 2x at 60 degrees), so storing in 3857 invites people to compute "distances" that are wrong. Transform to 3857 only at tile-rendering time; for accurate areas, cast to geography or transform to a local equal-area projection (a projection centred and scaled specifically for one region so area math near that region comes out correct, unlike Web Mercator, whose area distortion away from the equator makes area calculations unreliable almost everywhere).

Why the representative point is not the centroid

A centroid is the average position of the shape, and for a concave shape it can land outside the shape. Run on PostGIS 3.4 for a U-shaped lot:

SELECT ST_AsText(ST_Centroid(g)), ST_Covers(g, ST_Centroid(g)),
       ST_AsText(ST_PointOnSurface(g)), ST_Covers(g, ST_PointOnSurface(g))
FROM (SELECT 'POLYGON((0 0,3 0,3 3,2 3,2 1,1 1,1 3,0 3,0 0))'::geometry g) t;

POINT(1.5 1.357142857142857)|f|POINT(0.5 2)|t

The centroid (1.5, 1.36) sits in the empty notch of the U, so a pin placed there shows the listing on the neighbour's land, and a point-in-polygon neighbourhood lookup on it can pick the wrong neighbourhood. ST_PointOnSurface returns a point that is guaranteed to be inside.

How each access pattern is served

1. Radius / distance search.

SELECT listing_id, ST_Distance(rep_geog, :pt::geography) AS metres
FROM listings
WHERE status = 'active' AND ST_DWithin(rep_geog, :pt::geography, :radius_m)
ORDER BY rep_geog <-> :pt::geography
LIMIT 50;

The <-> operator above is PostGIS's k-nearest-neighbour distance operator: ordering by it lets the spatial index return rows nearest-first directly, so LIMIT 50 can stop early instead of computing and sorting every matching row's exact distance. A partial GiST index (a GiST index built over only the rows that satisfy a WHERE condition, here status = 'active', so the index stays smaller and inactive rows never have to be filtered out at query time) on rep_geog WHERE status = 'active' keeps this to an index probe.

For large properties the business question is usually "does any part of the property fall within 10 km", not "is its representative point within 10 km". The point-only query above can get this wrong: a 2,000-acre ranch is often long and narrow rather than compact, so its representative point (ST_PointOnSurface) can sit outside the radius even while the property's near edge is genuinely inside it. Concretely, a ranch whose point is 11 km from the search point (excluded by the query above, since 11 km is greater than a 10 km radius) but whose nearest edge is only 9 km away (a real part of the property is within 10 km) is exactly the case a point-only search misses. For rows that carry a footprint, add a second predicate against footprint::geography:

SELECT listing_id, ST_Distance(rep_geog, :pt::geography) AS metres
FROM listings
WHERE status = 'active'
  AND (
    ST_DWithin(rep_geog, :pt::geography, :radius_m)
    OR (footprint IS NOT NULL AND ST_DWithin(footprint::geography, :pt::geography, :radius_m))
  )
ORDER BY rep_geog <-> :pt::geography
LIMIT 50;

Only a small fraction of rows carry footprints, so this second check is cheap.

2. Neighbourhood-level aggregation. Assign neighborhood_id at write time with ST_Covers(boundary, rep_point) (covers includes points on the boundary line). Aggregations such as median price by neighbourhood then need no spatial work at query time. When boundaries are edited, re-run the assignment for listings inside the old and new boundary only. If analysts need arbitrary ad-hoc polygons, that is a live ST_Intersects query against the GiST index, which is fine at interactive rates for one polygon.

3. Map rendering at multiple zoom levels. Web maps divide the world into a tile pyramid: at zoom z there are 2^z by 2^z tiles, and a quadkey (or z/x/y triple) names one tile, which makes tiles cacheable in a CDN (content delivery network).

• High zoom (street level, roughly z14 and above): return real listings as vector tiles. A vector tile is not a picture: unlike a pre-rendered image tile (a PNG or JPEG baked at one fixed zoom and style), a vector tile is a small binary package of the raw shapes and attributes inside that tile's box, so the browser draws, styles and labels them itself and can restyle or highlight a listing without fetching a new image. PostGIS builds these directly with ST_TileEnvelope(z, x, y) to find the tile's box, ST_AsMVTGeom to clip (cut each shape down to just the part inside the tile's box) and quantise (snap coordinates onto a small fixed integer grid inside the tile, typically 0 to 4096, which is what keeps the encoded tile small), and ST_AsMVT to encode Mapbox Vector Tile (MVT) format, a compact binary tile layout that most web map renderers (Mapbox GL, MapLibre and others) read directly. Draw footprints as outlines and ordinary listings as pins.
• Low zoom (city or region): never send individual listings. Serve counts from a pre-aggregated table keyed by grid cell (for example an H3 or geohash cell column) or by neighbourhood, refreshed on a schedule or incrementally on listing change.
• Cache rendered tiles by z/x/y with a short TTL (time-to-live) because listings go on and off market; purge tiles by quadkey prefix when a hot area changes.

Worked example: sizing the low-zoom problem

Take 5,000,000 active listings. At zoom 4 (16 by 16 tiles worldwide) the continental US, roughly longitude -125 to -67 and latitude 25 to 49, falls in tile columns x = 2 to 5 and rows y = 5 to 6: 8 tiles. A naive tile would carry 5,000,000 / 8 = 625,000 points on average, which is megabytes per tile and an unusable map. A pre-aggregated grid of, say, 5,000 cells nationally with a count and median price per cell turns the same view into 5,000 small features. The rule: row count per tile should be bounded by design, not by data.

Trade-offs and pitfalls

• Point-only model (everything as a point): simplest and fastest, but misrepresents big properties in radius search and in "draw the lot" UIs. Choose it only if large properties are a rounding error in the catalogue.
• Footprint-only model: accurate, but every radius query pays polygon distance math and pins need a representative point anyway. Keeping both is the committed choice.
• Storing in Web Mercator to "save a transform at render time" corrupts every distance and area calculation done later.
• Computing neighbourhood per query with a spatial join on millions of rows is the common slow-dashboard mistake; it is a write-time attribute.
• Geocode quality matters more than index choice: a listing geocoded to a postcode centroid will appear in radius results kilometres from where it really is. Store accuracy and let search down-rank low-precision rows.
• What would change the design: if the dataset were global and dominated by distance queries over long ranges, geography for footprints would be worth its cost; if tiles were the dominant load, a dedicated tile server reading the same tables would replace per-request ST_AsMVT.

Direct answer

Store each restaurant's latitude/longitude plus a precomputed geohash string (a short code that represents a location by repeatedly halving the map into smaller rectangles, explained below), and index that string with an ordinary sorted (B-tree) index. For a query, pick the geohash precision whose cell is at least as large as the search radius, look up the user's cell and its 8 neighbours, compute exact distance only for the restaurants in those 9 cells, drop anything beyond the radius, and sort by distance. The neighbour lookup is what handles a user sitting at a cell boundary: a restaurant 30 m away but across the edge lives in a different cell, and searching only the user's own cell would miss it.

Why a full scan is too slow

Assume 10,000,000 restaurants and 5,000 searches per second at peak. A full scan computes

and almost all of them are for restaurants in other cities. The goal of any spatial index is to turn "all rows" into "rows in a small area around the user".

Representing locations: the geohash

A geohash encodes a point by repeatedly halving the world: the first bit says east or west of the prime meridian, the second says north or south of the equator, and so on, alternating longitude and latitude. Every 5 bits become one base-32 character. Each extra character makes the cell smaller, and points that share a prefix are usually close together. That last property is what makes a plain string index usable for spatial search: "all rows in cell dr5ru7" is an equality or prefix lookup on a B-tree.

Because the cell size comes from the bit counts, it can be computed exactly rather than looked up. Precision 6 uses 30 bits (15 for longitude, 15 for latitude), so a cell is 360 / 2^15 degrees wide and 180 / 2^15 degrees tall.

Runnable demonstration: the boundary problem and the 3x3 fix

import math

BASE32 = "0123456789bcdefghjkmnpqrstuvwxyz"

def encode(lat, lon, precision):
    lat_rng, lon_rng = [-90.0, 90.0], [-180.0, 180.0]
    bits, bit_count, even, out = 0, 0, True, []
    while len(out) < precision:
        rng, val = (lon_rng, lon) if even else (lat_rng, lat)
        mid = (rng[0] + rng[1]) / 2
        if val >= mid:
            bits = (bits << 1) | 1; rng[0] = mid
        else:
            bits = bits << 1; rng[1] = mid
        even = not even
        bit_count += 1
        if bit_count == 5:
            out.append(BASE32[bits]); bits, bit_count = 0, 0
    return "".join(out)

def cell_size_deg(precision):
    total_bits = 5 * precision
    lon_bits = (total_bits + 1) // 2
    lat_bits = total_bits // 2
    return 180.0 / 2**lat_bits, 360.0 / 2**lon_bits

def neighbours(lat, lon, precision):
    # 3x3 block: re-encode the centre of each adjacent cell.
    dlat, dlon = cell_size_deg(precision)
    return sorted({encode(lat + i * dlat, lon + j * dlon, precision)
                   for i in (-1, 0, 1) for j in (-1, 0, 1)})

def haversine_m(lat1, lon1, lat2, lon2):
    r = 6371000.0
    p1, p2 = math.radians(lat1), math.radians(lat2)
    dp, dl = p2 - p1, math.radians(lon2 - lon1)
    a = math.sin(dp/2)**2 + math.cos(p1)*math.cos(p2)*math.sin(dl/2)**2
    return 2 * r * math.asin(math.sqrt(a))

lat0 = 40.7580
for p in (5, 6):
    dlat, dlon = cell_size_deg(p)
    print(f"precision {p}: cell = {dlat*111.195:.3f} km tall x "
          f"{dlon*111.195*math.cos(math.radians(lat0)):.3f} km wide at lat {lat0}")

P = 6
dlat, dlon = cell_size_deg(P)

# A user just west of a cell edge and a restaurant just east of it.
# Round -73.9855 up to the nearest cell-edge longitude (measured in whole
# multiples of the cell width dlon from -180), then place the user just
# before that edge and the restaurant just after it.
edge_lon = -180 + math.ceil((-73.9855 + 180) / dlon) * dlon
user = (lat0, edge_lon - 0.0002)
restaurant = (lat0, edge_lon + 0.0002)
print("user cell      ", encode(*user, P))
print("restaurant cell", encode(*restaurant, P))
print(f"distance {haversine_m(*user, *restaurant):.1f} m")
print("restaurant cell in user's 3x3 block:", encode(*restaurant, P) in neighbours(*user, P))
print("3x3 block:", neighbours(*user, P))

Output:

precision 5: cell = 4.886 km tall x 3.701 km wide at lat 40.758
precision 6: cell = 0.611 km tall x 0.925 km wide at lat 40.758
user cell       dr5ru7
restaurant cell dr5rue
distance 33.7 m
restaurant cell in user's 3x3 block: True
3x3 block: ['dr5ru4', 'dr5ru5', 'dr5ru6', 'dr5ru7', 'dr5rud', 'dr5rue', 'dr5ruh', 'dr5ruk', 'dr5rus']

What it shows:

• At midtown Manhattan's latitude a precision-6 cell is 0.611 km tall and 0.925 km wide; precision 5 is 4.886 km by 3.701 km. Cells get narrower away from the equator because lines of longitude converge, which is why the width is scaled by cos(latitude).
• The user and the restaurant are 33.7 m apart but in different cells (dr5ru7 and dr5rue). A same-cell-only search would never return a restaurant across the street.
• The restaurant's cell is inside the user's 3x3 block, so the neighbour search finds it.

The query algorithm

1. Choose precision from the radius. The 3x3 block is guaranteed to contain the whole search circle only if the radius is no larger than the cell's smallest side (the user can be anywhere in the centre cell, including right at its edge). From the output: precision 6 covers radii up to 0.611 km at this latitude; precision 5 covers up to 3.701 km. So a 500 m search uses precision 6 and a 2 km search uses precision 5.
2. Fetch candidates: WHERE geohash6 IN (the 9 cell strings), one indexed lookup per cell. Store one column per precision you support (geohash5, geohash6), or a single precision-7 column queried by prefix (LIKE 'dr5ru7%' on a B-tree index with a text pattern operator class, a variant B-tree build, such as PostgreSQL's text_pattern_ops, whose entries are sorted by raw byte value instead of locale-aware collation, so a LIKE 'prefix%' query can actually use the index).
3. Refine: compute the exact great-circle distance (the shortest path between two points along the curved surface of the Earth, here computed with the haversine formula) for each candidate and drop those beyond the radius. The 9-cell block is a square-ish area, and the circle is only part of it.
4. Rank: sort by distance, return the top N. Add rating or open-now filters here.

flowchart LR
  A[User lat lon and radius] --> B[Pick precision so cell side is at least radius]
  B --> C[Encode user cell and 8 neighbours]
  C --> D[Index lookup on 9 cell IDs]
  D --> E[Exact distance filter]
  E --> F[Sort and return top N]

Worked example: how much work a query does

Assume a dense downtown with 400 restaurants per km² and a 500 m radius at midtown's latitude (precision 6):

So each search checks about 2,035 rows (about 6.5 times the true answer set) instead of 10,000,000. That overscan is the price of fixed cells, and it is cheap.

Choices and what would change them

Edge cases and pitfalls

• Radius larger than the cell: the 3x3 block no longer covers the circle. Drop to a coarser precision, or add further rings of neighbours.
• Sparse areas: in a suburb the 9 cells may hold fewer results than requested. Expand to a coarser precision and repeat, with a cap.
• Poles and the antimeridian (longitude plus or minus 180): the simple "offset by one cell" neighbour trick used in the demo must wrap longitude; production geohash libraries handle this, and restaurant search rarely meets it.
• Prefix proximity is not symmetric: two points with a long shared prefix are close, but two close points can share no prefix at all (points either side of the equator or prime meridian). Never rank by "length of shared prefix".
• Caching: results can be cached per (cell, filters) rather than per exact coordinate, which turns millions of distinct user positions into a bounded key space.

Direct answer

For a time-series logging database, range (time-based) sharding wins for time-range queries and for TTL, because a query for "the last hour" touches only the shards that hold that hour, and expiring old data means dropping whole old shards. Hash sharding wins for write balance, because every write lands on a random shard instead of all writes hitting the newest one. I would not pick either alone at billions of rows: I recommend range partitioning on time at the top level (daily partitions) with hash distribution on a source key (host or service) inside each time range. That keeps TTL as a cheap partition drop and spreads the write hot spot.

Range sharding assigns each shard a contiguous interval of the key, here time (shard 1 holds 1 to 2 September, shard 2 holds 2 to 3 September). Hash sharding computes hash(key) mod N or places the key on a consistent-hash ring, which scatters neighbouring keys across shards. TTL (time-to-live) is the rule that deletes data older than some age. Compaction is the background process that merges small files into large ones and physically removes deleted rows.

Comparison on the four named axes

Why those rebalancing fractions. A consistent-hash ring places every node at a point on a circle (a "ring" of hash values from 0 up to some maximum, wrapping back to 0); each node owns the arc running clockwise from the previous node's point to its own. A key is hashed to a point on the same circle and belongs to the first node reached going clockwise from it. Adding a node adds one more point, which only splits the single arc it lands inside: every other node's arc, and every key in it, is untouched. Once the new node joins, there are N+1 points cutting the ring into N+1 arcs, and by symmetry no arc is special, so each one, including the new node's own arc, has an expected length of 1/(N+1) of the ring. Since only the keys in that one arc change hands, the expected fraction of all keys that move is 1/(N+1): with 4 existing nodes, a 5th takes an expected 1/5 = 20%, and every other node keeps everything it had.

Plain hash(key) mod N has no such locality: moving from mod N to mod (N+1) changes almost every key's target node, not just a fifth of them, because the two functions agree only where k mod N and k mod (N+1) happen to coincide. Running that check for keys 0 to 19 against mod 4 and mod 5 makes it concrete:

keys = range(20)
same = [k for k in keys if k % 4 == k % 5]
print(f"stay on the same node under mod4->mod5: {len(same)} of {len(keys)} ({100*len(same)/len(keys):.0f}%)")

stay on the same node under mod4->mod5: 4 of 20 (20%)

16 of the 20 keys, 80%, land on a different node purely from adding one node, which is what "almost every row moves" means in practice; a real deployment growing from 4 shards to 5 under plain mod N would have to physically relocate about 80% of its rows in one operation.

Worked example

Assume 2 billion log rows/day at roughly 100 bytes/row compressed, 30-day retention, 16 nodes.

• Average write rate: 2,000,000,000 / 86,400 = 23,148 rows/s (peaks are higher).
• Storage: 2e9 × 100 B = 200 GB/day, so 6 TB for 30 days.
• Pure range, one shard per day: the whole 23,148 rows/s average lands on one node while the other 15 idle. That is the classic hot-shard failure.
• Pure hash over 16 nodes: each node takes about 1,447 rows/s, but a "last 15 minutes of errors" query must ask all 16 nodes and each one has to scan all 30 days of its data, since nothing about the layout says where "recent" rows are. The tail latency of the query (its response time is set by the slowest handful of requests, not the average or median) is set by the slowest of those 16 full-table scans. Each day, TTL has to delete 2 billion rows spread across 16 nodes, generating tombstones that compaction must rewrite 200 GB of files to purge.
• Composite (recommended): partition by day; within each day, distribute by hash(host) across 16 nodes. Writes spread (about 1,447 rows/s per node). A query scoped to one host and 15 minutes prunes to that host's slice of one day's partition on effectively one node, the best case. A query that is not host-scoped, such as "last 15 minutes of errors across the fleet", still has to ask all 16 nodes, exactly as under pure hash, since every node holds a slice of every host; what the day partition buys there is not fewer nodes but far less data per node: each node scans only today's partition instead of all 30 days, roughly a 30x cut in rows scanned even though the same 16 nodes are still involved. TTL drops yesterday-minus-30's partition on every node, removing 200 GB with no rewrite. Adding a 17th node only needs to own a share of future partitions; old partitions age out on their own in 30 days, so you often never move historical data at all.

Trade-offs and pitfalls

• Partition granularity. Too fine (hourly × host) creates millions of tiny partitions and files, which hurts query planning (the database has to decide, before a query even runs, which of the many small files could hold matching rows) and compaction. Daily is a good default at this volume; hourly only if a single day's partition is too large to manage.
• Late-arriving rows. Logs arrive minutes or hours late. Range partitioning must accept writes into older partitions, so do not make partitions read-only the instant their day ends, and do not run TTL so aggressively that a late row lands in an already-dropped partition.
• Skewed hash keys. Hashing by host works only if hosts are roughly balanced. One chatty service producing 30% of logs will make its hash bucket hot; add a salt (hash of host plus a small random suffix) for known heavy hitters.
• Queries that are not time-bounded ("all logs ever for request ID X") are bad under both schemes. Serve them with a secondary index or a separate lookup table rather than bending the sharding key.
• When pure hash is right: a key-value workload that fetches single records by ID and has no time-range reads and no bulk TTL. That is not a logging workload.

Direct answer

Keep the live "where is every available driver" state in memory, sharded by city, in a structure that answers radius queries directly: a geohash-keyed sorted set (Redis GEO) is the pragmatic choice. GPS pings flow through a partitioned log (Kafka) into a location service that overwrites each driver's single latest position, so the index size tracks online drivers, not ping history. A rider request does one radius query for the nearest 20 or so available drivers, filters out anyone whose last ping is too old, and re-ranks the short list by estimated time of arrival (ETA). If the index is stale or down, fall back in steps: widen the radius and the staleness tolerance, then query a coarser, slower copy of recent positions, and only then fail the request with a clear retry.

Requirements and load (inputs stated, arithmetic shown)

Assumptions for a large multi-city service: 500,000 drivers online at peak, one ping every 4 seconds, the biggest city holding 60,000 of them, 2,000 ride requests per second at peak across all cities, and a matching budget of well under a second (target: the proximity lookup under 20 ms at p99, the 99th-percentile latency).

So the index is write-heavy (125,000 writes against 2,000 reads per second) and small (one entry per online driver). Storage of history is a separate, append-only concern.

Architecture

flowchart LR
  D[Driver app] -->|ping every 4 s| G[Ingest gateway]
  G --> K[(Kafka topic partitioned by city)]
  K --> L[Location service]
  L --> R[(In-memory geo index per city)]
  L --> S[(Driver state: status and last seen)]
  K --> W[(Cold store for analytics)]
  Q[Rider request] --> M[Matching service]
  M --> R
  M --> S
  M --> E[ETA service]

Why a log in the middle. Kafka (a durable, partitioned, append-only message log) absorbs bursts, lets the location service restart and resume from its last offset (the position number of the next unread message in its partition; storing just that number, not the pings themselves, is what makes a restart cheap), and lets analytics consume (read) the same pings without touching the hot path. Kafka splits a topic into partitions (independent, ordered logs); partitioning by city keeps each city's writes on one partition, read by one consumer (a process reading a partition in order), so a driver's pings stay in order.

Representing and indexing driver locations

How Redis GEO works. A Redis sorted set keeps members ordered by a numeric score. GEOADD encodes longitude/latitude into a 52-bit geohash (an interleaving of latitude and longitude bits, so nearby points usually share leading bits) and uses it as the score. GEOSEARCH ... BYRADIUS scans the few score ranges covering the circle and then checks exact distance. Re-adding a member overwrites its position, which is exactly the "latest position only" semantics needed. Run against Redis 7 (redis-cli output, with each result's nested reply compacted onto one line):

> GEOADD drivers:available -97.7431 30.2672 d1 -97.7400 30.2700 d2 -97.7000 30.3000 d3 -97.9000 30.1000 d4
(integer) 4
> GEOSEARCH drivers:available FROMLONLAT -97.7420 30.2680 BYRADIUS 3 km ASC COUNT 10 WITHDIST
1) 1) "d1"  2) "0.1381"
2) 1) "d2"  2) "0.2939"
> GEOADD drivers:available -97.7419 30.2681 d4
(integer) 0
> GEOSEARCH drivers:available FROMLONLAT -97.7420 30.2680 BYRADIUS 3 km ASC COUNT 10 WITHDIST
1) 1) "d4"  2) "0.0148"
2) 1) "d1"  2) "0.1381"
3) 1) "d2"  2) "0.2939"

d3 (about 5 km away) is excluded. The second GEOADD returned 0 because d4 already existed: its position was updated in place and it now ranks first.

Committed layout, per city:

• drivers:available:{city}: the geo set holding only drivers who can take a ride right now. Available-only membership means a query never wastes its COUNT on busy drivers.
• drivers:lastseen:{city}: a sorted set scored by last ping time, used to evict drivers whose app went silent.
• driver:{id}: a hash with status, vehicle type and the ping's own timestamp.
• Status changes (driver accepts a trip, goes offline) remove the driver from the available set and update the hash in one atomic step (a MULTI transaction: Redis's way of running several commands as one all-or-nothing batch, or a small Lua script: a short program Redis runs server-side in a single atomic step instead of several separate round trips), so a busy driver is never offered.

Why not the alternatives. A PostGIS table with a GiST index answers the same query well, but PostgreSQL never updates a row in place: an UPDATE writes a brand-new copy of the row and marks the old copy a dead row version, left for a background process called vacuum to reclaim later. So 15,000 row updates per second in one city means 15,000 dead row versions and 15,000 index entries per second for vacuum to clean up, for data that is worthless 30 seconds later; vacuum falling behind that rate bloats the table and slows every query on it. An in-process grid (hash map from cell ID to driver set) is the fastest option and is what very large operators build eventually, but it means writing your own replication and sharding. Redis is the middle: in-memory, replicated, with the radius query built in.

Keeping the index fresh under heavy ping volume

1. Overwrite, never append: one member per driver, so memory is bounded by online drivers.
2. Coalesce: if the consumer falls behind, apply only the newest ping per driver from each batch (use a pipeline of GEOADD calls: send many Redis commands back to back without waiting for each individual reply, so one network round trip carries the whole batch). Applying 5 old pings for the same driver is pure waste.
3. Drop out-of-order pings: compare the ping's device timestamp with the stored one; ignore older ones so a delayed retry cannot move a driver backwards.
4. Evict the silent: every few seconds, ZRANGEBYSCORE drivers:lastseen:{city} -inf (now - 30s) and remove those drivers from the available set: this reads every member scored from negative infinity up to, but not including (the leading (), "now minus 30 seconds", which is exactly the drivers whose last ping is more than 30 seconds old. Sorted-set members have no per-member expiry, so this sweep is required.
5. Check age at read time too: the matcher drops any candidate whose last ping is older than, say, 15 seconds, so a sweep that lags does not produce ghost offers.
6. Watch consumer lag: the gap, in seconds, between the newest message at the head of the Kafka partition (the most recently written offset) and the offset the location service has actually processed. Track it as the freshness SLO (service-level objective), for example 99% of pings applied within 2 seconds.

Serving a request

1. GEOSEARCH radius 3 km, ASC COUNT 20 WITHDIST on the rider's city set.
2. Drop stale or ineligible candidates (wrong vehicle type, rating filters).
3. Straight-line distance is a poor proxy across rivers and one-way grids, so send the short list to the ETA service and rank by estimated pickup time.
4. If fewer than 3 candidates survive, retry with 6 km, then 10 km.
5. Offer to the best candidate with a short reservation (a lock with an expiry on that driver) so two riders cannot both be matched to the same driver.

Fallbacks when the index is stale or unavailable

The key property is that the index is rebuildable: it is a cache of the last 4 seconds of pings, so recovery is "wait one ping interval", not "restore a backup".

Trade-offs and pitfalls

• Hot cities and events: a stadium letting out concentrates demand into a few cells. Sharding by city handles steady load; a single dense city may need splitting by geohash prefix.
• Memory is not the constraint: 60,000 members per city is a small sorted set. Write rate per shard is the real limit, which is why the city split and coalescing matter.
• Ping rate is a cost lever: halving the interval to 2 seconds doubles writes to 250,000 per second and only reduces the staleness from 60 m to 30 m. Adaptive rates (faster while en route to a pickup, slower while idle) usually beat a global increase.
• What would change the design: if matching needed road-network proximity for every candidate across a very dense city, an in-process grid co-located with the ETA engine would replace the network hop to Redis.