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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
