Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3771C2E34D2 for ; Wed, 2 Jan 2008 12:49:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 58966-06 for ; Wed, 2 Jan 2008 12:49:03 -0400 (AST) Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.224]) by postgresql.org (Postfix) with ESMTP id B99F22E3497 for ; Wed, 2 Jan 2008 12:49:02 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id l8so848024nzf.28 for ; Wed, 02 Jan 2008 08:49:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; 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=g3JdIqk3m6Cyqr/3EG1xs0CjupN2ZGgI61y7d/iurGc=; b=dIkB+/wG5wXeHGHb2HZdUkzoyo/uqTEr7umxgu/mTDagAPEG/eVUnjlVsinncFMqDPZbIy9lUBfSLmyFpu1KBi9XXRwb/WaZzrTtAfHYJo8BYoZKOEYzZvq2LTUzbkoOsUNos9H1hlF6WAzU/do++Y7ssm8zRdzDpm84fVCFomE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=x983BGa7Xffmzv0Z/bdfB+4tojWDNk9S3nLSuQdIT/0fZE/q9h/Zivilgy8q6zknoB4N1v4pdbQFLERmJ0f0sLLTMPQLQenop2FPP4qNVL9ECl4JzWKX2CFNjS/Cem8hhOEPC2/BoFe0P0DGdlmqehU37zs9II8iIDacVNfVulo= Received: by 10.114.167.2 with SMTP id p2mr15230401wae.78.1199292541098; Wed, 02 Jan 2008 08:49:01 -0800 (PST) Received: by 10.114.131.4 with HTTP; Wed, 2 Jan 2008 08:49:01 -0800 (PST) Message-ID: Date: Wed, 2 Jan 2008 10:49:01 -0600 From: "Scott Marlowe" To: "Chris Hoover" Subject: Re: Is there a limit to the number of partitions? Cc: "pgsql-admin@postgresql.org Admin" In-Reply-To: <1d219a6f0801020840k27e076d9s913e5855a591ddd2@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1d219a6f0801020840k27e076d9s913e5855a591ddd2@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/21 X-Sequence-Number: 27795 On Jan 2, 2008 10:40 AM, Chris Hoover wrote: > Is there a limit to the number of partitions a table can have in PostgreSQL? > > The reason I as is we are looking again at partitioning our databases with > the possibility of doing the partitioning by year, month, or even day. > However, we are required by HIPPA to keep 7 years of data, and we are > planning on maintaining the data online in our databases. While I can't > imagine year or month being a problem, 7 years of daily partitions would be > 2555+ partitions per table. Can PostgreSQL handle this many partitions per > table? Is that feasible, or would the cost of the rules become to expensive > to be feasible? That's a LOT of partitions, but it's definitely doable. However, under no circumstances should you maintain that many partitions with rules. Triggers are a much better choice (usually anyway) for large numbers of partitions. There are a few things you can do to alleviate the issue. One is to put older data sets into larger partitions. Since they are accessed less often, it's not as big of a deal if it takes an extra second to get to the data in one. That was when a query runs, it doesn't have to check the exclusion constraints of 2555 partition tables, just 40 or 50.