Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 10E6E6332B5 for ; Sat, 2 Jan 2010 08:25:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 35160-03 for ; Sat, 2 Jan 2010 12:25:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.hi-media-techno.com (smtp1.hi-media-techno.com [195.158.241.85]) by mail.postgresql.org (Postfix) with ESMTP id 5C797632E08 for ; Sat, 2 Jan 2010 08:25:17 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by smtp1.hi-media-techno.com (Postfix) with ESMTP id 9BDA393BA4; Sat, 2 Jan 2010 13:25:15 +0100 (CET) Received: from smtp1.hi-media-techno.com ([127.0.0.1]) by localhost (smtp1.backbone.hi-media-techno.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id RgtPllwIaGus; Sat, 2 Jan 2010 13:25:15 +0100 (CET) Received: from mail1.hi-media-techno.com (unknown [192.168.27.17]) by smtp1.hi-media-techno.com (Postfix) with ESMTP id 2365893B85; Sat, 2 Jan 2010 13:25:15 +0100 (CET) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail1.hi-media-techno.com (Postfix) with ESMTP id 1015CC4E08; Sat, 2 Jan 2010 13:25:15 +0100 (CET) X-Virus-Scanned: amavisd-new at mail1.hi-media-techno.com Received: from mail1.hi-media-techno.com ([127.0.0.1]) by localhost (mail1.hi-media-techno.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id eBPstPIsAS89; Sat, 2 Jan 2010 13:25:14 +0100 (CET) Received: from localhost (89-158-241-71.rev.dartybox.com [89.158.241.71]) by mail1.hi-media-techno.com (Postfix) with ESMTPSA id C2251C4D08; Sat, 2 Jan 2010 13:25:14 +0100 (CET) From: Dimitri Fontaine To: Jesper Krogh Cc: pgsql-performance@postgresql.org Subject: Re: Message queue table - strange performance drop with changing limit size. Organization: Hi-Media References: <4B3DE11B.1010708@krogh.cc> User-Mail-Address: dfontaine@hi-media.com Date: Sat, 02 Jan 2010 13:25:14 +0100 In-Reply-To: <4B3DE11B.1010708@krogh.cc> (Jesper Krogh's message of "Fri, 01 Jan 2010 12:48:43 +0100") Message-ID: User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1.50 (darwin) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.844 tagged_above=-10 required=5 tests=AWL=-1.433, BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/4 X-Sequence-Number: 36884 Jesper Krogh writes: > I have a "message queue" table, that contains in the order of 1-10m > "messages". It is implemented using TheSchwartz: > http://search.cpan.org/~bradfitz/TheSchwartz-1.07/lib/TheSchwartz.pm One way to approach queueing efficiently with PostgreSQL is to rely on PGQ. New upcoming 3.0 version (alpha1 has been released) contains the basics for having cooperative consumers, stable version (2.1.10) only allows multiple consumers to all do the same work (think replication). http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial Regards, -- dim