1

I'm working on a Postgresql database with a lot of data.

I created R Shiny application which is linked to this database, but the number of records is too large for this app - it works slowly.

I would like to create new table which will aggregate data from many tables into 2 or 3 aggregated so called "data marts". I prepared 'SELECT query' which aggregate data, and I know how to run it manually by 'INSERT query' into new one - but that is not the point and a solution.

Is it possible to set it once and it will be updating by itself?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
brtk
  • 107
  • 7
  • 3
    hey, you might wanna take a look at this : https://www.postgresql.org/docs/current/rules-materializedviews.html – TheWildHealer Apr 30 '19 at 09:51
  • Thank You! It seems to be perfect. I will try this option. – brtk Apr 30 '19 at 10:40
  • btw this might interest you https://stackoverflow.com/questions/47012961/refresh-materialized-view-periodically-postgres – TheWildHealer Apr 30 '19 at 10:52
  • ehh... In Postgresql 9.5 and pgAdming there is no options to set automatically update (as Oracle can). Cron-job works with websites and does not link directly with database? – brtk Apr 30 '19 at 12:09
  • I don't know, never used what I linked x) it just "had the smell of what you need", so I shared, but if I knew what exactly to do it would be in an answer – TheWildHealer Apr 30 '19 at 12:25
  • I also found this previous question which talks about refreshing Postgresql materialized views -- basically you need to use the `REFRESH` command, and it talks about using cron or database triggers to do that. https://stackoverflow.com/questions/29437650/how-can-i-ensure-that-a-materialized-view-is-always-up-to-date – saritonin Apr 30 '19 at 21:25
  • Possible duplicate of [How can I ensure that a materialized view is always up to date?](https://stackoverflow.com/questions/29437650/how-can-i-ensure-that-a-materialized-view-is-always-up-to-date) – saritonin Apr 30 '19 at 21:26

0 Answers0