agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

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
Date: Thu, 5 Mar 2015 18:16:01 -0300
Message-ID: <20150305211601.GW3291@alvh.no-ip.org> (raw)
In-Reply-To: <VisenaEmail.7.3433a658591afa2a.14beb71d3ac@tc7-visena>
References: <VisenaEmail.7.3433a658591afa2a.14beb71d3ac@tc7-visena>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (6+ messages)  latest in thread

Message-ID: <20150305211601.GW3291@alvh.no-ip.org>
Permalink:  ../20150305211601.GW3291@alvh.no-ip.org/
Also on:    postgresql.org/message-id/20150305211601.GW3291@alvh.no-ip.org

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: alvherre@2ndquadrant.com, andreas@visena.com
  Subject: Re: Schema for caching message-count in folders using triggers
  In-Reply-To: <20150305211601.GW3291@alvh.no-ip.org>

* 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