Geospatial and Time-Series Data Questions

Requirements & trade-offs:

• Ingest millions of points/min => favor append-optimized stores (native TSDB or columnar OLAP like ClickHouse). OLTP RDBMS can’t handle that write throughput without sharding/ingress proxies.
• Need efficient downsampling & retention => keep raw high-cardinality short-term, store pre-aggregates (per-minute/hour) for long-term; use TTLs and compression.
• Choose native TSDB (Prometheus/Influx/Timescale/ClickHouse) if you want built-in compaction & query patterns. Use SQL warehouse (ClickHouse, Snowflake) when you need complex analytics and columnar compression.

Schema patterns (ClickHouse, columnar + TTL + partitioning):

CREATE TABLE metrics_raw (
  org_id UInt32,
  metric_name LowCardinality(String),
  tags Nested(key String, value String),
  ts DateTime64(3),
  value Float64
) ENGINE = MergeTree()
PARTITION BY toDate(ts)
ORDER BY (org_id, metric_name, ts)
TTL ts + INTERVAL 7 DAY DELETE
SETTINGS index_granularity = 8192;

Downsample/aggregates table (per-minute):

CREATE TABLE metrics_min (
  org_id UInt32,
  metric_name LowCardinality(String),
  minute DateTime,
  max_value Float64,
  min_value Float64,
  sum_value Float64,
  count UInt64
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(minute)
ORDER BY (org_id, metric_name, minute)
TTL minute + INTERVAL 365 DAY;

Ingestion / downsample job (batch or streaming):

• Use Kafka -> consumer (Flink/Spark/Materialized) to aggregate into 1-minute windows and INSERT INTO metrics_min (upsert/merge or SummingMergeTree).

Sample queries

• Per-minute max for a metric in a time range:

SELECT minute, max_value
FROM metrics_min
WHERE org_id = 42 AND metric_name = 'cpu.user'
  AND minute BETWEEN '2025-11-20 00:00:00' AND '2025-11-20 01:00:00'
ORDER BY minute;

• One-hour aggregate (max of per-minute maxes or rollup from raw):
  Option A, from per-minute aggregates:

SELECT toStartOfHour(minute) as hour,
       max(max_value) AS hour_max,
       sum(sum_value) AS hour_sum,
       sum(count) AS hour_count
FROM metrics_min
WHERE org_id=42 AND metric_name='cpu.user' AND minute >= now() - INTERVAL 7 DAY
GROUP BY hour;

Option B, from raw (if needed for exactness; expensive):

SELECT toStartOfHour(ts) as hour, max(value) as hour_max
FROM metrics_raw
WHERE org_id=42 AND metric_name='cpu.user' AND ts BETWEEN ... 
GROUP BY hour;

Partitioning/compression/retention advice:

• Partition by day or month to limit file scans.
• Order/primary key should include low-cardinality dimensions first (org_id, metric_name) then time.
• Use columnar store with compression codecs (ZSTD) and proper granularity.
• TTLs: delete raw after short retention; keep aggregates longer.
• Use sparse/inverted index for high-cardinality tags or store tags in a separate labels table and reference by label_id.

When to use which system:

• ClickHouse/TimescaleDB: if you need SQL + high ingestion + rollups. Timescale provides hypertables + continuous aggregates.
• Prometheus/Influx: best for monitoring; retention and native scrape model.
• Data warehouse (Snowflake/BigQuery): good for analytical joins & long-term retention; ingest via micro-batches, but watch ingest cost and latency.

Operational notes:

• Buffer writes through Kafka; use idempotent writes/ordering keys (org+metric) to avoid hotspots.
• Monitor partition sizes and compaction; use partition pruning in queries.
• Provide materialized views / continuous aggregates to serve low-latency queries and avoid scanning raw data.

Requirements & constraints:

• Functional: return nearest N available drivers within radius R with latency <100–200ms.
• Non-functional: high throughput (thousands req/s), geo-fenced queries, eventual consistency with authoritative OLTP (driver status/location).

Schema (authoritative OLTP / Postgres+PostGIS):

• drivers table (driver_id PK, vehicle_id, status ENUM {available, busy, offline}, last_seen TIMESTAMP, geom GEOGRAPHY(Point,4326), metadata JSONB)
• driver_location_history (driver_id, ts, geom GEOGRAPHY)
• indexes: GIST on geom, btree on status + last_seen
  Example:
• drivers(driver_id UUID, status TEXT, last_seen TIMESTAMP, geom GEOGRAPHY(Point,4326), attrs JSONB)
• CREATE INDEX ON drivers USING GIST (geom);
• CREATE INDEX ON drivers (status, last_seen);

Realtime read path (fast matching): in-memory geo store + streaming updates

1. Location ingestion: mobile client → API gateway → Kafka topic partitioned by driver_id. Validate/authenticate at edge.
2. Stream processing: consume Kafka in Flink/Beam/Spark Structured Streaming to:
   • Filter noise, dedupe, smooth (simple Kalman or velocity threshold)
   • Enrich (map matching if needed)
   • Emit two sinks: (a) write event to OLTP (async batch upserts), (b) update Redis GEO or specialized in-memory spatial index (e.g., Tile38 or RedisGEO).
3. Redis usage:
   • Use Redis GEO (GEOADD with member=driver_id, long/lat) plus a hash for driver metadata (status, last_seen, score).
   • Query with GEORADIUS/GEORADIUSBYMEMBER to get nearest driver_ids then filter by metadata.
   • Optionally use Tile38 or a spatial index in-memory for fast bounding-box + radius queries with better semantics and notifications.

Geospatial representations & indexing:

• Store precise points as PostGIS GEOGRAPHY(Point,4326) for accurate distance on earth.
• In-memory use lon/lat doubles (WGS84) and geohash for sharding.
• Indexing strategies:
  • OLTP: GIST (R-tree on PostGIS) for storage and analytical queries.
  • Realtime: geohash bucketing into Redis key spaces (e.g., geohash prefix length 6–7) to limit candidate sets; or use native Redis GEO which internally uses sorted sets (approx via geohash).
  • For very large scale, pre-partition by grid (S2/Geohash) and maintain per-cell sets of available drivers.

Precomputation & optimization:

• Maintain per-cell aggregated availability counts to quickly decide if cell has drivers (fast rejection).
• Precompute driver “hotness” score or expected time-to-pickup using historical travel times per cell pair (Materialized views or feature store).
• Cache nearest-driver results for a few seconds when appropriate (TTL 1–5s) to reduce load during bursts.

Fallback & degraded modes:

• If Redis overloaded or unavailable:
  • Fallback A (graceful): use coarse geohash cell lookup (precomputed in a CDN/fast KV) and perform matching using fewer candidates.
  • Fallback B (authoritative DB): query Postgres+PostGIS for nearest drivers but with higher latency and rate-limited. Use read replicas and bounding-box then limit.
  • Fallback C: return best-effort from last-known-assignment or return empty with an explanation.
• Circuit-breakers and adaptive throttling: reject non-essential queries, increase radius progressively (exponential backoff) to find drivers under load.

Keeping system eventually consistent:

• Source-of-truth: OLTP (Postgres). Streaming pipeline is single writer to Redis but asynchronous to OLTP.
• Exactly-once-ish patterns: use idempotent upserts to OLTP and Kafka offsets checkpointing to avoid duplicates.
• Reconciliation jobs:
  • Periodic batch job (every 30–60s) to diff Redis vs OLTP for active drivers in hot cells and repair (e.g., read drivers where last_seen > threshold).
  • On startup or failover, stream a snapshot sync: read active drivers from OLTP and repopulate Redis (bounded, parallel).
• Conflict resolution:
  • Redis is optimized for speed; treat it as cache: authoritative reads/writes from OLTP, but Redis wins for transient decisions as long as reconciliation runs frequently.
  • Use last_write_ts and version vectors stored with each entry; the periodic reconciliation resolves older timestamps to OLTP state.
• Observability & alerts:
  • Monitor Kafka lag, Redis memory, command latency, Postgres replica lag, reconciliation drift metrics (# mismatches).
  • Automated alerts to trigger resync when drift > threshold.

Trade-offs & rationale:

• Redis GEO provides sub-100ms queries; PostGIS provides correctness and analytics. Using geohash/S2 enables sharding and candidate pruning.
• Precomputation and caching reduce compute but introduce staleness—acceptable when TTL small and orders of magnitude faster.
• Periodic reconciler keeps eventual consistency; idempotent writes and Kafka ensure resilience.

Example query flow (simplified):

• Rider requests: determine cell(s) to search → Redis GEORADIUS to get k candidates → fetch driver metadata hash → filter by status/last_seen → rank by ETA (compute approximate driving time with cached cell travel times) → return candidate list and reserve driver via write to OLTP/assignment topic.

This architecture balances low latency, scalability, and eventual consistency suitable for a production driver-matching service.

Requirements & constraints:

• Fast rolling metrics per user for 7/30/90-day windows, high write volume, large number of users, low-latency analytical queries.
• Target: columnar cloud DW (BigQuery / Snowflake / Redshift Spectrum). Principles apply to Postgres-like OLTP with adjustments.

Logical schema:

• events (
  event_id UUID,
  user_id STRING,
  event_type STRING,
  event_ts TIMESTAMP, -- event time
  ingest_ts TIMESTAMP, -- ingestion time
  properties VARIANT / JSON, -- optional payload
  value DOUBLE -- numeric value to roll up
  )

Partitioning:

• Partition by DATE(event_ts) (daily partitions). Rationale: rolling windows are time-bound; daily partitions keep scans limited.
• Add partition retention (data TTL) if appropriate.

Clustering / sort keys:

• Cluster (or SORT) by (user_id, event_ts) to physically colocate per-user time ranges and enable efficient range scans for window functions.
• In Redshift use sort key (user_id, event_ts); in BigQuery use clustering on user_id,event_ts.

Compression:

• Use columnar compression (native DW). Ensure properties JSON is compressed or stored in separate object store if large.
• Encode categorical columns (event_type) with dictionary encoding.

Secondary indexes / materialized structures:

• Avoid per-row secondary indexes in columnar DW. Instead:
  • Materialized views / pre-aggregations: maintain daily per-user aggregates (user_id, day, sum_value, count, distinct_x) computed via micro-batch streaming or scheduled jobs.
  • Use a covering index or secondary index in OLTP (if using Postgres) on (user_id, event_ts DESC) for hot-user queries.

Query pattern (CTE + window functions):

• Example (BigQuery/standard SQL) computing rolling 7/30/90-day sums using daily pre-agg table user_daily:

WITH days AS (
SELECT user_id, day, SUM(value) AS day_sum
FROM user_daily
WHERE day BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
GROUP BY user_id, day
),
windows AS (
SELECT
user_id,
day,
SUM(day_sum) OVER (PARTITION BY user_id ORDER BY day
RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS sum_7d,
SUM(day_sum) OVER (PARTITION BY user_id ORDER BY day
RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW) AS sum_30d,
SUM(day_sum) OVER (PARTITION BY user_id ORDER BY day
RANGE BETWEEN INTERVAL 89 DAY PRECEDING AND CURRENT ROW) AS sum_90d
FROM days
)
SELECT * FROM windows WHERE day = CURRENT_DATE();

Reasoning & trade-offs:

• Pre-aggregating to daily per-user dramatically reduces rows scanned for rolling windows (instead of raw events). Use event-level table for ad-hoc drilling.
• Partition by day limits scan; clustering by user_id speeds per-user ranges.
• Materialized daily aggregates updated via streaming/ETL give near-real-time metrics; full recompute jobs can backfill.
• For extremely high-cardinality users and tiny per-user data, consider time-series optimized stores (ClickHouse, TimescaleDB) that support hypertables and native compression and indexes.
• Monitor skew: hot users may need separate handling (cache recent aggregates).

Edge cases:

• Late-arriving events: use watermarking + reprocessing for affected daily aggregates.
• Out-of-order timestamps: store ingest_ts and choose event_ts semantics.

Requirements & constraints:

• Support city heatmaps (many zoom levels), neighborhood aggregates, and route-level queries with low latency for exploration and batch analytics.
• Scale: millions of trips/day, multi-tenant, hourly/daily rollups.

H3 indexing strategy

• Choose a small set of H3 resolutions that map to UI zoom levels (example mapping):
  • Zoom 6 → H3 res 5 (~4.3km), Zoom 10 → res 8 (~0.18km), Zoom 13 → res 9 (~0.07km).
• Always compute and store multiple resolutions per geometry/event so queries can use nearest pre-aggregated resolution. For each event/trip geometry compute h3_cells = {h3_to_parent(h3(event), r) for r in prechosen_resolutions}.
• Use h3-extensions in Spark (h3-java / h3-py) to compute cells during ingestion.

Storage/data model (example tables)

• raw_trips (parquet/Delta)
  • trip_id, start_ts, end_ts, driver_id, geom_line STRING/WKB, start_h3_res9, end_h3_res9, h3_sequence ARRAY<STRING>, attributes...
• h3_cells_master
  • h3_index STRING PRIMARY KEY, res INT, geography GEOGRAPHY (cell polygon), centroid GEOGRAPHY, city_id
• preagg_tile_metrics (base unit for fast heatmaps)
  • date_hour, h3_index (for chosen res set, e.g., res7,res8,res9), metric_counts JSON or columns (trips, pickups, avg_wait), shard_key
  • Partition by date_hour, cluster by h3_index (or Z-order / sort on h3_index)
• neighborhoods table
  • neighborhood_id, name, geography GEOGRAPHY, outer_h3_indexes ARRAY<STRING> (precomputed at a chosen res)

Pre-aggregations & pipelines

• Real-time: streaming job (Spark Structured Streaming / Flink) consumes events, maps to h3 cells for the set of resolutions, increments streaming materialized aggregates in a fast store (Redis or Kafka + windowed aggregates), writes batched updates to preagg_tile_metrics hourly.
• Batch: daily rollups from raw_trips to compute aggregated metrics at all resolutions; write compact parquet/Delta partitioned files and update preagg_tile_metrics via upsert.
• Precompute common tiles (resolutions mapping to UI zooms) and store as materialized views for low-latency queries.

Route-level model & analytics

• Store route as polyline (WKB/GEOGRAPHY) and as sequence of h3 cells (h3_sequence). For route comparisons/heatmaps, use sequences or compute route tile counts per trip (explode h3_sequence).
• Precompute route-level aggregates: route_id, frequent_h3_sequence_signature (MinHash or hashed n-grams), metrics (avg duration, congestion signals).

Spatial joins & warehouse indexing

• Use native GEOGRAPHY types in warehouse (BigQuery/Redshift/Snowflake/Snowflake GEOGRAPHY via GEOGRAPHY type/BigQuery GEOGRAPHY/Redshift with GEOMETRY extension).
• For point-in-polygon / neighborhood joins:
  • Option A: Use h3 precomputed cell lists for polygons: join on h3_index IN neighborhood.outer_h3_indexes (fast equality join).
  • Option B: Use ST_CONTAINS(neighborhood.geography, point) using GEOGRAPHY functions—ensure spatial indexes where supported.
• Indexing advice:
  • Postgres/PostGIS: GiST index on geography column (CREATE INDEX ON table USING GIST (geog));
  • Snowflake: cluster by h3_index or use SEARCH optimization; store h3_index as varchar for clustering keys.
  • BigQuery: partition by date and clustering on h3_index and city_id to accelerate range scans.
• For joins between large tables, prefer hash joins on h3_index (explode arrays), pushdown filters by date_hour and h3_index to prune data.

Performance & storage best-practices

• Store cell geometries once (h3_cells_master); reference by h3 index to avoid repeating polygons.
• Use columnar storage (Parquet/ORC/Delta) for preagg_tile_metrics and raw_trips.
• Materialize aggregated tiles for top N zoom levels; compute higher-res tiles on demand via rollup/downsampling using parent-child h3 relationships.
• Use compaction jobs to merge small files and optimize layout; use Z-order or sort-by h3_index for locality.

Edge cases & trade-offs

• Choosing too many resolutions increases storage; pick small set matching UI needs and support on-the-fly aggregation for others.
• Point-in-polygon with complex neighborhood boundaries: prefer h3-based cover + final ST_INTERSECTS filter to reduce expensive geometry ops.
• Route sequence storage can be large: compress h3 sequences (delta-encode) or store only hotspots and frequent-pattern signatures.

Example Spark pseudocode (mapping events to multi-res h3)

from h3 import h3
def map_event_to_cells(lat, lng):
    res_list = [7,8,9]
    base = h3.geo_to_h3(lat,lng,9)  # highest-res cell
    return {r: h3.h3_to_parent(base, r) for r in res_list}

This model gives fast, scalable heatmaps via pre-aggregated tiles, accurate neighborhood joins using both h3 covers and GEOGRAPHY functions, and route-level analytics by storing sequences + aggregated signatures.

Requirements & constraints:

• Ingest: 10k events/sec (~864M events/day if 1/sec sensors) — assume 1 row/event, small payload.
• Queries: recent-time range scans (last hours/days), sensor-level lookups, occasional aggregates.
• RDBMS target: Postgres (or cloud equivalent like Aurora/Cloud SQL) with partitioning and compression.

High-level design:

1. Schema

• sensor_readings(sensor_id UUID, ts TIMESTAMPTZ, value DOUBLE, tags JSONB, PRIMARY KEY (sensor_id, ts))
• Store payload small; use separate metadata table for sensor config.

2. Partitioning

• Range partition on ts: daily partitions (or hourly if per-partition size > 10GB). Use declarative partitioning. For 10k/sec ~ 864M rows/day; with 100 bytes/row → ~86GB/day, so choose hourly partitions (~3.6GB/hour) or 6-hr partitions depending on DB limits.
• Sub-partition by sensor_id hash if hotspotting by a few sensors.

3. Indexing

• Clustered index (or physical ordering) on (ts DESC) within each partition to make range scans contiguous. In Postgres, use BRIN on ts for very large partitions and B-tree on (sensor_id, ts) for sensor-specific queries. Consider CLUSTER command periodically per partition to maintain physical order.
• Covering indexes on (sensor_id, ts) INCLUDE (value) for point scans.

4. Ingest pattern & write amplification

• Use bulk/batched inserts (COPY or multi-row INSERTs sized ~1-5k rows) to amortize WAL and checkpoint overhead.
• Disable synchronous_commit for ingestion replicas, use group commit tuning.
• Keep partitions append-only to avoid random updates. Avoid secondary indexes on high-ingest columns where possible to reduce write amplification; prefer BRIN which is cheap to update.
• Use partitioned tables to limit index maintenance to the active partition.

5. Retention & archival

• Implement retention policies with automated partition lifecycle: for data older than N days, either DROP partition (fast) or ATTACH/MOVE to cheaper storage (export to Parquet on S3) then DROP. Use background jobs (Airflow/Kubernetes CronJob) to handle snapshot+upload then drop.
• Keep hot window (e.g., 7 days) in DB, warm (30-90 days) in compressed columnar store, cold archived.

6. Compression & storage

• Enable DB-level compression (TOAST tuning) for large JSONB. For maximal compression, periodically export partitions and store as columnar Parquet with gzip/ZSTD; use predicates on ts for fast range scans in analytics.
• If using Postgres extension (pg_compress or zheap), leverage page-level compression.

7. Operational considerations

• Monitor partition sizes, index bloat; run VACUUM/ANALYZE on inactive partitions.
• Use connection pooling and backpressure; implement buffering (Kafka, Kinesis) to smooth spikes and enable efficient consumer-side bulk loads.
• Benchmark: for 10k/sec, pipeline -> Kafka -> consumer writing batched COPY to hourly partitions, expect sustained insert throughput; tune checkpoint_segments, wal_buffers, shared_buffers, maintenance_work_mem.

Trade-offs:

• More partitions (hourly) => easier drop/parallel maintenance but more partition management overhead.
• Fewer secondary indexes => lower write amplification but slower ad-hoc queries; mitigate with materialized views on warm data.

Example numbers:

• 10k/s = 36M/hour; with 100B/row ≈ 3.6GB/hour. Hourly partitions keep partition files ~3–5GB manageable; daily partition would be ~86GB causing longer maintenance.

This design prioritizes append efficiency, low write amplification, fast range scans on ts, and cheap retention via partition drops/archival.