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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com