agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: Schema for caching message-count in folders using triggers
Date: Thu, 5 Mar 2015 22:20:57 +0100 (CET)
Message-ID: <VisenaEmail.8.eb822b698d9ef1c3.14bebcfc508@tc7-visena> (raw)
In-Reply-To: <20150305211601.GW3291@alvh.no-ip.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På torsdag 05. mars 2015 kl. 22:16:01, skrev Alvaro Herrera <
alvherre@2ndquadrant.com <mailto: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 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
view thread (6+ messages)
Message-ID: <VisenaEmail.8.eb822b698d9ef1c3.14bebcfc508@tc7-visena>
Permalink: ../VisenaEmail.8.eb822b698d9ef1c3.14bebcfc508@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.8.eb822b698d9ef1c3.14bebcfc508@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: Schema for caching message-count in folders using triggers
In-Reply-To: <VisenaEmail.8.eb822b698d9ef1c3.14bebcfc508@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox