Обсуждение: 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
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.