CODEWITHSUNDEEP
djangopostgresql
Richard
Richard

Reputation: 65510

Django: is using a ForeignKey likely to affect read performance on a big db?

I have a largeish Postgres database (about 500m rows, 150GB in total). The data is relatively static (only updated once a quarter) so I want to optimise it for reading, not writing.

Currently I have the following database structure, using a ForeignKey:

 class Practice(models.Model):
    code = models.IntegerField()
    name = models.CharField(max_length=200)

 class Prescription(models.Model):
    practice = models.ForeignKey(Practice)
    presentation_code = models.CharField(max_length=15)

If I want to do queries like this frequently, returning large datasets:

 p = Practice.objects.filter(code=1234)
 prescriptions = Prescription.objects.filter(practice=p)

Would it be more efficient to reorganise the database so it doesn't have a ForeignKey:

 class Prescription(models.Model):
    practice_id = models.IntegerField()
    presentation_code = models.CharField(max_length=15)

and then do this in the view?

 p = Practice.objects.filter(code=1234)
 prescriptions = Prescription.objects.filter(practice_id=1234)

I understand that if I do this, I run the risk of creating orphaned rows in the data import - that's not a problem. I'm more interested in ensuring that the database runs quickly and can cope with multiple users.

Currently I have the first setup, and django-debug-toolbar shows that the view query looks like this: 

 SELECT COUNT(*) FROM "frontend_prescription" WHERE 
   "frontend_prescription"."chemical_id" = '0212000AA';

Looking at that, perhaps we can infer that changing the database structure wouldn't speed up read queries?

I understand that I could test this by reorganising my database and checking the execution time, but with 500m rows that's quite a lot of work :)

Upvotes: 0

Views: 75

Answers (2)

knbk
knbk

Reputation: 53649

No, that won't speed up performance. Internally, a foreign key is little more than an indexed IntegerField with a db constraint. The constraint won't affect single-table read performance, though it might (positively) affect read performance when using joins, i.e. select_related.

Upvotes: 1

Marius Darila
Marius Darila

Reputation: 873

Do you have indexes on your tables? In your case b-tree or hash would help. http://www.postgresql.org/docs/9.1/static/indexes-types.html . Also please dont denormalize db, thats why you use sql database ...for normalization.

Upvotes: 0

Related Questions