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?
1
-
1PgQ because it seems reasonable and if you read it over in this wiki it seems to have some of the requirements you are in seek of for a potential solution. Another good read post on it that may help too Message Queuing Using Native PostgreSQL but it might have various flavors to implement per the GitHub.– IT Thug NinjaFeb 8 at 0:38
Add a comment
|
2 Answers
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.
