We have this table. It contains data which will be picked up by an hourly batch job for processing. Five times a day, there are also multiple batch ETL jobs which get data from other source table and transfers it to this table.

Originally, that was it's only function. Now we were told that there will be a new page in our web application that would read data from this table. My worry is that due to the number of batch job read and writes to the table, would it still be feasible to read data from it and show it in our web page? Our web app has about 30,000 users that would be able to access that new page and it is estimated they most users use the web page everyday and they would want to view the contents as quickly as possible (there's an SLA). Due to the number of read and writes to the table from the batch jobs, achieving the desired page response time in the SLA might not be possible

We are thinking twice of creating a read replicate since the hourly batch job that reads from this table updates statuses of records that are processed. There would also be additional synch jobs needed if we would use a read replica.

Where do we draw the line between saying "OK we need a read replica for this" and "No problem we still can have one table to do all that" ? What kind of data do we need to determine this? or is there no choice but to do load testing using production data?

Would appreciate any help. Thanks

EDIT: Additional information

Total no of records now in table is 2.3 million. Total number of records added by all the batch jobs: ave of 5000 per day

  • Are the queries limit to a few types or are they all ad hoc queries? – Michael Kutz Dec 13 '19 at 14:51
  • How much of your workload can be handled by your web infrastructure - especially caching at different levels? How big is that table in the first place? How much time do (common) queries usually take? How many of the 30,000 users will visit per day? How many queries will each visitor issue? How are the queries distributed over the course of a day/week/…? Where is what percentage of the time spent from when the user submits the query until the response is returned? Can you upgrade your (database) server (hardware)? - But in the end: If you want to play safe, you will need to test near real life… – Abecee Dec 14 '19 at 21:55
  • @MichaelKutz queries from the web page are limited to only data connected to the particular user. batch job queries involve data from all users – lecarpetron dookmarion Dec 16 '19 at 1:41
  • @Abecee: Total no of records now in table is 2.3 million. Total number of records added by batch jobs: ave of 5000 per day – lecarpetron dookmarion Dec 16 '19 at 1:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.