(This is a brief overview – I did some measurements at G but never got round to ask permission to publish the data)
Jaiku uses the Symbian databases heavily. All of the posts you see in the overview, by-author and thread views; their idempotence; unread status etc. are managed in one table. This was decided upon based on two factors: we had good experience of read performance on DBMS (in fact it’s excellent, when you scroll the posts in Jaiku the data is read row-by-row from the db) and we were still designing for low-memory phones (can’t hold the data in memory).
However, if you receive a lot of posts on an older phone the interaction sucks. The app pauses randomly for several hundred milliseconds every now and then. (It’s not as bad on more recent phones).
Turns out the major variable in how fast _writes_ to the database are is the number of indices on the table. Jaiku has 7 indices on the main table and 2 on an auxiliary table.
I guess the right way to do this would be to split the indices to separate tables that are updated incrementally at a low priority and making the post visible once all the auxiliary indices have been created. (We already have tuned the transaction size and Compress() interval to get the best interactive performance).