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?
