mod_push SQL query optimization for PostgreSQL

[logicwonder]

Environment

• ejabberd version: 22.05
• Erlang version: 13.1.4
• OS: Linux (Debian)
• Installed from: source

Optimization

The mod_push_sql.erl has function delete_session/3 that deletes the current push token from active session as well as the SQL table push_session. If a new push token is published, store_session is used to upsert the record.

Frequent delete from push_session SQL table creates dead tuples in PostgreSQL table along with corresponding index changes. This implementation requires frequent vacuum and reindexing for maintaining table performance. Also deleting and then inserting the push session consists of two operations which doubles the DB operation cost. Can the delete from push session table be completely avoided so that UPSERT can take care of updating the latest push session. This would reduce the number of DB operations to one.

Also implementing Redis backend support for mod_push could completely eliminate this performance issue with SQL backend.

The above is my analysis after analyzing a performance issue with push_session table.