-1

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.

8
  • You are doing too much work on the database. read all the data you need. change in in the batch (without calling db) insert changed rows into temp table, merge query to update real data from temp table.
    – Ewan
    Jan 4, 2022 at 12:27
  • 1) In the batches, do you have some commits at the end ? or do you commit frequently ? 2) Have you profiled the db operations of the batches, i.e. is it possible that some missing indexes slow down the work? 3) Ar the batches time critical, i.e. can they be spread over a longer period or even postponed over night ?
    – Christophe
    Jan 4, 2022 at 12:37
  • @Ewan Unfortunately it's not that easy. The data consists of millions of records to be stored, so I will meed a lot of RAM to handle that. Also the data comes from multiple calls to multiple rest apis that are usually paged. Jan 4, 2022 at 13:17
  • 1
    if its just that the shear number of inserts is overloading the DB, rate limit them
    – Ewan
    Jan 4, 2022 at 14:24
  • 1
    I think there is still some important information missing: do the REST updates potentially manipulate the records changed by the batches? So there is a risk to get collisions? Or are the tables filled by the batches intentionally "read-only" for the REST api during the batch run? I think you have to give us a little bit more details on how much these parts can be separated, or how much they are interwoven with each other.
    – Doc Brown
    Jan 4, 2022 at 15:15

2 Answers 2

1

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)
-1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.