I am caught up in a situation where I need to index like seven columns used for search and filtering on a table, but this is obviously going to hurt the performance for inserts, updates and deletes when dataset on the table grows(and its going to). Now I am thinking of using a solution like meilisearch for search and filtering and only maintain the index on primary and foreign keys and drop the indexes on the other columns. Is this the right way to go about a problem like this?
MeiliSearch seems to fit your use case as it can support a lot of document with a lot of fields.
But the way to efficiently add these documents in MeiliSearch is to add them in batches. So I you have 1 million documents, you add them in batches of 1000 instead of in one batch of 1 million. You can have more information about limitations here.
Also, we are not limited to 10mb! That was the previous default payload size which now is 100mb. Meaning that you can not make a request with more than 100mb at once. If you want to change that number you can with the right flag.