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

Alexander Staubo a écrit :
> On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra<mateo21(at)siteduzero(dot)com> wrote:
>> This "flags" table has more or less the following fields:
>>
>> UserID - TopicID - LastReadAnswerID
>
> We are doing pretty much same thing.
>
>> My problem is that everytime a user READS a topic, it UPDATES this flags
>> table to remember he has read it. This leads to multiple updates at the
>> same time on the same table, and an update can take a few seconds. This
>> is not acceptable for my users.
>
> First of all, and I'm sure you thought of this, an update isn't needed
> every time a user reads a topic; only when there are new answers that
> need to be marked as read. So an "update ... where last_read_answer_id
> < ?" should avoid the need for an update.

We don't work that way. We just "remember" he has read these answers and
then we can tell him "there are no new messages for you to read".
So we just need to write what he has read when he reads it.

>
> (That said, I believe PostgreSQL diffs tuple updates, so in practice
> PostgreSQL might not be writing anything if you run an "update" with
> the same value. I will let someone more intimate with the internal
> details of updates to comment on this.)
>
> Secondly, an update should not take "a few seconds". You might want to
> investigate this part before you turn to further optimizations.

Yes, I know there is a problem but I don't know if I am competent enough
to tune PostgreSQL for that. It can take a while to understand the
problem, and I'm not sure I'll have the time for that.

I am, however, opened to suggestions. Maybe I'm doing something wrong
somewhere.

>
> In our application we defer the updates to a separate asynchronous
> process using a simple queue mechanism, but in our case, we found that
> the updates are fast enough (in the order of a few milliseconds) not
> to warrant batching them into single transactions.

A few milliseconds would be cool.
In fact, defering to another process is a good idea, but I'm not sure if
it is easy to implement. It would be great to have some sort of UPDATE
... LOW PRIORITY to make the request non blocking.

Thanks.