After analysis of this query performance it looks like it works fast if I/O is not restricted (ie. due to limits on a hardware side). All queries are done using indexes so there is nothing to optimize.

We could record jids that were used in messages that were removed and check just them, but I do not think this will have a major impact on the installation.

Is there any hint about performance in explain delete?

I've checked those deletes and it iterates over all jids as it has to, but it does a very fast check against tig_ma_msgs as it uses indexes to verify if particular row from tig_ma_jids is used.

So basically again a case to de-normalize the schema?

Yes, we could denormalize schema and make one big table and that should work well, however that would created JID duplication, but I'm not sure we need to worry about that.

I'd say duplication would be less of a tradeoff in this case than dealing with full-row scans. AFAIR normalization is not always the best solution.

I was restarting our test server instance and I noticed that there seems to be an error in the schema:

xmpp-1  |   		         Loading schema from file(s): postgresql-message-archiving-3.3.0.sql, URI: jdbc:postgresql://db/tigasedb?user=tigase_user&password=***********
xmpp-1  |   		         Failed to execute query: do $$ begin if exists (select 1 where (select to_regclass('public.tig_ma_msgs_ts_index')) is not null) then drop index tig_ma_msgs_ts_index on tig_ma_msgs; end if; end$$
xmpp-1  |
xmpp-1  |
xmpp-1  |
xmpp-1  |   		         =====
xmpp-1  |   		         Failure: ERROR: syntax error at or near "on"
xmpp-1  |   		           Position: 141
xmpp-1  |   		         =====
xmpp-1  |
xmpp-1  |
xmpp-1  |   		         Failure: ERROR: syntax error at or near "on"
xmpp-1  |   		           Position: 141

I've changed SP and checked using newest container image and all worked without any errors.