Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 64F939FC5F2 for ; Wed, 31 Oct 2007 15:23:03 -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 73050-03-3 for ; Wed, 31 Oct 2007 15:22:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 97F449FCFAD for ; Wed, 31 Oct 2007 14:03:24 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so170031nfh for ; Wed, 31 Oct 2007 10:03:22 -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:references; bh=mwgy/E1xAv3cQsv76zf56P6B4Aw6VHLLmJvNRsGegGE=; b=M83LhIvA8vXRAfRJNNFOpq2wyael5V55cYA8js3L+C0hVQMCG1UI4DfqDF6BhUIQP454Hc4g63lEA66iD0IBAMBEJGIEl43pR1tr1MRaDjxe181xOpL51aGYCgraUUO75cpoFIMBAEYrU2lkZ73fM15L8fIYbfcMnUR18RO6ppE= 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:references; b=R5QLPMW23G9Q9geO2VjfKW2EvPAgU0FvJKL5MCatx1JD9/wd+t0XlXd6Bluj2OMyDlTjqI8EdZ72ilN5ZzviwFZVYSN035XGTUijymkEXRGXzWNImPOGuRG1skskNJIC+pulemO7ZGwXRK9UfoO4e98j8bJJP/SPJs3rn6cs+JE= Received: by 10.78.131.8 with SMTP id e8mr6331051hud.1193850201931; Wed, 31 Oct 2007 10:03:21 -0700 (PDT) Received: by 10.78.135.11 with HTTP; Wed, 31 Oct 2007 10:03:21 -0700 (PDT) Message-ID: <357fa7590710311003s653a455ew6e14bcf36b3448ed@mail.gmail.com> Date: Wed, 31 Oct 2007 13:03:21 -0400 From: "Steven Flatt" To: "Pablo Alcaraz" Subject: Re: tables with 300+ partitions Cc: pgsql-performance@postgresql.org In-Reply-To: <47289EEE.50008@laotraesquina.com.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9009_24027552.1193850201922" References: <47277341.7070704@laotraesquina.com.ar> <357fa7590710301400x3a05796dle6bae219a1a25fc@mail.gmail.com> <47289EEE.50008@laotraesquina.com.ar> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/377 X-Sequence-Number: 27399 ------=_Part_9009_24027552.1193850201922 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/31/07, Pablo Alcaraz wrote: > > I was a program inserting into the base table. The program ran in 200+ > threads and every thread insert data on it. Every thread inserts a row every > 3 seconds aprox.(or they used to do it), but when I put more partitions the > insert speed went to 1 insert every 2 minutes. > We still need to know how you're redirecting inserts to the appropriate partition. My guess is that you have rules on the base table which say "if an incoming row matches a certain criteria, then insert into partition x instead". There are several discussions on this newsgroup already about why using rules for partitioning hurts insert performance. Next question is *how* are you partitioning the table? If you're partitioning based on some sort of log time field (i.e. today's data goes in this partition, tomorrow's data goes in that partition, etc.), then it is reasonably easy to use a single trigger (as oppose to many rules) on the base table which you can modify on some schedule (using cron, for example). If you're partitioning based on some other id field, then using a trigger won't work as nicely. Do I need to do a trigger for insert only or I need a trigger to update and > delete too? > For delete, probably not. For update, depends on how you're partitioning. Could the update cause the row to belong to a different partition? Do you care about moving it at that point? Steve ------=_Part_9009_24027552.1193850201922 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 10/31/07, Pablo Alcaraz <pabloa@laotraesquina.com.ar> wrote:
I was a program inserting into the base table. The program ran in 200+ threads and every thread insert data on it. Every thread inserts a row every 3 seconds aprox.(or they used to do it), but when I put more partitions the insert speed went to 1 insert every 2 minutes.
 
We still need to know how you're redirecting inserts to the appropriate partition.  My guess is that you have rules on the base table which say "if an incoming row matches a certain criteria, then insert into partition x instead".  There are several discussions on this newsgroup already about why using rules for partitioning hurts insert performance.
 
Next question is *how* are you partitioning the table?  If you're partitioning based on some sort of log time field (i.e. today's data goes in this partition, tomorrow's data goes in that partition, etc.), then it is reasonably easy to use a single trigger (as oppose to many rules) on the base table which you can modify on some schedule (using cron, for example).  If you're partitioning based on some other id field, then using a trigger won't work as nicely.
 

Do I need to do a trigger for insert only or I need a trigger to update and delete too?
 
For delete, probably not.  For update, depends on how you're partitioning.  Could the update cause the row to belong to a different partition?  Do you care about moving it at that point?
 
Steve
 
------=_Part_9009_24027552.1193850201922--