Re: Large number of partitions of a table

On Mon, Jan 17, 2022 at 7:58 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ron <ronljohnsonjr(at)gmail(dot)com> writes:
> > On 1/16/22 11:38 PM, Victor Sudakov wrote:
> >> so no query should ever have to use more than 1 partition.
>
> > I find it hard to believe that you'll *never* run a report against more
> > customers than are in a single partition.
>
> Yeah, I'm a little suspicious of that.

Is "never" really the correct threshold here? Sure there may be a few
queries that run over multiple customers but it's also likely that so long
as they don't actually crash the system the performance will be
acceptable. Plus not every table would have to be partitioned on customer;
I can imagine that for queries where I do care about multiple customers
that I'd setup triggers on individual custom tables to update the
consolidated table - then run the multi-customer query against that.

I say this having written a system, that has been running for over a
decade, whose usage patterns for multi-tenancy are basically that (it
doesn't use partitions though it's probably worse for not having done so).
In my case I would need multiple thousands of partitions, but indeed every
main operational query would have the tenantID as a predicate. That seems
like a fundamental property of a multi-tenant system...

David J.