X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 68FD5D1F057 for ; Tue, 4 May 2004 15:15:00 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 90497-02 for ; Tue, 4 May 2004 15:14:39 -0300 (ADT) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 693A5D1F0E2 for ; Tue, 4 May 2004 15:14:36 -0300 (ADT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id D0E5917C028; Tue, 4 May 2004 14:14:15 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1BL4Qp-0007AT-00; Tue, 04 May 2004 14:14:15 -0400 To: Joseph Shraibman Cc: "scott.marlowe" , pgsql-performance@postgresql.org Subject: Re: cache table References: <4097B679.60301@selectacast.net> In-Reply-To: <4097B679.60301@selectacast.net> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 04 May 2004 14:14:15 -0400 Message-ID: <87wu3sxdoo.fsf@stark.xeocode.com> Lines: 39 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/26 X-Sequence-Number: 6857 Joseph Shraibman writes: > scott.marlowe wrote: > > > I think you might be interested in materialized views. You could create this > > as a materialized view which should be very fast to just select * from. > > That seems to be the count table I envisioned. It just hides the details for > me. It still has the problems of an extra UPDATE every time the data table is > updated and generating a lot of dead tuples. The dead tuples is only going to be a problem if you have lots of updates. If that's the case then you're also going to have problems with contention. This trigger will essentially serialize all inserts, deletes, updates at least within a group. If you use transactions with multiple such updates then you will also risk creating deadlocks. But I think these problems are fundamental to the problem you've described. Keeping denormalized aggregate data like this inherently creates contention on the data and generates lots of old data. It's only really useful when you have few updates and many many reads. If you know more about the behaviour of the updates then there might be other options. Like, do you need precise data or only approximate data? If approximate perhaps you could just do a periodic refresh of the denormalized view and use that. Are all the updates to the data you'll be querying coming from within the same application context? In which case you can keep a cache locally in the application and update it locally. I often do this when I have rarely updated or insert-only data, I just do a lazy cache using a perl hash or equivalent. If you're really happy with losing the cache, and you don't need complex transactions or care about serializing updates then you could use something like memcached (http://www.danga.com/memcached/). That might be your best fit for how you describe your requirements. -- greg