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

You're holding this behavior to far too strict of a transactional guarantee.

The client software can cache a set of recent views, and sent updates in
bulk every 1 or 2 seconds. Worst case, if your client crashes you lose a
second worth of user metadata updates on last accessed and view counts.
This isn't a financial transaction, don't build the app like one.

The same facility can serve as a read cache for other bits that don't need
to be 'perfect' in the transactional sense -- counts on the number of views
/ posts of a topic, etc. Using the db to store and retrieve such counts
synchronously is frankly, a bad application design.

The tricky part with the above is two fold: you need to have client
software capable of a thread-safe shared cache, and the clients will have to
have sticky-session if you are load balancing. Corner cases such as a
server going down and a user switching servers will need to be worked out.

On 6/23/09 4:12 AM, "Mathieu Nebra" <mateo21(at)siteduzero(dot)com> wrote:

> Hi all,
>
> I'm running a quite large website which has its own forums. They are
> currently heavily used and I'm getting performance issues. Most of them
> are due to repeated UPDATE queries on a "flags" table.
>
> This "flags" table has more or less the following fields:
>
> UserID - TopicID - LastReadAnswerID
>
> The flags table keeps track of every topic a member has visited and
> remembers the last answer which was posted at this moment. It allows the
> user to come back a few days after and immediately jump to the last
> answer he has not read.
>
> 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.
>
> Question: what is the general rule of thumb here? How would you store
> this information?
>
> Thanks a lot in advance.
> Mathieu.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>