Simon Riggs <simon@2ndquadrant.com> writes:
> The imbalance across partitions would be a major issue because of the
> difficulty of selecting a well-distributed partitioning key. If you use
> the LOCKTAG, then operations on the heaviest hit tables would go to the
> same partitions continually for LockRelation requests. The frequency of
> access per table usually drops off dramatically in rank order: look at
> TPC-B (pgbench) and TPC-C; my own experience would be that you seldom
> have as many even as 16 heavy hit tables. My guess would be that doing
> all of that would do little more than reduce contention to ~50%, and
> that this would show quickly diminishing returns for N > 4. Also, the
> more sharply defined your application profile, the worse this effect
> will be.
That's a fair point, and reinforces my instinct that having a large
number of partitions would be a losing game. But you are mistaken to
think that the number of hot-spot tables is the only limit on the number
of usable partitions. It's the number of their indexes that matters most.
(The pgbench test is if anything probably understating the problem,
because it has only a single index on each table.) In any case, even a
factor of 2 or so reduction in direct conflicts should have a useful
impact on the number of semop waits, because it's a nonlinear effect...
regards, tom lane