Skip site navigation (1) Skip section navigation (2)

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

From: Craig James <craig_james(at)emolecules(dot)com>
To: Mathieu Nebra <mateo21(at)siteduzero(dot)com>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Scott Carey <scott(at)richrelevance(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: How would you store read/unread topic status?
Date: 2009-06-23 21:29:04
Message-ID: 4A414920.6030705@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-performance
Mathieu Nebra wrote:
> Greg Stark a écrit :
>> All the other comments are accurate, though it does seem like
>> something the database ought to be able to handle.
>>
>> The other thing which hasn't been mentioned is that you have a lot of
>> indexes. Updates require maintaining all those indexes. Are all of
>> these indexes really necessary? Do you have routine queries which look
>> up users based on their flags? Or all all your oltp transactions for
>> specific userids in which case you probably just need the index on
>> userid.
> 
> 
> We are using these indexes, but I can't be sure if we _really_ need them
> or not.
> 
> I can go into detail. We have:
> 
> UserID - TopicID - LastReadAnswerID - WrittenStatus - IsFavorite
> 
> So basically, we toggle the boolean flag WrittenStatus when the user has
> written in that topic. The same goes for IsFavorite.

Do those last two columns hold much data?  Another thing to consider is to split this into two tables:

  UserID - TopicID - LastReadAnswerID 

  UserID - TopicID - WrittenStatus - IsFavorite

As others have pointed out, an UPDATE in Postgres is a select/delete/insert, and if you're updating just the LastReadAnswerID all the time, you're wasting time deleting and re-inserting a lot of data that never change (assuming they're not trivially small columns).

This might also solve the problem of too many indexes -- the table that's updated frequently would only have an index on (UserID, TopicID), so the update only affects one index.

Then to minimize the impact on your app, create a view that looks like the original table for read-only apps.

Craig

In response to

Responses

pgsql-performance by date

Next:From: Alan McKayDate: 2009-06-23 23:21:38
Subject: SOLVED: processor running queue - general rule of thumb?
Previous:From: Alan McKayDate: 2009-06-23 20:41:53
Subject: Re: processor running queue - general rule of thumb?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group