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

På torsdag 05. mars 2015 kl. 22:16:01, skrev Alvaro Herrera <
alvherre(at)2ndquadrant(dot)com <mailto:alvherre(at)2ndquadrant(dot)com>>: 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   Like it, thanks!   -- Andreas Joseph
Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>