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

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.

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

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.