Experience in using PGPOOL OR Separate reader and writer db instances with chatwoot

[nikparmar]

HIGHLIGHT

We have been using Aurora Postgres Cluster with only 1 Writer endpoint so far with our self-hosted Chatwoot server. We have so far reached the following milestone in last couple of months:

1. ~6L Conversations
2. Daily volume of 5k Conversations with 1 message each
3. 15 Agents using the system
4. API Based Inbox Type
5. Chatwoot Version - 3.4

Our Aurora PG Cluster Configuration:

1. ACU - 1.5(Min) - 6 (Max)
2. Postgres Version - 15.3

ISSUE

For the last couple of weeks, we started seeing degradation in the database performance when the volume was high for the conversations and agents were resolving tickets. We have been seeing the pattern as more agents log in the volume for read query explodes and chokes the I/O of the database and the overall performance of the database gets degraded including delay in message getting created, conversation getting resolved.
Typical API call for conversation and messages shoot from milliseconds to an average 30-40 seconds

Approaches Taken

1. Added a reader instance to our Aurora PG Cluster so that all the read queries are redirected towards the reader instance.
2. But as Chatwoot doesn't directly support setting up a RO instance, we had set up PGPOOL to solve this.
3. When tested with PGPOOL we couldn't get the best configuration to set with it because of the replication lag the API calls were randomly FAILING with the following error:

"404 - "{\"error\":\"Resource could not be found\"}"

We are looking if the community have faced similar issues with their self-hosted versions and what can they suggest for the database configuration if any.
Thanks,
Nikhil

[nikparmar]

Hi @sojan-official any thoughts on above?