Query across all schemas on identical table on Postgres

user_8291840 0
Viewed 203

I'm using postgres and I have multiple schemas with identical tables where they are dynamically added the application code.

foo, bar, baz, abc, xyz, ...,

I want to be able to query all the schemas as if they are a single table

!!! I don't want to query all the schemas one by one and combine the results I want to "combine"(not sure if this would be considered a huge join) the tables across schemas and then run the query.

For example, an order by query shouldn't be like

 1. schema_A.result_1
 2. schema_A.result_3
 3. schema_B.result_2
 4. schema_B.result 4

but instead it should be

 1. schema_A.result_1
 2. schema_B.result_2
 3. schema_A.result_3
 4. schema_B.result 4

If possible I don't want to generate a query that goes like

SELECT schema_A.table_X.field_1, schema_B.table_X.field_1 FROM schema_A.table_X, schema_B.table_X

But I want that to be taken care of in postgresql, in the database.

Generating a query with all the schemas(namespaces) appended can make my queries HUGE with ~50 field and ~50 schemas.

Since these tables are generated I also cannot inherit them from some global table and query that instead.

I'd also like to know if this is not really possible in a reasonable speed.

EXTRA:

I'm using django and django-tenants so I'd also accept any answer that actually helps me generate the entire query and run it to get a global queryset EVEN THOUGH it would be really slow.

postgresql django sql python
1 Answers

Your question isn't as much of a question as it is an admission that you've got a really terrible database and applicaiton design. It's as if you parittioned something that iddn't need to be parittioned, or partitioned it in the wrong way.

Since you're doing something awkward, the database itself won't provide you with any elegant solution. Instead, you'll have to get more and more awkward until the regret becomes too much to bear and you redesign your database and/or your application.

I urge you to repent now, the sooner the better.

After that giant caveat based on a haughty moral position, I acknolwedge that the only reason we answer questions here is to get imaginary internet points. And so, my answer is this: use a view that unions all of the values together and presents them as if they came from one table. I can't make any sense of the "order by query", so I just ignore it for now. Maybe you mean that you want the results in a certain order; if so, you can add constants to each SELECT operand of each UNION ALL and ORDER BY that constant column coming out of the union. But if the order of the rows matters, I'd assert that you are showing yet another symptom of a poor database design.

You can programatically update the view whenever it is you update or create the new schemas and their catalogs.

A working example is here: http://sqlfiddle.com/#!17/c09265/1

with this schema creation and population code:

CREATE Schema Fooey;
CREATE SCHEMA Junk;

CREATE TABLE Fooey.Baz (SomeINteger INT);
CREATE TABLE Junk.Baz (SomeINteger INT);

INSERT INTO Fooey.Baz (SomeInteger) VALUES (17), (34), (51);
INSERT INTO Junk.Baz (SomeInteger) VALUES (13), (26), (39);

CREATE VIEW AllOfThem AS
SELECT 'FromFooey' AS SourceSchema, SomeINteger FROM Fooey.Baz
UNION ALL 
SELECT 'FromJunk' AS SourceSchema, SomeInteger FROM Junk.Baz;

and this query:

  SELECT *
    FROM AllOfThem
ORDER BY SourceSchema;

Why are per-tenant schemas a bad design?

This design favors laziness over scalability. If you don't want to make changes to your application, you can simply slam connections to a particular shcema and keep working without any code changes. Adding more tennants means adding more schemas, which it sounds like you've automated. Adding many schemas will eventually make database management cumbersome (what if you have thousands or millions of tenants?) and even if you have only a few, the dynamic nature of the list and the problems in writing system-wide queries is an issue that you've already discovered.

Consider instead combining everything and adding the tenant ID as part of a key on each table. In that case, adding more tenants means adding more rows. Any summary queries trivially come from single tables, and all of the features and power of the database implementation and its query language are at your fingertips without any fuss whatsoever.

It's simply false that a database design can't be changed, even in an existing and busy system. It takes a lot of effort to do it, but it can be done and people do it all the time. That's why getting the database design right as early as possible is important.

The README of the django-tenants package you're using describes thier decision to trade-off towards laziness, and cites a whitpaper that outlines many of the shortcomings and alternatives of that method.

user_18582410
Related