During the initial analysis, I found the following queries which may cause performance issues:

1. Query to find record by origin stanza id It takes ~70ms and is executed on every "outgoing" stanza received (ie. from MAM or from Carbons)
2. Query to find items that are unread before the date Takes ~40ms and is executed for each "outgoing" stanza received (ie. from MAM or from Carbons)
3. Query to mark all messages before the date as read Takes ~20ms and is executed after SQL no. 2 if it returns more than 0 rows.
4. Query to count unread messages Takes 74ms and is executed when each chat is opened to be displayed.

After modifications to the schema and small changes to queries, I've got the following performance gains:

1. From 70ms to 1ms after adding 2 new indexes and enforcing the database to use it.
2. From 40ms (20-80ms) to 20ms (20-1ms) after enforcing usage of the specific index which already exists.
3. From 20ms to 1ms after enforcing usage of the index.
4. From 74ms to 1ms after enforcing usage of the index.

That would suggest that inserting "outgoing" stanzas will be around 100ms faster, which should greatly improve the performance of message synchronization. Assuming that at least 1 on 4 messages which we sync were sent by us, for 1000 messages we would reduce inserting messages by 25 s (25000ms). That with large no. of items to be synced (like in SiskinIM) will greatly speed up message synchronization on application startup.