I'm using Postgres 9.6 in a project and one of the requirements is for the system to send an email to each new client.
1 - Trigger is a good way to do?
2 - When it comes to performance. What would be the impact on sending these emails per trigger?
3 - Or is this a job for a background service?
As the previous answer said, you should think about the performance issue. And responding directly to your question about "Or is this a job for a background service?", I would say it would be advisable to do so.
Think about the problems that could be caused if you entered 10 records, and suddenly 3 of these email sending processes hang. Or even think about a scenario, if you were using a gmail SMTP for example, and this request took about 15 seconds to be resolved (as has happened to me in some cases). This would most certainly affect the performance of your database.
I would think about another question: it is not my database responsibility to send an email. So, I would do this either through a command line or, as already suggested, using an application's backend to do this.
As I suggested in my comment, I might consider using a TRIGGER to trigger a command running in Background, depending on the link below:
In my humble opinion: I think this kind of thing should be done at the application layer.
Hello
I would not do this using the database, I would do this using the programming language. Most current languages support the execution of asynchronous tasks, which helps in case of delay in sending.
Here, this link talks about why it is not a good idea to send emails using the database: link
p>Sending email directly from the database may not be a good idea. And if the resolution of the DNS is slow and everything is broken in 30 seconds and expire? And if your email server is problems and it takes 5 minutes to accept messages? You will get database sessions suspended on your trigger until you are in max_connections, and suddenly you can not do anything except wait or start manually canceling transactions.
