public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Steve Baldwin <[email protected]>
Cc: Norbert Sándor <[email protected]>
Cc: [email protected]
Subject: Re: query multiple schemas
Date: Sun, 21 Apr 2024 17:11:56 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKE1AiaoBZvtcM-ifUbgXYAJoxD-U0c1rq-s+eW9qQuB6jAYpA@mail.gmail.com>
References: <[email protected]>
	<CAKE1AiaoBZvtcM-ifUbgXYAJoxD-U0c1rq-s+eW9qQuB6jAYpA@mail.gmail.com>

Steve Baldwin <[email protected]> writes:
> If the number of tenant schemas is reasonably static, you could write a
> plpgsql function to create a set of UNION ALL views with one view for each
> table in all tenant schemas. You could re-run the function each time a
> tenant schema is added. Having the set of views would allow you to query
> them as you would any of the underlying tables, and the query planner could
> likely optimise the query better. With your current function, if you needed
> to add a WHERE clause and the underlying tables were large, it would likely
> not perform as well as the UNION ALL view.

Another idea is to build a partitioned table comprising all the
individual tables.  This might require the tables to have all the
same owner (not sure about that) and it'd require you to store the
partition key, ie tenant, explicitly in every table which might
seem a bit wasteful.  But it'll likely outperform any other solution
for the union queries.  Notably, adding or deleting a partition would
be much less painful than redefining a UNION ALL view.

			regards, tom lane

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: query multiple schemas
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox