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

Thanks for your reply Rob. Reading the below documentation link says the
EVENT trigger is only supported for DDL commands. Is it not correct ?.

*1) https://www.postgresql.org/docs/9.6/event-trigger-definition.html
<https://www.postgresql.org/docs/9.6/event-trigger-definition.html> *
(An event trigger fires whenever the event with which it is associated
occurs in the database in which it is defined. Currently, the only
supported events are ddl_command_start, ddl_command_end, table_rewrite and
sql_drop. Support for additional events may be added in future releases.).
2) Doesnt the trigger slow down inserts/update we are doing to the table ?.
Does it slow down if we are reading the data using the API when we have a
trigger in place ?.

Ron- Its a tiny subset of 1 GB Data for every 5 seconds but not on the
entire data.

Thanks !!.

On Wed, Jan 9, 2019 at 11:10 AM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> On Jan 9, 2019, at 10:02 AM, github kran <githubkran(at)gmail(dot)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
>>
>
> 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.
>