Re: Schema for caching message-count in folders using triggers - Mailing list pgsql-sql
På torsdag 05. mars 2015 kl. 22:16:01, skrev Alvaro Herrera <alvherre@2ndquadrant.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