Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YTdCg-0007td-7l for pgsql-sql@arkaria.postgresql.org; Thu, 05 Mar 2015 21:21:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YTdCf-0003Q2-K1 for pgsql-sql@arkaria.postgresql.org; Thu, 05 Mar 2015 21:21:09 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YTdCe-0003Pr-6F for pgsql-sql@postgresql.org; Thu, 05 Mar 2015 21:21:08 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YTdCa-0006Xb-9u for pgsql-sql@postgresql.org; Thu, 05 Mar 2015 21:21:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=fc92pQxSNK9JnTxMauoZnYjoI1Unev+ndYtcSWbtN1I=; b=sYMH4U3Khtg6Cm9wEitEaRlMcc9D3sNxqAoeqFM/DqIzMLLNmd8LjLZj0UFNRQW+GKAdEvAJfbWMCL0Nvb5rt9Hvs0BukkxktuFSbHIU1s7AT7OdOxHPpkUcEpgwlsZfwilmYPf+UMDNJ1mT45h+MO3h6QleivZbMbsdcAoSC9c=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1YTdCS-0002tn-LE for pgsql-sql@postgresql.org; Thu, 05 Mar 2015 22:20:58 +0100 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1YTdCT-0007F2-72 for pgsql-sql@postgresql.org; Thu, 05 Mar 2015 22:20:57 +0100 Date: Thu, 5 Mar 2015 22:20:57 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: <20150305211601.GW3291@alvh.no-ip.org> Subject: Re: Schema for caching message-count in folders using triggers MIME-Version: 1.0 X-Mailer: Visena Mail 1.9.0-SNAPSHOT X-Spam-Score: -0.3 X-Spam-Report: SpamAssasin (score=-0.3, required 5.0 ALL_TRUSTED=-1, HTML_IMAGE_ONLY_28=0.726, HTML_MESSAGE=0.001) X-Pg-Spam-Score: -0.6 (/) Content-Type: multipart/related; boundary="----=_Part_16_684137583.1425590457043" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_15_543804474.1425590457043 Content-Type: multipart/related; boundary="----=_Part_16_684137583.1425590457043" ------=_Part_16_684137583.1425590457043 Content-Type: multipart/alternative; boundary="----=_Part_17_1772816629.1425590457073" ------=_Part_17_1772816629.1425590457073 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 torsdag 05. mars 2015 kl. 22:16:01, skrev Alvaro Herrera < alvherre@2ndquadrant.com >: Andreas Joseph= =20 Krogh wrote: > Hi all. =C2=A0 I'm facing a problem with my current schema for email whe= re=20 folders > start containing several 100K of messages and count(*) in them taks=20 noticeable > time. This schema is accessible from IMAP and a web-app so lots of queri= es=20 of > the type "list folders with message count" are performed. =C2=A0 So, I'm= toying=20 with > this idea of caching the message-count in the folder-table itself. You can do this better by keeping a table with per-folder counts and deltas.=C2=A0 There is one main row which keeps the total value at some po= int in time.=C2=A0 Each time you insert a message, add a "delta" entry with va= lue 1; each time you remove, add a delta with value -1.=C2=A0 You can do this with a trigger on insert/update/delete.=C2=A0 This way, there is no contention because there are no updates. To figure out the total value, just add all the values (the main plus all deltas for that folder). From time to time you have a process that summarizes all these entries into one total value again.=C2=A0 Something like =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WITH deleted AS (DELETE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM counts =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE type =3D 'delta' RETURNING val= ue), =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0total AS (SELECT coalesce(sum(value), 0) as sum =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM deleted) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 UPDATE coun= ts =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0SET value =3D counts.value + total.sum =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM= total WHERE type =3D 'total' =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURNING counts.va= lue =C2=A0 Like it, thanks! =C2=A0 -- Andreas Joseph=20 Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com= =20 www.visena.com =20 =C2=A0 ------=_Part_17_1772816629.1425590457073 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 torsdag 05. mars 2015 kl. 22:16:01, skrev Alvaro Herrera <<= a href=3D"mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com>= :
And= reas Joseph Krogh wrote:
> Hi all. =C2=A0 I'm facing a problem with my current schema for email w= here folders
> start containing several 100K of messages and count(*) in them taks no= ticeable
> time. This schema is accessible from IMAP and a web-app so lots of que= ries of
> the type "list folders with message count" are performed. = =C2=A0 So, I'm toying with
> this idea of caching the message-count in the folder-table itself.

You can do this better by keeping a table with per-folder counts and
deltas.=C2=A0 There is one main row which keeps the total value at some poi= nt
in time.=C2=A0 Each time you insert a message, add a "delta" entr= y with value
1; each time you remove, add a delta with value -1.=C2=A0 You can do this with a trigger on insert/update/delete.=C2=A0 This way, there is no
contention because there are no updates.

To figure out the total value, just add all the values (the main plus
all deltas for that folder).

From time to time you have a process that summarizes all these entries
into one total value again.=C2=A0 Something like

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WITH deleted AS (DELETE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM counts
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE type =3D 'delta' RETURNING val= ue),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0total AS (SELECT coalesce(sum(value), 0) as sum
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM deleted)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 UPDATE count= s
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0SET value =3D counts.value + total.sum
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM = total WHERE type =3D 'total'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURNING counts.val= ue
=C2=A0
Like it, thanks!
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_17_1772816629.1425590457073-- ------=_Part_16_684137583.1425590457043 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_16_684137583.1425590457043-- ------=_Part_15_543804474.1425590457043--