I have a Postgres database running a small website. Most of the tables in the database are calculated values derived from various input data. This input data is updated approximately monthly. The tables in question are not otherwise altered.

What I need to do each month is:

  1. acquire the new input data (mostly downloading from a few sources)
  2. use the new data to compute new calculated values for the db tables. Only a smallish fraction (< 5%) of the records will actually change with each update
  3. replace the existing tables in my production db with the newly-calculated tables

Although it would be convenient, I don't feel it is prudent to do step 2 on the actual production server (the calculations are a bit processor heavy, and I don't want to bog it down). That seems to leave me with carrying out steps 1 & 2 elsewhere and then somehow transferring the new data to the production server. Right now that "elsewhere" is simply on my local machine that has its own Postgres db, but it might end up being something like an EC2 instance.

Is there a smart way to carry out step 3 without resorting to full data dumps? At this point my db is <2GB and a data dump is feasible, but the data calculated may expand significantly, and transferring only the changed records will likely become more important.

I'm hoping there is a generally-accepted answer to this question, although I can see that it has the potential for subjectivity and, thus, might not be the ideal fit for this forum. I'm sorry if that's the case - I'm hoping to learn from the folks here and am not sure where else to ask.

  • 1
    Well if there's only 5% new data every month then in 20 years you're going to have around 5.5 GB. So maybe you don't have such a big problem as you might expect? – Jacob H Apr 24 at 15:34
  • Yes, the changes to existing calculations might not be too problematic, but we are hoping to add to the data that we calculate - this is where the size could get quite a lot larger. – susie derkins Apr 24 at 15:46

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.