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

From: Chris St Denis <lists(at)on-track(dot)ca>
To: Mathieu Nebra <mateo21(at)siteduzero(dot)com>
Cc: Alexander Staubo <alex(at)bengler(dot)no>, pgsql-performance(at)postgresql(dot)org
Subject: Re: How would you store read/unread topic status?
Date: 2009-06-24 07:16:38
Message-ID: 4A41D2D6.8010100@on-track.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Mathieu Nebra wrote:
> 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.
>
>
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.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Chris St Denis 2009-06-24 07:20:18 tsvector_update_trigger performance?
Previous Message Greg Stark 2009-06-23 23:48:24 Re: How would you store read/unread topic status?