Is using an Audit Table in Postgres to create triggers for NOTIFY/LISTEN a good idea?
Via NOTIFY/LISTEN, the PRO-s:
Light communications with the server, no need to pull for the data changes.
Via NOTIFY/LISTEN, the CON-s:
The practice shows that it is not sufficient just to set it up and listen for the events, because every so often the channel goes down, due to various communication problems. For a serious system you would need to put in place an additional monitoring service that can verify that your listeners are still operating, and if not - destroy the existing ones and create new ones. This can be tricky, and you probably won't find a good example of doing it.
Via scheduled data pulls, PRO-s:
- Simplicity - you just check for the data change according to the schedule;
- Reliability - there is nothing to break there, once the pull implementation is working.
Via scheduled data pulls, CON-s:
Additional traffic for the server, depending on how quickly you need to see the data change, and how would that interfere (if at all) with other requests to the server.