We have a POST API that takes data from the Client and send it to different applications (Payment, Email, SMS). We also store all the data sent by the Client in a database for future analytics purposes.
We get Bulk vs Transaction requests through our API, my concern is regarding optimizing bulk requests. We can receive up to 10-15 bulk requests a day and each request is around 50-100k records and each of these 100k records has 30-35 key: value pairs in the request body (This will double by early next year)
These values are sent to different applications and also stored in multiple tables e.g.
- Customer Detail
- Customer Account
- Customer Address
- Customer Contact
- Customer Invoice
- Customer Custom Values Table (Values that don't fit into the other tables go to this table as a key/value. There are on average 10-15 such values, our issue is we are onboarding lot of clients with different DB models and we can't map everything into our relational DB (POSTGRES) 1:1)
This means that with each bulk request of 100k we have to add 2 million rows in different tables and a good chunk goes to custom table
Each day we are processing .5 million records and storing 10 million rows. Our tables are already huge especially the custom table.
We started off with relational DB design, not knowing the volumes could increase this much. We are not doing much with the stored data at this point, but the direction is to store everything that client sends us in database
I would need some advice on what would be the best design in our case. Shall we store the custom fields as a JSON in the DB column (JSONB), so we have 1 entry per record instead of 15? Shall we start looking into NoSQL DBS? Do we need to re-think the model especially what to do with values that don't map directly to our database and if yes where do we start?
I am concerned about large storage space, increasingly slow writes and reads that might affect our performance.