Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C5C0F9F96AF for ; Wed, 12 Sep 2007 18:58:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 06229-01-3 for ; Wed, 12 Sep 2007 18:58:32 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id CC0469FA660 for ; Wed, 12 Sep 2007 18:58:35 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so341571ugf for ; Wed, 12 Sep 2007 14:58:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=T0kuope1+BDH1RS8w7Jhd1DwSIYFwm4DVEqxVMOHvdg=; b=MSUc/8E3S2u6A8mZvq7Gs8XqnxMizVSQt2Ss2x4YsAVSmKSRt4+jo3fewzElwWMyzvcoa4fqmw900eVIOMJjwXnkZMN36E60MkOUy2Vz0MFmCH57i0Z2SdsGRDzMwdUHdjEL6J3g8aisapWPPMdfMaKLfebKAqct85u3NUyODqY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=eiXD0BRCtG4C/LKHXcLuf63O4eG3Q51OPYqf8AIhU0hBBUbw/JLnM1AmXa8kH5uXL+ndgKTQMaONktU/kmNbVr9lHaBtVfFgDx68BpDfUcmANcDEUBh9OZEWwKcBFE6lq9RJeqCQ5IzNb7cgnWJnjiv8liDH5+dNh+QWdGh851E= Received: by 10.66.222.9 with SMTP id u9mr2294206ugg.1189634314232; Wed, 12 Sep 2007 14:58:34 -0700 (PDT) Received: by 10.66.220.7 with HTTP; Wed, 12 Sep 2007 14:58:34 -0700 (PDT) Message-ID: Date: Wed, 12 Sep 2007 16:58:34 -0500 From: "Scott Marlowe" To: chambers@imageworks.com Subject: Re: db performance/design question Cc: pgsql-performance@postgresql.org In-Reply-To: <46E84D15.8020600@imageworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <46E84D15.8020600@imageworks.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/236 X-Sequence-Number: 26781 On 9/12/07, Matt Chambers wrote: > > > I'm designing a system that will be doing over a million inserts/deletes on > a single table every hour. Rather than using a single table, it is possible > for me to partition the data into multiple tables if I wanted to, which > would be nice because I can just truncate them when I don't need them. I > could even use table spaces to split the IO load over multiple filers. The > application does not require all this data be in the same table. The data > is fairly temporary, it might last 5 seconds, it might last 2 days, but it > will all be deleted eventually and different data will be created. > > Considering a single table would grow to 10mil+ rows at max, and this > machine will sustain about 25mbps of insert/update/delete traffic 24/7 - > 365, will I be saving much by partitioning data like that? This is the exact kind of application for which partitioning shines, especially if you can do the inserts directly to the partitions without having to create rules or triggers to handle it. If you have to point at the master table, stick to triggers as they're much more efficient at slinging data to various sub tables.