Re: On partitioning

On Tue, Sep 02, 2014 at 09:44:17AM -0400, Bruce Momjian wrote:
> 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".
>
> I am unclear why having information per-partition rather than on the
> parent table helps with online reparitioning.

An example:

We have three partitions, one for X<0 (A), one for 0<=X<5 (B) and one
for X>=5 (C). These are in three different tables.

Now we give the command to merge the last two partitions B&C. You now
have the choice to lock the table while you move all the tuples from C
to B.

Or you can make some adjustments such that new tuples that would have gone
to C now go to B. And if there is a query for X=10 that you look in
*both* B & C. Then the existing tuples can be moved from C to B at any
time without blocking any other operations.

Is this clearer? If you up front decide that which partition to query
will be determined by a function that can only return one table, then
the above becomes impossible.

> 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.

Doing it as some tool seems like a hack to me. And since the idea was (I
thought) that partitions would not be directly accessable from SQL, it
has to be in the database itself.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer