Re: Schema for caching message-count in folders using triggers

Andreas Joseph Krogh wrote:
> Hi all.   I'm facing a problem with my current schema for email where folders 
> start containing several 100K of messages and count(*) in them taks noticeable 
> time. This schema is accessible from IMAP and a web-app so lots of queries of 
> the type "list folders with message count" are performed.   So, I'm toying with 
> this idea of caching the message-count in the folder-table itself.

You can do this better by keeping a table with per-folder counts and
deltas.  There is one main row which keeps the total value at some point
in time.  Each time you insert a message, add a "delta" entry with value
1; each time you remove, add a delta with value -1.  You can do this
with a trigger on insert/update/delete.  This way, there is no
contention because there are no updates.

To figure out the total value, just add all the values (the main plus
all deltas for that folder).

From time to time you have a process that summarizes all these entries
into one total value again.  Something like
             WITH deleted AS (DELETE                                 FROM counts                               WHERE
type= 'delta' RETURNING value),                     total AS (SELECT coalesce(sum(value), 0) as sum
           FROM deleted)                  UPDATE counts                    SET value = counts.value + total.sum
          FROM total WHERE type = 'total'               RETURNING counts.value
 

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services