Re: Pulling data from Postgres DB table for every 5 seconds. - Mailing list pgsql-general

From Tony Shelver
Subject Re: Pulling data from Postgres DB table for every 5 seconds.
Date
Msg-id CAG0dhZAzEhuUbQW9tdCJaucqAPdhSEBCD7FhrqmOXvqiV__6OA@mail.gmail.com
Whole thread Raw
In response to Re: Pulling data from Postgres DB table for every 5 seconds.  (github kran <githubkran@gmail.com>)
List pgsql-general
I am not familiar with Aurora, but..


Set up a message queue in Postgres, which calls into AMPQ (RabbitMQ) to send a message for consumption by one or more clients.

This provides a function that can be called from a trigger to send the message.

After that, you have all the goodness of a standards-based open source MQ platform to distribute your data / notifications.



On Wed, 9 Jan 2019 at 19:02, github kran <githubkran@gmail.com> 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

pgsql-general by date:

Previous
From: Kim Rose Carlsen
Date:
Subject: Postgres wont remove useless joins, when the UNIQUE index is partial
Next
From: Dave Cramer
Date:
Subject: Re: jdbc PGCopyOutputStream close() v. endCopy()