Database Services for Quick Commerce Platforms

Inspects nearest-rider spatial queries, assesses GiST index scan efficiency against sequential scans, and identifies slow geospatial dispatch queries (>20ms) during active order allocation rushes.

-- 1. Identify slow nearest-rider dispatch queries (>20ms) executing ST_DWithin / k-NN
SELECT pid,
       now() - query_start AS duration,
       state,
       wait_event_type,
       wait_event,
       LEFT(query, 140) AS dispatch_query
FROM pg_stat_activity
WHERE (query ILIKE '%st_dwithin%' OR query ILIKE '%<->%')
  AND state != 'idle'
  AND now() - query_start > interval '20 milliseconds'
ORDER BY duration DESC
LIMIT 10;

-- 2. Verify GiST spatial index scan ratio on rider location tables
SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan AS spatial_index_scans,
       idx_tup_read AS tuples_read,
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname IN ('rider_locations', 'delivery_zones')
  AND indexrelname LIKE '%gist%';

Pinpoints root blocking transaction PIDs, contending row-level locks on store inventory tables, and lock wait duration across concurrent SKU reservation transactions without acquiring shared catalog locks.

-- 1. Isolate root blocking transactions contending on dark-store inventory SKU rows
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid    AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS blocking_statement,
       now() - blocked_activity.query_start AS wait_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;