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 <robjsarg...@gmail.com> wrote: > > > On Jan 9, 2019, at 10:02 AM, github kran <githubk...@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 >> > > 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. >
