Re: Schema for caching message-count in folders using triggers - Mailing list pgsql-sql

From Andreas Joseph Krogh
Subject Re: Schema for caching message-count in folders using triggers
Date
Msg-id VisenaEmail.a.dd389169d0fb6df0.14beb890fcb@tc7-visena
Whole thread Raw
In response to Re: Schema for caching message-count in folders using triggers  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Schema for caching message-count in folders using triggers
List pgsql-sql
På torsdag 05. mars 2015 kl. 20:59:28, skrev Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/05/2015 11:45 AM, 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.
> I currently have this:
>
> CREATE or replace FUNCTIONcount_increment_tf()RETURNS TRIGGER AS$_$
> BEGIN
> UPDATE folder SETmessage_count  =message_count  + 1 WHEREid  =NEW.folder_id;
> RETURNNEW;
> END $_$LANGUAGE'plpgsql';
>
> CREATE or replace FUNCTIONcount_decrement_tf()RETURNS TRIGGER AS$_$
> BEGIN
>      UPDATE folder SETmessage_count  =message_count  - 1 WHEREid  =OLD.folder_id;
> RETURNOLD;
> END $_$LANGUAGE'plpgsql';
>
> CREATE or replace FUNCTIONcount_update_tf()RETURNS TRIGGER AS$_$
> BEGIN
>      UPDATE folder SETmessage_count  =message_count  - 1 WHEREid  =OLD.folder_id;
>      UPDATE folder SETmessage_count  =message_count  + 1 WHEREid  =NEW.folder_id;
> RETURNNEW;
> END $_$LANGUAGE'plpgsql';
>
> CREATE TRIGGERincrement_folder_msg_tAFTER INSERT ON message FOR EACH ROW EXECUTE PROCEDUREcount_increment_tf();
> CREATE TRIGGERdecrement_folder_msg_tAFTER DELETE ON message FOR EACH ROW EXECUTE PROCEDUREcount_decrement_tf();
> CREATE TRIGGERupdate_folder_msg_tAFTER UPDATE ON message FOR EACH ROW EXECUTE PROCEDUREcount_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?

How accurate does this have to be?

Not exactly following what is folder?
Is it a table that contains the messages?

A top of the head idea would be to use sequences. Create a sequence for
each folder starting at current count and then use nextval, setval to
change the value:

http://www.postgresql.org/docs/9.4/interactive/functions-sequence.html

It is not transactional, so it would probably not be spot on, which is
why I asked about accuracy earlier.
 
Yes, 'folder' is a table which contains 'message':
 
create table folder(    id serial PRIMARY KEY,    name varchar not null unique,    message_count integer not null default 0
);

create table message(    id serial PRIMARY KEY,    folder_id INTEGER NOT NULL REFERENCES folder(id),    message varchar not null
);
 
The count has to be exact, no estimate from EXPLAIN or such...
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-sql by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Schema for caching message-count in folders using triggers
Next
From: Adrian Klaver
Date:
Subject: Re: Schema for caching message-count in folders using triggers