Schema for caching message-count in folders using triggers - Mailing list pgsql-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 FUNCTION count_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_t AFTER 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 PROCEDURE count_decrement_tf();
CREATE TRIGGER update_folder_msg_t AFTER 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