agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Schema for caching message-count in folders using triggers
Date: Thu, 5 Mar 2015 20:45:32 +0100 (CET)
Message-ID: <VisenaEmail.7.3433a658591afa2a.14beb71d3ac@tc7-visena> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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. I
currently have this: CREATE or replace FUNCTION count_increment_tf() RETURNS
TRIGGER AS$_$ BEGIN UPDATE folder SET message_count = message_count + 1 WHERE id
=NEW.folder_id; RETURN NEW; END $_$ LANGUAGE 'plpgsql'; CREATE or replace
FUNCTIONcount_decrement_tf() RETURNS TRIGGER AS $_$ BEGIN UPDATE folder SET
message_count = message_count - 1 WHERE id = OLD.folder_id; RETURN OLD; END $_$
LANGUAGE'plpgsql'; CREATE or replace FUNCTION count_update_tf() RETURNS TRIGGER
AS$_$ BEGIN UPDATE folder SET message_count = message_count - 1 WHERE id = OLD.
folder_id; UPDATE folder SET message_count = message_count + 1 WHERE id = NEW.
folder_id; RETURN NEW; END $_$ LANGUAGE 'plpgsql'; CREATE TRIGGER
increment_folder_msg_tAFTER INSERT ON message FOR EACH ROW EXECUTE PROCEDURE
count_increment_tf(); CREATE TRIGGER decrement_folder_msg_t AFTER DELETE ON
message FOR EACH ROW EXECUTE PROCEDUREcount_decrement_tf(); CREATE TRIGGER
update_folder_msg_tAFTER UPDATE ON message FOR EACH ROW EXECUTE PROCEDURE
count_update_tf(); The problem with this is locking (waiting for another TX
to commit when updating the same folder) and deadlock issues when trying to
simultaneously insert/delete/update messages in a folder. Does anyone have
any better ideas for safely caching the message-count in each folder without
locking and deadlock issues? 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) latest in thread
Message-ID: <VisenaEmail.7.3433a658591afa2a.14beb71d3ac@tc7-visena>
Permalink: ../VisenaEmail.7.3433a658591afa2a.14beb71d3ac@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.7.3433a658591afa2a.14beb71d3ac@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.7.3433a658591afa2a.14beb71d3ac@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