Optimizing inserting/storing Large amount of data in database

Question

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.

1. Customer Detail
2. Customer Account
3. Customer Address
4. Customer Contact
5. Customer Invoice
6. 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.

Answer 1

How will you process this bulk data within the DB? Not in API code that shreds it and forwards it to other applications - I mean actual queries running in the DB that stores these bulk files.

You state "We are not doing much with the stored data at this point." If you're not querying it directly then hold it in the most space- and time-efficient way you can. This may be JSON, or compressed string. Look into TOAST.

Moreover, if this data is not delivering value do not hold it in what is likely the most expensive persistence container. Stream it to a file system or cloud blob storage. Later, when you've decided how it will be used, it can be parsed and imported, or read in situ. Many analytics engines can read blob containers.

If you do split storage do please consider the needs for transactional consistency, and how your DR strategy copes with restoring different files using different technologies on different platforms in a consistent way.