ARTICLE AD BOX
I have a React UI with a Spring Boot backend that retrieves data from a SQL database.
The screen contains around 45 filter columns, where filters work in a cascading way. For example:
Selecting a value in Filter 1 determines the available values in Filter 2
Selecting Filter 2 further restricts values for Filter 3
This continues for up to 45 filters
Each filter can have up to ~10,000 possible values in the database.
Currently, the backend runs SQL queries with WHERE conditions based on selected filters to return the next set of possible values. However, I am concerned about performance and scalability, since:
The number of filters is large (45)
Each filter can contain many values
Queries may become complex as more filters are applied
The UI may trigger frequent requests as users change filters
Example simplified flow:
User selects value for column1
Backend runs a query to fetch valid values for column2
User selects value for column2
Backend fetches values for column3
And so on...
Example query pattern:
SELECT DISTINCT column2 FROM data_table WHERE column1 = ?Then:
SELECT DISTINCT column3 FROM data_table WHERE column1 = ? AND column2 = ?My questions:
What is the recommended backend design pattern for implementing large cascading filters like this?
Should this continue to be handled via SQL queries, or is it better to use something like a search/indexing engine (e.g., Elasticsearch/OpenSearch)?
Are there common optimization techniques (caching, precomputed aggregates, materialized views, etc.) that work well for this type of filtering system?
Any architectural or performance recommendations would be appreciated.
