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

Craig James a écrit :
> 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:

The last two columns only store TRUE or FALSE, they're booleans. So
you're saying that an index on them might be useless ? We're retrieving
1000-2000 rows max and we need to extract only those who have TRUE on
the last column for example.

>
> 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).

They are 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.

I'll investigate that way.

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

Good idea, thanks again.