From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Baldwin <steve.baldwin@gmail.com>
cc: =?UTF-8?Q?Norbert_S=C3=A1ndor?= <sandor.norbert@erinors.com>,
        pgsql-general@lists.postgresql.org
Subject: Re: query multiple schemas
In-reply-to: <CAKE1AiaoBZvtcM-ifUbgXYAJoxD-U0c1rq-s+eW9qQuB6jAYpA@mail.gmail.com>
References: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com> <CAKE1AiaoBZvtcM-ifUbgXYAJoxD-U0c1rq-s+eW9qQuB6jAYpA@mail.gmail.com>
Comments: In-reply-to Steve Baldwin <steve.baldwin@gmail.com>
	message dated "Mon, 22 Apr 2024 06:35:41 +1000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2857957.1713733916.1@sss.pgh.pa.us>
Date: Sun, 21 Apr 2024 17:11:56 -0400
Message-ID: <2857958.1713733916@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2857958.1713733916%40sss.pgh.pa.us>
Precedence: bulk

Steve Baldwin <steve.baldwin@gmail.com> 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