Q: Database responsibility segregation I have the following architecture: Database (Postgres), Batch(java app - long running operations that delete, update, insert lots of data in the database), Rest API(java app - provides rest services to the front end).
The problem is that when the Batches starts to run this spikes the utilization of the database to 100% and the Rest API becomes very slow while the batches run.
Does anyone have expirience with a simmilar problem? Maybe load all of the data with the Batch component in one database instance and then copy-paste it in a different database instance that is only for the FE to read from.
The Batches most of the time delete the current data from the database and insert fresh one from 3th party apis.
The Rest API mostly read from the database but also does small amount of inserts and updates.

A: First you need to profile your operations to see where your time is going.  Is it

*

*CPU

*IO

*Contention on shared resources such as indexes

*Memory

Your options really depend on where your resources are going.  Perhaps you really do need to throw hardware at it.  Profiling will give you your directions.
Understanding this will open different optimisation options.  Without knowing your code it's going to be hard for any proper advice, though in general:

*

*Each transaction has an overhead, so perhaps inserting into a staging table then doing a bulk update to your working tables from that would be more efficient than row-by-row inserts. (CPU & IO)

*Obviously check for missing indexes, particularly on primary & foreign keys, though by bulking up these can become less useful. (IO & Contention)

*Can you prioritise your batch connections lower than your APIs? (All)

*Are there any partitions that segregate your incoming vs what is being read (Contention)?

*Can your batch process be made to work on independent data snapshots then "swap" tables to make the new copy the real one (similar to your copy/paste idea but at a table level). (Contention)


A: 
one database instance and then copy-paste it in a different database instance that is only for the FE to read from.

This is the correct solution, except you shouldn't be reinventing the wheel. Postgres has a whole solution ready made for you (you should probably read at least the introduction to Section 27 in the Postgres manual while you're there to understand the other options available to you and whether they are right for your solution).
