Hi Adrian, thanks for your answer! The problem is, that i don't know how many projects will be created. Every user of the application can create and delete new projects. Maybe it's possible to execute some raw sql commands whenever a project is created or deleted (to create the new table and insert trigger). Do you think that's possible? Do you have any good tutorial about postgres and partitioning? I'm completly new to this topic.
Cheers Tom On 8 Jan., 22:01, Adrian Maier <adrian.ma...@gmail.com> wrote: > Hello Tom, > > My feeling is that you have almost defined the solution on your own > already. I think that you are on the right track. > > First, forget about multiple databases : table partitioning has nothing to > do with it. > > Secondly, the fact that django is not explicitly supporting partitioning is > not an issue: partitioning is transparent for the applications. An > apllication that executes select/insert/update/delete commands doesn't need > to do anything special . > > Thirdly, perhaps you should simply try to apply partitioning for your table, > starting from the examples given in the "Partitioning" chapter that you've > mentioned : > - create the Measurands table > - create several project specific tables : > create table Measurands_project1 ( > check PROJECT_ID=1 > ) inherits (Measurands); > > create table Measurands_project2 ( > check PROJECT_ID=2 > ) inherits (Measurands); > > create table Measurands_project3 ( > check PROJECT_ID=3 > ) inherits (Measurands); > > - create the trigger on Measurands that inserts each new record into the > right child Measurands_projectXX table . > > When selecting records from Measurands , the table inheritance mechanism > will automatically provide acces to the project-tables: > - select * from Measurands .... will retrieve all the records (because > of table inheritance) > - select * from Measurands where PROJECT_ID=2 .... will retrieve the > records for project 2 . > > In the second situation, the 'constraint exclusion' optimization of the > recent PostgreSQL versions will cause that the database engine will read > data only from Measurands_Project2 . > > The Django aplication will automatically take advantage of this optimization > without any modification : the only catch is that you'll have to create the > tables manually (not with manage.py syncdb). Also, the number of projects > will have to be fixed in general (every new project involves creation of a > new table , and modifying the insert trigger). > > After creating the tables you should do some testing to see that using > partitioning is indeed giving better performance. Adding some indexes could > be an alternative solution, and the only way for knowing what's the best > solution is testing . > > Good luck with experimenting .... > > Adrian Maier
-- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
