Search Postgresql Archives

Ways to aggregate table info from variable number of schemas

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Our PostgreSQL-based system uses a small number of fixed schemas,
and an arbitrary number of per-project schemas (one schema per project).
This will is given, sorry, cannot be changed. Really.

The fixed schemas contain metadata about the projects (including which schema a given project uses).

My problem is that some of the important metadata is not in the fixed (administrative) schemas,
but in the per-project schemas. Which means you can't do a declarative SQL query for those
metadata across projects, since you can't do static / non-dynamic SQL across schemas.

I've brainstormed the different ways I could easily query those "spread-out" metadata,
and I'm trying my chance at community input. Here's what I've considered so far:

1) Mega union-all view, updated (programmatically) when schemas are added/removed
2) Concrete tables in one of the fixed schemas, the project-schemas push data to, via triggers
3) Partitioned table. No experience with it, but from what I've read, a manually created partitioned table with manually added/removed partition seems viable. The caveat being that I don't have a (virtual) column right now in the per-project schema tables that would identify the rows across schemas/projects. But that could be added, if this is viable.
4) SQL functions taking a project-id, and doing dynamic SQL inside against a given schema, to fetch the metadata of interest for that SQL function. Most of a PULL approach, which allows a kind of hidden dynamic JOIN when scanning the projects from the fixed schema table(s). This might be the simplest?

Maybe there are other options too?

Basically I need to do a little data-warehouse of sort, no?

Insights, inputs, would be greatly appreciated. Thanks, --DD

PS: A recent comment by Tom seemed to indicate that union-all views are not well optimized, and don't trim branches of the union-all based on the where clause. So would not scale well (one internal install has 3000 projects)

PPS: I like the idea of the partitioned table, since could do efficient partition elimination, but this is completely new territory for me, and I fear the complexity might be high. Thoughts?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux