On Mon, Jan 17, 2022 at 7:58 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ron <ronljohnsonjr@xxxxxxxxx> 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.
