Date: Thu, 5 Mar 2015 18:16:01 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Schema for caching message-count in folders using triggers
Message-ID: <20150305211601.GW3291@alvh.no-ip.org>
References: <VisenaEmail.7.3433a658591afa2a.14beb71d3ac@tc7-visena>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <VisenaEmail.7.3433a658591afa2a.14beb71d3ac@tc7-visena>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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).