Re: How would you store read/unread topic status?

Mathieu Nebra wrote:
> Greg Stark a écrit :
>> All the other comments are accurate, though it does seem like
>> something the database ought to be able to handle.
>>
>> The other thing which hasn't been mentioned is that you have a lot of
>> indexes. Updates require maintaining all those indexes. Are all of
>> these indexes really necessary? Do you have routine queries which look
>> up users based on their flags? Or all all your oltp transactions for
>> specific userids in which case you probably just need the index on
>> userid.
>
>
> We are using these indexes, but I can't be sure if we _really_ need them
> or not.
>
> I can go into detail. We have:
>
> UserID - TopicID - LastReadAnswerID - WrittenStatus - IsFavorite
>
> So basically, we toggle the boolean flag WrittenStatus when the user has
> written in that topic. The same goes for IsFavorite.

Do those last two columns hold much data? Another thing to consider is to split this into two tables:

UserID - TopicID - LastReadAnswerID

UserID - TopicID - WrittenStatus - IsFavorite

As others have pointed out, an UPDATE in Postgres is a select/delete/insert, and if you're updating just the LastReadAnswerID all the time, you're wasting time deleting and re-inserting a lot of data that never change (assuming they're not trivially small columns).

This might also solve the problem of too many indexes -- the table that's updated frequently would only have an index on (UserID, TopicID), so the update only affects one index.

Then to minimize the impact on your app, create a view that looks like the original table for read-only apps.

Craig