pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is there a limit to the number of partitions?
2+ messages / 2 participants
[nested] [flat]

* Is there a limit to the number of partitions?
@ 2008-01-02 16:40  Chris Hoover <revoohc@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Chris Hoover @ 2008-01-02 16:40 UTC (permalink / raw)
  To: pgsql-admin

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?

Thanks for any comments.

Chris

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Is there a limit to the number of partitions?
@ 2008-01-02 16:49  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Chris Hoover <revoohc@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Scott Marlowe @ 2008-01-02 16:49 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

On Jan 2, 2008 10:40 AM, Chris Hoover <revoohc@gmail.com> 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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2008-01-02 16:49 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-02 16:40 Is there a limit to the number of partitions? Chris Hoover <revoohc@gmail.com>
2008-01-02 16:49 ` Scott Marlowe <scott.marlowe@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox