agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: Schema for caching message-count in folders using triggers
Date: Thu, 5 Mar 2015 21:04:59 +0100 (CET)
Message-ID: <VisenaEmail.a.dd389169d0fb6df0.14beb890fcb@tc7-visena> (raw)
In-Reply-To: <54F8B5A0.4020607@aklaver.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På torsdag 05. mars 2015 kl. 20:59:28, skrev Adrian Klaver <
adrian.klaver@aklaver.com <mailto: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 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.a.dd389169d0fb6df0.14beb890fcb@tc7-visena>
Permalink: ../VisenaEmail.a.dd389169d0fb6df0.14beb890fcb@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.a.dd389169d0fb6df0.14beb890fcb@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.a.dd389169d0fb6df0.14beb890fcb@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