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(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>