2

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?

Improve this question
4
  • Honestly? I suggest you leave it alone. Yes it isn't organized how you like, but it works and that alone counts. Imagine if you took all the time that you might spend on this task, and added a new feature or fixed an actual bug in your system rather than reorganizing things to your liking. Time is important, this change will only make you happier, your customers will be oblivious, if you want to make $, make your customers happy. Commented Aug 29, 2012 at 12:46
  • 1
    search_path is your friend. There are a number of ways how to make use of it. Commented Aug 29, 2012 at 15:38
  • Erwin is very correct. Making use of search_path is a good thing not something to avoid. Commented Aug 29, 2012 at 15:42
  • You don't need to run set search_path on each page. Just do an alter user to change the search_path permanently for that user. Commented Aug 29, 2012 at 16:41

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

If I knew why I didn't like it, it wouldn't be subconsciously any more, would it?
Ok.. Subconsciously isn't part of any scientific discovery process that I'm aware of. Ignore it and move on with the facts at hand.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.