Do Functions perform better than dynamic (inline) SQL in PostgreSQL?
I'm pretty new to PostgreSQL and have a basic design question I'd like some advice on.
Our company has 1000+ identical schemas defined in our PostgreSQL DB. We have separate Schemas (work places) defined for each of our clients. The overall DB Schema design is something which cannot be altered.
We have to create routines which will gather data from all the schemas and store it in DataTables in a separate (centralized) schema.
One approach would be to write a .Net based app using he npgsql interface. The application would iterate over all the schemas and run dynamic (inline) SQL to return the desired data.
Another approach would be to push all the data gathering routines into PostgreSQL functions. The functions would contain dynamic SQL, accept a schema as parameter, and then run that SQL against the target schema.
Is there any difference in the performance of the two approaches? I'm trying to figure out which approach will be best in terms of performance and maintainability.
Is there a better approach to take?
Thanks, JohnB
Our company has 1000+ identical schemas defined in our PostgreSQL DB. We have separate Schemas (work places) defined for each of our clients. The overall DB Schema design is something which cannot be altered.
We have to create routines which will gather data from all the schemas and store it in DataTables in a separate (centralized) schema.
One approach would be to write a .Net based app using he npgsql interface. The application would iterate over all the schemas and run dynamic (inline) SQL to return the desired data.
Another approach would be to push all the data gathering routines into PostgreSQL functions. The functions would contain dynamic SQL, accept a schema as parameter, and then run that SQL against the target schema.
Is there any difference in the performance of the two approaches? I'm trying to figure out which approach will be best in terms of performance and maintainability.
Is there a better approach to take?
Thanks, JohnB
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not sure of what the reasoning was behind creating separate schemas (partitioning/isolation of data)?Yep, that's what comments and documentation are for. There may have been a perfectly good reason but you're left to guess what that might be. Maybe it was a valid reason at the time but the reason is no longer a consideration. Or just maybe that was how the designer decided to do it because that's his style.
ASKER
That's what I kind of figured.
I'm not sure of what the reasoning was behind creating separate schemas (partitioning/isolation of data)?
It certainly makes it more challenging.
JB