Tuning postgresql (for fast reads with django)

Question

we have a django & postgresql setup running on ec2. Our application is always writing to the DB in the background - but this is not initiated from user action.

The problem is that when a user does use the system we need to do a great big read, sometimes with full text search, of around 20k items. Any tips on tuning for this scenario??

Post you schema, adding additional indexes / keys is the simplest. By that I mean creating indexes for common filtering fields etc. — j_mcnally
– j_mcnally, Commented Mar 10, 2012 at 0:14
I have successfully improved postgresql performance on EC2 by following some of the tips in this video: blip.tv/djangocon/secrets-of-postgresql-performance-5572403 but as j_mcnally says, we need to know more about the queries you are trying to optimize to give concrete advice. — SingleNegationElimination
– SingleNegationElimination, Commented Mar 10, 2012 at 2:45
blog.it-agenten.com/2015/04/tuning-django-orm-text-queries - I have written down some performance tuning steps especially for Django + Postgres in that blog article. — Risadinha
– Risadinha, Commented May 23, 2017 at 13:48

Christophe · Accepted Answer · 2012-03-17 22:42:47Z

2

20k items is not that big a read. :)

On EC2, the main things to do are:

Get as much memory as you can rationally afford; EBS performance is terrible, and you want as much cache as you can manage.
Make sure your shared_buffers setting is correct; 25% of available RAM is a good starting point.
Look at the big read with EXPLAIN ANALYZE to look for opportunities to create indexes (but don't just create indexes without a practical reason; they're expensive if they are not being used for anything).
If changing EBS configuration is an option, consider moving to an 8-stripe soft-RAID configuration.

answered Mar 17, 2012 at 22:42

Christophe

2,0781 gold badge19 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Tuning postgresql (for fast reads with django)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related