Re: On partitioning

On Sun, Aug 31, 2014 at 10:45:29PM +0200, Martijn van Oosterhout wrote:
> There is one situation where you need to be more flexible, and that is
> if you ever want to support online repartitioning. To do that you have
> to distinguish between "I want to insert tuple X, which partition
> should it go into" and "I want to know which partitions I need to look
> for partition_key=Y".
>
> For the latter you really have need an expression per partition, or
> something equivalent. If performance is an issue I suppose you could
> live with having an "old" and an "new" partition scheme, so you
> couldn't have two "live repartitionings" happening simultaneously.
>
> Now, if you want to close the door on online repartitioning forever
> then that fine. But being in the position of having to say "yes our
> partitioning scheme sucks, but we would have to take the database down
> for a week to fix it" is no fun.
>
> Unless logical replication provides a way out maybe??

I am unclear why having information per-partition rather than on the
parent table helps with online reparitioning.

Robert's idea of using normal table inheritance means we can access/move
the data independently of the partitioning system. My guess is that we
will need to do repartitioning with some tool, rather than as part of
normal database operation.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +