Обсуждение: Is there a limit to the number of partitions?

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

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.