I am trying to implement CQRS fully inside a PostgreSQL v15.1 database as it is all what I have for the moment. The implementation will be via asynchronous messages (i.e. events) that needs to be queued somehow for projection processors inside the database. The transaction that produced the event should be separate from the transaction that will consume it asynchronously. This should be done without an application server or a dedicated messaging platform. It is OK to utilize existing and well-maintained PG extensions for that matter. What do you suggest addressing my question and why?
Why do you consider application servers off?
Nevermind, as you already said you can use PG extensions.
- you can use "pg_notify" to send a message to a background worker whenever an event is generated by a transaction. The background worker can then perform the necessary operations to update the read model, if this is what you mean.
However, pg_notify has its limitations:
- Payload size is limited to 8192
- The number of listeners is limited to 32
If you plan to run it completely inside Postgres, there are at least a couple of options you can use:
- PgQ as was mentioned above - an out of the box solution
- Manual implementation, i.e. a generic queue processor, you can implement it based on a custom table that stores a queue and a routine called using pg_cron for example, i.e. periodical calls triggered by pg_cron that can act either as triggers to run a processor, or you can have a processor called in a long loop and check if that loop is still active using pg_cron and restart if necessary. Basically, it can be either a short polling solution or a long polling solution.