CODEWITHSUNDEEP
postgresqlschemagrouping
Aleks G
Aleks G

Reputation: 57326

Grouping objecs in PostgreSQL database

I inherited a project with a large Postgres database (over 150 tables, over 200 custom types, almost 1000 functions, triggers, etc.) Unfortunately, everything is dumped into one schema (public). It works just fine from the point of view of the application, however it's a major nightmare to maintain.

The obvious thing would be to split these objects into separate schemas by function (e.g. all supplier-related stuff goes into supplier schema, all admin related stuff into admin schema, etc.). Once this is done, of course, some changes (ok, a lot of changes) to the code would be required to refer to the new schemas. Considering that the web application contains about 2000 php files, it may be quite a task. Again, as every php in the system already starts with require_once('controller/config.php'); I could add a call there to set search path to include all the new schemas: SET search_path = 'public, supplier, admin, ...', yet somehow subconsciously I don't like this solution.

Is there any other way to deal with issue? I don't want to spend more effort than absolutely necessary on reorganising the database. I also can barely incur any downtime on the main web site, as it's used by clients across the world (Australia, Europe, North America).

What would your recommend I do?

Upvotes: 2

Views: 298

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

I can understand not liking the search path solution. However did you know that search_path can be set per user or per database? You can ALTER DATABASE SET search_path and then you are done, and better yet it isn't a global change that affects others.

http://www.postgresql.org/docs/9.1/static/config-setting.html

Keep in mind that per user settings are cluster-global and override per-database settings.

Upvotes: 0

Kuberchaun
Kuberchaun

Reputation: 30342

I think the search_path method would be the way you would have to do it if you go that route. Why do you subconsciously not like solution? Seems it would remove a maintenance nightmare if you can over come it. Which is worse maintenance nightmare or search_path usage?

Per the docs:

To organize database objects into logical groups to make them more manageable.

Upvotes: 1

Related Questions