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?

Aleks G
  • 56,435
  • 29
  • 168
  • 265
  • 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. – anio Aug 29 '12 at 12:46
  • 1
    `search_path` is your friend. There are [a number of ways how to make use of it](http://stackoverflow.com/a/9067777/939860). – Erwin Brandstetter Aug 29 '12 at 15:38
  • Erwin is very correct. Making use of search_path is a good thing not something to avoid. – Kuberchaun Aug 29 '12 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. –  Aug 29 '12 at 16:41

2 Answers2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Kuberchaun
  • 29,160
  • 7
  • 51
  • 59
  • If I knew why I didn't like it, it wouldn't be subconsciously any more, would it? – Aleks G Aug 29 '12 at 16:44
  • 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. – Kuberchaun Aug 30 '12 at 00:57
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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182