Share

For real estate websites, the speed of map-based property searches is critical. After extensive testing, Redfin's migration from MySQL to PostgreSQL with the PostGIS extension resulted in dramatic performance improvements for complex geographic queries, particularly those combining location with price and feature filters. This transition, while challenging, solved significant slowdowns that occurred when users searched large areas with specific criteria, making property searches faster and more reliable.
What are the performance challenges with MySQL for real estate searches?
The core of any real estate platform is its search functionality. Users typically constrain a search by numerous criteria, such as a maximum price or a minimum number of bedrooms, all within a specific geographic area defined by the map view. In MySQL, a query might look for condos under $400,000 with at least two bedrooms within a defined polygon on the map.
The primary challenge with MySQL is its indexing limitation: a standard index cannot include spatial columns (like geographic coordinates), and a spatial index cannot include standard columns (like price or bedroom count). This means the database must choose between using an index for the geographic area or an index for the property attributes, but never a single, optimized index for both. This performs well for simple searches but leads to major slowdowns, or "killer queries," under specific conditions:
How does PostGIS solve the multi-column indexing problem?
The solution came with PostgreSQL and its PostGIS extension. PostGIS supports the creation of multicolumn GIS indexes, which can include geographic data (like a property's centroid coordinates) alongside standard data types like price and bedroom count. This single, powerful index allows the database to instantly narrow down properties that meet all criteria simultaneously, rather than performing a slow, sequential scan of a large geographic area.
For Redfin, this technical upgrade translated into a direct user benefit. Queries that previously took an unacceptably long time—such as an investor searching a large metropolitan area for fixer-upper properties with specific price and square footage requirements—now execute swiftly. The advanced geographic functionality within PostGIS, including precise polygon containment checks and distance calculations, also allows for more sophisticated and accurate search features without sacrificing performance.
| Search Scenario | MySQL Performance | PostGIS Performance | User Impact |
|---|---|---|---|
| Zoomed-in map with simple filters | Fast | Fast | Minimal |
| Zoomed-out map with specific filters (e.g., price, bedrooms) | Very Slow (Killer Query) | Fast | Significant Improvement |
| Large area search for rare properties | Slow | Fast | Major Improvement |
What were the other benefits of switching to PostgreSQL?
Beyond query performance, the move to PostgreSQL offered additional advantages over MySQL's MyISAM table engine, which was required for spatial indexes.
CLUSTER command can physically order the table data based on the multicolumn index, making access even faster.The transition was not without its difficulties. Setting up replication in PostgreSQL was more complex than in MySQL, and supporting two database systems during the migration increased code complexity. However, based on our experience assessment, the performance gains and improved system stability have made the effort worthwhile.
Conclusion
For a real estate platform reliant on complex, map-driven searches, the choice of database is crucial. The key takeaway is that a multicolumn GIS index in PostGIS can eliminate the performance bottlenecks common in MySQL when queries combine geographic and standard filters. This leads to a faster, more reliable user experience, especially for power users performing detailed searches across large areas. While the migration requires careful planning, the outcome is a more robust and scalable foundation for location-based real estate search.









