Re: Pulling data from Postgres DB table for every 5 seconds.

[Rob Sargent <robjsargent@xxxxxxxxx>]

On Jan 9, 2019, at 10:02 AM, github kran <githubkran@xxxxxxxxx> wrote:

Hi Postgres Team,

I have an application using RDS Aurora Postgresql 9.6 version having 4 TB of DB size. In this DB we have a table PRODUCT_INFO with around  1 million rows and table size of 1 GB.

We are looking for a implementation where we want to pull the data in real time for every 5 seconds from the DB ( Table mentioned above) and send it to IOT topic whenever an event occurs for a product. ( event is any new product information or change in the existing 

product information.). 

This table has few DML operations in real time either INSERT or UPDATE based on the productId. ( Update whenever there is a change in the product information and INSERT when a record doesnt exists for that product).

We have REST API's built in the backend pulling data from this backend RDS Aurora POSTGRES DB and used by clients. 

UseCase

We dont want clients to pull the data for every 5 seconds from DB but rather provide a service which can fetch the data from DB in real time and push the data to IOT topic by pulling data for every 5 seconds from DB.

Questions

1) How can I get information by pulling from the DB every 5 seconds without impacting the performance of the DB.

2) What are the options I have pulling the data from this table every 5 seconds. Does POSTGRES has any other options apart from TRIGGER ?.

Any ideas would be helpful.

Thanks !!

GithubKran

There is DML event trapping.  You don’t poll every 5seconds you react immediately to each event (with trigger or event).  From the trigger perspective you probably have everything you need to update IOT with addition searching.