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

Mathieu Nebra wrote:

Alexander Staubo a écrit : 

On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra<mateo21@siteduzero.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.
 

I use pg_send_query() <http://ca2.php.net/manual/en/function.pg-send-query.php> in php to achieve this for a views counter. "Script execution is not blocked while the queries are executing."

It looks like this may just be a direct translation of PQsendQuery() from libpq. Your preferred language may have a function like this.