On Fri, Jan 8, 2010 at 15:36, tom <toab...@googlemail.com> wrote: > Hi, > > i use postgres and want to use partitioning for tables (see > http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html ) > > My Model looks like this: > > class Measurand(models.Model): > project = models.ForeignKey(Project) > avg_value = models.DecimalField(max_digits=10, decimal_places=4, > db_index=True) > > > I never want to query over all measurands. I just want to query over > all measurands from _one_ project like this: project.measurand_set.all > () > > So the best solution i think is to have one table for every project. > Is this possible with django and the new multidb-api? >
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.
