The Problem Statement

After starting the migration of all our data to PostgreSQL and we encounter the case where we were using the Elasticsearch for geo-distance based filtering and sorting. Some of the classical use cases were:

• Filter Service Providers where Providers have offices in xxx Miles (e.g 50 Miles, 100 Miles) radius from Chicago.

• Sort the Service Providers based on distance from the given Center.

We had physical locations data for each Service Provider, means we had latitude and longitude information of their offices.

Our Elasticsearch cluster was shared across the different team, we can't guarantee the performance because other teams uses Elasticseach heavily, and sometime that adversely impact the performance of whole cluster. We had two options:

1. We can provision new independent Elasticsearch cluster dedicated to our application unfortunately, we could't because there was cost concern and use two different datasource ( hybrid implementation), means filtering/sorting using Elasticsearch and resolving the entities/data from the PostgreSQL can be complex.

2. We can just get rid of Elasticsearch and look for alternative especially PostGIS provided by PostgreSQL ecosystems. With that, we won't have to introduce complexity of resolving data from two different data sources.

Here are some benefits of moving to PostGIS

• PostGIS supports more advance geographical and spatial queries which means this would open the door for future requirements.

• Since it's an extension of PostgreSQL, it allow easy integration with our existing database schema and we can leverage our current data and resources efficiently.

• With proper indexing and optimisation, we can expect high performance geo-distance queries.

• Eliminates the need to maintain/use Elasticsearch and simplifies our stack.

Challenges we can't ignore when moving to PostGIS

• Learning Curve: Writing the equivalent queries in PostGIS, required understanding of PostGIS ecosystem.

• Performance: After moving to PostGIS, we have to monitor and optimise the performance as data and number of requests grows.

• DBA Supports: Need DBA/DevOps support to enable the extension, monitor the resources under the same schema and add more resources if needed.