If someone asked me how to match drivers and passengers at Uber scale using only a relational database, I would acknowledge that it's quite challenging, but here's a straightforward way I would tackle it:
Think Spatially: First off, relational databases aren't designed for this kind of real-time, geospatial matching. But if we have to, we'd likely divide the cities into manageable zones or grids (using geohashing or a similar approach) to make querying and updating locations more feasible.
Index Smartly: In terms of indexing, we'd want to create indices on these spatial zones and possibly on timestamps to find who's where and when quickly. However, too many indices could slow things down because of the constant updates, so we'd have to be selective and smart about it.
Periodic Polling for Matching: Since we're constrained by periodic polling, we could set up a background job to run at intervals. It would pull the latest driver locations and match them with waiting passengers, kind of like "batch matchmaking." This won't be as fast as real-time matching, but it's workable.
Handle High Traffic Areas: For busy areas like urban cities, we might need to run this matching process more frequently and possibly use smaller grid sizes to get more precise matches without overloading the system.
Race Condition Management: To prevent issues like race conditions, especially when several matches are happening at once, we might need to use some form of transaction isolation. However, that could slow down the system, so we'd need to balance our safeguards with performance.