Re: Declarative partitioning - another take

On 2016/12/09 0:25, Robert Haas wrote:
> On Wed, Dec 7, 2016 at 11:42 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>>> Congrats to everyone working on this! This is a large step forward.
>>
>> Congratulations to all! It was a long way to this result.
>
> Yes. The last effort in this area which I can remember was by Itagaki
> Takahiro in 2010, so we've been waiting for this for more than 6
> years. It's really good that Amit was able to put in the effort to
> produce a committable patch, and I think he deserves all of our thanks
> for getting that done - and NTT deserves our thanks for paying him to
> do it.
>
> Even though I know he put in a lot more work than I did, let me just
> say: phew, even reviewing that was a ton of work.

Absolutely! Your review comments and design suggestions have been
instrumental in improving (and cutting down on the size of) the patches.

> Of course, this is the beginning, not the end.

+1000!

> I've been thinking
> about next steps -- here's an expanded list:
>
> - more efficient plan-time partition pruning (constraint exclusion is too slow)
> - run-time partition pruning
> - partition-wise join (Ashutosh Bapat is already working on this)
> - try to reduce lock levels
> - hash partitioning
> - the ability to create an index on the parent and have all of the
> children inherit it; this should work something like constraint
> inheritance. you could argue that this doesn't add any real new
> capability but it's a huge usability feature.
> - teaching autovacuum enough about inheritance hierarchies for it to
> update the parent statistics when they get stale despite the lack of
> any actual inserts/updates/deletes to the parent. this has been
> pending for a long time, but it's only going to get more important
> - row movement (aka avoiding the need for an ON UPDATE trigger on each
> partition)
> - insert (and eventually update) tuple routing for foreign partitions
> - not scanning the parent
> - fixing the insert routing so that we can skip tuple conversion where possible
> - fleshing out the documentation

I would definitely want to contribute to some of these items. It's great
that many others plan to contribute toward this as well.

> One thing I'm wondering is whether we can optimize away some of the
> heavyweight locks. For example, if somebody does SELECT * FROM ptab
> WHERE id = 1, they really shouldn't need to lock the entire
> partitioning hierarchy, but right now they do. If the root knows
> based on its own partitioning key that only one child is relevant, it
> would be good to lock *only that child*. For this feature to be
> competitive, it needs to scale to at least a few thousand partitions,
> and locking thousands of objects instead of one or two is bound to be
> slow. Similarly, you can imagine teaching COPY to lock partitions
> only on demand; if no tuples are routed to a particular partition, we
> don't need to lock it. There's a manageability component here, too:
> not locking partitions unnecessarily makes ti easier to get DDL on
> other partitions through. Alternatively, maybe we could rewrite the
> lock manager to be hierarchical, so that you can take a single lock
> that represents an AccessShareLock on all partitions and only need to
> make one entry in the lock table to do it. That means that attempts
> to lock individual partitions need to check not only for a lock on
> that partition but also on anything further up in the hierarchy, but
> that might be a good trade if it gives us O(1) locking on the parent.
> And maybe we could also have a level of the hierarchy that represents
> every-table-in-the-database, for the benefit of pg_dump. Of course,
> rewriting the lock manager is a big project not for the faint of
> heart, but I think if we don't it's going to be a scaling bottleneck.

Hierarchical lock manager stuff is interesting. Are you perhaps alluding
to a new *intention* lock mode as described in the literature on multiple
granularity locking [1]?

> We also need to consider other parts of the system that may not scale,
> like pg_dump. For a long time, we've been sorta-kinda willing to fix
> the worst of the scalability problems with pg_dump, but that's really
> no longer an adequate response. People want 1000 partitions. Heck,
> people want 1,000,000 partitions, but getting to where 1000 partitions
> works well would help PostgreSQL a lot. Our oft-repeated line that
> inheritance isn't designed for large numbers of inheritance children
> is basically just telling people who have the use case where they need
> that to go use some other product. Partitioning, like replication, is
> not an optional feature for a world-class database. And, from a
> technical point of view, I think we've now got an infrastructure that
> really should be able to be scaled up considerably higher than what
> we've been able to do in the past. When we were stuck with
> inheritance + constraint exclusion, we could say "well, there's not
> really any point because you'll hit these other limits anyway". But I
> think now that's not really true. This patch eliminates one of the
> core scalability problems in this area, and provides infrastructure
> for attacking some of the others. I hope that people will step up and
> do that. There's a huge opportunity here for PostgreSQL to become
> relevant in use cases where it currently falters badly, and we should
> try to take advantage of it. This patch is a big step by itself, but
> if we ignore the potential to do more with this as the base we will be
> leaving a lot of "win" on the table.

Agreed on all counts.

Thanks,
Amit

[1] https://en.wikipedia.org/wiki/Multiple_granularity_locking