I’m bootstrapping a Saas where I’ve got several customers, each having their own set of “end_customers”. On average, a customer has around 3,000 to 5,000 end customers, but this could range anywhere from 1,000 up to 300k.
Each customer can define up to 30 custom fields for their end customers, with a mix of field types including text, number, date, or a constrained text value. All of this data is currently stored in a MariaDB database, with a main
end_customer table and a
end_customer_field table for the custom fields. However, I’ve run into some serious performance issues when trying to allow my customers to execute dynamic filter queries on their end customers.
These filter queries involve joining the
end_customer table with the
end_customer_field table up to 30 times, and converting text values to numbers or dates for certain data types. I’ve also implemented a variety of logical comparison operators based on the data type. Unfortunately, more complex filters are currently taking several minutes to run on my local system before timing out, so I urgently need to find a solution to this problem.
I’ve been exploring a few potential approaches to improve performance, including limiting the number of search criteria, restructuring the data storage, and even considering a switch to a different type of database. For instance, I could change the structure of the
end_customer_field table to have multiple columns for different data types, or I could split it into separate tables per data type. This would complicate the join/lookup logic, but could avoid the need for casting.
Another option I’m considering is a switch to a NoSQL database, such as MongoDB. I haven’t used NoSQL databases before, but I’ve heard they could potentially handle this type of data and querying more efficiently. However, I’m not exactly sure how to go about this, or whether it would really solve my performance issues.
I’m reaching out to the community here to see if anyone has any insights or suggestions. Would a NoSQL database be a good fit for this use case? How could I structure my data in MongoDB (or another NoSQL database) to handle these dynamic filter queries efficiently?
Any guidance or advice would be greatly appreciated.