PostgreSQL connection leak: a new sqlstore pool is created on instance reconnect

[Talis-dev]

Description

Evolution Go continuously creates new idle PostgreSQL connections in evogo_auth. The connections are not reused or closed after WhatsApp instance reconnects.

Over time, PostgreSQL reached its max_connections limit of 200 and started rejecting connections.

Environment

• Evolution Go version: 0.7.2
• Docker image: evoapicloud/evolution-go:0.7.2
• Deployment: Docker
• Database: PostgreSQL
• POSTGRES_AUTH_DB: PostgreSQL database evogo_auth
• POSTGRES_USERS_DB: PostgreSQL database evogo_users
• Connected WhatsApp instances: 4
• Created but disconnected instances: 1

Observed behavior

Immediately after restarting the Evolution Go container, PostgreSQL showed approximately:

evogo_users: 1 idle connection
evogo_auth: 7 idle connections

About 18 minutes later, evogo_auth had 11 idle connections and continued growing:

datname     | state | connections
------------+-------+------------
evogo_auth  | idle  | 11
evogo_users | idle  | 1

Before the restart, the same behavior eventually consumed all 200 PostgreSQL connection slots.

The new connections originate from the Evolution Go container. New evogo_auth connections appear after instance disconnect/reconnect cycles and remain idle.

Temporary mitigation

I configured:

ALTER ROLE <evolution_db_user>
IN DATABASE evogo_auth
SET idle_session_timeout = '5min';

After restarting Evolution Go, PostgreSQL began terminating the old idle connections after approximately five minutes. This prevents unlimited accumulation, but it does not fix the underlying pool lifecycle.

Suspected cause

StartClient creates a new Whatsmeow SQL store container for every client start:

container, err = sqlstore.New(
    context.Background(),
    "postgres",
    w.config.PostgresAuthDB,
    dbLog,
)

Source:

https://github.com/evolution-foundation/evolution-go/blob/main/pkg/whatsmeow/service/whatsmeow.go

When a Disconnected event occurs, ReconnectClient removes the client from the runtime maps and starts it again. However, the previously created sqlstore.Container is not retained by the service and is not closed.

Therefore, every reconnection may create another PostgreSQL pool while the previous pool remains open.

Expected behavior

• PostgreSQL connections should be reused.
• Reconnecting an instance should not permanently increase the number of idle database connections.
• The number of PostgreSQL connections should be bounded by a shared pool, not by the number of reconnect attempts.
• Hundreds or thousands of WhatsApp instances should not require one independent PostgreSQL pool per instance.

Suggested solution

Create one shared PostgreSQL sql.DB and one shared sqlstore.Container during application startup, using the Whatsmeow NewWithDB API.

Inject the shared container into whatsmeowService.

StartClient should only call:

container.GetDevice(...)

or:

container.NewDevice()

It should not call sqlstore.New() for every instance start.

The shared pool should be closed only during application shutdown.

If separate containers must remain, Evolution Go should retain and explicitly close each container before replacing or deleting a client.

Additional note

The v0.7.2 changelog mentions that the upstream Whatsmeow PostgreSQL pool patch was replaced by the application-side NewWithDB approach. However, StartClient still appears to use sqlstore.New() for each instance.

[Clalber]

Production confirmation from an Evolution Go 0.7.2 Docker deployment:

• Four WhatsApp instances are configured.
• After the service had been running for a few hours, PostgreSQL showed 24
  idle connections in the authentication database, all from the Evolution Go
  application role/client.
• There were no idle in transaction sessions.
• The oldest and newest idle backends had different creation times, matching
  the reconnect path opening additional pools.
• The deployment previously exhausted PostgreSQL connections while sharing a
  database server with other applications. Isolating its PostgreSQL instance
  contained the blast radius, but did not address the pool lifecycle.

Reviewing 0.7.2 confirms the same path described here: StartClient calls
sqlstore.New, which creates another database/sql pool with default
unbounded limits.

We are now testing the shared, capped container implementation from PR #117
against 0.7.2. We will report the post-deployment connection count and
reconnect behavior here once the production validation window is complete.

Thanks for documenting and fixing this.

[Clalber]

Initial production validation of the PR #117-based build is successful:

• Swarm completed the monitored update without rollback.
• All four configured instances reconnected successfully.
• Before the update, the auth database had 24 idle connections.
• Immediately after the update it had 2 idle connections; subsequent samples
  over the initial validation window showed 0 auth-idle connections and 6
  total server connections.
• The application health endpoint remained healthy and PostgreSQL reported no
  idle in transaction sessions.
• A host monitor now also alerts on both an absolute connection threshold and
  rapid connection growth.

This confirms the shared capped container fixes the accumulating-pool behavior
in our 0.7.2 deployment. We will keep monitoring the count across natural
reconnects; the long-running observation is still in progress.