Search to be implemented for currently developing a project which is being developed using Spring Boot and Postgres.
The search table has at least 8 columns, all the columns are used in the search filtering. For this, how many indices need to be created? What type of index needs to be created?
There will be at least 1M records in the table. Is Postgres enough with better indices? or need to choose any other NoSQL like Elastic Search?
Please share your thoughts and experience on this.
please refer to the above screen capture for my search mock design. All the combinations of each column would be used for filtering. If so 8 power 8 would be 40320. So do I need to create 40320 indices? FYI: I have few more searches same like this.
Since I have many columns, so I guess there would be more indices to be created which is not the right way to do. in this case,
Elastic Search is better to choose or
Postgres itself serves the purpose